How heavy is your usage of TEMPORARY TABLES? I don't use them much
I'm sure that the others on the list will have something to say in that
To get a better look at MySQL's usage of memory, you could try looking
at the output of
SHOW STATUS .
Markus Fischer wrote:
> 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
> relevant pieces):
>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,
> - Markus