Re: Statistics bottleneck on large table

[Nick Lott]

I've verified that the same problem occurs on a much smaller but similarly architectured table, which makes me think it must be some system problem.

The MYD on this table is only 128Mb. System RAM is 4Gb, but it's not using 2Gb of this. Probably this means I can up some mem vars further, but mysql is not busy on this box.

total used free shared buffers cached
Mem: 4149216 3931396 217820 0 16128 2181596
-/+ buffers/cache: 1733672 2415544
Swap: 1052248 100 1052148


Information as follows:

mysql> show profile for query 2;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000021 |
| checking query cache for query | 0.000071 |
| Opening tables | 0.000012 |
| System lock | 0.000004 |
| Table lock | 0.000028 |
| init | 0.000042 |
| optimizing | 0.000016 |
| statistics | 0.011564 |
| preparing | 0.000005 |
| executing | 0.000053 |
| end | 0.000004 |
| query end | 0.000003 |
| freeing items | 0.000023 |
| storing result in query cache | 0.000218 |
| logging slow query | 0.000002 |
| cleaning up | 0.000002 |
+--------------------------------+----------+
16 rows in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'test';
+-------------+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |
+-------------+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
| test | MyISAM | 10 | Fixed | 1781948 | 75 | 133646100 | 21110623253299199 | 53298176 | 0 | NULL | 2010-08-23 02:14:30 | 2010-08-23 07:14:19 | NULL | latin1_swedish_ci | NULL | | |
+-------------+--------+---------+------------+---------+----------------+-------------+-------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-------------------+----------+----------------+---------+
1 row in set (0.01 sec)


CREATE TABLE `test` (
`rt` datetime NOT NULL,
`vt` datetime NOT NULL,
`lat` decimal(6,3) NOT NULL,
`lon` decimal(6,3) NOT NULL,
`data_1` decimal(5,2) DEFAULT NULL,
`data_2` decimal(5,2) DEFAULT NULL,
`data_3` decimal(5,2) DEFAULT NULL,
`data_4` decimal(5,2) DEFAULT NULL,
`data_5` decimal(5,2) DEFAULT NULL,
`data_6` decimal(5,2) DEFAULT NULL,
`data_7` decimal(5,2) DEFAULT NULL,
`data_8` decimal(5,2) DEFAULT NULL,
`data_9` decimal(5,2) DEFAULT NULL,
`data_10` decimal(5,2) DEFAULT NULL,
`data_11` decimal(5,2) DEFAULT NULL,
`data_12` decimal(5,2) DEFAULT NULL,
`data_13` decimal(5,2) DEFAULT NULL,
`data_14` decimal(5,2) DEFAULT NULL,
`data_15` decimal(5,2) DEFAULT NULL,
`data_16` decimal(5,2) DEFAULT NULL,
PRIMARY KEY (`vt`,`lat`,`lon`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

mysql> SHOW VARIABLES LIKE 'key_buffer%';
+-----------------+------------+
| Variable_name | Value |
+-----------------+------------+
| key_buffer_size | 1073741824 |
+-----------------+------------+
1 row in set (0.00 sec)

I will try the range request with the alternative key order. It is only the vt field that will vary.