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
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.
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.