Hi Rainer, You might get more improvement by optimizing your application and queries than by tuning hardware or MySQL. :-)
About switching to InnoDB, are you doing lots of writes that are causing locked tables? e.g. What's the ratio of Table_locks_immediate to Table_locks_waited in SHOW STATUS? If you aren't doing lots of UPDATEs or DELETEs that cause locking problems, InnoDB won't really give "higher concurrency." Remember, INSERTs aren't usually a problem, since SELECTs can run at the same time with MyISAM as long as there's no free space in the data file. Anyway, about your my.cnf. I don't think it's as important as people make it that the key_buffer be so large. If it's unnecessarily big, it's taking away free memory that the OS could use to cache *file* data (key_buffer only caches indexes, not the data file). It's a lot faster to read indexes "from disk" (they may be still cached by the OS anyway if not by MySQL) than it is to read data rows! I don't know how big your indexes are and if only the same small portions are usually used or the whole thing? In other words, no matter the size of the indexes, how much of them is actually accessed? If it's less than key_buffer size, key_buffer is too big. e.g. If Key_blocks_read is not much more than Key_blocks_used, key_buffer is too big. Of course, Key_blocks_read will go up if a table is closed/flushed and indexes are reloaded, so this could be artificially high. Again, I don't know how much of your indexes are frequently used, but a key_buffer of 64-128M is probably plenty. If Key_reads divided by Key_read_requests is less than 0.005-0.01, it's definitely OK. Also, right now, MySQL uses a global mutex lock or something in the key buffer (e.g. exclusive lock even when *reading* keys!) which hurts MyISAM concurrency. I believe the new rewritten key cache system is implemented in version 4.1.1, which should be out within a month. Sounds like good news. Is your table_cache always full (Open_tables is 1024?) and Opened_tables status variable "high?" If so, you may want to increase the table_cache to 1536 or something. For sort, join, and record/read buffers, I don't know if the full amount is allocated right away, or as needed up to the limit. Anyone know? If it's all at once, the system may be allocating and releasing too much memory all the time. See http://jeremy.zawodny.com/blog/archives/000034.html Why do you have thread_cache_size set to 512 when max_connections is only 500? :-) I think you should set thread_cache_size to a little less than the amount of clients that are usually connected. tmp_table_size seems a bit big. query_cache_size seems a bit small, assuming you have lots of queries that could be cached. In MySQL 4, skip-locking is the default; "set-variable =" syntax is deprecated; record_buffer is now read_buffer; and it's mysqld_safe instead of safe_mysqld. And thread_concurrency only applies to Solaris, BTW. Without knowing about your workload, you might try something like this for your my.cnf: [client] socket=/var/lib/mysql/mysql.sock [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock skip-innodb max_connections=500 max_allowed_packet=1M key_buffer_size=96M sort_buffer_size=2M join_buffer_size=1M read_buffer_size=1M read_rnd_buffer_size=1M table_cache=1024 thread_cache_size=64 tmp_table_size=48M myisam_sort_buffer_size=512M query_cache_size=32M query_cache_limit=2M #max_connect_errors=1000 #back_log=100 #log-bin [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid open-files-limit=8192 [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=384M set-variable = read_buffer=16M set-variable = write_buffer=16M [myisamchk] set-variable = key_buffer=256M set-variable = sort_buffer=384M set-variable = read_buffer=16M set-variable = write_buffer=16M [mysqlhotcopy] interactive-timeout Hope that helps. Matt > ----- Original Message ----- > From: "Rainer Sip" > 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]
