What is the best use of memory in MySQL?

Question

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?

Answer 1

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

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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