# Performance Degradation Problem

**URL:** <https://forums.percona.com/t/performance-degradation-problem/2767>\
**Category:** Other MySQL® Questions\
**Created:** [June 20, 2013, 7:04pm UTC](https://forums.percona.com/t/performance-degradation-problem/2767 "2013-06-20T19:04:46Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![peterf](https://avatars.discourse-cdn.com/v4/letter/p/4af34b/32.png) [@peterf](https://forums.percona.com/u/peterf)\
**Post date:** [June 20, 2013, 7:04pm UTC](https://forums.percona.com/t/performance-degradation-problem/2767/1 "2013-06-20T19:04:46Z")

</div>

Hi,

I have recently started to experience very poor performance from our MariaDB installation as some time passes after a restart. We run MariaDB 5.2.9 on a CentOS release 5.7 (Final) server, it has 32 GB of memory and just short of a 200 GB total database size. After a lot of investigations and not being able to find any problem of significance I am a bit out of ideas.

As the problem only occurs after some time I guess it is because of memory or buffer pool problems. All of our tables are INNODB. These are the INNODB setting from the  
my.cnf file.

innodb\_buffer\_pool\_size = 22G  
innodb\_additional\_mem\_pool\_size = 128M  
innodb\_log\_buffer\_size = 30M  
innodb\_file\_per\_table = 1  
innodb\_file\_format = Barracuda  
innodb\_open\_files = 2000  
innodb\_io\_capacity = 400  
innodb\_flush\_log\_at\_trx\_commit = 1  
innodb\_lock\_wait\_timeout = 5

If I restart MySQL and immediately run a full system (by schema) backup using mysqldump command or a mk-table-checksum toolkit command (To compare multiple replicas) over the complete database the backup completes in approximately 5 hours as does the checksum command. The following day if the backup or checksum is run again it can take more than 15 hours to complete.

Everything runs in a VMware 5.1 environment. I can migrate systems from one host to another and than back to the original host but results vary from test to test and I suspect that this is relates to what tables are in use at the particular time.

The only strange status values I can find is that the pages\_data actually greater than the pages\_total which might explain the ridiculous value for the pages\_misc value.

| Innodb\_buffer\_pool\_pages\_data | 1653141 |  
| Innodb\_buffer\_pool\_pages\_dirty | 0 |  
| Innodb\_buffer\_pool\_pages\_flushed | 1656996 |  
| Innodb\_buffer\_pool\_pages\_free | 0 |  
| Innodb\_buffer\_pool\_pages\_misc | 18446744073709340266 |  
| Innodb\_buffer\_pool\_pages\_total | 1441791 |  
| Innodb\_buffer\_pool\_read\_ahead | 8459648 |  
| Innodb\_buffer\_pool\_read\_ahead\_evicted | 10650729 |  
| Innodb\_buffer\_pool\_read\_requests | 11602730510 |  
| Innodb\_buffer\_pool\_reads | 13956217 |  
| Innodb\_buffer\_pool\_wait\_free | 0 |  
| Innodb\_buffer\_pool\_write\_requests | 254088485 |

Has any one experienced such a problem and have an answer. I see a number of post on this issue but cannot find any solution.

---

<div class="post-metadata">

**Author:** ![peterf](https://avatars.discourse-cdn.com/v4/letter/p/4af34b/32.png) [@peterf](https://forums.percona.com/u/peterf)\
**Post date:** [June 21, 2013, 4:53pm UTC](https://forums.percona.com/t/performance-degradation-problem/2767/2 "2013-06-21T16:53:00Z")

</div>

I forgot to add that we have 4 compressed tables ‘key block size =4’ and it is when these tables start to be processed by the backup that the buffer pool pages status figures go bad and performance drops to standstill. Until these tables are started to be processed the buffer pool is full but performance is OK and pages are loading and evicting from the buffer as expected.

---

<div class="post-metadata">

**Author:** ![peterf](https://avatars.discourse-cdn.com/v4/letter/p/4af34b/32.png) [@peterf](https://forums.percona.com/u/peterf)\
**Post date:** [June 24, 2013, 3:57pm UTC](https://forums.percona.com/t/performance-degradation-problem/2767/3 "2013-06-24T15:57:35Z")

</div>

HI, I have some more ‘findings’. I have on a test system upgraded the MariaDB to the latest stable version (V5.5.31) and the server still has the same problem.  
I restart the server and immediately start a --all-databases mysqldump. As the backup runs it first fills up the pages\_data to basically equal the pages\_total and then processes away quite happily. As soon as the compressed table is reached the status figures go ‘bad’. The backup still continues (very slowly) and does complete. As it happens the very last table to back up on our server is a compressed table, so at completion of the backup the status figures remain ‘bad’ but static when displayed. If I then start another backup that does not include any compressed files the pages\_data figure gradually reduces to the point where it matches the pages\_total and when this happens the pages\_misc instantly reverts to a normal value. See the figures below. I am still testing on performance/throughput at the various stages.

Any suggestions on how to fix this would be much appreciated, or is this a MySQL bug.

MariaDB [(none)]\> show global status like ‘INNODB\_BUFFER\_POOL\_P%’;  
±----------------------------------------±---------------------+  
| Variable\_name | Value |  
±----------------------------------------±---------------------+  
| Innodb\_buffer\_pool\_pages\_data | 658832 |  
| Innodb\_buffer\_pool\_pages\_dirty | 0 |  
| Innodb\_buffer\_pool\_pages\_flushed | 1 |  
| Innodb\_buffer\_pool\_pages\_LRU\_flushed | 0 |  
| Innodb\_buffer\_pool\_pages\_free | 0 |  
| Innodb\_buffer\_pool\_pages\_made\_not\_young | 0 |  
| Innodb\_buffer\_pool\_pages\_made\_young | 3684671 |  
| Innodb\_buffer\_pool\_pages\_misc | 18446744073709089391 |  
| Innodb\_buffer\_pool\_pages\_old | 243181 |  
| Innodb\_buffer\_pool\_pages\_total | 196607 |  
±----------------------------------------±---------------------+  
10 rows in set (0.00 sec)

MariaDB [(none)]\> show global status like ‘INNODB\_BUFFER\_POOL\_P%’;  
±----------------------------------------±---------------------+  
| Variable\_name | Value |  
±----------------------------------------±---------------------+  
| Innodb\_buffer\_pool\_pages\_data | 658832 | \<— Stable figure when compressed files backup has finished  
| Innodb\_buffer\_pool\_pages\_dirty | 0 |  
| Innodb\_buffer\_pool\_pages\_flushed | 1 |  
| Innodb\_buffer\_pool\_pages\_LRU\_flushed | 0 |  
| Innodb\_buffer\_pool\_pages\_free | 0 |  
| Innodb\_buffer\_pool\_pages\_made\_not\_young | 0 |  
| Innodb\_buffer\_pool\_pages\_made\_young | 3684671 |  
| Innodb\_buffer\_pool\_pages\_misc | 18446744073709089391 |  
| Innodb\_buffer\_pool\_pages\_old | 243181 |  
| Innodb\_buffer\_pool\_pages\_total | 196607 |  
±----------------------------------------±---------------------+  
10 rows in set (0.00 sec)  
\<— Started a 2nd mysqldump on a different session (no compressed tables in it)  
MariaDB [(none)]\> show global status like ‘INNODB\_BUFFER\_POOL\_P%’;  
±----------------------------------------±---------------------+  
| Variable\_name | Value |  
±----------------------------------------±---------------------+  
| Innodb\_buffer\_pool\_pages\_data | 657725 | \<— pages\_data begins to reduce  
| Innodb\_buffer\_pool\_pages\_dirty | 0 |  
| Innodb\_buffer\_pool\_pages\_flushed | 1 |  
| Innodb\_buffer\_pool\_pages\_LRU\_flushed | 0 |  
| Innodb\_buffer\_pool\_pages\_free | 0 |  
| Innodb\_buffer\_pool\_pages\_made\_not\_young | 0 |  
| Innodb\_buffer\_pool\_pages\_made\_young | 3685045 |  
| Innodb\_buffer\_pool\_pages\_misc | 18446744073709090498 |  
| Innodb\_buffer\_pool\_pages\_old | 242773 |  
| Innodb\_buffer\_pool\_pages\_total | 196607 |  
±----------------------------------------±---------------------+  
10 rows in set (0.00 sec)

\*  
\*  
\*  
\*  
\*

MariaDB [(none)]\> show global status like ‘INNODB\_BUFFER\_POOL\_P%’;  
±----------------------------------------±---------------------+  
| Variable\_name | Value |  
±----------------------------------------±---------------------+  
| Innodb\_buffer\_pool\_pages\_data | 230449 | \<— still reducing as 2nd backup is running  
| Innodb\_buffer\_pool\_pages\_dirty | 0 |  
| Innodb\_buffer\_pool\_pages\_flushed | 1 |  
| Innodb\_buffer\_pool\_pages\_LRU\_flushed | 0 |  
| Innodb\_buffer\_pool\_pages\_free | 0 |  
| Innodb\_buffer\_pool\_pages\_made\_not\_young | 0 |  
| Innodb\_buffer\_pool\_pages\_made\_young | 3836372 |  
| Innodb\_buffer\_pool\_pages\_misc | 18446744073709517774 |  
| Innodb\_buffer\_pool\_pages\_old | 85048 |  
| Innodb\_buffer\_pool\_pages\_total | 196607 |  
±----------------------------------------±---------------------+  
10 rows in set (0.00 sec)

MariaDB [(none)]\> show global status like ‘INNODB\_BUFFER\_POOL\_P%’;  
±----------------------------------------±--------+  
| Variable\_name | Value |  
±----------------------------------------±--------+  
| Innodb\_buffer\_pool\_pages\_data | 196594 | \<— 2nd backup is still running as pages\_data reaches \< pages\_total  
| Innodb\_buffer\_pool\_pages\_dirty | 0 |  
| Innodb\_buffer\_pool\_pages\_flushed | 1 |  
| Innodb\_buffer\_pool\_pages\_LRU\_flushed | 0 |  
| Innodb\_buffer\_pool\_pages\_free | 0 |  
| Innodb\_buffer\_pool\_pages\_made\_not\_young | 0 |  
| Innodb\_buffer\_pool\_pages\_made\_young | 3855393 |  
| Innodb\_buffer\_pool\_pages\_misc | 13 | \<— pages\_misc value returns to normal.  
| Innodb\_buffer\_pool\_pages\_old | 72551 |  
| Innodb\_buffer\_pool\_pages\_total | 196607 |  
±----------------------------------------±--------+  
10 rows in set (0.00 sec)

MariaDB [(none)]\>

---

<div class="post-metadata">

**Author:** ![revin](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/revin/32/824_2.png) [@revin](https://forums.percona.com/u/revin)\
**Post date:** [June 24, 2013, 9:02pm UTC](https://forums.percona.com/t/performance-degradation-problem/2767/4 "2013-06-24T21:02:04Z")

</div>

peterf, if you checksum or dump only the compressed tables after a cold start, does performance degrade similar to when doing those procedures on the non-compressed tables first? If the total size of the compressed tables versus the non-compressed tables is larger to cause buffer pool eviction during the checksum or dump, it can definitely produce a significant amount of reads and writes where you will start to notice the degradation. Also note that for compressed tables, both the compressed and non-compressed values will be stored in the buffer pool.

---

<div class="post-metadata">

**Author:** ![peterf](https://avatars.discourse-cdn.com/v4/letter/p/4af34b/32.png) [@peterf](https://forums.percona.com/u/peterf)\
**Post date:** [June 27, 2013, 7:48pm UTC](https://forums.percona.com/t/performance-degradation-problem/2767/5 "2013-06-27T19:48:30Z")

</div>

Hi, Thanks for your interest. I have not tested dumping only the compressed tables. The size of the tables is always far in excess of any buffer size that I can set. My test system has only 4GB total memory and I switch buffer pool size around from 1 GB up to 3 GB for different test. The table size of the compressed file is 2.5GB compressed and 20GB uncompressed, so the buffer always quickly fills up and starts evicting data. I have run so many test lately and I get different results from time to time I am not sure what is happening. My most recent tests suggest that the stats above relating to pages\_data and pages\_misc apart from having strange values are infact not impacting performance at all.  
About the only hard fact that I can say is that when performance is bad (say when I run a checksum test over 2 systems) the ‘mysqld’ process has 1 of the systems 2 CPU’s maxed out at 100% and that even after 30 mins or more this system does not respond with the checksum result but the 2nd system will have sent its checksum result back in just a number of seconds. The system with the problem shows no other stress at all (no swapping, no disk I/O pressure or anything that I can find).  
I think I will be doing a lot more testing and trouble shooting to lock down what the problem is.