EMS Implementation Lessons Learned: Database Performance
Posted by Rick Pandolfi on Thu, Feb 03, 2011 @ 09:27 AM
EMS Implementation Lessons Learned: Database Performance
My customers run the gamut in terms of their ITSM maturity, their business objectives and their technology platforms. But there are some lessons learned that I believe are universal or near universal. When I find those, I will post them here.
Recently, during a project to stand up a production Enterprise Management System solution my team encountered some interesting challenges and machine behavior. The EMS platform we were working with included the Monolith Software Event Manager Suite with a back end database of MySQL 5.1.36. from Oracle/Sun. The operating system was AS5 Linux from Red Hat.
In conjunction with the customers Architecture team, we sized the servers to account for a large volume of SQL inserts and updates. However, very quickly we encountered unexpected resource consumption issues when the applications spun up any significant insert/update activity -- off the charts load average, CPU and memory usage.
Troubleshooting included a fair amount of trial and error. Finally, we came upon a combination of settings in my.cnf that worked. We learned some valuable lessons that may help on a MySQL standalone server.
5 Settings with Performance Impact
● Setting 1: max_connections: By default, max_connections is 150 for a huge MySQL server. We boosted ours to 500 and found that applications such as the Trapd Aggregator and Syslog Aggregator performed much better. Try this out in a test environment.
● Setting 2: innodb_additional_mem_pool_size: By default, this is 3M. We put ours at 6M and it demonstrated significant improvement.
● Setting 3: innodb_log_buffer_size: We put ours at 64MB to account for large log files.
● Setting 4: innodb_buffer_pool_size: This setting seemed to be a key to our success. We set ours at 16384K. By default, it is SUPER low (1024K) for the machine we were using (our machines had 40GB RAM). After we changed this setting MySQL had a far larger amount of memory allocated to it than prior. Before setting this to 16384, we would do a “top” and MySQL was using less than 1GB of RAM. After resetting it, MySQL used 15GB of RAM. This may not sound like a good thing, but it doesn’t cause any problems to our experience. The MySQL program allocated the memory, but doesn’t use it unless needed.
● Setting 5: innodb_thread_concurrency: By default, this is 8 for a huge configuration. We put the count to 16 and saw much better CPU usage results on “top”.
After making these changes, we experienced substantial improvement in MySQL performance. For anyone with a very large dedicated server for MySQL trying to get the most out of their hardware, check out the entire my.cnf file at http://www.mkadvantage.com/apps-and-tools. Download the file called MySQL my.cnf SUPER HUGE Example. And best of luck.
Christopher Schaft