help tuning a DB installation
Hi. I'm wondering if anyone can help me tune this database so it runs better on my hardware. I've made some attempts, but either they've made it worse or not changed anything. Changing the database design itself has shown the most improvement, but I'd still like to know how to tune things on the db side. I understand from the manual that he two most important variables to configure are key_buffer_size and table_cache, but HOW to do this is another question. The machine is a 1.2 GHz Celeron, with 512 MB of RAM and an IDE disk. The database typically has about 40-50 open connections, most of them in state Sleep, but with a few reads and writes, and two slaves replicating from it. Top looks like this: PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 15899 mysql 26 10 45004 12M 3864 S N 35.1 2.4 98:58 0 mysqld There is typically about 32M of swap being used by mysql. I can see that the HD light is almost always on. Here, finally, is the salient bit of the my.cnf file: [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16K max_allowed_packet = 1M table_cache= 4 sort_buffer_size = 128K net_buffer_length = 2K thread_stack = 64K set-variable = max_connections=750 set-variable = default-character-set=cp1251 log-bin server-id = 4 Thanks in advance for any advice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help tuning a DB installation
Jim wrote: Hi. I'm wondering if anyone can help me tune this database so it runs better on my hardware. I've made some attempts, but either they've made it worse or not changed anything. Changing the database design itself has shown the most improvement, but I'd still like to know how to tune things on the db side. I understand from the manual that he two most important variables to configure are key_buffer_size and table_cache, but HOW to do this is another question. The machine is a 1.2 GHz Celeron, with 512 MB of RAM and an IDE disk. [...] Top looks like this: PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 15899 mysql 26 10 45004 12M 3864 S N 35.1 2.4 98:58 0 mysqld What's the output of free? I assume there are no other applications running on that server except mysql and you are using myisam-tables? increase key_buffer_size *a lot*. You have 512megs of RAM that mysql could use, but it's only using 2.4% of that. You should leave some RAM for the OS and its caching, but I'd start setting key_buffer_size to 300megs. And change table_cache to 64 or more - depending on the number of concurrent connections. Your table_cache is set to 4, that means all mysql-processes may have only 4 tables open at a time - in total! have a look at the docs at mysql.com, they explain what the variables mean and how to find out how useful your settings are. Looking at Your settings I'd say your server spends most of the time waiting for the disc since it does no caching (increase key_buffer_size so mysql can cache its indexes in memory) and the processes/clients are waiting to open a table since all of them may only have 4 of them open at a time... Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help tuning a DB installation
Hi Jim, First of all, you should assingn a greater value for the key_buffer_size variable than 16k. This should help to reduce the readings on your hd. You should consider to set up the query cache. Best regards Alejandro -Mensaje original- De: Jim [mailto:[EMAIL PROTECTED] Enviado el: Martes, 03 de Agosto de 2004 01:36 p.m. Para: [EMAIL PROTECTED] Asunto: help tuning a DB installation Hi. I'm wondering if anyone can help me tune this database so it runs better on my hardware. I've made some attempts, but either they've made it worse or not changed anything. Changing the database design itself has shown the most improvement, but I'd still like to know how to tune things on the db side. I understand from the manual that he two most important variables to configure are key_buffer_size and table_cache, but HOW to do this is another question. The machine is a 1.2 GHz Celeron, with 512 MB of RAM and an IDE disk. The database typically has about 40-50 open connections, most of them in state Sleep, but with a few reads and writes, and two slaves replicating from it. Top looks like this: PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 15899 mysql 26 10 45004 12M 3864 S N 35.1 2.4 98:58 0 mysqld There is typically about 32M of swap being used by mysql. I can see that the HD light is almost always on. Here, finally, is the salient bit of the my.cnf file: [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking key_buffer = 16K max_allowed_packet = 1M table_cache= 4 sort_buffer_size = 128K net_buffer_length = 2K thread_stack = 64K set-variable = max_connections=750 set-variable = default-character-set=cp1251 log-bin server-id = 4 Thanks in advance for any advice. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
