0

I just get a Dedicated Server running UBUNTU and I need to optimize the variables in my.cnf for Drupal 7.

I understand that this is tailed job. Then Is there any tool to help me to do this? I am a newby at this.

I found this article (which seems a little outdated). It shows some values for a server with 500mb of RAM.

My server has 16Gb of RAM. I guess is not as ease as to multiply those values by 32...?

I use InnoDB tables.

For a setup with 500mb of RAM your my.cnf file may look like this:

[mysqld]
max_connections = 150
max_user_connections = 150
key_buffer = 36M
myisam_sort_buffer_size = 64M
join_buffer_size = 2M
read_buffer_size = 2M
sort_buffer_size = 3M
table_cache = 1024
thread_cache_size = 286
interactive_timeout = 25
wait_timeout = 1800
connect_timeout = 10
max_allowed_packet = 16M
max_connect_errors = 1000
query_cache_limit = 1M
query_cache_size = 16M
query_cache_type = 1
tmp_table_size = 16M
innodb-flush-log-at-trx-commit=2
1

Your config looks pretty good. If you have 16 GB of ram and it is a dedicated server for Mysql, then I would bump innodb to 8GB. Mysql will also need to "warm up" which is the time is takes to start caching the most frequently used tables. Depending on your application, query cache might not be useful. (I would turn if off). Make sure to turn on Slow Query log, set long query to 5 sec. If nothing shows up after a day or two, set long query to 3 sec. with a 8GB innodb_buffer, you should be able to handle a database size > 20GB. You can always increase the innodb buffer pool but it will require a restart.

slow-query-log          = 1
slow-query-log_file     = /mysqldb/logs/.hostname..-slow-query.log
long_query_time         = 5
log_slow_verbosity      = full   
0

There is no magic file configuration, every server has diferent specifications of load, users, type of use, size of the database and a lot of things.

Although there are some tools like mysqltuner http://mysqltuner.com/ that play safe with some obvious assumptions. Use it like a hint provider, not like the one and real Truth.

And some hints about your config:

The most important parameter to change using InnoDB is the innodb_buffer_pool_size, the bigger it is the less I/O you have, even I have seen recomendations to 80% of the total memory (probably you dont need that much)

Some people see improvement disabling the query_cache in machines with multiple cores.

innodb-flush-log-at-trx-commit=2 its a risky option, you have the possibility to lose 1 second of transactions. I think the 1 option its safer and probably there are other things to change before that.

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.