Very BIG SIZE and SLOW InnoDB Tables, when MyISAM are very fast and small.

[test157]

Dear Sirs,

I'am useing 2 type of engines on my servers, MyISAM and InnoDB.

For test I created 2 tables, one is with MyISAM type another one with InnoDB. The tables have so kind of structure:


`a` int(10) unsigned NOT NULL default '0',
`b` varchar(128) NOT NULL default '',
`c1` bit(1) NOT NULL default '\0',
`c2` bit(1) NOT NULL default '\0',
`c3` bit(1) NOT NULL default '\0',
.................
`c994` bit(1) NOT NULL default '\0',
`c995` bit(1) NOT NULL default '\0',
`c996` bit(1) NOT NULL default '\0',
`c997` bit(1) NOT NULL default '\0',
`c998` bit(1) NOT NULL default '\0',

So in my TEST table is 1000 COLUMNS (for some reason I wasn't able to create more columns in INNODB, so it seems InnoDB tables is limited to 1000 Fields, with MyISAM more is OK)

a - is Auto_increment Primary Key
b - is Unique (50)

After that, I add about 5 million of random records into InnoDB table and about 9 random records into MyISAM table (import speed of InnoDB was too slow, so When in MyISAM were 9 million records in InnoDB only 5).

And tried to use so sql's;
mysql> select count(*) from a1 where c1=b'0'; // My ISAM table
+----------+
| count(*) |
+----------+
| 4630768 |
+----------+
1 row in set (36.52 sec)

mysql> select count(*) from c1 where c1=b'0'; // InnoDB table
+----------+
| count(*) |
+----------+
| 3072703 |
+----------+
1 row in set (2 min 16.51 sec)

As you can see InnoDB table with LESS records take more time than MyISAM. So what is problem of so slow speed? I tried to use InnoDB Tablespace in files on the same hard drive where MyISAM is + I tried to use RAW partition on another drive, where all HDD was for InnoDB tablespace. And in both situation I saw slow speed.

My InnoDB settings are so:
| innodb_additional_mem_pool_size | 20971520 |
| innodb_autoextend_increment | 8 |
| innodb_buffer_pool_awe_mem_mb | 0 |
| innodb_buffer_pool_size | 268435456 |
| innodb_checksums | ON |
| innodb_commit_concurrency | 0 |
| innodb_concurrency_tickets | 500 |
| innodb_data_file_path | ibdata1:3G |
| innodb_data_home_dir | /home/mysql |
| innodb_doublewrite | ON |
| innodb_fast_shutdown | 1 |
| innodb_file_io_threads | 4 |
| innodb_file_per_table | OFF |
| innodb_flush_log_at_trx_commit | 1 |
| innodb_flush_method | |
| innodb_force_recovery | 0 |
| innodb_lock_wait_timeout | 50 |
| innodb_locks_unsafe_for_binlog | OFF |
| innodb_log_arch_dir | |
| innodb_log_archive | OFF |
| innodb_log_buffer_size | 8388608 |
| innodb_log_file_size | 67108864 |
| innodb_log_files_in_group | 2 |
| innodb_log_group_home_dir | /home/mysql/ |
| innodb_max_dirty_pages_pct | 90 |
| innodb_max_purge_lag | 0 |
| innodb_mirrored_log_groups | 1 |
| innodb_open_files | 300 |
| innodb_support_xa | ON |
| innodb_sync_spin_loops | 20 |
| innodb_table_locks | ON |
| innodb_thread_concurrency | 20 |
| innodb_thread_sleep_delay | 10000 |

MySQL version is the latest, 5.0.18.

Both HDD hdparm gives about 60MB/s + 1GB memory P4 3.0 CPU, OS: Fedora Core 4 - with non moduled leightweight own compiled kernel v2.6.15.

Please HELP ME ;)

P.S: The Size of INNODB Tables is IN 8 TIMES BIGGER!!!! Than MyISAM - maybe here is the reason of it's SLOW work?? Maybe BIT field in InnoDB stored not as BIT but as TINY INTEGER??? so it's the reason why they are so big? So it takes 1 BYTE(8bits) to store one BIT??