| 1 |
SET GLOBAL DEBUG="+d, parallel_read_buffering_print"; |
| 2 |
# |
| 3 |
# Sampling on an empty table |
| 4 |
# |
| 5 |
SET GLOBAL innodb_monitor_enable = module_sampling; |
| 6 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE NAME='sampled_pages_read'; |
| 7 |
NAME COUNT |
| 8 |
sampled_pages_read 0 |
| 9 |
CREATE TABLE t1 (a INT); |
| 10 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS; |
| 11 |
Table Op Msg_type Msg_text |
| 12 |
test.t1 histogram status Histogram statistics created for column 'a'. |
| 13 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a'; |
| 14 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 15 |
test t1 a {"buckets": [], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 50} |
| 16 |
DROP TABLE t1; |
| 17 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; |
| 18 |
NAME COUNT |
| 19 |
sampled_pages_read 0 |
| 20 |
sampled_pages_skipped 0 |
| 21 |
SET GLOBAL innodb_monitor_disable = module_sampling; |
| 22 |
SET GLOBAL innodb_monitor_reset_all = "module_sampling"; |
| 23 |
# |
| 24 |
# Case where only root node is present |
| 25 |
# |
| 26 |
SET GLOBAL innodb_monitor_enable = module_sampling; |
| 27 |
SET DEBUG='+d,histogram_force_sampling'; |
| 28 |
CREATE TABLE t1 (a INT); |
| 29 |
INSERT INTO t1 VALUES (10), (20), (30), (40), (50), (60), (70), (80), (90), (100); |
| 30 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS; |
| 31 |
Table Op Msg_type Msg_text |
| 32 |
test.t1 histogram status Histogram statistics created for column 'a'. |
| 33 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a'; |
| 34 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 35 |
test t1 a {"buckets": [[10, 0.1], [20, 0.2], [30, 0.3], [40, 0.4], [50, 0.5], [60, 0.6], [70, 0.7], [80, 0.8], [90, 0.9], [100, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 0.5, "histogram-type": "singleton", "number-of-buckets-specified": 50} |
| 36 |
DROP TABLE t1; |
| 37 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; |
| 38 |
NAME COUNT |
| 39 |
sampled_pages_read 1 |
| 40 |
sampled_pages_skipped 0 |
| 41 |
SET GLOBAL innodb_monitor_disable = "module_sampling"; |
| 42 |
SET GLOBAL innodb_monitor_reset_all = "module_sampling"; |
| 43 |
SET DEBUG='-d,histogram_force_sampling'; |
| 44 |
# |
| 45 |
# Case where only two levels is present with sampling_percentage = 50 |
| 46 |
# |
| 47 |
SET GLOBAL innodb_monitor_enable = module_sampling; |
| 48 |
SET DEBUG='+d,histogram_force_sampling'; |
| 49 |
set global innodb_limit_optimistic_insert_debug = 2; |
| 50 |
CREATE TABLE t1 (a INT); |
| 51 |
INSERT INTO t1 VALUES (10), (20), (30); |
| 52 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS; |
| 53 |
Table Op Msg_type Msg_text |
| 54 |
test.t1 histogram status Histogram statistics created for column 'a'. |
| 55 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a'; |
| 56 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 57 |
test t1 a {"buckets": [], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 0.5, "histogram-type": "singleton", "number-of-buckets-specified": 50} |
| 58 |
DROP TABLE t1; |
| 59 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; |
| 60 |
NAME COUNT |
| 61 |
sampled_pages_read 0 |
| 62 |
sampled_pages_skipped 2 |
| 63 |
SET GLOBAL innodb_monitor_disable = module_sampling; |
| 64 |
SET GLOBAL innodb_monitor_reset_all = "module_sampling"; |
| 65 |
set global innodb_limit_optimistic_insert_debug = 0; |
| 66 |
SET DEBUG='-d,histogram_force_sampling'; |
| 67 |
# |
| 68 |
# Case where only two levels is present with sampling_percentage = 100 |
| 69 |
# |
| 70 |
SET GLOBAL innodb_monitor_enable = module_sampling; |
| 71 |
set global innodb_limit_optimistic_insert_debug = 2; |
| 72 |
CREATE TABLE t1 (a INT); |
| 73 |
INSERT INTO t1 VALUES (10), (20), (30); |
| 74 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS; |
| 75 |
Table Op Msg_type Msg_text |
| 76 |
test.t1 histogram status Histogram statistics created for column 'a'. |
| 77 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a'; |
| 78 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 79 |
test t1 a {"buckets": [[10, 0.3333333333333333], [20, 0.6666666666666666], [30, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 50} |
| 80 |
DROP TABLE t1; |
| 81 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; |
| 82 |
NAME COUNT |
| 83 |
sampled_pages_read 2 |
| 84 |
sampled_pages_skipped 0 |
| 85 |
SET GLOBAL innodb_monitor_disable = module_sampling; |
| 86 |
SET GLOBAL innodb_monitor_reset_all = "module_sampling"; |
| 87 |
set global innodb_limit_optimistic_insert_debug = 0; |
| 88 |
# |
| 89 |
# Normal case with small dataset |
| 90 |
# |
| 91 |
SET GLOBAL innodb_monitor_enable = module_sampling; |
| 92 |
set global innodb_limit_optimistic_insert_debug = 2; |
| 93 |
CREATE TABLE t1 (a INT); |
| 94 |
INSERT INTO t1 VALUES (10), (20), (30), (40), (50); |
| 95 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS; |
| 96 |
Table Op Msg_type Msg_text |
| 97 |
test.t1 histogram status Histogram statistics created for column 'a'. |
| 98 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a'; |
| 99 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 100 |
test t1 a {"buckets": [[10, 0.2], [20, 0.4], [30, 0.6], [40, 0.8], [50, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 50} |
| 101 |
set global innodb_limit_optimistic_insert_debug = 0; |
| 102 |
DROP TABLE t1; |
| 103 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; |
| 104 |
NAME COUNT |
| 105 |
sampled_pages_read 3 |
| 106 |
sampled_pages_skipped 0 |
| 107 |
SET GLOBAL innodb_monitor_disable = module_sampling; |
| 108 |
SET GLOBAL innodb_monitor_reset_all = "module_sampling"; |
| 109 |
# |
| 110 |
# Check that histogram with sampling works as expected with large dataset |
| 111 |
# |
| 112 |
SET GLOBAL innodb_monitor_enable = module_sampling; |
| 113 |
set global innodb_limit_optimistic_insert_debug = 2; |
| 114 |
CREATE TABLE t1 (col1 DOUBLE); |
| 115 |
INSERT INTO t1 SELECT RAND(1); |
| 116 |
INSERT INTO t1 SELECT RAND(2) FROM t1; |
| 117 |
INSERT INTO t1 SELECT RAND(3) FROM t1; |
| 118 |
INSERT INTO t1 SELECT RAND(4) FROM t1; |
| 119 |
INSERT INTO t1 SELECT RAND(5) FROM t1; |
| 120 |
INSERT INTO t1 SELECT RAND(6) FROM t1; |
| 121 |
INSERT INTO t1 SELECT RAND(7) FROM t1; |
| 122 |
INSERT INTO t1 SELECT RAND(8) FROM t1; |
| 123 |
INSERT INTO t1 SELECT RAND(9) FROM t1; |
| 124 |
INSERT INTO t1 SELECT RAND(10) FROM t1; |
| 125 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1 WITH 5 BUCKETS; |
| 126 |
Table Op Msg_type Msg_text |
| 127 |
test.t1 histogram status Histogram statistics created for column 'col1'. |
| 128 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'col1'; |
| 129 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 130 |
test t1 col1 {"buckets": [[0.00004702340815870409, 0.19918984193279393, 0.201171875, 103], [0.1999328222125143, 0.40959737115502115, 0.40234375, 103], [0.40979592260885606, 0.6418956067803275, 0.603515625, 103], [0.6427194789449866, 0.8223161593287402, 0.8046875, 103], [0.823130699641193, 0.9968274254322308, 1.0, 100]], "data-type": "double", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "equi-height", "number-of-buckets-specified": 5} |
| 131 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; |
| 132 |
NAME COUNT |
| 133 |
sampled_pages_read 257 |
| 134 |
sampled_pages_skipped 0 |
| 135 |
SET GLOBAL innodb_monitor_disable = module_sampling; |
| 136 |
SET GLOBAL innodb_monitor_reset_all = "module_sampling"; |
| 137 |
# |
| 138 |
# Check that histogram with sampling works as expected with large dataset and different sampling percentage |
| 139 |
# |
| 140 |
SET GLOBAL innodb_monitor_enable = module_sampling; |
| 141 |
SET DEBUG='+d,histogram_force_sampling'; |
| 142 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON col1 WITH 50 BUCKETS; |
| 143 |
Table Op Msg_type Msg_text |
| 144 |
test.t1 histogram status Histogram statistics created for column 'col1'. |
| 145 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'col1'; |
| 146 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 147 |
test t1 col1 {"buckets": [[0.011373644705278468, 0.03017090822585943, 0.021352313167259787, 8], [0.03405159156215525, 0.049177242488765385, 0.042704626334519574, 8], [0.05157797043358718, 0.07857959631698168, 0.06405693950177936, 8], [0.07900210849847841, 0.10384682668731253, 0.08540925266903915, 8], [0.10532979118202794, 0.11960073851011763, 0.10676156583629894, 8], [0.12111200589790196, 0.14349767951620526, 0.12811387900355872, 8], [0.14375604981906345, 0.15302471644526786, 0.1494661921708185, 8], [0.15431178561813363, 0.1663194039522851, 0.1708185053380783, 8], [0.1667632499400184, 0.17662728687434204, 0.19217081850533807, 8], [0.17775701189204773, 0.20359221771693994, 0.21352313167259787, 8], [0.20669731889543805, 0.2262614101416072, 0.23487544483985764, 8], [0.24205637652618472, 0.254564837789689, 0.25622775800711745, 8], [0.2554747008303876, 0.2674569676327118, 0.2775800711743772, 8], [0.276700429876056, 0.29336898335569445, 0.298932384341637, 8], [0.29497343608641385, 0.3172216995779627, 0.3202846975088968, 8], [0.3191653679303502, 0.34015713198125097, 0.3416370106761566, 8], [0.3420773095843171, 0.37191512377179703, 0.36298932384341637, 8], [0.37307905813034536, 0.3845601374139638, 0.38434163701067614, 8], [0.38665528352060846, 0.4141541108620857, 0.40569395017793597, 8], [0.4152270494170739, 0.4405881151934975, 0.42704626334519574, 8], [0.44108103443037816, 0.4866683506282683, 0.4483985765124555, 8], [0.4876218330931122, 0.5016206162996782, 0.4697508896797153, 8], [0.5050184973562308, 0.5347126485171846, 0.49110320284697506, 8], [0.5376834865079108, 0.5625990122208363, 0.5124555160142349, 8], [0.5629817047742957, 0.5791910519629634, 0.5338078291814946, 8], [0.5826131967665751, 0.5915928376760267, 0.5551601423487544, 8], [0.5919730324223387, 0.6096605915666191, 0.5765124555160143, 8], [0.6130504166828938, 0.6413875069854664, 0.597864768683274, 8], [0.6418956067803275, 0.6636503838595472, 0.6192170818505338, 8], [0.6659471072870745, 0.6720468659624894, 0.6405693950177936, 8], [0.6730587460781063, 0.6941590809208891, 0.6619217081850534, 8], [0.6942706012113677, 0.6983557359300142, 0.6832740213523132, 8], [0.6999798563308827, 0.7164718487453384, 0.7046263345195729, 8], [0.7168923567206528, 0.7439201099275798, 0.7259786476868327, 8], [0.7469746263203906, 0.7543273062951186, 0.7473309608540926, 8], [0.7599285894631674, 0.7916310800161502, 0.7686832740213523, 8], [0.7922831790449872, 0.8120951073356859, 0.7900355871886121, 8], [0.8137168584519223, 0.8324380236048605, 0.8113879003558719, 8], [0.8329705389523604, 0.8394600682328083, 0.8327402135231317, 8], [0.8410516156265992, 0.8643595006916295, 0.8540925266903915, 8], [0.8691186363521206, 0.878240405468494, 0.8754448398576512, 8], [0.8808078327037467, 0.8928038663163818, 0.896797153024911, 8], [0.8986274859855208, 0.9246205304978606, 0.9181494661921709, 8], [0.9308878350359274, 0.9578673000986383, 0.9395017793594306, 8], [0.9607241777337382, 0.9739582445229946, 0.9608540925266904, 8], [0.9793567359255224, 0.9869397673634251, 0.9822064056939501, 8], [0.9876527199406668, 0.9881308385991778, 0.9893238434163701, 3], [0.9897249396794708, 0.9897249396794708, 0.9928825622775801, 1], [0.9926347527584385, 0.9926347527584385, 0.99644128113879, 1], [0.9955119751351997, 0.9955119751351997, 1.0, 1]], "data-type": "double", "null-values": 0.0, "collation-id": 8, "sampling-rate": 0.5, "histogram-type": "equi-height", "number-of-buckets-specified": 50} |
| 148 |
DROP TABLE t1; |
| 149 |
set global innodb_limit_optimistic_insert_debug = 0; |
| 150 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; |
| 151 |
NAME COUNT |
| 152 |
sampled_pages_read 141 |
| 153 |
sampled_pages_skipped 116 |
| 154 |
SET GLOBAL innodb_monitor_disable = module_sampling; |
| 155 |
SET GLOBAL innodb_monitor_reset_all = "module_sampling"; |
| 156 |
SET DEBUG='-d,histogram_force_sampling'; |
| 157 |
# |
| 158 |
# Normal case with small dataset with different sampling_percentage |
| 159 |
# |
| 160 |
SET GLOBAL innodb_monitor_enable = module_sampling; |
| 161 |
SET DEBUG='+d,histogram_force_sampling'; |
| 162 |
set global innodb_limit_optimistic_insert_debug = 2; |
| 163 |
CREATE TABLE t1 (a INT); |
| 164 |
INSERT INTO t1 VALUES (10), (20), (30), (40), (50), (60), (70), (80), (90), (100); |
| 165 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS; |
| 166 |
Table Op Msg_type Msg_text |
| 167 |
test.t1 histogram status Histogram statistics created for column 'a'. |
| 168 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a'; |
| 169 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 170 |
test t1 a {"buckets": [[40, 0.2], [50, 0.4], [80, 0.6], [90, 0.8], [100, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 0.5, "histogram-type": "singleton", "number-of-buckets-specified": 50} |
| 171 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; |
| 172 |
NAME COUNT |
| 173 |
sampled_pages_read 3 |
| 174 |
sampled_pages_skipped 3 |
| 175 |
SET GLOBAL innodb_monitor_disable = module_sampling; |
| 176 |
SET GLOBAL innodb_monitor_reset_all = "module_sampling"; |
| 177 |
# |
| 178 |
# Case to check if same rows are returned for the same seed |
| 179 |
# |
| 180 |
SET GLOBAL innodb_monitor_enable = module_sampling; |
| 181 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS; |
| 182 |
Table Op Msg_type Msg_text |
| 183 |
test.t1 histogram status Histogram statistics created for column 'a'. |
| 184 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a'; |
| 185 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 186 |
test t1 a {"buckets": [[40, 0.2], [50, 0.4], [80, 0.6], [90, 0.8], [100, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 0.5, "histogram-type": "singleton", "number-of-buckets-specified": 50} |
| 187 |
DROP TABLE t1; |
| 188 |
set global innodb_limit_optimistic_insert_debug = 0; |
| 189 |
SET DEBUG='-d,histogram_force_sampling'; |
| 190 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; |
| 191 |
NAME COUNT |
| 192 |
sampled_pages_read 3 |
| 193 |
sampled_pages_skipped 3 |
| 194 |
SET GLOBAL innodb_monitor_disable = module_sampling; |
| 195 |
SET GLOBAL innodb_monitor_reset_all = "module_sampling"; |
| 196 |
# |
| 197 |
# Case were there is an error while buffering |
| 198 |
# |
| 199 |
SET GLOBAL innodb_monitor_enable = module_sampling; |
| 200 |
SET DEBUG='+d,histogram_force_sampling'; |
| 201 |
SET GLOBAL DEBUG='+d,parallel_reader_histogram_induce_error'; |
| 202 |
set global innodb_limit_optimistic_insert_debug = 2; |
| 203 |
CREATE TABLE t1 (a INT); |
| 204 |
INSERT INTO t1 VALUES (10), (20), (30), (40), (50), (60), (70), (80), (90), (100); |
| 205 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON a WITH 50 BUCKETS; |
| 206 |
Table Op Msg_type Msg_text |
| 207 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'a'; |
| 208 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 209 |
DROP TABLE t1; |
| 210 |
set global innodb_limit_optimistic_insert_debug = 0; |
| 211 |
SET GLOBAL DEBUG='-d,parallel_reader_histogram_induce_error'; |
| 212 |
SET DEBUG='-d,histogram_force_sampling'; |
| 213 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; |
| 214 |
NAME COUNT |
| 215 |
sampled_pages_read 0 |
| 216 |
sampled_pages_skipped 0 |
| 217 |
SET GLOBAL innodb_monitor_disable = module_sampling; |
| 218 |
SET GLOBAL innodb_monitor_reset_all = "module_sampling"; |
| 219 |
# |
| 220 |
# Case were partitions are involved |
| 221 |
# |
| 222 |
SET GLOBAL innodb_monitor_enable = module_sampling; |
| 223 |
CREATE TABLE p (col1 INT PRIMARY KEY, col2 INT, col3 INT) |
| 224 |
PARTITION BY KEY (col1) PARTITIONS 4; |
| 225 |
INSERT INTO p VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3); |
| 226 |
ANALYZE TABLE p UPDATE HISTOGRAM ON col2 WITH 50 BUCKETS; |
| 227 |
Table Op Msg_type Msg_text |
| 228 |
test.p histogram status Histogram statistics created for column 'col2'. |
| 229 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'col2'; |
| 230 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 231 |
test p col2 {"buckets": [[1, 0.3333333333333333], [2, 0.6666666666666666], [3, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 50} |
| 232 |
DROP TABLE p; |
| 233 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; |
| 234 |
NAME COUNT |
| 235 |
sampled_pages_read 3 |
| 236 |
sampled_pages_skipped 0 |
| 237 |
SET GLOBAL innodb_monitor_disable = module_sampling; |
| 238 |
SET GLOBAL innodb_monitor_reset_all = "module_sampling"; |
| 239 |
# |
| 240 |
# Case were there is an error while buffering when partitions are involved |
| 241 |
# |
| 242 |
SET GLOBAL innodb_monitor_enable = module_sampling; |
| 243 |
SET DEBUG='+d,histogram_force_sampling'; |
| 244 |
SET GLOBAL DEBUG='+d,parallel_reader_histogram_induce_error'; |
| 245 |
set global innodb_limit_optimistic_insert_debug = 2; |
| 246 |
CREATE TABLE p (col1 INT PRIMARY KEY, col2 INT, col3 INT) |
| 247 |
PARTITION BY KEY (col1) PARTITIONS 4; |
| 248 |
INSERT INTO p VALUES (1, 1, 1), (2, 2, 2), (3, 3, 3); |
| 249 |
ANALYZE TABLE p UPDATE HISTOGRAM ON col2 WITH 50 BUCKETS; |
| 250 |
Table Op Msg_type Msg_text |
| 251 |
test.p histogram status Histogram statistics created for column 'col2'. |
| 252 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'col2'; |
| 253 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 254 |
test p col2 {"buckets": [[1, 0.3333333333333333], [2, 0.6666666666666666], [3, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 0.5, "histogram-type": "singleton", "number-of-buckets-specified": 50} |
| 255 |
SELECT NAME,COUNT FROM INFORMATION_SCHEMA.INNODB_METRICS WHERE SUBSYSTEM='sampling'; |
| 256 |
NAME COUNT |
| 257 |
sampled_pages_read 3 |
| 258 |
sampled_pages_skipped 0 |
| 259 |
DROP TABLE p; |
| 260 |
set global innodb_limit_optimistic_insert_debug = 0; |
| 261 |
SET GLOBAL DEBUG='-d,parallel_reader_histogram_induce_error'; |
| 262 |
SET DEBUG='-d,histogram_force_sampling'; |
| 263 |
SET GLOBAL innodb_monitor_disable = module_sampling; |
| 264 |
SET GLOBAL innodb_monitor_reset_all = "module_sampling"; |
| 265 |
# |
| 266 |
# RAPID use case : Break from sampling after sampling half the records |
| 267 |
# |
| 268 |
CREATE TABLE t1 (id int); |
| 269 |
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); |
| 270 |
SET DEBUG = "+d,sample_read_sample_half"; |
| 271 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON id WITH 50 BUCKETS; |
| 272 |
Table Op Msg_type Msg_text |
| 273 |
test.t1 histogram status Histogram statistics created for column 'id'. |
| 274 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'id'; |
| 275 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 276 |
test t1 id {"buckets": [[1, 0.2], [2, 0.4], [3, 0.6], [4, 0.8], [5, 1.0]], "data-type": "int", "null-values": 0.0, "collation-id": 8, "sampling-rate": 1.0, "histogram-type": "singleton", "number-of-buckets-specified": 50} |
| 277 |
SET DEBUG = "-d, sample_read_sample_half"; |
| 278 |
DROP TABLE t1; |
| 279 |
# |
| 280 |
# Error case where the table is discared |
| 281 |
# |
| 282 |
CREATE TABLE t1 (id int); |
| 283 |
ALTER TABLE t1 DISCARD TABLESPACE; |
| 284 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON id WITH 50 BUCKETS; |
| 285 |
Table Op Msg_type Msg_text |
| 286 |
test.t1 histogram Error Tablespace has been discarded for table 'test/t1' |
| 287 |
DROP TABLE t1; |
| 288 |
# |
| 289 |
# Simulated error case where parallel read thread returns error |
| 290 |
# |
| 291 |
CREATE TABLE t1 (id int); |
| 292 |
INSERT INTO t1 VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10); |
| 293 |
SET GLOBAL DEBUG = "+d,simulate_sample_read_error"; |
| 294 |
ANALYZE TABLE t1 UPDATE HISTOGRAM ON id WITH 50 BUCKETS; |
| 295 |
Table Op Msg_type Msg_text |
| 296 |
SELECT SCHEMA_NAME, TABLE_NAME, COLUMN_NAME, JSON_REMOVE(histogram, '$."last-updated"') FROM information_schema.column_statistics WHERE COLUMN_NAME = 'id'; |
| 297 |
SCHEMA_NAME TABLE_NAME COLUMN_NAME JSON_REMOVE(histogram, '$."last-updated"') |
| 298 |
SET GLOBAL DEBUG = "-d,simulate_sample_read_error"; |
| 299 |
DROP TABLE t1; |
| 300 |
# |
| 301 |
# Cleanup |
| 302 |
# |
| 303 |
SET GLOBAL innodb_monitor_enable = default; |
| 304 |
Warnings: |
| 305 |
Warning 1230 Default value is not defined for this set option. Please specify correct counter or module name. |
| 306 |
SET GLOBAL innodb_monitor_disable = default; |
| 307 |
Warnings: |
| 308 |
Warning 1230 Default value is not defined for this set option. Please specify correct counter or module name. |
| 309 |
SET GLOBAL innodb_monitor_reset_all = default; |
| 310 |
Warnings: |
| 311 |
Warning 1230 Default value is not defined for this set option. Please specify correct counter or module name. |
| 312 |
SET GLOBAL DEBUG="-d, parallel_read_buffering_print"; |