Degraded performance due to FKs

Bug #1323765 reported by Seppo Jaakola on 2014-05-27
10
This bug affects 1 person
Affects Status Importance Assigned to Milestone
MySQL patches by Codership
Undecided
Unassigned
Percona XtraDB Cluster moved to https://jira.percona.com/projects/PXC
Status tracked in 5.6
5.5
Confirmed
Medium
Unassigned
5.6
Confirmed
Medium
Unassigned

Bug Description

Foreign key processing is suspected to cause performance bottleneck.

One test manifesting this is to load employee database from: https://launchpad.net/test-db/, and measure load times when FKs are enabled / disabled.

Here are test results with Galera Cluster 5.6.17 development head version, compared to native MySQL 5.6.17:

mysql -uroot < employees.sql

      2 node Cluster mysql 5.6.17

FK ON: 1m32.696s 0m36.379s
FK OFF: 1m1.442s 0m35.025s

=> FK overhead in cluster is roughly 50%

description: updated
description: updated
description: updated
Marco Tusa (marcotusa) wrote :
Download full text (28.0 KiB)

Guys here is the oprofile output.
What I was noticing is that when using the FK the calls to handle_connections_sockets Just jumps up, as well as THD::THD(bool, bool) so main thread object creation when WSREP is active.

let me know if I can help more.

Without FK
--------------
Using /tmp/oprofile/oprofile_data/samples/ for samples directory.
CPU: Core 2, speed 2.2e+06 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 100000
CPU_CLK_UNHALT...|
  samples| %|
------------------
     3251 100.000 mysqld
 CPU_CLK_UNHALT...|
   samples| %|
 ------------------
      1185 36.4503 libc-2.17.so
      1066 32.7899 no-vmlinux
       907 27.8991 mysqld
        70 2.1532 libpthread-2.17.so
         8 0.2461 libstdc++.so.6.0.17
         6 0.1846 libgalera_smm.so
         5 0.1538 [vdso] (tgid:4884 range:0xb77d5000-0xb77d5fff)
         3 0.0923 libcrypto.so.1.0.0
         1 0.0308 libgcc_s.so.1

With FK
--------------
root@tusacentral01:/tmp/oprofile# opreport
Using /tmp/oprofile/oprofile_data/samples/ for samples directory.
CPU: Core 2, speed 2.2e+06 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 100000
CPU_CLK_UNHALT...|
  samples| %|
------------------
     7239 100.000 mysqld
 CPU_CLK_UNHALT...|
   samples| %|
 ------------------
      2811 38.8313 no-vmlinux
      2779 38.3893 mysqld
      1164 16.0796 libc-2.17.so
       340 4.6968 libpthread-2.17.so
        88 1.2156 [vdso] (tgid:4884 range:0xb77d5000-0xb77d5fff)
        57 0.7874 libstdc++.so.6.0.17

Without FK
--------------
root@tusacentral01:/tmp/oprofile# opreport -l -p /lib/modules/`uname -r`
Using /tmp/oprofile/oprofile_data/samples/ for samples directory.
warning: /no-vmlinux could not be found.
warning: [vdso] (tgid:4884 range:0xb77d5000-0xb77d5fff) could not be found.
CPU: Core 2, speed 2.2e+06 MHz (estimated)
Counted CPU_CLK_UNHALTED events (Clock cycles when not halted) with a unit mask of 0x00 (Unhalted core cycles) count 100000
samples % image name symbol name
1185 36.5290 libc-2.17.so /lib/i386-linux-gnu/libc-2.17.so
1066 32.8607 no-vmlinux /no-vmlinux
50 1.5413 mysqld show_status_array(THD*, char const*, st_mysql_show_var*, enum_var_type, system_status_var*, char const*, TABLE*, bool, Item*)
44 1.3564 mysqld my_utf8_uni
42 1.2947 mysqld THD::THD(bool, bool)
36 1.1097 mysqld heap_scan
34 1.0481 mysqld hp_allocate_chunkset
28 0.8631 mysqld my_qsort
27 0.8323 mysqld my_well_formed_len_mb
25 0.7707 libpthread-2.17.so pthread_mutex_trylock
22 0.6782 mysqld __i686.get_pc_thunk.bx
19 0.5857 mysqld strnmov
18 0.5549 mysqld Field_varstring::store(char const*, unsigned int, charset_info_st const*)
18 0.5549 mysqld handle_connections_sockets()
16 ...

Few things about the oprofile report:

a) I don't see much of libgalera_smm.so in the output there. Was this done with
single node or multiple nodes (for galera overhead to be counted).

b) Were both the FK/non-FK done at same point during benchmark cycle? This is
because some of the symbols in non-FK like my_qsort are missing in FK one.
sql_rnd_with_mutex is there in both though.
Also, query cache seems to be enabled, which may be adding overhead.

c) A perf report may also be helpful.

@Seppo,

In that, were foreign_key_checks set to 0 when FK -tables were loaded.

https://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_foreign_key_checks

Seppo confirmed that they have a design for optimising keys for FK related certification, however work on the implementation has not started yet. I change this to confirmed.

Changed in codership-mysql:
status: New → Confirmed

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PXC-1104

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers