MySQL Bugs: #36556: MySQL slows down after a while (after FLUSH TABLES okay again)

Bug #36556	MySQL slows down after a while (after FLUSH TABLES okay again)
Submitted:	7 May 2008 9:51	Modified:	27 Jan 2013 19:38
Reporter:	Vygintas Krasauskas	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: MyISAM storage engine	Severity:	S5 (Performance)
Version:	5.0, 5.5.27	OS:	Linux (Red Hat Enterprise 4 64 bit)
Assigned to:		CPU Architecture:	Any
Tags:	performance

Description:
I have nocited that on two of our servers MySQL performance drops down significantly after a while.

We have several servers, two of them (with Master-Master replication) are running 64-bit RedHat Enterprise. Both machines have same hardware and software (Core Duo processors, 4GB RAM etc). MySQL 5.0 on both of them (and only on them) slows really down after a while. Firs I have restarted MySQL to fix it but nos I have nocited that FLUSH TABLES helps a well.

We did not have such kind of issues on other servers (64-bit Suse Linux Enterprise 10 with 2 GB RAM and 32-bit Suse 9.3 with 1GB).

DETAILED EXAMPLE:

We have PHP (CLI) scripts which performs lots of small MySQL updates. The scripts show current performance in updates per second. Normally we have 2,000-4,000 updates/sec. Most of the scripts are running evers 6 minutes and need under 1 minute to finish. There are other sceipts which are starting every night and need longer. After few (1-5) days, some of the updates are starting to be slow. Affected scripts display eg. only 140-180 updates/sec then. Yesterday it was only 15-18 in one case! I have restarted the MySQL and then performance was again over 2,000 updates/sec. Today in the morning it was again dropped to ~160, and I have executed FLUSH TABLES while the script was still running. It took 24 seconds to complete and right after them the update speed has reached its normal level!

Interestingly, only some updates on some tables are slowing down. Other scripts, which use other tables, are still running fast.

Sometimes even very small tables are affected: every INSERT INTO ... VALUES (...) into empty table for some reason CONSTANTLY takes up to 10 ms

There is nothing special visible in process list or status.

Both servers are starting to be slow after *approximately* same time: when one performs bad, then most likely also the second one is slow as well (but sometimes still not).

We're running these two servers for almost one year (always with most recent version of MySQL 5), and those problems were virtually always here; recently more often probebly because of bigger load.

How to repeat:
Unfortunately, I did not yet find any possibilities to reproduce this problem.
Sometimes it takes two weeks until it happens, sometimes (today) less than 24 hours.
Some tables/updates are more ofter affected than others.

Thank you for a problem report. What exact version, 5.0.x, do you use?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I am having same issues with 5.5.27.
How this case can be reopened? I can provide all necessary information

Yuriy,

Please, provide all the details on how to reproduce the problem with 5.5.27.

Hi Valeriy,

To start please read this forum post: http://forum.percona.com/index.php?t=msg&goto=9101 as I have described the whole situation there.

My thoughts on how to reproduce the problem:
1.Set key cache in my.cnf to a rather large value (i tried 18-25Gb, the idea is so  cache prunes will not occur due to cache being full)

2. Create a table and load with data so you end up with 5Gb of index
My table structure:
CREATE TABLE IF NOT EXISTS `input_line_statistics` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `pid` mediumint(8) unsigned NOT NULL,
  `date` date NOT NULL,
  `period` smallint(5) unsigned NOT NULL,
  `count` int(11) unsigned NOT NULL,
  `avg30` int(10) unsigned DEFAULT NULL,
  `stddev1` mediumint(8) unsigned DEFAULT NULL,
  `stddev3` mediumint(8) unsigned DEFAULT NULL,
  `stddev7` mediumint(8) unsigned DEFAULT NULL,
  `stddevp30` mediumint(8) unsigned DEFAULT NULL,
  `ab` tinyint(3) unsigned DEFAULT NULL,
  UNIQUE KEY `stat` (`pid`,`date`,`period`),
  KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ROW_FORMAT=COMPACT
PARTITION BY KEY (pid)
PARTITIONS 20   ;

3. Perform inserts so "Key_blocks_not_flushed" keeps growing continuously.

4. Run a test query continuously and notice performance degradation at some point when key_blocks_not_flushed is huge.
My test query is of this type:
SELECT ( 4 * HOUR( a.date ) + FLOOR( MINUTE( a.date ) /15 ) ) AS period, SUM( a.mv ) AS count, DATE( date ) AS date
FROM table a
WHERE a.date > DATE_SUB( '2012-08-08 16:14:55', INTERVAL 10
MINUTE )
AND a.date <= '2012-08-08 16:44:58'
AND location = 'some_location'
AND ...
GROUP BY period

4. Run "FLUSH TABLES" and notice that performance restored.

Let me know how it goes, I can do more tests on my side if you want!

I've also encountered this issue on one of our production machines. (5.5.27 on FC9) 

mysql> flush tables;
Query OK, 0 rows affected (15 min 57.33 sec)

The FLUSH TABLES workaround did work, the whole operation took ~15 mins, however the server returned to the normal condition almost immediately after issuing the command. As a temporary solution I've tried to set global flush_time to 1800 (make full flush every 3 hours), but it wasn't successful.

Any suggestions? I can provide any extra information required to troubleshoot this.

Thanks in advance.

Ok, so now I know how to reproduce this problem.

This happens on MyISAM tables with very large index > 2Gb with delay_key_write = 1 query mix: select/insert/update, but no deletes. This in turn will cause the key unflushed blocks grow to "unknown" size when MySQL performance will crap out (probably due to inefficient key cache use). Right now I take consistent performance hits when Key_blocks_not_flushed ~2.2Gb (this changed from 1.6Gb after I created separate key caches). Solution is to: delay_key_write = 0 for the table and take a performance hit, or flush the table periodically.

Have you considered trying the flush_time parameter to flush tables every X seconds ?  What about keycache_division_limit tuning?

http://dev.mysql.com/doc/refman/5.5/en/midpoint-insertion.html
http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_flush_time

Related:
http://bugs.mysql.com/bug.php?id=43726

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".