Really slow inserts on small tables?

[Cabbar Duzayak]

Hi everyone,

We are running Percona 5.6 on Fedora (12 core with 24GB memory) and during our system tests,we open a total of ~ 60 active connections via JDBC connection pools on 6 different jvms. All of them are not active and load is not super high (~ 6 for 12 cores). And, client and mysqld are on the same server, so no network latency there.

But, we are seeing realllly slow INSERT and INSERT ... ON DUPLICATE KEY calls on a regular basis. Basically, most inserts are fine, SELECTs are fine, but some inserts timeout quite often after 5 seconds. The usage scenario is as follows:

- Before tests start, we drop all tables and recreate them (~ 20 tables)

- We have very little data in these tables  (~ 100-200 rows)

- We have only 2 indexes, one for ID (UUID) and another unique index for email fields

- Tests do insert / update / selects consecutively 

Our slow query log below is baffling and it is driving me crazy... Profile_Update is taking 8.75 seconds, but there is no lock wait, no table scan, no tmp table, IO values look OK, cpu usage is low...

Any idea what is going on and/or how we can improve this?

Thanks in advance...

# User@Host: YYY[YYY] @ localhost [127.0.0.1]  Id:   520

# Schema: ZZZZZZZZ  Last_errno: 0  Killed: 0

# Query_time: 8.751765  Lock_time: 0.000356  Rows_sent: 0  Rows_examined: 0  Rows_affected: 2

# Bytes_sent: 11  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0

# Profile_starting: 0.000209 Profile_starting_cpu: 0.000210 Profile_checking_permissions: 0.000010 Profile_checking_permissions_cpu: 0.000010 Profile_Opening_tables: 0.000030 Profile_Opening_tables_cpu: 0.000031 Profile_init: 0.000097 Profile_init_cpu: 0.000097 Profile_System_lock: 0.000014 Profile_System_lock_cpu: 0.000014 Profile_update: 8.750325 Profile_update_cpu: 0.000508 Profile_end: 0.000024 Profile_end_cpu: 0.000016 Profile_query_end: 0.000954 Profile_query_end_cpu: 0.000085 Profile_closing_tables: 0.000029 Profile_closing_tables_cpu: 0.000018 Profile_freeing_items: 0.000066 Profile_freeing_items_cpu: 0.000052 Profile_logging_slow_query: 0.000005 Profile_logging_slow_query_cpu: 0.000003

# Profile_total: 8.751763 Profile_total_cpu: 0.001043

# InnoDB_trx_id: 1655C3

# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No

# Filesort: No  Filesort_on_disk: No  Merge_passes: 0

#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000

#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000

#   InnoDB_pages_distinct: 3

[Kenny Gryp]

Hi Cabbar,

is this Percona XtraDB Cluster?

If so, then I would recommend you to look at this blog post: http://www.percona.com/blog/2013/05/02/galera-flow-control-in-percona-xtradb-cluster-for-mysql/

Regards,

Kenny Gryp

--
You received this message because you are subscribed to the Google Groups "Percona Discussion" group.
To unsubscribe from this group and stop receiving emails from it, send an email to percona-discuss...@googlegroups.com.
To post to this group, send email to percona-d...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.