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

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

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

    
Can you paste and example select and insert query? –  ajtrichards 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

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.