Can someone with some SQL knowledge verify my SQL my.cnf looks allright? I am trying to fine tune it as most of my websites are SQL intensive. Code: [mysqld] socket=/var/lib/mysql/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 Again, I have very intensive SQL usage on my websites as they are fully SQL driven. I'm not sure how to fine tune the system best. However, after a clean OS install and reinstall of ISPCONFIG (lost my old my.cnf in a crash) I have noticed a horrible slow down in response time of SQL. Hence trying to make the best my.cnf I can. Thanks!
Did you try the "MySQL Performance Tuning Primer Script" from http://www.day32.com/MySQL/ ? It's really good.
That script isn't working. When it asks for the User/Password I assume that is the root user and its password for SQL. I gave it that and it said that it's login failed.
