Tuning MySQL server

We've got many instances of MySQL on the same server and we have run some of them on Solaris x64 for last few days. Well, it's Solaris - I couldn't resist and did look a little bit around on this server. Below you can find some examples what you can observer using DTrace and how easy it is. These examples aren't exactly the ones I did use on production - these are similar but changed a little bit for simplification. Anyway they're still useful. This time let's try IO Provider.

I noticed using iostat that /var is being written to a lot. Probably /var/tmp or some logs - let's check it!

bash-3.00# dtrace -n io:::start'/args[2]->fi_mount == "/var"/{trace(args[2]->fi_pathname);}'
dtrace: description 'io:::start' matched 6 probes
CPU     ID                    FUNCTION:NAME
0     94              bdev_strategy:start   /var/tmp/#sql_e91_4.MYD
0     94              bdev_strategy:start   /var/tmp/#sql_e91_4.MYD
0     94              bdev_strategy:start   /var/tmp/#sql_e91_4.MYD
0     94              bdev_strategy:start   /var/tmp/#sql_e91_2.MYD
[...]
^C

Ok, so it's /var/tmp after all. Probably it's good to make /var/tmp a tmpfs filesystem.

Now let's see which instance of MySQL is doing most of the IOs.

bash-3.00# dtrace -n io:::start'{@a[execname]=count();}'
dtrace: description 'io:::start' matched 6 probes
^C

sched                                                             6
mysqld2                                                           7
fsflush                                                         225
mysqld1                                                         244
mysqld3                                                        2993
bash-3.00#

How many bytes are actually transfered during IOs by each instance?

bash-3.00# dtrace -n io:::start'{@a[execname]=sum(args[0]->b_bcount);}'
dtrace: description 'io:::start' matched 6 probes
^C

sched                                                        286208
fsflush                                                     2939904
mysqld2                                                     5169152
mysqld1                                                    21300224
mysqld3                                                    51502592
bash-3.00#

Maybe some IO's of given instance are mostly cached by filesystem so let's see how many bytes are transferred only when physical IO is done.

bash-3.00# dtrace -n io:::start'/args[0]->b_flags & B_PHYS/{@a[execname]=sum(args[0]->b_bcount);}'
dtrace: description 'io:::start' matched 6 probes
^C

sched                                                        165888
mysqld2                                                      397312
fsflush                                                     1048576
mysqld1                                                     6002176
mysqld3                                                    85908480
bash-3.00#

Let's say we want to know which file is being mostly accessed.

bash-3.00# dtrace -n io:::start'{@a[args[2]->fi_pathname]=sum(args[0]->b_bcount);}'
dtrace: description 'io:::start' matched 6 probes
^C

/opt/mysql1/master.info                                        3072
/opt/mysql2/kawiarenki_dir/MiniCzaty.MYD                       3072
/opt/mysql3/ib_logfile0                                       12288
/opt/mysql2/opteron-slow.log                                  12288
/opt/mysql2/dzieci2/DAYRATE.MYD                               12288
/opt/mysql2/kawiarenki_dir/Pokoje.TMD                         12288
/opt/mysql2/hosting/hp_pages.TMD                              12288
/opt/mysql1/ib_logfile0                                       12288
/opt/mysql2/opteron-bin.012                                   24576
/opt/mysql1/opteron-bin.006                                   24576
/opt/mysql1/opteron-relay-bin.008                             86016
/opt/mysql1/ibdata6                                          147456
/opt/mysql3/opteron-bin.017                                  221184
/opt/mysql1/ibdata2                                          393216
/opt/mysql2/users/users.TMM                                  643072
/opt/mysql1/ibdata3                                          753664
/opt/mysql1/ib_logfile1                                      819200
/opt/mysql1/ibdata5                                         2457600
/var/tmp/#sql_49fc_0.MYD                                    3784704
/opt/mysql3/ibdata1                                         3833856
/opt/mysql1/ibdata4                                         4030464
/opt/mysql3/ibdata3                                         4317184
/var/tmp/#sql_49fc_1.MYD                                    5382144
/opt/mysql2/users/users.TMD                                 5455872
/opt/mysql3/ibdata5                                         5603328
/opt/mysql3/ib_logfile2                                     5664768
/opt/mysql1/ibdata1                                         8970240
/opt/mysql3/ibdata4                                         9183232
/opt/mysql3/ibdata2                                        20111360
bash-3.00#

What you can get freom all of this? Well, first it's probably worth either linking /var/tmp to /tmp or make /var/tmp tmpfs filesystem. You know exactly which instance of MySQL is making most use of disks - so if you have a problem with storage performance you know which instance move to other server or to give it separate storage (probably faster). Then if some subset of files is beeing accessed much more than the rest you can spread these files to separate storage.

And so on... all of this on a production box in a safe manner. All you need is just an imagination of what to ask and DTrace gives you an answer :) (but you've got to know how to ask :))))

IBM supports Solaris

Looks like IBM is going to officially support Solaris on its Bladecenter systems.

I'm pleased to announce we've signed up our first tier 1 systems vendor as a Solaris supporter: it's IBM, and their decision to provide comprehensive support for Solaris on Bladecenter definitely puts them ahead of the other blade vendors in offering a truly OS neutral product.

As a result of our agreement, IBM will be adding value to BladeCenter, optimizing Solaris for IBM hardware offerings, adding volume to the Solaris community, and proving that the best choice for customers is, in fact, real choice. It sends a clear message to IBM accounts that Solaris is now a top tier option for BladeCenter deployments.

Read more

UPDATE: According to article on The Register -"IBM has agreed to sell Sun's operating system with its BladeCenter servers in "the coming months," according to an IBM spokesman."

3mln Solaris licenses!

It hasn't even been a year since Solaris 10 was released and yet yesterday Solaris 10 hits 3,000,000 registered licenses milestone. Official annoucement here.

Winter is coming...

Winter is coming and if you do not like cold nights then go and by Intel CPUs. However if you've got enough heat in your datacenter then looks like AMD is a way to go. Some benchmarks comparing new Intel dual core CPUs to AMD are here. Power consumption benchmark is on page 3.

btw: and despite that Opterons produces much less heat they are faster too! :)))

DTrace - virtualized consumer

One of the really nice features of DTrace is its virtualized consumer. It means that you can run different actions for the same probes at the same time on the same system. This is really useful when many sys admins or developers are looking at the system and application. Thanks to this feature they don't have to care that someone else is doing his work at the same time. This is specially useful for scripts which are run for many hours as you can still do the other work with DTrace.

Secure your laptop

Securing the GRUB Menu.

SX 10/05 is out

New Solaris Express release is out - 10/05 is based on Nevada 23. There's also available SX Community Edition build 24.

And as usual thanks to Dan Price you can read his What's New.

btw: if you put SX on a computer with Windows or diagnostic partition it should detect it automatically and put appropriate GRUB menu entries - for me it just works :) Small but nice.

Interview with Andy Bechtolsheim

Interview with Andy Bechtolsheim - the creator of Galaxy servers.

OpenWeekend in Prague

I attended as a speaker OpenWekend conference in Prague this weekend. I was giving a presentation about DTrace and there were other people talking about OpenSolaris. During the conference first Czech Open Solaris User Group (CZOSUG) meeting happened which I was lucky to attend to. Big thanks to Katarina Machalkova for some translation - without her help I wouldn't understand a word (and they say polish is similar to czech - no, it isn't). The conference was well organized and I think there were over 100 attendees.

I wanted during the presentation to give some live demo of DTrace but unfortunately there was a problem with getting video from Xorg out and there was no time to figure it out. Anyway I gave a small presentation using my laptop to few folks after my presentation - I think people were interested in DTrace.

After the official conference speakers had a meeting and I was talking with other fellows about Linux, Open Solaris, DTrace - this was quite hot conversation for many hours :)

This is me giving presentation :)


Here you can find more pictures.

DTrace updates with new kernel

Recently new kernel patch level became available for Solaris 10 (Sparc). If you do use DTrace I belive you will be interested in this patch. The patch number is 118222-19 and is available for free (no support contract is needed). What's New in -19 revision? - lot of DTrace as you can see - some problems/RFEs are mine :)

update: patch 118844-19 is available for free, this is x86/x64 version of the kernel

4923208 Sb150 systems hang -WARNING: ata_controller - Can not reset Primary channel
5029967 dtrace should provide an option to show probe argument types, stability
5108961 vestigial kadb turds left in dtrace
6213962 dtrace -G doesn't work on sparcv8+ object files
6214615 Conversion of bufinfo_t's b_resid is not defined in the io.d translator
6217821 dtrace cmd fails assertion in dt_proc_lock()
6218854 USDT and the jstack() action don't work on processes on a zone
6219195 lockstat under Solaris 10 unacceptably degrades performance
6220843 dt_pid should look for PR_OBJ_LDSO rather than ld.so
6221490 plockstat(1M) needs options to set aggsize and aggrate
6221495 plockstat(1M) needs a verbose option to report when tracing has started
6221496 plockstat(1M) should have an option to exit after a time limit expires
6221498 plockstat(1M) should have an option to limit number of entries in output
6223379 lockstat fails to report one stack frame
6223603 the pid provider is willing to instrument things it shouldn't
6225650 D compiler can't resolve past implicit forward declarations
6226263 usdt probes will fail to instantiate if pid probes are specified first
6226302 must allow enablings to be retained and rematched
6226320 must allow enablings to be duplicated after tracing is enabled
6226345 dtrace_consume() can call record callbacks with incorrect argument
6228044 the pid provider can miss some function returns
6229159 dtrace should be able to trace dynamically loaded objects
6230315 pid123::ioctl:return finds the wrong instruction
6231207 libdtrace is not able to properly resolve some probe argument types
6232748 pid provider can miss recursive function returns
6234004 libctf should support interfaces for client data in ctf_file_t's
6234033 ctf_type_name() should return NULL if input fp is NULL
6234037 D strchr2esc() incorrectly includes sign extension bit
6234063 D compiler support for USDT translators (part 1)
6234072 pid provider mishandles recursive returns
6234449 ctf_lookup_by_name() fails when typedef is a qualifier substring
6236617 D compiler support for restrict keyword
6236726 ustack() at pid provider return probes can be confusing
6238322 fasttrap::fasttrap:fasttrap args are broken on amd64
6239626 helpers aren't backward compatible with S10
6250382 ctf_type_name() wrong when type order conflicts with lexical precedence
6250386 ctf should not require callers to hardcode type name buffer size
6253027 bufpolicy of "fill" or "ring" causes dtrace(1M) to consume 100% of CPU
6253028 dtrace_probe()/dtrace_state_go() race can induce D data corruption
6253030 adding an action to an ECB takes quadratic time
6253031 dtrace_consume() can (still) call record callbacks with bad argument
6253033 aggregations should be sorted on key as well as value
6254258 dtrace doesn't pick up usdt probes in dlopened objects
6254741 usdt generation can do the wrong thing with tail calls
6258738 fbt refuses to instrument functions starting with branches
6264469 fbt is confused by jump tables in code
6264473 fbt is confused by the return instruction
6265086 DTrace has anemic string handling facilities
6265087 clause-local D variables can only be scalars
6265088 storing NULL to by-reference static variables induces an error
6265090 need mechanism to redirect stdout from within a D script
6265094 copyinstr() should take optional limit parameter
6267670 dt_type_pointer() should report reason for failure
6267671 ctf_add_type() fails when definition added after forward declaration
6267680 D compiler should not permit void parameters to have names
6267682 D compiler is not checking array redeclarations properly
6267693 D compiler support for inline associative array references
6267695 DTrace should provide a fds[] array for file descriptor information
6275414 unary operator * doesn't work properly when applied to args[] elements
6282291 D compiler core dumps in dt_node_dynamic() for inline parameter
6303053 pid provider panic under low memory conditions
6303188 some dtrace scripts with speculations fail to load
6304654 predicates containing args[] references may be incorrectly cached
6209411 truss -u can make a target hang up
6210881 When there is memory pressure dnlc not setting dca_dircache to DC_RET_LOW_MEM
6213074 kphysm_add_memory_dynamic calls dump_resize late
6265027 rpc destroys a CV with waiters
6233615 Fatal System Bus Error during suspend/resume cycle for DR
6235086 divide by zero panic in lgrp_move_thread() during network boot on v40z
6249712 unconfigure memory hangs lgrp_mem_init()
6232864 panic in lgrp_mem_choose() during dr testing
6251625 missed change to prototype in lgrpplat causes build failures for OEM customers
6244519 dead code to suspend kernel threads on OS quiesce should be removed
6271688 chdir'ing in /proc blows up.
6271759 pwdx lets you see other users' processes working directories
6272865 race condition between SIGKILL and /proc PCAGENT
6240456 Need topo enumeration for PCI Express
6288246 amd64 kernel needs to detect AMD Opteron erratum 131
6290459 SIGVTALRM signal delivery delayed under Solaris 10 due to t_astflag not being set
6302751 add ptl1_gregs[MAXGL + 1], change PTL1_MAXTL to 2 and rename MMU fields in ptl1_regs structure
6312753 workaround required for PLX erratum 34
6313403 disabling CPUs on console can hang console interface
6313788 Glvc driver need to enable interrupt on virtual channel
6313837 additional safe measure in px required to make cpr to work
6313842 incorrect checking causes non fatal imu_rbne panic
6317693 Chicago needs to use ebus RTC instead of southbridge RTC

SX b24 is out

Get it here.

Where we are in a file?

Let's say you are tar+gzip'ing large file and wonder what is a progres. Well with DTrace that's really simple. First check what is the file size then lets check every 10s where were are currently in a file. Additionally write to the output how many bytes we read since last time.

bash-3.00# ls -l
total 9223488
-rw-r--r-- 1 nobody other 2 Oct 4 11:30 bounds
drwxrwxrwx 2 nobody other 2560 Oct 7 08:13 core
-rw-r--r-- 1 nobody other 1206160 Oct 4 11:21 unix.0
-rw-r--r-- 1 nobody other 4718870528 Oct 4 11:30 vmcore.0

bash-3.00# dtrace -n io:::start'/args[2]->fi_pathname == "/mnt/vmcore.0"/{fs=args[2]->fi_offset;}' -n BEGIN'{last=0;}' -n tick-10s'{trace(fs);trace(fs-last);last=fs;}'
dtrace: description 'io:::start' matched 6 probes
dtrace: description 'BEGIN' matched 1 probe
dtrace: description 'tick-10s' matched 1 probe
CPU ID FUNCTION:NAME
0 47663 :tick-10s 3758587904 3758587904
0 47663 :tick-10s 3792044032 33456128
0 47663 :tick-10s 3838902272 46858240
0 47663 :tick-10s 3890118656 51216384
0 47663 :tick-10s 3936387072 46268416
0 47663 :tick-10s 3977871360 41484288
0 47663 :tick-10s 4020830208 42958848
0 47663 :tick-10s 4069294080 48463872
0 47663 :tick-10s 4120510464 51216384
^C

bash-3.00#

New laptop community

New laptop community has been created on Open Solaris. WiFi Solaris Wireless Driver for Atheros AR52xx 802.11b/g Chipset-based Cards has been posted with source. Also new wificonfig tool is available.
If you've got an laptop and have any problems with putting (Open) Solaris on it this new community is a place to go.

StarOffice 8 for free

"Academic and Research institutions, including Primary and Secondary (K12) Schools, 2-and 4-year Colleges, and Universities, are eligible for a no-cost license of StarOffice 8. All you have to do to obtain the StarOffice 8 Software License and Entitlement is purchase at least one StarOffice 8 Enterprise Media Kit or download the software and click thru the license agreement on the Sun Software Download Center as described below."

"If you are a student, researcher, staff, or faculty member you can download StarOffice 8 for free from Sun's Software Download Center."

I've just installed it on my workstation with Solaris 10 - works great. Some MS Office ducuments which weren't imported properly using SO7 are working now!

Official page here.

Sun and Google partnership

Sun and Google announced partnership. No much was reveled except that Sun will bundle Google Toolbar with Java. Google will buy more Sun hardware - "We're already a Sun systems customer, and we're going to extend that quite significantly," said Google CEO Eric Schmidt.

"There's a huge alignment strategy with research and development, (involving) OpenDocument format, OpenOffice and OpenSolaris," said McNealy.

You can find a lot of media coverage for this announcement - the one I get citations from is here.

Well, this is good news. If Google will really get involved seriously with Open Solaris that would be great. Then Google buying more hardware from Sun (Opteron?) is another good news.

Another Open Solaris distribution

LiveCD distribution based on Open Solaris named BeleniX - more info here.
So right now there are 3 distributions based on Open Solaris: Solaris Express, SchilliX and BeleniX. Of course SX is most mature and complete.