mySQL and memory

I'm not much into the server admin bit so I might just be rambling out of my ass now, but the way I've understood databases such as mySQL is that each request to the database will use a % of the available memory.

Does this mean that as you increase the physical memory on your machine you lower the % of memory each process is allowed to use because if you don't your memory will just get eaten anyway?

If these guesses are true, are there any suggested memory settings for 1 gig, 2 gig, 4 gig etc of ram?

Thanks

 

What you are hinting is tuning your MySQL server.

There is no 1 config is perfect for every situation, but there are some nice tools that can help you configure your MySQL so that it uses only the necessary memory to run as fast as possible.

When performance tuning any application, there are trade-offs, and you need to understand what every option is and what it does, and the effects of them before changing.

Now what I do to tune a MySQL server is to have a few basic tools, dedicate a bit of time to get it set up right, and then do a quick check every month or so to ensure it is still running fine with no issues.

The tools I choose to use are:
PHPMyAdmin
vpstop with mysqlreport installed and configured,
tuning-primer.sh

By using the reports generated by all of these tools, and doing a few searches of the mySQL manual you can generally get a Server set up to be pretty good within a week or so.

The tuning-primer.sh script is the most valuagble tool -as it makes recommendations as to how you might be able to get better performance.

I have not actually answered your question as what works just fine for 1 person will not be right for you. Each MySQL server should be individually tuned. As a starting point, you can look at the 5 mysql configs that come bundled with it. Even running without any config will work, but for best performance you really do have to put in the time to tweak and tune the config.

 

The main parameters that you can configure for mysql are max_connections key_buffer table_cache sort_buffer thread_stack
innodb_buffer_pool_size innodb_additional_mem_pool_size innodb_autoextend_increment innodb_log_buffer_size
innodb_log_file_size
innodb_open_files

However as the database size grows the performance always slow down, so it is better to keep purging old records.
Increasing memory will not solve all your problem, you need to just configure the parameters properly

key_buffer_size - Very important if you use MyISAM tables. Set up to 30-40% of available memory if you use MyISAM tables exclusively.

innodb_buffer_pool_size This is very important variable to tune if you’re using Innodb tables. Innodb tables are much more sensitive to buffer size compared to MyISAM. MyISAM may work kind of OK with default key_buffer_size even with large data set but it will crawl with default innodb_buffer_pool_size. Also Innodb buffer pool caches both data and index pages so you do not need to leave space for OS cache so values up to 70-80% of memory often make sense for Innodb only installations. Same rules as for key_buffer apply - if you have small data set and it is not going to grow dramatically do not oversize innodb_buffer_pool_size you might find better use for memory available.

innodb_additional_pool_size This one does not really affect performance too much, at least on OS with decent memory allocators. Still you might want to have it 20MB (sometimes larger) so you can see how much memory Innodb allocates for misc needs.

innodb_log_file_size Very important for write intensive workloads especially for large data sets. Larger sizes offer better performance but increase recovery times so be careful. I normally use values 64M-512M depending on server size.

innodb_log_buffer_size Default for this one is kind of OK for many workloads with medium write load and shorter transactions. If you have update activity spikes however or work with blobs a lot you might want to increase it. Do not set it too high however as it would be waste of memory - it is flushed every 1 sec anyway so you do not need space for more than 1 sec worth of updates. 8MB-16MB are typically enough. Smaller installations should use smaller values.


table_cache - Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use. You do not want this to happen so frequently and it is typically best to size your cache so it is large enough to keep most of your tables open. It uses some OS resources and some memory but for modern hardware it is typically not the problem. 1024 is good value for applications with couple hundreds tables (remember each connection needs its own entry) if you have many connections or many tables increase it larger. I’ve seen values over 100.000 used.

thread_cache Thread creation/destructions can be expensive, which happen at each connect/disconnect. I normally set this value to at least 16. If application has large jumps in amount of concurrent connections and I see fast growth of
Threads_Created variable I boost it higher. The goal is not to have threads created in normal operation.

query_cache If your application is read intensive and you do not have application level caches this can be great help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from 32M to 512M normally make sense. Check it however after a while and see if it is well used. For certain workloads cache hit ratio is lower than would justify having it enabled.

 

Thanks

Trying a different configuration now, will see if server holds up better. Doubt it will be enough, simply need more physical memory on the machine.

 

itcareer: great detail, I appreciate it.

anyone have any links to tools out there that will automatically generate rough estimates of what your settings should be based on your hardware, what software you're running (apache & mysql vs just mysql, etc)? know it wouldn't be the most accurate thing in the world but it'd still be nice to have, if it exists out there someone

if it doesn't, someone make a decent one and I'll do what I can to get the word around

 

Damn, didn't work out.

It's my arcade website, I don't understand why but it's using extreme amounts of server resources to handle very few requests (compared to other sites).

 

Do you have the database log ?

 

do "mysqladmin stats." should give you some info.

also try enabling the full logging and/or the slow query log and see where the queries are coming from.

if the content is mostly static, look into caching.