MYSQL Tune up

[matomoto]

Hi there
can someone advice me on increasing MySQL Server performance

i know that i need to modify the my.cnf file or something

hope to hear form you guys on some guides or examples

[Rock]

Hi Matamoto,

Do you have any control panel installed on your VPS Hosting? If it's cPanel or Plesk,we can provide you with few tips on getting 100% out of MySQL..

[matomoto]

yeap
we have cpanel installed with our VPS Hosting

[eUKShane]

Hey,

Yes, MySQL Server provides a configuration file located in /etc/my.cnf. From here you can set all of the memory, table, and connection limits as well as a host of other options.

You can get the default buffer sizes used by the mysqld server with this command:

root@VPS Hosting [~]# mysqld --help
This command produces a list of all mysqld options and configurable variables.

If there is a mysqld server currently running, you can see what values it actually is using for the variables by executing this command:

root@VPS Hosting [~]# mysqladmin variables

MySQL uses algorithms that are very scalable, so you can usually run with very little memory. If you, however, give MySQL more memory, you will normally also get better performance.

When tuning a MySQL server, the two most important variables to use are key_buffer_size and table_cache.

When you have installed MySQL, the `support-files' directory will contain some different `my.cnf' example files, `my-huge.cnf', `my-large.cnf', `my-medium.cnf', and `my-small.cnf', you can use as a base to optimize your system.

Note : As you are having cPanel then these files should be located in /usr/share/mysql/ directory.

If there are very much connections, “swapping problems'' may occur unless mysqld has been configured to use very little memory for each connection. mysqld performs better if you have enough memory for all connections, of course 

You can use following my.cnf, most of our VPS Hosting customers are using same and they are very much happy with it 

[mysqld]
max_connections = 100
key_buffer = 16M
myisam_sort_buffer_size = 32M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 10
wait_timeout = 10
connect_timeout = 10
max_allowed_packet = 1M
max_connect_errors = 10
query_cache_limit = 1M
query_cache_size = 10M
query_cache_type = 1
tmp_table_size = 16M
skip-innodb

[mysqld_safe]
open_files_limit = 8192

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer = 32M
sort_buffer = 32M
read_buffer = 16M
write_buffer = 16M

[mysqlhotcopy]
interactive-timeout

Actually this also depends on your VPS Hosting / server configuration. If you are having 1 GB or more RAM then you can increase these parameters.

There are few ready scripts (e.g. ELS) which can be used for MySQL Server optimization. You just have to google :P

[mauriciocb]

i've been reading this and seems very useful, but i still don't know which parameters to increase if I have lets say, 16Gb of ram and 4 cpu's on my vps.
i would really appreciate it if you help me out here!
thanks

[Cristiano]

i've been reading this and seems very useful, but i still don't know which parameters to increase if I have lets say, 16Gb of ram and 4 cpu's on my vps.
i would really appreciate it if you help me out here!
thanks

Parameters as quoted below will give you a idea depending on the RAM and CPU of the server.

query_cache_size=64M ## 32MB for every 1GB of RAM
key_buffer=256M ## 128MB for every 1GB of RAM
sort_buffer_size=2M ## 1MB for every 1GB of RAM
read_buffer_size=2M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=2M  ## 1MB for every 1GB of RAM
thread_concurrency=8 ## Number of CPUs x 2

[mauriciocb]

Parameters as quoted below will give you a idea depending on the RAM and CPU of the server.

query_cache_size=64M ## 32MB for every 1GB of RAM
key_buffer=256M ## 128MB for every 1GB of RAM
sort_buffer_size=2M ## 1MB for every 1GB of RAM
read_buffer_size=2M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=2M  ## 1MB for every 1GB of RAM
thread_concurrency=8 ## Number of CPUs x 2

thanks a lot Cristiano! i'll test those configs and i'll come back to you! i don't have clear the settings after [ ]

[mysqld_safe]

[mysqldump]

[myisamchk]

[mysqlhotcopy]

what are this for??

and in my DB I only use MyIsam, just to let you know!

[D@@in@]

Please check the function of the following mysql variables.
mysqld_safe is a server startup scripts.
mysqldump make database backups
myisamchk performs table maintenance operations
mysqlhotcopy program support option files.