Translated from 日本語 • View original **I'm going to try tuning MySQL for lack of power. **
Good evening. This is Miyashita, who is in charge of the platform.
This time, the
I would like to improve performance.
We have taken several measures so far, but first we will review the environment.
◇ The running environment uses a large instance in AWS EC2.
The service is a service of SNS using OpenPNE. The target Mysql is running for backup and data aggregation purposes.
(Not used in production service)
◇ The mysql version is a bit old and is "5.0.77". I have it started with multi
In addition to this, MySQL for the development environment is also running. By the way, the current delay seconds are still increasing at "1512561 seconds".
◇ Regarding the changes made so far,
Increased "innodb_buffer_pool_size" from 512M to 2028M,
Changed "innodb_flush_log_at_trx_commit" from 2 to 0,
(Prioritize throughput over recovery accuracy)
changed "key_buffer_size" from 32M to 300M,
"query_cache_size = 0" and disabled it.
The effect is that the I/O of the DISK has been reduced by about half, but it is not said to eliminate the delay.
◇ Finally, check the data you are getting in munin.
Delays are increasing day by day. Sometimes it decreases, but I don't know the factor.
There is always a lot of read I/O on the DISK.
The cause is clear, and it is because the image and video data are stored in the DB data.
Since the data size cannot fit in the buffer pool size, DISK read/write is always occurring.
Also, MyISAM and Innodb are mixed, so resources are not used efficiently,
OpenPNE is issuing a large number of queries "delete aaa filename LIKE '%bbb%'"
There are many issues that need to be solved.
By sorting out the current situation, I would like to implement improvement measures one by one from the next time.