Hello,


I have encountered a problem with MySQL performance. I am dealing with large databases ( > 1GB) so the main problem while executing heavy queries is the disk read speed. Unfortunately, I cannot afford RAID right now, neither the memory amount sufficient for RAMDriving my whole database into memory.

I am executing an average query - "Query A". It takes 90 seconds, mysqld-max-nt eats about 60 MB of RAM and the HDD goes mad. Then I execute it for a second time: no HDD activity, 10 seconds.

Okay. Now I take a heavier one - "Query B". Disk reads, 150 MB of RAM eaten by the server, 400 seconds in execution. Memory stays at 150 MB.
BUT! I launch it for a second time, and: Disk reads, memory _STILL_ stayes at 150 MB of RAM (which lead me to believing that all the required data is in the memory already), 400 seconds in execution.
No caching!

I have played with all kinds of my.cnf (my.ini) parameters, but to no use. 1GB of memory is available to the server, but it just wont use it!!

I have even gone into query caching, but it doesn't help either. It seems that the system refuses to cache any data for "Query B".


What should I do? How do I make mysql use the whole amount of available memory?
I am not caring about how much time the first execution takes, just the second, third, etc.


Thank you,
Eugene

http://www.mysql.com/doc/en/Server_parameters.html

Have read this several times already. As well as google replies for all kinds of "mysql ..." queries.

table_cache, key_buffer - all tuned so that no problems show up in SHOW STATUS. Key reads low, table(s) opened - low. Threads - 2 (single processor), thread_cache - sufficient.

what about limits for mysql user?
try to execute "ulimit -a" and that show you the limits for user

check the following variables:

query_cache_limit 1048576
Meaning: Dont cache results that are bigger than this (default 1M, could be too less for you)

query_cache_size 0
Meaning: The amount of memory (specified in bytes) allocated to store results from old queries. If this is 0, the query cache is disabled (default).

query_cache_type ON
Meaning: This may be set (only numeric) to 0 (OFF), 1 (ON), 2 (DEMAND) ???

more info here
http://www.mysql.com/doc/en/Query_Cache.html

frankn

Current setting are:

query_cache_size = 512M
query_cache_type = 1
query_cache_limit = 512M


My concern is that query caching, while it WILL solve my current problem, is definitely NOT the right solution. It just caches queries that are BINARY equal. But think about it, should mysqld cache the DATA that is required to execute query, but not the result. It SHOULD, and it DOES.
If I set query_cache_size to 0, I still do get bonus performance when executing "query A" for a second time, but not so for "Query B". I would say that there is not enough space for caching "Query B" data, IF I didn't provide mysqld with TONS of memory.