i have a strange problem with mysql.
My selects goes fast. My insert/create/delete/drop/update go very slow.
I have done some tests on 2 machines. My old machine and my new machine. Old is worst than new one, obviously.
- 160 create table InnoDB.
- 10s on old machine
- 70s on new machine
- 200 create table MyIsam.
- 15s on old machine
- 90s on new machine
- select avg(fk_mezzi) from log_2013_06_06
- 7s on old machine
- 2s on new machine
I exampled "create" but is the same for insert / update / drop. Select goes fast instead. Every call that write data go slow.
I tested the disk for writing 19Mb/s old server --- 70Mb/s new server. So i excluded the disk.
i don't know how to solve this absurd problem.
I have this dedicated server on OVH :
- Intel(R) Core(TM) i5-2400 CPU @ 3.10GHz (quad-core)
- RAM : 16Gb
- 2 x 2Tb Sata. Raid mirror software
This is the tuning settings of Mysql.
key_buffer = 16M max_allowed_packet = 16M thread_stack = 192K thread_cache_size = 8 myisam-recover = BACKUP query_cache_limit = 1M query_cache_size = 16M log_error = /var/log/mysql/error.log 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
I add other examples :
Simple product listing in Prestashop (200 products) :
SQL Queries: 1536 queries Time spent querying: 345ms
Delete of a product in PrestaShop :
SQL Queries: 1840 queries Time spent querying: 27.235s 17 times : UPDATE `prstshp_category_product` SET `position` = 'XX' WHERE `id_category` = XX AND `id_product` = XX 16 times : DELETE FROM `prstshp_product_attribute_shop` WHERE `id_product_attribute`=XX AND id_shop IN (XX) 16 times : DELETE FROM `prstshp_product_attribute_combination` WHERE `id_product_attribute` = XX
Duplicate a product in PrestaShop :
SQL Queries: 1842 queries Time spent querying: 18.496s 32 times : INSERT INTO `prstshp_product_attribute_combination` (`id_attribute`, `id_product_attribute`) VALUES ('XX', 'XX') 17 times : INSERT INTO `prstshp_stock_available` (`out_of_stock`, `id_product`, `id_product_attribute`, `id_shop_group`, `id_shop`) VALUES ('XX', 'XX', 'XX', 'XX', 'XX') 16 times :INSERT INTO `prstshp_image_lang` (`id_image`, `id_lang`) VALUES ('XX', 'XX')
I add profiling from mysql as suggested in the comments. This is the query and below the results :
CREATE TABLE IF NOT EXISTS `accounts` ( `id` int(11) unsigned NOT NULL auto_increment, `company_id` int(11) unsigned NOT NULL, `name` varchar(255) collate utf8_unicode_ci default NULL, `tel` varchar(255) collate utf8_unicode_ci default NULL, `email` varchar(255) collate utf8_unicode_ci default NULL, PRIMARY KEY (`id`), UNIQUE KEY `nome` (`name`), KEY `company_id` (`company_id`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
Old server :
starting =>0.000087 checking permissions =>0.000034 **** creating table =>0.052633 **** After create =>0.000006 query end =>0.000003 freeing items =>0.000012 logging slow query =>0.000003 cleaning up =>0.000003
New Server :
Status =>Duration starting =>0.000116 checking permissions =>0.000015 Opening tables =>0.000038 System lock =>0.000026 **** creating table =>0.279799 **** After create =>0.000017 query end =>0.000009 closing tables =>0.000014 freeing items =>0.000030 logging slow query =>0.000008 cleaning up =>0.000007
I use create table cause is the simplest test that don't involve the size of the DB. A "create table" execute 6x slower.
I have deleted and recreated the PrestaShop DB two times, but the problem is present on all databases in my server
I add other benchmarks done with sysbench. Those are 2 test. * The first is a mix of read/write operations done in 60s. * The second is a read-only test done in 60s.
Old machine - Read and write
queries performed: read: 47460 write: 16950 other: 6780 total: 71190 transactions: 3390 (56.46 per sec.)
New machine - Read and write
queries performed: read: 31038 write: 11085 other: 4434 total: 46557 transactions: 2217 (36.86 per sec.)
Old machine - Only read
queries performed: read: 421330 write: 0 other: 60190 total: 481520 transactions: 30095 (501.49 per sec.)
New machine - Only Read
queries performed: read: 1401470 write: 0 other: 200210 total: 1601680 transactions: 100105 (1668.35 per sec.)
Without writes the new machine is 3 times faster. If i do some writes the speed is about half the old one.
Thanks for your time Daniele
I add some new informations to the post.
I've done 3 simple benchmarks using Memory, MyIsam and InnoDB engines.
Write 1000 rows in engine memory : 0.514s
drop table log_2013_09_24_memory; create table log_2013_09_24_memory ENGINE=MEMORY select * from log_2013_09_24 limit 0,100000; /* Affected rows: 100.000 Righe trovate: 0 Avvisi: 0 Durata di 2 queries: 0,514 sec. */
Write 1000 rows in engine myisam : 0.608s
drop table log_2013_09_24_MYISAM; create table log_2013_09_24_MYISAM ENGINE=MYISAM select * from log_2013_09_24 limit 0,100000; /* Affected rows: 100.000 Righe trovate: 0 Avvisi: 0 Durata di 2 queries: 0,608 sec. */
Wrtie 1000 rows in engine innodb : 12,232s
drop table log_2013_09_24_INNODB; create table log_2013_09_24_INNODB ENGINE=INNODB select * from log_2013_09_24 limit 0,100000; /* Affected rows: 100.000 Righe trovate: 0 Avvisi: 0 Durata di 2 queries: 12,232 sec. */
I think this explain some questions. But what could break my performance like that ?
I've found a little solution to that. Those are my new configuration for innodb.
tmp_table_size = 128M max_heap_table_size = 128M innodb_buffer_pool_size = 1G innodb_flush_log_at_trx_commit = 0 innodb_log_buffer_size = 8M innodb_log_file_size = 256M innodb_flush_method=O_DIRECT default-storage-engine = MYISAM table_cache = 1K table_definition_cache = 4K open_files_limit = 3K
After that my last query (100k rows insert in innodb) took 2.5s from the 12s. I think it's acceptable. I hope that will help someone.