InnoDB tablespace, single Vs. multiple, and InnoDB defragment

The ibdata file is too big 10GB, and actually we've only about 2GB (data+index) in innodb storage engine.

How we can defragment this file and reduce it?

How is this happened?

By default the ibdata file created initially by (innodb_data_file_path = ibdata1:10M:autoextend) and auto extended by (innodb_autoextend_increment = 8MB) when it’s needed, and this file (tablespace) contain all innodb tables (innodb_file_per_table=OFF) single tablespace.

In this configuration the file will too big, especially when u need to test something in innodb engines and create a lot of innodb tables.

another problem if you truncate or delete all or some data the ibdata file will not decrease size, and when optimize the table the data part of this table will optimize but still there are gaps between tables in tablespace.


In my case:

ibdata file in 10GB and the total sum of (data_length+index_length) of all innodb tables not exceed 2GB!!!!!!!!!!

OS and I/O manipulate with 10GB file but we need only 2GB in worst case!!!!!

If we set innodb_file_per_table=ON then alter all innodb tables, mysql will generate ibdata for each table with its size, its good!!!, but the shared tablespace still exist (10GB) and cannot delete it (it’s necessary even innodb_file_per_table enable or disable) BAD!!!!!!


The Solution:

1- Get all innodb tables in your database:
Select table_name, table_schema from information_schema.tables where engine = 'innodb';

Convert all to myisam by using: alter table table_name engine myisam;
2- Then shutdown mysql.

Till now ibdata file same size 10GB

3- Rename or move to another directory ibdata and ib_log files.
Check this global variables innodb_data_home_dir and innodb_log_group_home_dir to know where are there.

4- Add innodb_file_per_table to cnf file.

5- Then start mysql

mysql will create ibdata file with default size 10MB, and ib_logfile0 and ib_logfile1 (logs).

6- Return back tables to innodb by using: alter table table_name engine innodb;

mysql will create file for each table in database directory.


See this scenario:

My.cnf
innodb_additional_mem_pool_size = 128M
innodb_buffer_pool_size = 2G
innodb_log_buffer_size = 16M
innodb_log_file_size = 16M
innodb_file_per_table

note:
this [user@server~]$ in shell command
and this mysql> in mysql command
in sequence order

mysql> create table musers_test like musers;
Query OK, 0 rows affected (0.02 sec)

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 432K Jun 16 06:28 /var/lib/mysql/test_db/musers_test.ibd

mysql> insert into musers_test select * from musers limit 500000;
Query OK, 500000 rows affected, 1 warning (1 min 12.43 sec)
Records: 500000 Duplicates: 0 Warnings: 0

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:31 /var/lib/mysql/test_db/musers_test.ibd

mysql> delete from musers_test;
Query OK, 500000 rows affected (1 min 26.64 sec)

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:34 /var/lib/mysql/test_db/musers_test.ibd

mysql> insert into musers_test select * from musers limit 500000;
Query OK, 500000 rows affected, 1 warning (2 min 58.44 sec)
Records: 500000 Duplicates: 0 Warnings: 0
/* notice the execution time is double previous insert (from 1 min 12.43 sec to 2 min 58.44 sec) */

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 416M Jun 16 06:38 /var/lib/mysql/test_db/musers_test.ibd

mysql> alter table musers_test;
Query OK, 0 rows affected (0.00 sec)

/* decrease file size because innodb_file_per_table=ON */
[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:43 /var/lib/mysql/test_db/musers_test.ibd

mysql> delete from musers_test;
Query OK, 500000 rows affected (1 min 2.82 sec)

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 368M Jun 16 06:47 /var/lib/mysql/test_db/musers_test.ibd

mysql> alter table musers_test engine innodb;
Query OK, 0 rows affected (5.91 sec)
Records: 0 Duplicates: 0 Warnings: 0

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 432K Jun 16 06:48 /var/lib/mysql/test_db/musers_test.ibd

mysql> insert into musers_test select * from musers limit 5000;
Query OK, 5000 rows affected, 1 warning (1.18 sec)
Records: 5000 Duplicates: 0 Warnings: 0

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 13M Jun 16 06:49 /var/lib/mysql/test_db/musers_test.ibd

mysql> delete from musers_test limit 4000;
Query OK, 4000 rows affected, 1 warning (0.52 sec)

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 13M Jun 16 06:49 /var/lib/mysql/test_db/musers_test.ibd

mysql> alter table musers_test engine innodb;
Query OK, 1000 rows affected (0.37 sec)
Records: 1000 Duplicates: 0 Warnings: 0

[user@server~]$ ll -h /var/lib/mysql/test_db/musers_test.ibd
-rw-rw---- 1 mysql mysql 9.0M Jun 16 06:50 /var/lib/mysql/test_db/musers_test.ibd

also you can run shel command from mysql as mysql> \! ls -lh /var/lib/mysql/test_db/musers_test.ibd


Regards,
Mohammad Lahlouh
:)

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