Knowledgebase
Portal Home > Knowledgebase > Articles Database > MySQL configs
MySQL configs
Posted by ProjectAuda, 12-20-2010, 12:42 PM Hello, I just bought a 1G RAM VPS and it works great. Expect, I would need a way to make MySQL run faster, or/and use less resources (Ram/CPU). Anyone got any simple tips/tricks for me? Any tip at all to lower my RAM usage would be appreciated. Using the lastest MySQL server verison.
Posted by indya, 12-21-2010, 03:23 AM Search for my.cnf optimization. You can find different parameters to edit and set up. The best way is to decide based on what applications will be running, and how many users you expect to be having and accessing. Example: [mysqld] socket=/path/to/mysql.sock datadir=/var/lib/mysql skip-locking skip-innodb # MySQL 4.x has query caching available. # Enable it for vast improvement and it may be all you need to tweak. query_cache_type=1 query_cache_limit=1M query_cache_size=32M # max_connections=500 # Reduced to 200 as memory will not be enough for 500 connections. # memory=key_buffer+(sort_buffer_size+read_buffer_size)*max_connections # which is now: 64 + (1 + 1) * 200 = 464 MB # max_connections = approx. MaxClients setting in httpd.conf file # Default set to 100. #max_connections=200 #interactive_timeout=180 interactive_timeout=100 #wait_timeout=180 #wait_timeout=100 # Reduced wait_timeout to prevent idle clients holding connections. #wait_timeout=30 wait_timeout=15 connect_timeout=10 # max_connect_errors is set to 10 by default #max_connect_errors=10 #table_cache=256 #table_cache=1024 # Checked opened tables and adjusted accordingly after running for a while. table_cache=512 #tmp_table_size=32M by default #thread_cache=128 # Reduced it to 32 to prevent memory hogging. Also, see notes below. thread_cache=32 # key_buffer=258M # Reduced it by checking current size of *.MYI files, see notes below. key_buffer=128M # Commented out the buffer sizes and keeping the default. # sort_buffer_size=2M by default. #sort_buffer_size=1M # read_buffer_size=128K by default. #read_buffer_size=1M # 1Mb of read_rnd_buffer_size for 1GB RAM — see notes below. # read_rnd_buffer_size=256K by default. #read_rnd_buffer_size=1M # myisam_sort_buffer_size used for ALTER, OPTIMIZE, REPAIR TABLE commands. # myisam_sort_buffer_size=8M by default. #myisam_sort_buffer_size=64M # thread_concurrency = 2 * (no. of CPU) thread_concurrency=2 # log slow queries is a must. Many queries that take more than 2 seconds. # If so, then your tables need enhancement. log_slow_queries=/var/log/mysqld.slow.log long_query_time=2 [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid open_files_limit=8192 [mysqldump] quick max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [myisamchk] key_buffer=64M sort_buffer=64M read_buffer=16M write_buffer=16M [mysqlhotcopy] interactive-timeout [client] socket=/path/to/mysql.sock
Add to Favourites 
Print this Article
Also Read
