Thursday, June 04, 2009

vBulletin, session table is InnoDB


In large vBulletin forum we had strange problem in memory table "session", we've 25M post, 1.7M user, 20K online user.

So we change engine of session table to InnoDB and set configuration of innoDB as follow (be careful this configuration is not proper for other tables because this is good in performance but bad in crash and recovery, and data reliability)

innodb_data_home_dir = /dev/shm/mysql/ #this path in memory partition
innodb_log_group_home_dir = /dev/shm/mysql/
innodb_flush_method = O_DIRECT
innodb_support_xa = 0
innodb_thread_concurrency = 20
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 50
innodb_flush_log_at_trx_commit = 0
innodb_doublewrite = 0

thanks

any comment?

UPDATE:


In this configuration when restart MySQL in error log will see:

090620 1:20:06 InnoDB: Failed to set O_DIRECT on file /dev/shm/mysql/ibdata1: OPEN: Invalid argument, continuing anyway
090620 1:20:06 InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662
090620 1:20:06 InnoDB: Failed to set O_DIRECT on file /dev/shm/mysql/ibdata1: OPEN: Invalid argument, continuing anyway
090620 1:20:06 InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL Bug#26662

So may be a bug with tmpfs and O_DIRECT!!!

see http://bugs.mysql.com/45671

1 comment: