On multiple servers I have seen this behaviour as reported by users but now on my own server I have seen it first hand. The server is FreeBSD 9.2 based. In this instance MySQL is 5.5 percona but the version isn't relevant as I've seen it with all versions of MySQL. My application is cacti. The problem is seen and 100% repeatable as follows.
meg MB database.
Someone else reported the issue here. In his case it seems the problem only occured on MyISAM, I do plan to change this DB to innodb to see if the issue goes away. I have the reccomended recordsize and other reccomended settings.
Here is the slow log entry from when is metadata only.
Same query from a normal primarycache.
- Load up a data template.
- Hit save to save that template, the entire database is 3.5
megMB and the table is just 300 KB.
- On UFS I get a small amount of I/O on both read/write with a 0.2 second query.
- On ZFS with
Code:
primarycache=metadata
mMB/sec and a resulting 9.1 second query. :\ - On ZFS with
Code:
primarycache=all
Someone else reported the issue here. In his case it seems the problem only occured on MyISAM, I do plan to change this DB to innodb to see if the issue goes away. I have the reccomended recordsize and other reccomended settings.
Here is the slow log entry from when is metadata only.
Code:
# Time: 131225 13:40:04
# User@Host: cacti[cacti] @ localhost []
# Thread_id: 44313 Schema: cacti Last_errno: 0 Killed: 0
# Query_time: 9.199591 Lock_time: 0.000038 Rows_sent: 10 Rows_examined: 23706 Rows_affected: 0 Rows_read: 23706
# Bytes_sent: 733 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# No InnoDB statistics available for this query
SET timestamp=1387978804;
SELECT data_template_data.id AS data_template_data_id, data_input_fields.id, data_input_fields.type_code, data_input_data.value, data_input_data.t_value FROM data_input_fields INNER JOIN (data_template_data INNER JOIN data_input_data ON data_template_data.id = data_input_data.data_template_data_id) ON data_input_fields.id = data_input_data.data_input_field_id WHERE (data_input_fields.input_output='in') AND data_input_fields.id IN (1,2,3,4,5,6,40,41,42,43) AND (data_input_data.t_value='' OR data_input_data.t_value IS NULL) AND (data_template_data.local_data_template_data_id=27);
Same query from a normal primarycache.
Code:
# Time: 131229 21:46:48
# User@Host: cacti[cacti] @ localhost []
# Thread_id: 3641 Schema: cacti Last_errno: 0 Killed: 0
# Query_time: 0.091359 Lock_time: 0.000037 Rows_sent: 10 Rows_examined: 23706 Rows_affected: 0 Rows_read: 23706
# Bytes_sent: 733 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# Filesort: No Filesort_on_disk: No Merge_passes: 0
# No InnoDB statistics available for this query
SET timestamp=1388353608;
SELECT data_template_data.id AS data_template_data_id, data_input_fields.id, data_input_fields.type_code, data_input_data.value, data_input_data.t_value FROM data_input_fields INNER JOIN (data_template_data INNER JOIN data_input_data ON data_template_data.id = data_input_data.data_template_data_id) ON data_input_fields.id = data_input_data.data_input_field_id WHERE (data_input_fields.input_output='in') AND data_input_fields.id IN (1,2,3,4,5,6,40,41,42,43) AND (data_input_data.t_value='' OR data_input_data.t_value IS NULL) AND (data_template_data.local_data_template_data_id=27);