Degraded performance due to FKs

[Seppo Jaakola]

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%

[Marco Tusa (marcotusa)]

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 ...

[Raghavendra D Prabhu (raghavendra-prabhu)]

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.

[Raghavendra D Prabhu (raghavendra-prabhu)]

@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

[Miguel Angel Nieto (miguelangelnieto)]

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.

[Shahriyar Rzayev (rzayev-sehriyar)]

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