5

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. – Tiago Gouvêa Jan 23 '11 at 1:29
5

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

3

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. – Tiago Gouvêa Jan 23 '11 at 12:46
2

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.

0

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? – 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
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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