Mysql – How to increase memory usage in MySQL Server to improve speed

iis-7MySQLwindows-server-2008

I have a Windows 2008 server with 8GB of RAM running IIS7 and MySQL. I've been tracking the memory, cpu and disk usage on the server and I found out that MySQL is using only 250MB of RAM, keeping the disks very busy, even though I have plenty of free ram laying around.

In SQL Server I can easily set the amount of memory I want it to use, I am looking for the same setting in MySQL.

How can I configure MySQL to use more memory and reduce the cpu and disk usage?

Best Answer

table_cache is the most useful configuration directive to change. Each time MySQL accesses a table, it loads the table into cache. If you've got a high number of tables, it's faster to have them cached.

Take a look at your server variables by running:

show status;

and have a look for the variable open_tables. If this is the same as your table_cache value, and opened_tables keeps going up, then you need to increase the table_cache value in your configuration file. You'll find a balance by experimenting with these variables during peak times. You want to configure it so that at peak times, there are a low amount of opened_tables even after the server has been up for a long time.

key_buffer_size is also a good variable to experiment with. This variable affects the index buffer size, and making this variable bigger increases MySQL's index handling speed. You can look at the variables with the show variables; command again, and compare key_read_requests to key_reads. Ideally, you want the ratio between these two variables to be as low as possible, and you can do this by increasing the size of the key_buffer_size. If you set this variable higher, you will have less writes and reads directly to and from the disk, which was your main concern.

Related Solutions

Ubuntu – MySQL: How to get the “Maximum possible memory usage” down

You have a server with 256M, but you can't use all of that -- remember there's some OS overhead. Add to that with the fact you're over committing as other folks have mentioned and you'll definitely thrash here. 256M is only enough for a small DB, 20 connections is a lot with what you've got configured.

1) reduce your max connections to 4 (you're using 3 out of 20)

2) optimize your query cache better; 8M is really large, and 64M total is a lot based on your hits/prunes; try a 4/32 combo and see how it goes. Really I think a 2/24 combo would work for you.

3) you have no sorts requiring temp tables, why is that max_heap_table_size verb in there? Comment that out, use the defaults

4) do you actually have 128 tables? Try cutting that table_cache in half to 64 or 48

5) reduce thread_cache_size to 4

6) optimize those tables to reduce fragmenting

Those are some things to start with. It looks like you threw a bunch of numbers in a config without any actual profiling to know what you needed and have created a mess; if all else fails go back to the defaults and get rid of your custom settings and start over using some performance tuning guides you can find on Google. Get the output of SHOW VARIABLES and SHOW STATUS, find any one of a bajillion tuning guides and plug in your actual, real numbers into their equations and that'll tell you the exact-ish numbers you need to put in your config file.

Windows Server 2008 R2 – Troubleshooting Weird Memory Usage

Is this a 64bit server - do you have the lock pages in memory local policy enabled? SQL is likely consuming the rest of your memory If you look at the perfmon counters you will see the memory allocation

Here is an article that explains it in depth

You can also view the counters in SQL

SELECT
    object_name
   ,Counter_name
   ,cntr_value
   ,ROUND(( cntr_value * 8192.0 ) / 1048576, 0) AS cntr_value_MB
FROM
    sys.dm_os_performance_counters
WHERE
    object_Name LIKE '%Buffer Manager%'
    AND RTRIM(counter_name) IN ( 'Free pages', 'Total pages',
                                 'Database pages' ) 
UNION SELECT
    object_name
   ,Counter_name
   ,cntr_value
   ,ROUND(( cntr_value / 1024 ), 0) AS cntr_value_MB
FROM
    sys.dm_os_performance_counters
WHERE
    counter_name IN ( 'Target Server Memory (KB)',
                      'Total Server Memory (KB)' )

Best Answer

Related Solutions

Ubuntu – MySQL: How to get the “Maximum possible memory usage” down

Windows Server 2008 R2 – Troubleshooting Weird Memory Usage

Related Topic