zfs - metadata only caching for mysql = slow

C

chrcol

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.
  • Load up a data template.
  • Hit save to save that template, the entire database is 3.5 meg MB and the table is just 300 KB.
I expect low amount of writes but instead I get the following.
  • 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
    (suggested value) I get around 9 seconds of heavy reads averaging 20 m MB/sec and a resulting 9.1 second query. :\
  • On ZFS with
    Code: 
    primarycache=all
    I get a small amount of irrelevant I/O read/write (even first run uncached) and a 0.09 second query.
The percona slow log says no tmp tables, no soft merge passes, no disk based tables, and I have no explanation as to why a metadata only primarycache reads approximately 180 megabyte of data of the HDD for saving a few kilobytes of data on a 3.5 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.

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);
 
chrcol said:
it seems the problem only occured on MyISAM
Click to expand...

MyISAM does not have a data cache of its own. It relies on the cache the file system provides. By setting
Code: 
primarycache=metadata
you've turned off ZFS' in-memory data cache.
 
worldi said:
chrcol said:
it seems the problem only occured on MyISAM
Click to expand...

MyISAM does not have a data cache of its own. It relies on the cache the file system provides. By setting
Code: 
primarycache=metadata
you've turned off ZFS' in-memory data cache.
Click to expand...

I know that.

But do you have an explanation as to why.
  1. - Guides from oracle and FreeBSD state to use
    Code: 
    primarycache=metadata
    for MyISAM.
  2. - With
    Code: 
    primarycache=metadata
    it is reading huge amounts of data way bigger than the entire database.
 
1. I haven't seen a single document that advises the use of primarycache=metadata for MyISAM databases. Can you provide links to documents that do? My guess is you're misinterpreting them (e.g. https://wiki.freebsd.org/ZFSTuningGuide#MySQL which obviously - but implicitly - talks about InnoDB tuning).

2. It looks like that without any caching every IO operation hits the disk. In theory a mere 1440 operations are enough to generate a transfer volume of 180MB (assuming a block size of 128k).
 
Code: 
primarycache=metadata
should only be used on an InnoDB engine and with great caution provided that you have tuned the ARC through my.cnf.

Aside from that using Percona with MyISAM is not your best bet for performance.
 
I have seen several articles.

After my observations and the replies to my posts I have contacted all the websites to see if they will update their articles, some of the articles dont specifically state its innodb only others have actually listed the command to be run on MyISAM datasets as well as innodb One of the articles is even on the oracle website.

Also its not a good assumption that everyone uses or can just switch all databases to innodb as shared hosting platforms use MyISAM by default, various internet applications will use MyISAM by default and some only support MyISAM.

The recordsize is 8k not 128k.

Thank you anyway for the responses.
 
You must log in or register to reply here.
Back
Top