We where hitting an issue today where certain select statements where taking way too long /ohmy.gif” style=”vertical-align:middle” emoid=”:o” border=”0″ alt=”ohmy.gif” />
CODE
select count(*) from performance_sample where sample_time > '2000-01-20 11:07:00';
We found a few issues problems however we managed to reduce some of the queries from 12-13 seconds to sub second by adding an index to the sample_time in the performance_sample table.
The problem…
CODE
| 210 | uptime | localhost:14559 | uptime | Query  |  213 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (11,3,'2011-07-27 11:18:01')      |
| 211 | uptime | localhost:14560 | uptime | Query  |  212 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (21143,1496,'2011-07-27 11:18:02') |
| 212 | uptime | localhost:14562 | uptime | Query  |  212 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (4745,653,'2011-07-27 11:18:02')  |
| 213 | uptime | localhost:14569 | uptime | Query  |  207 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (35,9,'2011-07-27 11:18:07')      |
| 214 | uptime | localhost:14580 | uptime | Query  |  212 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (18105,1418,'2011-07-27 11:18:02') |
| 215 | uptime | localhost:14601 | uptime | Query  |  208 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (36709,2120,'2011-07-27 11:18:06') |
| 216 | uptime | localhost:14603 | uptime | Query  |  204 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (15305,1333,'2011-07-27 11:18:10') |
| 217 | uptime | localhost:14604 | uptime | Query  |  200 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (22945,1577,'2011-07-27 11:18:14') |
| 218 | uptime | localhost:14605 | uptime | Query  |  199 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (938,235,'2011-07-27 11:18:15')    |
| 219 | uptime | localhost:14606 | uptime | Query  |  210 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (15280,1327,'2011-07-27 11:18:04') |
| 220 | uptime | localhost:14607 | uptime | Query  |  212 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (23236,1606,'2011-07-27 11:18:01') |
| 221 | uptime | localhost:14608 | uptime | Query  |  212 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (51049,2380,'2011-07-27 11:18:01') |
| 222 | uptime | localhost:14610 | uptime | Query  |  205 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (25117,1805,'2011-07-27 11:18:08') |
| 223 | uptime | localhost:14612 | uptime | Query  |  209 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (56018,2508,'2011-07-27 11:18:05') |
| 211 | uptime | localhost:14560 | uptime | Query  |  212 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (21143,1496,'2011-07-27 11:18:02') |
| 212 | uptime | localhost:14562 | uptime | Query  |  212 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (4745,653,'2011-07-27 11:18:02')  |
| 213 | uptime | localhost:14569 | uptime | Query  |  207 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (35,9,'2011-07-27 11:18:07')      |
| 214 | uptime | localhost:14580 | uptime | Query  |  212 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (18105,1418,'2011-07-27 11:18:02') |
| 215 | uptime | localhost:14601 | uptime | Query  |  208 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (36709,2120,'2011-07-27 11:18:06') |
| 216 | uptime | localhost:14603 | uptime | Query  |  204 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (15305,1333,'2011-07-27 11:18:10') |
| 217 | uptime | localhost:14604 | uptime | Query  |  200 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (22945,1577,'2011-07-27 11:18:14') |
| 218 | uptime | localhost:14605 | uptime | Query  |  199 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (938,235,'2011-07-27 11:18:15')    |
| 219 | uptime | localhost:14606 | uptime | Query  |  210 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (15280,1327,'2011-07-27 11:18:04') |
| 220 | uptime | localhost:14607 | uptime | Query  |  212 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (23236,1606,'2011-07-27 11:18:01') |
| 221 | uptime | localhost:14608 | uptime | Query  |  212 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (51049,2380,'2011-07-27 11:18:01') |
| 222 | uptime | localhost:14610 | uptime | Query  |  205 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (25117,1805,'2011-07-27 11:18:08') |
| 223 | uptime | localhost:14612 | uptime | Query  |  209 | Waiting for table lock | insert into performance_sample (erdc_id,uptimehost_id,sample_time) values (56018,2508,'2011-07-27 11:18:05') |
Show me all the data in my table… this was taking over 12 seconds.
CODE
select count(*) from performance_sample where sample_time > '2000-01-20 11:07:30';
+
+
+
| count(*) |
+
+
| 15280552 |
+
+
1 row in set (12.73 sec)
Show me the data in my table from 30 seconds ago… this was taking even longer ( more records by the time i ran it )
CODE
mysql> select count(*) from performance_sample where sample_time > '2011-07-27 11:08:30';
+
+
+
| count(*) |
+
+
|Â Â Â Â Â Â 279 |
+
+
1 row in set (13.64 sec)
I had a quick look the indexes on the performance_sample table
CODE
mysql> SHOW INDEXES FROM performance_sample;
+
+
+
+
+
+
+
+
+
+
+
+
+
+
+
| Table              | Non_unique | Key_name              | Seq_in_index | Column_name  | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+
+
+
+
+
+
+
+
+
+
+
+
+
+
| performance_sample |          0 | PRIMARY              |            1 | id            | A        |    15282696 |    NULL | NULL  |      | BTREE      |        |              |
| performance_sample |          1 | SAMPLE_ERDC_ID        |            1 | erdc_id      | A        |        1235 |    NULL | NULL  | YES  | BTREE      |        |              |
| performance_sample |Â Â Â Â Â Â Â Â Â Â 1 | UPTIMEHOST_IDÂ Â Â Â Â Â Â Â |Â Â Â Â Â Â Â Â Â Â Â Â 1 | uptimehost_id | AÂ Â Â Â Â Â Â Â |Â Â Â Â Â Â Â Â 1235 |Â Â Â Â NULL | NULLÂ Â | YESÂ Â | BTREEÂ Â Â Â Â Â |Â Â Â Â Â Â Â Â |Â Â Â Â Â Â Â Â Â Â Â Â Â Â |
| performance_sample |          1 | LATEST_SAMPLE        |            1 | erdc_id      | A        |        1235 |    NULL | NULL  | YES  | BTREE      |        |              |
| performance_sample |          1 | LATEST_SAMPLE        |            2 | sample_time  | A        |    15282696 |    NULL | NULL  | YES  | BTREE      |        |              |
| performance_sample |Â Â Â Â Â Â Â Â Â Â 1 | LATEST_SAMPLE_BY_HOST |Â Â Â Â Â Â Â Â Â Â Â Â 1 | uptimehost_id | AÂ Â Â Â Â Â Â Â |Â Â Â Â Â Â Â Â 1235 |Â Â Â Â NULL | NULLÂ Â | YESÂ Â | BTREEÂ Â Â Â Â Â |Â Â Â Â Â Â Â Â |Â Â Â Â Â Â Â Â Â Â Â Â Â Â |
| performance_sample |          1 | LATEST_SAMPLE_BY_HOST |            2 | sample_time  | A        |    15282696 |    NULL | NULL  | YES  | BTREE      |        |              |
+
+
+
+
+
+
+
+
+
+
+
+
+
+
7 rows in set (0.01 sec)
Just as a test, I added the below
CODE
mysql> CREATE INDEX dateIndex ON performance_sample (sample_time)
The same query from before is a little bit faster…
CODE
mysql> select count(*) from performance_sample where sample_time > '2000-01-20 11:07:30';
+
+
+
| count(*) |
+
+
| 15283101 |
+
+
1 row in set (10.93 sec)
The big jump was the queries for sample_time in the last few minutes. It was down to 0.01 seconds.
CODE
mysql> select count(*) from performance_sample where sample_time > '2011-07-27 11:08:30';
+
+
+
| count(*) |
+
+
|Â Â Â Â 2638 |
+
+
1 row in set (0.01 sec)
Now the smaller the sample time the faster the query… /biggrin.gif” style=”vertical-align:middle” emoid=”:D” border=”0″ alt=”biggrin.gif” />
CODE
mysql> select count(*) from performance_sample where sample_time > '2011-07-27 11:24:00';
+
+
+
| count(*) |
+
+
|Â Â Â Â Â Â 406 |
+
+
1 row in set (0.00 sec)
I am not saying this is the correct answer for anyone however it has worked for us /biggrin.gif” style=”vertical-align:middle” emoid=”:D” border=”0″ alt=”biggrin.gif” />