Monday, September 14, 2009

Infobright Tuning on OpenSolaris/Solaris 10

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)

Mutex hold

Count nsec Lock Caller
3634393 1122`libc_malloc_lock`_Znwm+0x2b
3626645 1047`libc_malloc_lock`_ZdlPv+0xe
2 536317885 0x177b878 mysqld`_ZN7IBMutex6UnlockEv+0x12
12 6338626 mysqld`LOCK_open mysqld`_Z10open_tableP3THDP13st_table_listP11st_mem_rootPbj+0x55a
9057 1275`libc_malloc_lock`_Znwm+0x2b
8493 1051`libc_malloc_lock`_ZdlPv+0xe
7928 1119`libc_malloc_lock`_ZdlPv+0xe
5 326542 0x177b878 mysqld`_ZN7IBMutex6UnlockEv+0x12
683 1189`libc_malloc_lock`_Znwm+0x2b
564 1339`libc_malloc_lock`_Znwm+0x2b
564 1274`libc_malloc_lock`_Znwm+0x2b
564 1156`libc_malloc_lock`_ZdlPv+0xe
17 36292 0x1777780 mysqld`_ZN7IBMutex6UnlockEv+0x12
2 246377 mysqld`rccontrol+0x18 mysqld`_ZN7IBMutex6UnlockEv+0x12
57 8074 mysqld`_iob+0xa8`_ZNSo5flushEv+0x30
218 1479`libc_malloc_lock`_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_malloc_lock`_ZdlPv+0xe
1931 12334706`libc_malloc_lock`_Znwm+0x2b
3 93404485`libc_malloc_lock mysqld`my_malloc+0x32
1 11581`libc_malloc_lock mysqld`_ZN11Item_stringD0Ev+0x49
1 1769`libc_malloc_lock`_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 and 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.

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 we found the reduction more like 3X when 4 queries were executing concurrently.

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

No comments: