Take the 2-minute tour ×
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. It's 100% free, no registration required.

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
add comment

5 Answers

up vote 4 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
add comment

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
add comment

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
add comment

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
add comment

In my.cnf:

innodb_additional_mem_pool_size=32M
innodb_flush_log_at_trx_commit=2
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=8M
innodb_buffer_pool_size=768M
innodb_thread_concurrency=8
innodb_autoinc_lock_mode=2
innodb_io_capacity=400
innodb_read_io_threads=16
innodb_write_io_threads=8
share|improve this answer
add comment

Your Answer

 
discard

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.