MYSQL Tune up

[matomoto]

Hi there
can someone advice me on increasing mysql 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? 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

[eUKShane]

Hey,

Yes, mysql 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 [~]# 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 [~]# 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 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 / 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 optimization. You just have to google :P