Adding Indexes To Speed Up Uptime

by Jul 27, 2011

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') |

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” />