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?

  • It's a dedicated Centos server. Commented Jan 23, 2011 at 1:29

5 Answers 5


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


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.

  • 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. Commented Jan 23, 2011 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.


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.

  • Ok mrdenny.. but, how I setup correctly the server to CONSUME right this memory? Commented Jan 23, 2011 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
    Commented Jan 24, 2011 at 23:27

In my.cnf:


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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