Greetings.... We have been experiencing a performance problem that relates to either mysql's buffers of the ext3 file cache. Our mysql servers are experiencing load spikes due to massive disk reads on datadir( > 3meg sec). Datadir is a seprate disk. Most of the day the disk
is being read at 50k-100k/sec. However at higher questions/sec the disk reads jump up and stay there for on the order of an hour. Running redhay 9 with 2X2.4 xeon with 4GB ram and the active portion of memory (according to top) is ~ 3GB. Filesystem has 3.3 GB and mysql ~500M. According to the ration or key_reads/key_requests the key_buffer is large enough and key_writes/key_write_requests ~ 1. I must admit I don't know if it is the MYI or MYD files that are being read during this period(tried lsof, any other tools to see disk activity by file?).
During this period of high read I see one specific query being heavily slowed. Whe shall call this query, A. Query A joins 3 tables but the explain reveals that all use an index with type (const,ref,ref). There is an order by in query A. I thought about extending the size of the sort_buffer or read_rnd_buffer but aren't those just pulling in record data which should be cached by the file system already? I have included the explain of query A below. Any advice would be most appreciated.
Regards,
Trevor
+-------+-------+----------------------------------------------+--------------------------+---------+-------+------+---------------------------------+
| table | type | possible_keys | key | key_len | ref | rows | Extra
|
+-------+-------+----------------------------------------------+--------------------------+---------+-------+------+---------------------------------+
| t | const | PRIMARY,tribe_id_idx | PRIMARY | 40 | const | 1 | Using temporary; Using filesort
|
| s | ref | PRIMARY,statistics_parent_id_idx | statistics_parent_id_idx | 40 | const | 1 | Using where
|
| c | ref | click_statistics_id_idx,click_visitor_id_idx | click_statistics_id_idx | 40 | s.ID | 81 | Using where
|
+-------+-------+----------------------------------------------+--------------------------+---------+-------+------+---------------------------------+
3 rows in set (0.00 sec)
Trevor
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
