Tuesday, September 9, 2008

Plesk MySQL Performance Settings

One of the servers I was working on recently had a Plesk control panel installed on it, which as it turns out can greatly effect MySQL performance. By default, the client database settings that Plesk uses for MySQL limit the resources allocated to your clients. This can be good or bad. If you are in the web hosting business and don't want your clients taking your server down with heavy MySQL usage this is probably a good thing. However, if you are running the server as a dedicated web application server and want all of the power you can get for your app, as I am, this is a bad thing.

After a lot of tracing, I found that my web application was experiencing terrible MySQL performance. I did a bunch of research and found a MySQL configuration that solved all of my performance issues. The key to getting good MySQL performance on a Plesk controlled server is to tweak the client MySQL settings.

MySQL my.ini Performance Configuration

I found the following MySQL configuration settings solved all of my performance issues. (Make sure you edit the client configuration, see the next section for more on that.)


[MySQLD]
port=3306
basedir=C:\\Program Files\\SWsoft\\Plesk\\Databases\\MySQL
datadir=C:\\Program Files\\SWsoft\\Plesk\\Databases\\MySQL\\Data
default-character-set=latin1
default-storage-engine=INNODB
query_cache_size=64M
table_cache=1024
tmp_table_size=32M
thread_cache=32
myisam_max_sort_file_size=100G
myisam_max_extra_sort_file_size=100G
myisam_sort_buffer_size=2M
key_buffer_size=32M
read_buffer_size=16M
read_rnd_buffer_size=2M
sort_buffer_size=2M
innodb_additional_mem_pool_size=24M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=10M
innodb_buffer_pool_size=64M
innodb_log_file_size=10M
innodb_thread_concurrency=8
max_connections=300
key_buffer=48M
max_allowed_packet=5M
sort_buffer=2M
net_buffer_length=4K
old_passwords=1
[client]
port=3306

My Server Specs:

Intel Dual Core @ 2GHz
2 GB of memory
(You may need to tweak the MySQL configuration settings I posted above if you server specs are drastically different than mine.)

Plesk MySQL Setting Locations

There are two different locations for MySQL settings in Plesk. One location contains a my.ini file that effects any client databases you create. The other location contains a my.ini file that effects Plesk's operation. The important one to focus on here is the Client my.ini.

Client MySQL Settings
C:\Program Files\SWSoft\Plesk\Databases\MySQL\Data\my.ini

Plesk MySQL Settings
C:\Program Files\SWSoft\Plesk\MySQL\Data\my.ini

-Aaron
http://www.churchofficeonline.com

2 comments:

Ryan said...

Would you recommend using this config for my website even though there are a couple other sites being hosted on the same box? I was just wondering if this config would cause the other 2-3 sites to "notice".

Ryan said...

forgot to subscribe...