I'm investigating a performance problem with mysql server set up.
The server is running linux with 1GB ram. I'ld like to tune the
configuration of the server to use as much RAM as possible without
swapping to the disc because of the big slow down.
The current configuration is as follows (I've just pasted the
set-variable = key_buffer=16M
set-variable = max_allowed_packet=200M
set-variable = thread_stack=128K
bind-address = 127.0.0.1
port = 3306
set-variable = query_cache_type=1
set-variable = query_cache_size=64M
set-variable = tmp_table_size=50M
The main purpose of the database is a heavily modified forum
application based on vBulletin.
One of the biggest table is the 'post' table with consumes about
617M currently; this table is often used in queries for index pages
which are the most frequent visited pages; indexes for the relevant
column in the WHERE statement are all created. The next tables in
size are using 22M and most of them much less.
I'm also having problems in determining accurately how much memory
MySQL uses when running on the system; the information provided by
ps doesn't seem relieable due the many processes/threads. Getting
the currently used memory and whether MySQL needed to swap would be
very valueable in chasing down this issue.
I'm also not sure whether the database is swapping temporary tables
to the disc or not; is there a way to verify if this is happening?
Other beneficial things would be to know how much memory certain
queries need (mostly the queries on index pages).
Besides more physical memory, are the other ways to better tune the
server for the 1GB ram? What performance analyses tool are
recommended for use with MySQL?
thanks for any advice,