Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2003
    Posts
    5

    Unanswered: MySQL does not use available memory?

    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

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: MySQL does not use available memory?

    Originally posted by refa
    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

  3. #3
    Join Date
    Sep 2003
    Posts
    5
    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.

  4. #4
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    Originally posted by refa
    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

  5. #5
    Join Date
    Feb 2003
    Location
    Nuremberg, Germany
    Posts
    12
    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

  6. #6
    Join Date
    Sep 2003
    Posts
    5
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •