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
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.