Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Can you paste and example select and insert query? –  ajtrichards_wales Sep 19 '13 at 13:00
    
I added some profiling from PrestaShop CMS that is the piece of software that rise this problem. On the old machine is slow, but never more than 1/2 seconds. On the new one some page goes in 30s timeout –  Daniele Ravizza Sep 19 '13 at 13:18
    
How many records do you have in each of the tables above? –  itsols Sep 19 '13 at 13:22
    
In the Create example i create table in an empty database. In the insert / delete i use a shop with 200 products. My biggest table has 500 records. –  Daniele Ravizza Sep 19 '13 at 13:26
1  
Then that's irrelevant. One thing I see in the settings is the low key_buffer_size. Why is it so low? Usual settings are up to 30-40% of total RAM, if you exclusively use MyISAM. –  ypercube Sep 19 '13 at 15:00
show 6 more comments

migrated from stackoverflow.com Sep 19 '13 at 13:33

This question came from our site for professional and enthusiast programmers.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.