I have some problems with a phpBB forum for 100 users online where some times some process, (I’m using SHOW PROCESSLIST) take great time to execute, then when more users enter processlist increase, server load increase too and the server crawl.

I think mysql configuration of my host is not adequate, I think they are using default mysql values.

The variable “table_cache” is 64 that I think is too low causing mysql spend much time open and closing tables slowing down the server and the problem is not with my forum , but with MySQL server not configured adequately.



What is “good” configuration for MySQL for server with 1 GIG of RAM?

Is not good idea to enable query cahche with MySQL 4 ?



Some variables that I think is important from SHOW VARIABLES

connect_timeout 5

have_query_cache YES
query_cache_limit 1048576
query_cache_size 0
query_cache_type ON
query_prealloc_size 8192

join_buffer_size 131072
key_buffer_size 8388600

long_query_time 10

max_user_connections 0
max_tmp_tables 32
open_files_limit 2510

table_cache 64

version 4.0.18-standard
wait_timeout 28800

The process of MySQL server optimization is normally a trial and error process for most people. Different hardware and software will react differently to various settings.

There are a number of things that I usually try first though because they generally provide improvements in most every situation:

--skip-external-locking (Only if running one server and never run myisamchk in this mode)

The 2 main ones to get right before going any further are key_buffer_size and table_cache. In your case I would try the following settings before anything else were changed.

--key_buffer_size=256M
--table_cache=200
or even
--table_cache=500

Play with these settings until you get the best performance. I have found it best to keep key_buffer around 1/4 the actual memory installed, some setups actually can make better use of more though so you will have to test it yourself. Just remember you still want to be able to use your other services on the box.

The table cache variable is another one that is especially important when running forums. It will be the one to limit the number of open tables per thread. Try and keep this as high as possible while still maintaining good performance. Continuasly check the server status variables to see how many open tables you have during peek usage times and adjust accordingly.

Once you have those 2 set the way you want then move on to http://dev.mysql.com/doc/mysql/en/Server_parameters.html and read for the rest of the day. Play with your settings and be carefull. ;)

chuck

Thank for your reply. But I’m not on dedicated server , I’m using a shared account and I can not change these parameters.

I think table_cache = 64 is too low, considering that not only my forum but other sites in the server too, and that is the cause of the problem of my forum and not a script with a bug.

Maybe I should try to find a host that have more experience with MySQL and not just use default parameters that I think in not adequate to my site and for whole server.

You can try and contact the company and see if they would optimize MySQL before you go looking for a new hosting company. You never know. They could be very receptive to such requests.

chuck

Do you think this 2001 article still update about MySQL optimization?

http://www.databasejournal.com/features/mysql/article.php/10897_1402311_3

My current stats ( SHOW STATS)

Open_tables 64
Opened_tables 570250
Uptime 223265

I think if I increase table_cache can make some improvements to the server.

Any other article about MySQL optimization that I can use to use to convince my host to at least try to change mysql parameters and see what's happen ?