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
Code:
[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