Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a MySql server with 2Gb of memory. What's the best configuration to use then with best results? How can I "distribute" this memory fine to MySql server?

share|improve this question
It's a dedicated Centos server. – Tiago Gouvêa Jan 23 '11 at 1:29
Can you post your table definitions? Also, how much data you have in indexes, MyISAM, and InnoDB. – Chris Henry Jan 23 '11 at 1:37
up vote 5 down vote accepted

This strongly depends on the application using the database. A good starting point for optimization is the MySQL Performance Tuning Primer Script

share|improve this answer
Thanks ddeimeke! It's fine! – Tiago Gouvêa Jan 23 '11 at 12:52

You can alter some values in your /etc/my.cnf file.

Some important ones are innodb_buffer_pool_size (assuming you are using InnoDB) and also query_cache_size. You should try to set the innodb_buffer_pool_size to 10% larger than the size of your data if you can. The default of 8MB really is woeful for performance.

share|improve this answer
The software are a big intranet. Is a InnoDb database, having near 70 tables. The larger table have 4,243,453 records (281.9 MiB), it's a relation table. But other tables have no more than 30,000 records (60.9 MiB). The queries are good at this time and I just want to "consume" fine the memory. – Tiago Gouvêa Jan 23 '11 at 12:46

If all your tables are InnoDB and the server is only used for databases, I'd set the InnoDB buffer pool (innodb_buffer_pool_size) to 3/4 of the system memory (1.5GB).

This is the pool of memory MySQL will use to cache both data and indexes of InnoDB tables. The larger it is, the more of your data and indexes can be kept in RAM, rather than read from disk when a query needs them.

share|improve this answer

RAM is cheap. Buy more memory. With smaller databases you want at least enough memory to hold the entire database in memory. With larger databases this isn't possible (you can't get 10 TB of memory in a single server for a data warehouse for example), but for smaller databases this shouldn't be a problem.

share|improve this answer
Ok mrdenny.. but, how I setup correctly the server to CONSUME right this memory? – Tiago Gouvêa Jan 23 '11 at 23:14
On MySQL I'm not the right person to ask. For SQL Server I could tell you, but I can't speak to anything more than generalities for MySQL (otherwise I would have). – mrdenny Jan 24 '11 at 23:27

In my.cnf:

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.