Difference between revisions of "MySQL Tunning"
m |
|||
| Line 5: | Line 5: | ||
* http://dba.stackexchange.com/questions/21209/innodb-high-disk-write-i-o-on-ibdata1-file-and-ib-logfile0 | * http://dba.stackexchange.com/questions/21209/innodb-high-disk-write-i-o-on-ibdata1-file-and-ib-logfile0 | ||
| − | + | == Use Case == | |
| − | == | ||
'''Server information''' | '''Server information''' | ||
* Virtualized Windows Server 2008 | * Virtualized Windows Server 2008 | ||
| Line 13: | Line 12: | ||
* JVM settings for JBoss: -Xms 3072 -Xmx 3072 -XX:PermSize=256m -XX:MaxPermSize=512m | * JVM settings for JBoss: -Xms 3072 -Xmx 3072 -XX:PermSize=256m -XX:MaxPermSize=512m | ||
* Repository is 1GB | * Repository is 1GB | ||
| − | |||
'''MySQL my.ini changes''' | '''MySQL my.ini changes''' | ||
| Line 20: | Line 18: | ||
* innodb_log_file_size=256M | * innodb_log_file_size=256M | ||
* innodb_log_buffer_size=8M | * innodb_log_buffer_size=8M | ||
| − | |||
Using MySQL server InnoDB engine with the default configuration parameters in my.ini file can cause a serious performance bottleneck in applications with high database read write activity. MySQL should have enough RAM to be able to cache the repository and avoid the high Disk I/O activity. | Using MySQL server InnoDB engine with the default configuration parameters in my.ini file can cause a serious performance bottleneck in applications with high database read write activity. MySQL should have enough RAM to be able to cache the repository and avoid the high Disk I/O activity. | ||
| Line 35: | Line 32: | ||
'''Considerations''' | '''Considerations''' | ||
| − | + | Repository is 1GB so 1792M is enough RAM for '''innodb_buffer_pool_size'''. | |
| − | Repository is 1GB so 1792M is enough RAM for innodb_buffer_pool_size. | + | The larger your repository is the more memory you need to allocate to '''innodb_buffer_pool_size'''. |
| − | The larger your repository is the more memory you need to allocate to innodb_buffer_pool_size. | ||
If you are using x86 mysql server you cannot allocate RAM larger than 2GB. | If you are using x86 mysql server you cannot allocate RAM larger than 2GB. | ||
| − | + | Information collected from [http://forum.openkm.com/viewtopic.php?f=5&t=10551 OpenKM forum post]. | |
| − | Information collected from | ||
[[Category: Installation Guide]] | [[Category: Installation Guide]] | ||
Revision as of 15:49, 12 September 2013
Take this documentation as is, not as a definitive guide to tunning MySQL. There're a lot of information and knowledge should be read before change MySQL configuration. The documentation has been created based on experice shared by OpenKM server administrator users.
Useful links should read:
- http://dev.mysql.com/doc/refman/5.0/en/innodb-tuning.html
- http://dba.stackexchange.com/questions/21209/innodb-high-disk-write-i-o-on-ibdata1-file-and-ib-logfile0
Use Case
Server information
- Virtualized Windows Server 2008
- CPU: Intel Xeon X3470 @2,93GHz
- RAM: 7GB
- JVM settings for JBoss: -Xms 3072 -Xmx 3072 -XX:PermSize=256m -XX:MaxPermSize=512m
- Repository is 1GB
MySQL my.ini changes
- innodb_buffer_pool_size=1792M
- innodb_additional_mem_pool_size=16M
- innodb_log_file_size=256M
- innodb_log_buffer_size=8M
Using MySQL server InnoDB engine with the default configuration parameters in my.ini file can cause a serious performance bottleneck in applications with high database read write activity. MySQL should have enough RAM to be able to cache the repository and avoid the high Disk I/O activity.
The most important parameters are:
- innodb_buffer_pool_size
- innodb_additional_mem_pool_size
- innodb_log_file_size
- innodb_log_buffer_size
 |
According to database experts, innodb_buffer_pool_size should occupy the 70 percent of RAM of a dedicated database server!!! |
 |
Before changing these values, please read MySQL documentation carefully. Before making any changes you should make sure that mysql server has been stopped correctly (check server log), otherwise the innodb will become corrupted and mysql server won't restart |
Considerations Repository is 1GB so 1792M is enough RAM for innodb_buffer_pool_size. The larger your repository is the more memory you need to allocate to innodb_buffer_pool_size. If you are using x86 mysql server you cannot allocate RAM larger than 2GB.
Information collected from OpenKM forum post.
