Recently I was working on a project which used Infobright as the database. The version tested was 3.1.1 both on OpenSolaris as well as Solaris 10. Infobright is like a column-oriented database engine for MySQL primarily targeted towards data warehouse, data mining type of project deployments.
While everything was working as expected, one thing we did notice that as number of concurrent connections tried to query against the database we noticed that queries deteriorated fast in the sense that not much parallel benefits were being squeezed from the machine. Now this sucks! (apparently sucks is now a technical term). It sucks because the server has definitely many cores and typically each Infobright query still can at the max peg a core. So the expectation will be typically to atleast handle concurrent queries which is close to the number of cores (figuratively speaking though in reality it depends).
Anyway we started digging into this problem. First we noticed that CPU cycles were heavy so IO was probably not the culprit (in this case). Using plockstat we found
# plockstat -A -p 2039 (where 2039 is the PID of mysqld server running 4 simultaneous queries)
^C
Mutex hold
Count nsec Lock Caller
-------------------------------------------------------------------------------
3634393 1122 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
3626645 1047 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_ZdlPv+0xe
2 536317885 0x177b878 mysqld`_ZN7IBMutex6UnlockEv+0x12
12 6338626 mysqld`LOCK_open mysqld`_Z10open_tableP3THDP13st_table_listP11st_mem_rootPbj+0x55a
9057 1275 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
8493 1051 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_ZdlPv+0xe
7928 1119 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_ZdlPv+0xe
5 326542 0x177b878 mysqld`_ZN7IBMutex6UnlockEv+0x12
683 1189 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
564 1339 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
564 1274 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
564 1156 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_ZdlPv+0xe
17 36292 0x1777780 mysqld`_ZN7IBMutex6UnlockEv+0x12
2 246377 mysqld`rccontrol+0x18 mysqld`_ZN7IBMutex6UnlockEv+0x12
57 8074 mysqld`_iob+0xa8 libstdc++.so.6.0.3`_ZNSo5flushEv+0x30
218 1479 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
4 78172 mysqld`rccontrol+0x18 mysqld`_ZN7IBMutex6UnlockEv+0x12
4 75161 mysqld`rccontrol+0x18 mysqld`_ZN7IBMutex6UnlockEv+0x12
….
R/W reader hold
Count nsec Lock Caller
-------------------------------------------------------------------------------
44 1171 mysqld`THR_LOCK_plugin mysqld`_Z24plugin_foreach_with_maskP3THDPFcS0_P13st_plugin_intPvEijS3_+0xa3
12 3144 mysqld`LOCK_grant mysqld`_Z11check_grantP3THDmP13st_table_listjjb+0x38c
1 14125 0xf7aa18 mysqld`_ZN11Query_cache21send_result_to_clientEP3THDPcj+0x536
1 12089 0xf762e8 mysqld`_ZN11Query_cache21send_result_to_clientEP3THDPcj+0x536
2 1886 mysqld`LOCK_grant mysqld`_Z11check_grantP3THDmP13st_table_listjjb+0x38c
2 1776 mysqld`LOCK_grant mysqld`_Z11check_grantP3THDmP13st_table_listjjb+0x38c
1 3006 mysqld`LOCK_grant mysqld`_Z11check_grantP3THDmP13st_table_listjjb+0x38c
1 2765 mysqld`LOCK_grant mysqld`_Z11check_grantP3THDmP13st_table_listjjb+0x38c
1 1797 mysqld`LOCK_grant mysqld`_Z11check_grantP3THDmP13st_table_listjjb+0x38c
1 1131 mysqld`THR_LOCK_plugin mysqld`_Z24plugin_foreach_with_maskP3THDPFcS0_P13st_plugin_intPvEijS3_+0xa3
Mutex block
Count nsec Lock Caller
-------------------------------------------------------------------------------
2175 11867793 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_ZdlPv+0xe
1931 12334706 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_Znwm+0x2b
3 93404485 libc.so.1`libc_malloc_lock mysqld`my_malloc+0x32
1 11581 libc.so.1`libc_malloc_lock mysqld`_ZN11Item_stringD0Ev+0x49
1 1769 libc.so.1`libc_malloc_lock libstdc++.so.6.0.3`_ZnwmRKSt9nothrow_t+0x20
..
Now typically if you see libc_malloc_lock in a plockstat for a multi-threaded program then it is a sign that the default malloc/free routines in libc is the culprit since the default malloc is not scalable enough for a multi-threaded program. There are alternate implementations which are more scalable than the default. Two such options which are already part of OpenSolaris, Solaris 10 are libmtmalloc.so and libumem.so. They can be forced to be used instead of the default without recompiling the binaries by preloading anyone of them before the startup command.
In case of the 64-bit Infobright binaries we did that by modifying the startup script mysqld-ib and added the following line just before invocation of mysqld command.
LD_PRELOAD_64=/usr/lib/64/libmtmalloc.so;
export LD_PRELOAD_64
What we found was now the response times for each query was more in-line as it was being executed on its own. well not true entirely but you get the point. For a 4 concurrent queries we found that it had improved from like 1X to 2.5X reduction in total execution time.
Similary when we used libumem.so we found the reduction more like 3X when 4 queries were executing concurrently.
LD_PRELOAD_64=/usr/lib/64/libumem.so;
export LD_PRELOAD_64
Definitely something to use for all Infobright installations on OpenSolaris or Solaris 10.
In a following blog post we will see other ways to tune Infobright which are not as drastic as this one but still buys some percentage of improvements. Stay tuned!!
PlanetMySQL Voting:
Vote UP /
Vote DOWN
The aims of this kind of blog post is simple – I want to help keep the masses informed as to what’s happening with MariaDB, as a whole. There is a community growing, and MariaDB is a community project, not necessarily a Monty Program Ab baby (and we’re clear on this distinction: think of it like Canonical/Ubuntu). So, think of it as such that I’m sharing the good news, and summarising what’s been happening, to save you time.