Description:
I've huge vbulletin System, i convert session table to InnoDB (it was in MEMORY but
change it because lock problem) till now it's OK, Then for better performance change
InnoDB configuration from disk to tmpfs (better performance and don't care data
integrity).
mysql> show global variables like 'inn%';
+---------------------------------+------------------------+
| Variable_name | Value |
+---------------------------------+------------------------+
| innodb_adaptive_hash_index | ON |
| innodb_additional_mem_pool_size | 20971520 |
| innodb_autoextend_increment | 8 |
| innodb_autoinc_lock_mode | 1 |
| innodb_buffer_pool_size | 536870912 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:10M:autoextend |
| innodb_data_home_dir | /dev/shm/mysql/ |
| innodb_doublewrite | OFF |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 0 |
| innodb_flush_method | O_DIRECT |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 67108864 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /dev/shm/mysql/ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_rollback_on_timeout | OFF |
| innodb_stats_on_metadata | ON |
| innodb_support_xa | OFF |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 20 |
| innodb_thread_sleep_delay | 10000 |
+---------------------------------+------------------------+
34 rows in set (0.00 sec)
NOTE: /dev/shm/mysql/ is tmpfs
and when start MySQL we get in error log:
090620 1:19:58 [Note] /usr/sbin/mysqld: Normal shutdown
090620 1:19:58 [Note] Event Scheduler: Purging the queue. 0 events
090620 1:20:03 InnoDB: Starting shutdown...
090620 1:20:05 InnoDB: Shutdown completed; log sequence number 5 2783614325
090620 1:20:05 [Warning] Forcing shutdown of 1 plugins
090620 1:20:05 [Note] /usr/sbin/mysqld: Shutdown complete
090620 01:20:05 mysqld_safe mysqld from pid file /data/mysql/host.com.pid ended
090620 01:20:05 mysqld_safe Starting mysqld daemon with databases from /data/mysql
090620 1:20:05 [Warning] The syntax '--log' is deprecated and will be removed in MySQL
7.0. Please use '--general_log'/'--general_l
og_file' instead.
090620 1:20:05 [Warning] The syntax '--log_slow_queries' is deprecated and will be
removed in MySQL 7.0. Please use '--slow_query_l
og'/'--slow_query_log_file' instead.
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
090620 1:20:06 InnoDB: Started; log sequence number 5 2783614325
090620 1:20:06 [Warning] 'user' entry 'root@host.com' ignored in --skip-name-resolve
mode.
090620 1:20:06 [Warning] 'user' entry '@host.com' ignored in --skip-name-resolve mode.
090620 1:20:06 [Note] Event Scheduler: Loaded 0 events
090620 1:20:06 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.34-community-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL
Community Server (GPL)
I see Bug#26662, but it's referent case.
How to repeat:
when start MySQL
InnoDB: Failed to set O_DIRECT on file /dev/shm/mysql/ibdata1: OPEN: Invalid argument,
continuing anyway
InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL
Bug#26662
InnoDB: Failed to set O_DIRECT on file /dev/shm/mysql/ibdata1: OPEN: Invalid argument,
continuing anyway
InnoDB: O_DIRECT is known to result in 'Invalid argument' on Linux on tmpfs, see MySQL
Bug#26662
