My favorite Linux tool in DB work is ‘iostat -x’ (and I really really want to see whenever I’m doing any kind of performance analysis), yet I had to learn its limitations and properties. For example, I took 1s snapshot from a slightly overloaded 16-disk database box:

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           8.12    0.00    2.57   21.65    0.00   67.66

Device:  rrqm/s   wrqm/s     r/s     w/s   rsec/s   wsec/s \
sda     7684.00    19.00 2420.00  498.00 81848.00  5287.00 \

        avgrq-sz avgqu-sz   await  svctm  %util
           29.86    32.99   11.17   0.34 100.00

I pasted this somewhere on IRC, and got “doesn’t look too healthy” and that it is disk-bound. Now, to understand if it really is, one has to understand what iostat tells here.

First line of numbers shows that we’ve got plenty of CPU resources (thats because nowadays it is quite difficult to get a box with not enough CPU power, …

I was having problems recently with a dedicated production server, that runs my MySQL Server and a number of websites. It’s most annoying when your system crashes without any reporting in /var/log/messages

The tool of choice from the host provider SoftLayer was PassMark BurnInTest Linux which is installed with every dedicated server.

I will need to investigate open source alternatives, as this is a commercial product, but for the purposes of my pain, this included tool was well worth the investment.

**************
RESULT SUMMARY
**************
Test Start time: Sun Feb 22 16:02:48 2009
Test Stop time: Sun Feb 22 16:07:49 2009
Test Duration: 000h 05m 01s

Test Name Cycles Operations Result Errors Last Error
CPU - Maths 261 488 Billion PASS 0 No errors
Memory (RAM) 2 3.081 Billion FAIL 1 Error verifying …

Monitoring MySQL Memory is a rather critical task because you can’t limit MySQL’s usage of physical memory resources. Improperly configured servers running MySQL can crash because you don’t understand memory usage.

MySQL uses memory in a number of different ways. Using the Oracle analogy, you can divide the mysqld memory usage into main areas of:

• SGA - System Global Area
• PGA - Process Global Area

The SGA is the footprint that MySQL uses for startup. This is attributed to the base footprint of the mysqld process and a number of buffers including:

• key_buffer_size - For MyISAM Indexes (note you can define multiple key_buffer’s The MyISAM Key Cache) …

This might be a bit cutting edge, but the new fallocate() call in > Linux 2.6.23 might be able to improve InnoDB performance.

When InnoDB needs more space it auto-extends the current data file by 8MB. If this is writing out zeros to the new data (to initialize it) then using fallocate() would certainly be faster.

Apparently, XFS supports this too but needs an ioctl. XFS could support fallocate in the future as well…

…

This is part 3 in my RAMSan Series.

While I am confident the read-only test was a reasonably good test ( I just needed to push more ), my mixed load test was marred by issues.  It was really a quick attempt to get a heavy read/write workload.  I ran into issues with how I wrote this so I will spare you the details.  Some flash devices are notoriously poor performing in writes, so its important to at least briefly look at this.  What I will share are the IOPS & latency numbers from this test.  The mixed workload does updates & selects at this point, these are a mix of PK updates, secondary index updates, etc.  These typically are built to run faster and smaller the the read-only IO bound workload.

By the 11th interval the Ramsan was pretty much complete.  The peaks are whats interesting…  lets look at this in a slightly different way.

So in the admittedly flawed mixed workload I am …

This is part 2 of My RAMSan Series.

In my normal suite of benchmarks I typically run dbt2 & sysbench oltp benchmarks next…  and I did run then, but to be honest they just weren’t that interesting.  They showed an improvement over my intel ssd results I ran on frankenmatt,  but it was difficult to provide an apples to apples comparison.   The server hardware was way different ( cpu, memory, controller, etc ).  Plus I typically run a test -vs- non-flash then a test with flash, and ran tests with varying degrees of memory… the test box had 2GB of memory and sparse internal disk, so my normal test cycles were already in jeopardy.  For what I ran   I was pushing CPU limits long before I was hitting the IOPS I saw above.  In fact in a 100W test I ended up peaking @ 1200 iops, while the CPU was @ 100%.

The challenge is building an effective solution that will easily maximize MySQL …

Well its about time I posted this:)  This is part 1 of 3 in my Ramsan series.

For those who have paid attention to my blog, know I love talking  IO!  I also love performance.  Absolutely love it.  Love disk, disk capactiy, io performance, solid state..  So as I march towards my UC session on MySQL Performance on Solid State Disk my goal is to try and test as many high end solid state disk systems as possible.  All the vendors have been great, giving me access to some really expensive and impressive toys.  I finished up testing Texas Memory System’s flash appliance the RamSAN 500 this week and wanted to post some numbers and some thoughts. TMS makes RamSAN appliances that merge disk and RAM into a really fast SANS.     First I go a ways back with TMS, I deployed and Oracle Rac installation on one of their …

InitMarketing has made its calendar of world-wide conferences and trade fairs related to Free and Open Source Software, IT and specific industries available to the public.

It currently includes 122 events in 17 countries taking place in 2009. 43 of them in Germany, 69 in USA. We use this calendar when planing events for our customers, thus we’ll regularly update it. Please let us know of any events which are not on our radar yet by commenting to my blog or commenting at the bottom of the events page.

A very special 5 minute DBA post here, we are crossing over… sys admin & dba oh my! I tend to always look first at the OS, and then move over to looking at what is going on inside the database. So if you have five minutes to look at the OS, what do you look for? What tools do you use? What gotchas are their?

First Everyone should be familiar with top. This is a great tool and place to start.

It is well known that MySQL can use a lot of temporary files and that, using a ramdisk (tmpfs on Linux) can improve performance drastically as Matt has shown here. The problem with tmpfs is its limited size. You need just one query creating a huge temporary file that happens once per day to screw up the tmpfs scenario. I have done some fuse programming in my previous job and I think it is possible to use fuse to mix a tmpfs filesystem with a regular filesystem, the regular filesystem being used only when the tmpfs is full. Fuse has a lower performance level than a regular filesystem but I think the possibility to use tmpfs the vast majority of the queries needing a temporary file will overcome the performance limitation of fuse.

That is the explanation for the MiniWheatFS name, the frosted side is the tmpfs and the healthy side is the regular filesystem. There are many fuse …