Getting Best out of MySQL on Solaris

I am sitting in the session "Getting Best out of MySQL on Solaris 10" by Yufei Zhu (yufei.zhu at sun.com)

She is going to focus on InnoDB performance tips and how to monitor performance on Solaris using Dtrace examples.

When we try to use mysqldump to backup in zones, it ends prematurely.

MySQL is single process, multi-threaded application. Thousands of user connections means thousands of threads and this becomes a problem for some customers. MySQL and Sun Microsystems are close partners (I really doubt the "close" part).

She is now going over the characteristics of InnoDB like the storage of data in primary key order physically. You don't want your primary key too long (haha, come to my session for a different point of view).

When MySQL is first started, 10 threads are created.

thread 1 handles network connections and creates new threads for new user connections.

thread 2 to 5 are read ahead thread, log write thread, insert thread, write thread.

thread 6 is rollback thread, thread 7 to 8 are monitor threads

thread 9 is master thread
thread 10 is signal handler thread

theoretically raw device will give you best performance.

Use "show innodb status", "show processlist", "mysqladmin extended-status" for performance monitoring.


Cannot have IO bottlenecks. prstat is much more useful that top. She encourages use of forcedirectio for UFS.

If there is a lot of write transactions then make sure to configure enough disks with very small latency for log files since MySQL does serialize pwrite.

You need low latency for log writes.

Innodb_buffer_pool_size: how much data you want to cache in memory.
Innodb_log_buffer_size :
innodb_thread_concurrency: don't set it too high as there can be locking contentions.
innodb_flush_log_at_trx_commit = 0 (by default set to 1: makes big gain but at risk of losing 1 second data.
innodb_flush_method: default is as good as it gets.

MyISAM performance is fairly good but non-transactional. In 5.1 there is row level locking too for MyISAM (she thinks). Each MyISAM table is stored on disk in three files.

MyISAM doesn't have special data buffer cache, relying on filesystem buffer cache. The default cache setting is 12% of file system. Be sure to tune that up.

MySQL 5.1 has started using mmap instead of malloc which gives huge performance gaines.
Each pread is 200 bytes. She has seen 50% of performance jump.

General performance tips:
key_reads/ key_reads_requests gives you hit rate. Increase key_buffer_size if necessary as this is the index cache.

read_rnd_buffer_size improves "order by" performance.

Since MyISAM doesn't have its own cache

segmap_percent=50 in /etc/system will use 50% of memory as file system buffer cache (if using MyISAM make sure you tune this setting from default of 12%)

Use query_cache only if needed, there are locking issues with some customer applications.

Only release 5.1 has mmap change. It needs "myisam_use_mmap=1" in my.cnf to be effective.

Don't use forcedirectio for MyISAM as it will bypass filesystem cache.


MYSQL system tables are MyISAM so should we really turn on forcedirectio? Yes, then build different filesystems, one with forcedirectio and other without it for MyISAM. MyISAM has to rely on filesystem caching.

MySQL does a lot of malloc.

If using 64bit MySQL. setenv LD_PRELOAD_64 / usr/lib/sparcv9/libumem.so before MySQL is started. Gives better performance

On 32 bit servers, setenv LD_PRELOAD /usr/lib/libumem.so before starting MySQL. 4G is obviously the limit.

You gotta look at IO and disk utilization. Always separate log files from data files. Make them use different disks. One must eliminate IO bottleneck as first step. Don't put log and data together as the response time can go higher.


Analyze MySQL performance counters by tuning mysql startup option, optimizing sql statements and optimizing queries with "EXPLAIN".

Another thing is to analyze system performance statistics like checking cpu/memory utilizations. Are there lock contentions? Is CPU idle during high load?


Examples of performance analysis using Solaris performance tools.
You can get hooked to them :)

Why is the performance bad? Her examples are read-only workloads.
mpstat 5
smtx: system level mtex contention.


vmstat 5
How much memory is being used. Watch for swap and free memory.

ps -ef | grep mysql
gives you the PID. then do a blank prstat .
prstat
mysqld/74 means 74 threads. take 10 threads for when mySQL is started with InnoDB. Here we can see 64 connections are open. Also shown is resident memory size. Who consumes the most CPU on the system.

prstat -Lmc -p 16613
Give me all top threads (who is using most CPU)?

mysqld/65 means 65th thread is using most processor. Also look at user time and system time.

LCK is not accurate but tells you how much time it is waiting for user level lock. Gives a rough indication

SLP is sleep and gives how much time spent sleeping.


SCL: System calls (percentage)

pstack 16613/65: [PID/thread id]: WHat is the thread doing. Where is it stuck? If it is writing where is it writing to?

Do you have IO bottleneck?
iostat -xtcnz 4

Key thing is service and response times of disk. Don't only look at %b. Look for service time. Don't go over 25ms. The less the better. If you have 60ms then you have IO bottleneck.


Use iostat and dtrace combined to work out IO issues.

#!/usr/sbin/dtrace -s
syscall:pwrite:entry
/arg3/
{ self->t1 = timestamp;
printf ("hirestime %u, tid=%d, length=%d

Look at its output. tid in output is thread id. Offset is the block number on the disk. You capture everything with dtrace. You can see the timestamps. Elapsed time gives how much time went in pwrite. Length tells how many blocks. Apache also serializes log writes. When running ab, turn off log file to see a huge performance gain.

truss -c -p 16613
truss can solve about 90% of the problems in real life. It's so powerful. -c will count calls. Then you can see read, write and time calls etc.

lwp_park: is user level sleep queues. It means a lot of user level locks in this application.

truss -p 16613

see what a process is running. See how many time () calls are there and when write is being called. Shows socket numbers

pfiles 16613
what files are opened by a process. Reference here with socket numbers in truss. There are generic dtrace scripts available to count user level function calls. memcpy was the most called followed by mutex_unlock etc. This gives indication of what user function calls are being made.

dtrace -F -p 2185 -n 'pid$target:::entry,pid$target:::return{trace(timestamp);}'


MySQL dtrace provider: Still working with MySQL to get it integrated. They did some probes inhouse. They integrated with Chime (graphical tool) which allows you to look for MySQL IO wait time, InnoDB index scan, system calls, table scans etc. Start MySQL and fire off Chime and watch for goodness.

In demo booth they are showing how to use Chime. You can even have your own probes to work with Chime.

Consolidations with Solaris container
Virtualization with very very low performance overhead. Configuration and administer of Solaris containers is very easy. You can have multiple MySQLD instances (as many as 7) to reduce lock and IO contention. Performance wise the overhead of virtualization is around 1%.

On website, they have a graphical tool to build a container and they have scripts to DIY. Containers improve system utilizations and scalability.

SAMP stack can be downloaded from http://cooltools.sunsource.net/coolstack . It has Apache2, MySQL5, PHP5 and even memcached. The stack is optimized for Sun Solaris OS environment. Just install with pkg command. Reduces installation time.