You need to figure out what's slowing down your application. It could be expensive queries which in turn could be caused by missing indexes. It could be that the machine is too slow or the configuration of MySQL is sub-optimal.
In this case, the machine looks fine. I can't comment on queries or indexes. Your tuning, I can. set-variable = key_buffer=512M This one is important; it caches data from the database in memory. How big is your database? Is it bigger than 512 meg? If so, is there free memory on the machine? If so, I'd recommend bumping it up as much as you can; it's faster to get data from memory than it is from disk. set-variable = sort_buffer=16M This is used to sort the results of queries; it's per-connection. I believe it is allocated on an as-needed basis. Regardless, are you doing a lot of ORDER BY/GROUP BY in your queries? This might be a little high. set-variable = table_cache=1024 How many tables do you have? This tells the database how many tables to keep open. It probably won't make much of a difference lowering it. set-variable = join_buffer=8M This one is used to join tables where no indexes exist. In otherwords, if you're using indexes, it won't be used. set-variable = record_buffer=8M This is used for reading in rows after a sort (from the sort_buffer). Again, per client. Do you need it? set-variable = query_cache_size=6M This one can be a waste of memory, or a huge bonus. Queries and their result sets are stored here. If you need to run the same query a second time, the database just pulls the results from the cache. There are a few issues, tho. If you have a query, "SELECT * FROM table_1 WHERE condition_1 = 12" then the result of the query will be stored. But if the next statement is, "UPDATE TABLE table_1..." then the data in the cache relating to table_1 have to be unloaded, as the UPDATE statement could have invalidated all of it. But, if you have some stock queries that constantly read data from tables that, in the business logic of your application, are read-only (ie you rarely, if ever, update the data in them), then the query cache can be a big bonus. Another interesting note is the query_cache_type variable. You can set it to 2 in the my.cnf file; this means that in your select statements, you add a hint to tell the database to cache or not cache the results of the query. So if you know a query and it's results are very dynamic, then it's not much use to use the query cache and you can tell the database to not put it in the query cache. The SELECT statement would look like, 'SELECT SQL_CACHE * FROM table_1 WHERE...'. Here's the page: http://www.mysql.com/doc/en/Query_Cache.html Next, moving to InnoDB. I found that, properly tuned, InnoDB is almost as fast as MyISAM. You'll want to set the innodb_buffer_pool_size as large as possible (it's the MyISAM key_buffer) to cache as much data. You'll need to set up a tablespace; you can put them all over your disks. Finally, play with innodb_flush_method. I set it to O_DSYNC and got a substantial increase in performance. Search the MySQL list-archives for discussions on the options, or check out http://www.innodb.com Hope that helps, David ----- Original Message ----- From: "Rainer Sip" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Tuesday, October 21, 2003 9:48 AM Subject: Mysql Performance Question I'm running a community site (Xoops) on Mysql 4.0.14. I found that the speed of my site is slow during peak hours, when there are 450 concurrent uers hanging on the site. Mytop showed that the queries per second maxed at 500. I believe this could be higher, provided that I have it running on a dedicated machine. I also noticed the load average is very high (12+ during peak hours) In the mid run I'm planning to mirgrate to innodb for higher concurrency (I'm currently using myisam). However, I'm seeking suggestions in fine tuning the parameters. The machine is a Dell PowerEdge 4400 with 2 Xeon 1G processors and 2GB of memory. There are 4 disks running raid 0+1. Attached the my.cnf for your easy review. Thanks a lot in advance. Cheers, Rainer [client] socket=/var/lib/mysql/mysql.sock [client] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-locking set-variable = key_buffer=512M set-variable = max_allowed_packet=1M set-variable = table_cache=1024 set-variable = sort_buffer=16M set-variable = join_buffer=8M set-variable = record_buffer=8M set-variable = thread_cache=512 set-variable = max_connections=500 set-variable = tmp_table_size=128M set-variable = thread_concurrency=4 set-variable = myisam_sort_buffer_size=512M set-variable = query_cache_size=6M #set-variable = max_connect_errors=1000 #set-variable = back_log=100 #log-bin skip-innodb [mysql.server] user=mysql basedir=/var/lib [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick set-variable = max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable = key_buffer=256M set-variable = sort_buffer=124M set-variable = read_buffer=2M set-variable = write_buffer=2M [myisamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=8M set-variable = write_buffer=4M [mysqlhotcopy] interactive-timeout -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
