View Full Version : MySQL simple INSERT too slow, help!

09-10-2010, 07:58 PM
An INSERT into the following table takes appr. 0,04 sek:

CREATE TABLE `tbl_products_offers_format_test` (
`fld_offer_id` INTEGER(11) DEFAULT NULL,
`fld_format_id` INTEGER(11) DEFAULT NULL,
`fld_format` VARCHAR(35) COLLATE utf8_unicode_ci DEFAULT NULL,
PRIMARY KEY (`fld_id`),
UNIQUE KEY `fld_offer_id` (`fld_offer_id`, `fld_format`)
AUTO_INCREMENT=197520 CHARACTER SET 'utf8' COLLATE 'utf8_unicode_ci';

Phenom X3 |64 Triple Core 2,3 GHz
Ubuntu Linux 10.04
Linux 2.6.32-21-server on x86_64
Swap Partition 4 GB

port = 3306
socket = /var/run/mysqld/mysqld.sock

socket = /var/run/mysqld/mysqld.sock
nice = 0

# * Basic Settings

user = mysql
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp


# * Fine Tuning
key_buffer = 256M #s 16M
max_allowed_packet = 2M #s 16M
thread_stack = 192K
thread_cache_size = 8
key_buffer_size = 256M
myisam_sort_buffer_size = 64M
read_rnd_buffer_size = 4M
read_buffer_size = 4M
sort_buffer_size = 4M
myisam_sort_buffer_size = 64M
tmp_table_size = 512M #64M
max_heap_table_size = 512M #64M
join_buffer_size = 2M

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP

max_connections = 50
table_cache = 256 #s 64
thread_concurrency = 8 #s was commented

# * Query Cache Configuration
query_cache_limit = 16M #s 1M
query_cache_size = 16M #s 16M

# * Logging and Replication
log_error = /var/log/mysql/error.log

# Here you can see queries with especially long duration
log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 3

# * InnoDB

default-storage-engine = INNODB
innodb_data_home_dir = /var/lib/mysql/innodb
innodb_data_file_path = ibdata1:10M:autoextend:max:4000M
innodb_buffer_pool_size = 512M
innodb_additional_mem_pool_size = 32M
innodb_log_file_size = 256M #128M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 20
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_thread_concurrency = 4
#innodb_flush_method = O_DIRECT

on my local server with default mysql setting on windows XP it takes only 0,001 sek for an INSERT.

I tried:
to tune MYSQL config as well as to use default parameters, no luck 0,04 is the fastest insert. Hard disk is according to support ok. Tried to restart the whole server to empty the swap, also no luck.

PLESE HELP! I have no idea why it takes on the web server 40 times more time for executing a simple INSERT.

Old Pedant
09-10-2010, 08:19 PM
There's a possible clue here:

... )ENGINE=InnoDB AUTO_INCREMENT=197520 ...

That says to me that *probably* the table already has about 200,000 rows. And so it would make sense that adding a new row would take longer than when the table has zero rows.

Now...40 times as long? Yes, that does seem way excessive.

But what else is happening on that server?? Maybe it is also serving 20 other users who are all performing "heavy" SQL operations. Or maybe it's also a web server with many users requesting HTML pages. Or....

It's pretty hard to compare your local machine--which is almost surely doing nothing else but servicing your one query--with a "loaded" server machine.

09-10-2010, 08:34 PM
thank you Old Pedant for a rush answer, buy no of the reasons could be an answer for my problem:

1) web server has no load, it is my testing server and only a few persons now about its existing

2) the table is empty on local mashine and web service, and i start to fill the emptu table

Old Pedant
09-10-2010, 08:53 PM
I'm afraid I'm out of ideas. I use MySQL on Windows 7 as well as on 4 or 5 Linux machines, and the Linux machines have a large variation in their hardware capabilities (from 1 cpu with 1GB RAM to 8 cpus with 8GB RAM) and I don't see *THAT* much difference between any of the machines. At least not when they aren't loaded.

09-10-2010, 08:56 PM
that is why i am also confused, 40 times difference is hard to explain. I also asked the support of the data center, but they are not much help(

I have made the following experiment - i've comverted the table from innodb to myisam and .. it run much much faster, just with normal speed expected to be, even more then 40 times faster. So the problem is located - it is InnoDB

But what could be wrong with my innodb configuration?