Difference between revisions of "MySQL Tunning"
From OpenKM Documentation
(→User2 Case) |
Ctanqueray (talk | contribs) m |
||
| (12 intermediate revisions by 2 users not shown) | |||
| Line 1: | Line 1: | ||
| − | Take this documentation as is, not as a definitive guide to | + | Take this documentation as is, not as a definitive guide to tuning MySQL. There is a lot of information and knowledge which should be read before changing your MySQL configuration. The documentation has been created based on experience shared by OpenKM server administrator users. |
| − | Useful links should read: | + | Useful links which should be read: |
* [http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html MySQL: InnoDB Performance Tuning Tips] | * [http://dev.mysql.com/doc/refman/5.1/en/innodb-tuning.html MySQL: InnoDB Performance Tuning Tips] | ||
* [http://dev.mysql.com/doc/refman/5.1/en/query-cache.html MySQL: The MySQL Query Cache] | * [http://dev.mysql.com/doc/refman/5.1/en/query-cache.html MySQL: The MySQL Query Cache] | ||
* [http://dba.stackexchange.com/questions/21209/innodb-high-disk-write-i-o-on-ibdata1-file-and-ib-logfile0 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 InnoDB - High disk write I/O on ibdata1 file and ib_logfile0] | ||
| − | == User | + | == User case 1 == |
| + | '''Application''' | ||
| + | * OpenKM Community Version | ||
| + | |||
'''Server information''' | '''Server information''' | ||
* Virtualized Windows Server 2008 | * Virtualized Windows Server 2008 | ||
| − | * CPU: Intel Xeon X3470 @2 | + | * CPU: Intel Xeon X3470 @2.93GHz |
* RAM: 7GB | * RAM: 7GB | ||
* JVM settings for Tomcat: -Xms 3072 -Xmx 3072 -XX:PermSize=256m -XX:MaxPermSize=512m | * JVM settings for Tomcat: -Xms 3072 -Xmx 3072 -XX:PermSize=256m -XX:MaxPermSize=512m | ||
| Line 30: | Line 33: | ||
{{Warning|According to database experts, '''innodb_buffer_pool_size should occupy the 70 percent of RAM of a dedicated database server'''!!!}} | {{Warning|According to database experts, '''innodb_buffer_pool_size should occupy the 70 percent of RAM of a dedicated database server'''!!!}} | ||
| − | {{Note|Before changing these values, please read MySQL documentation carefully. Before making any changes you should make sure | + | {{Note|Before changing these values, please read MySQL documentation carefully. Before making any changes you should make sure the MySQL server has been stopped correctly (check server log), otherwise the innodb will become corrupted and MySQL server will not restart}} |
'''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 | + | The larger your repository, the more memory you need to allocate to '''innodb_buffer_pool_size'''. |
| − | If you are using x86 | + | 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 [http://forum.openkm.com/viewtopic.php?f=5&t=10551 OpenKM forum post]. | ||
| − | == | + | == User case 2 == |
| + | '''Application''' | ||
| + | * OpenKM Community Version | ||
| + | |||
'''Server information''' | '''Server information''' | ||
* CPU: Intel Xeon E5606 @2.13GHz | * CPU: Intel Xeon E5606 @2.13GHz | ||
* RAM: 4GB | * RAM: 4GB | ||
| − | * System: Windows | + | * System: Windows Server 2008, 64bit |
* JAVA_OPTS=-Xms256m -Xmx1024m -XX:PermSize=256m -XX:MaxPermSize=512m -Djava.awt.headless=true -Dfile.encoding=utf-8 | * JAVA_OPTS=-Xms256m -Xmx1024m -XX:PermSize=256m -XX:MaxPermSize=512m -Djava.awt.headless=true -Dfile.encoding=utf-8 | ||
* Database size:2.5G | * Database size:2.5G | ||
| Line 65: | Line 71: | ||
'''Results''' | '''Results''' | ||
| − | |||
| − | {{Note|With more RAM will be possible get a | + | Before optimization, the login time was about 45-55 seconds with a lot of hard-disk I/O usage. After the MySQL optimizations, login takes about 10 seconds. |
| + | |||
| + | {{Note|Observation: With more RAM it will be possible to get a higher MySQL buffer and probably better performance}} | ||
| − | Information collected [http://forum.openkm.com/viewtopic.php?f=5&t=10551&start=15 Forum post] | + | Information collected from [http://forum.openkm.com/viewtopic.php?f=5&t=10551&start=15 OpenKM Forum post] |
[[Category: Installation Guide]] | [[Category: Installation Guide]] | ||
Latest revision as of 16:50, 24 April 2015
Take this documentation as is, not as a definitive guide to tuning MySQL. There is a lot of information and knowledge which should be read before changing your MySQL configuration. The documentation has been created based on experience shared by OpenKM server administrator users.
Useful links which should be read:
- MySQL: InnoDB Performance Tuning Tips
- MySQL: The MySQL Query Cache
- InnoDB - High disk write I/O on ibdata1 file and ib_logfile0
User case 1
Application
- OpenKM Community Version
Server information
- Virtualized Windows Server 2008
- CPU: Intel Xeon X3470 @2.93GHz
- RAM: 7GB
- JVM settings for Tomcat: -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 the MySQL server has been stopped correctly (check server log), otherwise the innodb will become corrupted and MySQL server will not restart |
Considerations Repository is 1GB so 1792M is enough RAM for innodb_buffer_pool_size. The larger your repository, 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.
User case 2
Application
- OpenKM Community Version
Server information
- CPU: Intel Xeon E5606 @2.13GHz
- RAM: 4GB
- System: Windows Server 2008, 64bit
- JAVA_OPTS=-Xms256m -Xmx1024m -XX:PermSize=256m -XX:MaxPermSize=512m -Djava.awt.headless=true -Dfile.encoding=utf-8
- Database size:2.5G
- Repository size:40GB
- 100,000 nodes(folders + documents)
MySQL my.ini changes
- query_cache_size=0
- table_open_cache=2000
- tmp_table_size=16M
- thread_cache_size=9
- myisam_max_sort_file_size=100G
- myisam_sort_buffer_size=32M
- key_buffer_size=8M
- read_buffer_size=64K
- read_rnd_buffer_size=256K
- sort_buffer_size=256K
- innodb_additional_mem_pool_size=16M
- innodb_log_buffer_size=8M
- innodb_buffer_pool_size=1042M
Results
Before optimization, the login time was about 45-55 seconds with a lot of hard-disk I/O usage. After the MySQL optimizations, login takes about 10 seconds.
|
Observation: With more RAM it will be possible to get a higher MySQL buffer and probably better performance |
Information collected from OpenKM Forum post
