I recently made changes to optimize performance for InnoDB engine-based DB and soon after when restarting I encounter into the following strange behavior:
- I have some tables with data in them
- I made a change to my.cnf - increased innodb_buffer_pool_size
- I restart MySQL server
- I do 'use database' and perform 'show tables' > all tables are shown
- I do 'select * from some_table_shown_in_the_list' and I get 'Table does not exist'
Seriously... what the hell? I've no idea what's going on...
My my.cnf settings (I am using only InnoDB engine for my tables):
[client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking key_buffer = 1M max_allowed_packet = 512K thread_stack = 128K thread_cache_size = 50 # InnoDB settings innodb_buffer_pool_size=384M innodb_log_file_size=32M innodb_log_buffer_size=2M innodb_flush_log_at_trx_commit=2 innodb_thread_concurrency=8 innodb_flush_method=O_DIRECT innodb_file_per_table query_cache_limit = 0M #query_cache_size = 16M expire_logs_days = 10 max_binlog_size = 100M [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M
What I did was to follow the steps as described at https://stackoverflow.com/questions/3456159/how-to-shrink-purge-ibdata1-file-in-mysql
MySQL version - 5.1.61-0+squeeze1-log
Engines available - MyISAM, InnoDB, MRG_MYISAM, BLACKHOLE, CSV, MEMORY, FEDERATED, ARCHIVE