Details
Minor
Description
On a test database with 10m rows, with the exact same config, it takes about 10-15% longer to perform a "SELECT COUNT(*) FROM table;" on an InnoDB table having 10m rows.
Launch arguments : /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib/mysql/plugin --user=mysql --open-files-limit=60000 --pid-file=/var/run/mysqld/mysqld.pid --socket=/var/run/mysqld/mysqld.sock --port=3306
The my.cnf :
[mysqld] skip-external-locking max_connections = 250 connect_timeout = 5 wait_timeout = 600 max_allowed_packet = 1G thread_cache_size = 128 sort_buffer_size = 4M bulk_insert_buffer_size = 64M max_heap_table_size = 64M tmp_table_size = 64M myisam_recover = BACKUP key_buffer_size = 8M open-files-limit = 60000 table_open_cache = 8000 table_definition_cache = 8000 myisam_sort_buffer_size = 128M concurrent_insert = 2 read_rnd_buffer_size = 128M query_cache_size = 0 query_cache_type = OFF log_warnings = 2 slow_query_log = 0 long_query_time = 4 expire_logs_days = 10 max_binlog_size = 150M default_storage_engine = InnoDB innodb_log_file_size = 2000M innodb_buffer_pool_size = 10G innodb_log_buffer_size = 16M innodb_file_per_table = 1 delay_key_write = ON innodb_buffer_pool_instances = 8 skip-name-resolve innodb_autoinc_lock_mode = 2 event_scheduler = off group_concat_max_len = 5000 innodb_file_format = Barracuda
And here is the table structure :
CREATE TABLE `subscribers` ( `subscriber_hash` varchar(8) NOT NULL, `subscriber_id` int(11) NOT NULL, `timestamp` datetime NOT NULL, `lastname` varchar(100) DEFAULT NULL, `firstname` varchar(100) DEFAULT NULL, `title` varchar(3) DEFAULT NULL, `creation_date` datetime DEFAULT NULL, `date_activation` datetime DEFAULT NULL, `birthdate` date DEFAULT NULL, `birthyear` int(4) DEFAULT NULL, `zipcode` varchar(10) DEFAULT NULL, `city` varchar(100) DEFAULT NULL, `country` varchar(100) DEFAULT NULL, `country_code` varchar(2) DEFAULT NULL, `cached_contract_id` int(11) DEFAULT NULL, `ISP` varchar(20) DEFAULT NULL, `isp_domain` varchar(96) DEFAULT NULL, `subscriber_cnt` int(4) DEFAULT NULL, PRIMARY KEY (`subscriber_hash`,`subscriber_id`,`timestamp`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8
—
On a dry run on a Maria server that is only having this table on it and that is only running this test, on a system that has no real load while performing the tests, with XtraDB :
# time mysql test_database -e "SELECT COUNT(*) FROM subscribers;" && time mysql test_database -e "SELECT COUNT(*) FROM subscribers;" && time mysql test_database -e "SELECT COUNT(*) FROM subscribers;" +----------+ | count(*) | +----------+ | 10000000 | +----------+ real 0m3.052s user 0m0.000s sys 0m0.010s +----------+ | count(*) | +----------+ | 10000000 | +----------+ real 0m2.966s user 0m0.000s sys 0m0.000s +----------+ | count(*) | +----------+ | 10000000 | +----------+ real 0m2.989s user 0m0.000s sys 0m0.000s
—
With InnoDB plugin loaded instead of XtraDB (similar results are achieved when ran on MySQL 5.5.29) :
# time mysql test_database -e "SELECT COUNT(*) FROM subscribers;" && time mysql test_database -e "SELECT COUNT(*) FROM subscribers;" && time mysql test_database -e "SELECT COUNT(*) FROM subscribers;" +----------+ | count(*) | +----------+ | 10000000 | +----------+ real 0m2.727s user 0m0.000s sys 0m0.000s +----------+ | count(*) | +----------+ | 10000000 | +----------+ real 0m2.758s user 0m0.000s sys 0m0.000s +----------+ | count(*) | +----------+ | 10000000 | +----------+ real 0m2.729s user 0m0.000s sys 0m0.000s
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
Possibly helpful related InnoDB work. WL#6742 is currently marked "Status: Complete".
Axel,
Could you please run similar tests in your performance environment and come up with statistics?
Probably the comparison should involve Percona server as well.