1

So, I'm trying to set up a Drupal 7 my.conf file that's combining best practices from various performance blogs. I'm realizing though that some of them are older than others, and many aren't assuming InnoDB. So of this list of configs, which are irrelevant if you're building for InnoDB. 

[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
open-files-limit = 4096

[mysqld]
port = 3306
user = mysql

default_storage_engine
default-storage-engine = InnoDB
socket = /var/run/mysqld/mysqld.sock
pid_file = /var/run/mysqld/mysqld.pid
basedir = /usr
tmpdir = /tmp
lc-messages-dir = /usr/share/mysql
local-infile = 0
automatic_sp_privileges = 0
safe-user-create = 1
secure-auth = 1
secure-file-priv = /tmp
symbolic-links = 0
key_buffer_size = 32M
myisam-recover = BACKUP,FORCE
concurrent_insert = 2
max_allowed_packet = 16M
max_connect_errors = 1000000
datadir = /var/lib/mysql
tmp_table_size = 64M
max_heap_table_size = 64M
query_cache_type = 1
query_cache_size = 0
query_cache_limit = 8M
query_cache_min_res_unit = 1K
default-storage-engine = InnoDB
thread_stack = 256K
thread_cache_size = 128
max_connections = 128
open_files_limit = 65535
skip-locking
skip-bdb
server-id  = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog_cache_size = 256K
sync_binlog  = 256
expire_logs_days  = 14
max_binlog_size = 1G
binlog_do_db  = include_database_name
binlog_ignore_db = include_database_name
max_user_connections = 150
key_buffer = 16M
key_cache_block_size     = 4K
bulk_insert_buffer_size  = 8M
innodb_buffer_pool_size  = 6G
myisam_sort_buffer_size  = 64M
join_buffer_size = 8M
read_buffer_size = 2M
sort_buffer_size = 3M
read_rnd_buffer_size = 64M
table_cache = 4096
table_definition_cache = 4096
table_open_cache = 16384
optimizer_search_depth   = 4
collation-server = utf8_general_ci
interactive_timeout = 400
wait_timeout = 300
connect_timeout = 10
thread_concurrency=8
back_log = 2048
open-files = 10000
query_prealloc_size = 65536
query_alloc_block_size = 131072
Mike Gifford
  • 131
  • 4
  • You are missing innodb buffer pool size. This is where you allocate around 80% of your memory. This is the heart of innodb and alot of performance is hidden there. Please make it part of your config if not already. Thanks. Masood – Masood Alam Mar 31 '13 at 17:41
  • This must have been a cut/paste error. I have innodb_buffer_pool_size in the original list. Thanks for pointing that out though!

    I added it into the version above.

     – Mike Gifford Apr 03 '13 at 18:36

1 Answers1

1

Please remove or change the following:

skip-locking
skip-bdb
binlog_do_db  = include_database_name
binlog_ignore_db = include_database_name
default_storage_engine
default_storage_engine = InnoDB
thread_concurrency=8
sync_binlog=256
bulk_insert_buffer_size = 8M

Here is why:

  • binlog_do_db and binlog_ignore_db are only for including/bypassing the recording of SQL targeted for specific databases.
  • skip-bdb is obsoloete since the BDB storage engine is no longer include in MySQL 5 starting with MySQL 5.1
  • If you install MySQL 5.5, InnoDB is the default storage engine. No need to include it in my.cnf
  • thread_concurrency can go since it only works with Solaris
  • sync_binlog does an OS flush of binlog changes every 256 binlog writes. Increase this if any long write bottlenecks manifest themselves
  • Your bulk_insert_buffer_size is set to the default. That needs to be way bigger (at least 256M). This would accommodate bulk loading of rows from LOAD DATA INFILE or heavily grouped row from the loading of mysqldumps.

There is so much to add based on the info in Drupal 7 Uses InnoDB, so do I need other configs?

Community
  • 1
RolandoMySQLDBA
  • 182,700
  • 33
  • 317
  • 520
  • Those are great answers, thanks!

    I also appreciate you linking the posts.

     – Mike Gifford Apr 01 '13 at 19:31
  • Why did you want to remove skip-locking?

    What is a good default number for sync_binlog? Or is it just good to monitor write speeds and increase it to see if it affects performance?

    Increasing the size of bulk_insert_buffer_size in the default isn't a problem, but would be good to have some more data to reference for bulk loading of rows from LOAD DATA INFILE.

     – Mike Gifford Apr 03 '13 at 18:28