Monday Sep 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)

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










Thursday Mar 06, 2008

Database Appliances Vs Embedded Databases

In my previous post I briefly mentioned Database Appliance with Project Indiana. Now that Sun has acquired MySQL (or as some people say MySQL has acquired Sun) and with our existing work in progress with PostgreSQL, there are quite a bit of options available of using some combination  of Storage, System, Operating System, Database  along with some end user application and present it as either Database Appliance or use it as Embedded Database. 

I thought I will just discuss the audience, symptoms, merits, cons etc regarding the two approach and how they can be useful. 

 The first question is who likes database appliances and who wants embedded database? To answer that I would put the question back: What do you in hand? A hammer or a screw-driver? Because if you have a hammer, the whole world is a nail to you and if you have a screw driver the whole world is full of screws. Similarly if you love databases (DBA) and  you want to query everything via a SQL statement then database appliances are for you. If you dont like to interact with databases (System Administrator, Application User) then you essentially want an embedded database where the OS or your end application takes care of interacting with the database. Well that is quite simple.

 First lets talk about database appliance. Think of it as your existing wireless routers. You bring it in house, plug it into your network (essentially to your cable modem) and power it on. Connect your laptop to one of the LAN ports, run DHCP, fire up a browser and go to typically htp://192.168.1.1 and lo and behold you get a website to login and administer your router. A typical database appliance in some way will behave similarly. It has a web-based administrator to set it up quickly and allow you to connect to the database via rest of your applications on the network. In fact all the pieces are already out there: Storage, Server, Operating System, Database and even a web based management tool like webconsole (to configure ZFS) and Webmin which even supports mysql. This help reduce the knowledge of underpinning Operating System that you trust to "just work". Once setup, your applications just connect through your applications talking to client/server setup of a normal database. While the appliance takes care of snapshots, backups, clones via the web-based tool. Can a database be easier than that? That's the values that a database appliance brings to the table. Its DBA's dream to not go through a System Administrator to allocate the OS resources for the database.  It abstracts the resilient operating system underneath it and makes it easier for the DBA to quickly deploy it in actual usage.

What about Embedded Databases? In some ways it is anti-database appliance. Other way to look at it is abstraction of the database functions itself via end application Functions which interacts with the database. The application can be either Operating System or the end application. One of the analogy that can be used here is an integrated database in the operating system which is controlled via services of the operating system. For example if you use "svcadm enable postgres:version_82" on Solaris, a PostgreSQL 8.2 instance is enabled for you in the background. Which means you are ready to use it with no idea on where it is or what it is (except from the svcadm name). That's an analogy of embedded database. Many applications infact create their own menu items to backup application which includes the database underneath it and so on. If things are setup right, then you dont even have to execute any dedicated PostgreSQL commands to stop/start, backup etc and everything is done using application like svcadm, zfs snapshot, etc.

After all it is all about choice and what one desires. Options and components already available. As database becomes a commodity, I wouldnt be surprised to see small consumer devices with a hard disk presenting itself as a MySQL or PostgreSQL database for structured information and as a NAS device for unstructured information. Talk about being a commodity.




 

Friday May 18, 2007

DBonSolaris.info

I just bought a domain name DBonSolaris.info from GoDaddy.com. They even gave an ad-supported free hosting for it. Using the hosting tools I found that they provide a wiki application from pmwiki.org. Not exactly similar to all the other wikis that I have used till date.

Anyhow I started putting some information on it. My idea is to use it to organize all database blog entries, articles, etc in a way easily consumed by someone who is looking for that information. Check it out and also see if you can also add information on it. If it doesnt save your information, do let me know. Since this is not entirely "custom", there are many limitation on what can be done using this wiki application. But afterall it is the information that counts.

Try DBonSolaris.info and let me know what you think about it.

Monday Jul 24, 2006

Product Operations Manager at Progress Software loves Solaris and Sun Studio Tools

Mike Furgual from Progress Software Corporation recently commented on why he likes Solaris and Sun Studio.

Following versions of Progress OpenEdge RDBMS are available on Solaris 10.

  • OpenEdge 10.1A (32-bit) on Solaris 10 (SPARC) with JVM 1.4.2 (32-bit)
  • OpenEdge 10.1A (64-bit) on Solaris 10 (SPARC) with JVM 1.4.2 (32-bit)
  • OpenEdge 10.0B (32-bit) on Solaris 10 (SPARC) with JVM 1.4.2_06 (32-bit)
  • OpenEdge 10.0B (32-bit) on Solaris 10 (SPARC) with JVM 1.4.2_06 (32-bit)
  • Progress 9.1E (32-bit) on Solaris 10 (SPARC)
  • Progress 9.1E (64-bit) on Solaris 10 (SPARC)
  • Progress 9.1E (32-bit) on Solaris 10 (x64)

Should we work on a Performance Tuning document for Progress OpenEdge on Solaris 10? Your vote will influence that decision.

Friday Oct 07, 2005

Ingres r3 on Solaris 10 (SPARC and x64)


If you were not already aware, you can download Ingres r3 3.0.2 (a64.sol/109) 32/64 bit for Solaris 10 x64 and Ingres r3 3.0.2 (su9.us5/109) 32/64 bit for Solaris 10 SPARC from the Ingres download page. Ingres r3 has new features which are attractive for heavy data warehousing type of workloads. Features include
  • Parallel Query Execution
  • Key Range Table Partitioning
  • Active-Active Cluster High Availability Option (Shared Disk Cluster)


Monday Sep 19, 2005

Informix (IDS) Support on Solaris 10 (SPARC, x86, Local Zones)


Just in case if you are not aware
IDS 10.00.UC3R1, 10.00.FC3R1, 9.40.UC7, 9.40.FC7, 7.31.UD8, 7.31.FD8 are supported on Solaris 10 SPARC. Infact it works with or without local zones

IDS 7.31UD7 is Supported on Solaris 10 x86 . Again even for Solaris 10 x86 it works with or without local zones
Thanks to Glen Kriekenbeck for passing on the information.
About

Jignesh Shah is Principal Software Engineer in Application Integration Engineering, Oracle Corporation. AIE enables integration of ISV products including Oracle with Unified Storage Systems. You can also follow me on my blog http://jkshah.blogspot.com

Search

Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today