• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

Help with InnoDB configuration

dbembibre

Active member
#1
Hi to all, im finish my local test and im ready to start with the migration process.
I need a help with the innodb configuration my actual config goes with skip-innodb because all my vb tables are MyISAM. I have experience with MyISAM configuration but no with InnoDB.
Please give me some help with an initial config. I know about percona i compile many times years ago but for the moment i plan to do the migration with my current mysql community edition and later switch to mariadb or percona.My forum have 12 million of posts.

The server is a dedicated DB server with only one database (vbulletin)
  • MySQL 5.5-30
  • Dual Dual Xeon 2.33GHZ (E5410)
  • SCSI 15K RPM Disk
  • 8 GB of RAM
  • CenOS 5.9 64 bits
I planning use 4GB for MySQL and 4GB for elastic search

My current my.cnf

Code:
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
symbolic-links=0
port = 3306
skip-name-resolve
bind-address = 192.168.x.x
#safe-show-database
back_log = 50
max_connections = 650
key_buffer_size =3072M
myisam_sort_buffer_size = 64M
join_buffer_size = 1M
read_buffer_size = 1M
sort_buffer_size = 2M
#table_cache = 8000
table_cache = 10000
thread_cache_size = 512
wait_timeout = 85
#wait_timeout=60
interactive_timeout=175
connect_timeout = 10
tmp_table_size = 64M
max_heap_table_size = 64M
max_allowed_packet = 64M
net_buffer_length = 16384
max_connect_errors = 100000
thread_concurrency = 16
concurrent_insert = 2
#table_lock_wait_timeout = 30
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 6M
query_cache_size = 256M
query_cache_type = 1
query_prealloc_size = 262144
query_alloc_block_size = 65536
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
default-storage-engine = MyISAM
max_write_lock_count = 8

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 8192
[mysqldump]
quick
max_allowed_packet = 64M
[myisamchk]
key_buffer = 1024M
sort_buffer = 16M
read_buffer = 16M
write_buffer = 16M
 

Slavik

XenForo moderator
Staff member
#2
add in

Code:
innodb_flush_method  = O_DIRECT
innodb_log_files_in_group  = 2
innodb_log_file_size  = 256M
innodb_flush_log_at_trx_commit = 2
innodb_file_per_table  = 1
innodb_buffer_pool_size  = 4G