Big DBA Head!

Wanted to remember these for later:

DELL PERC 6i & LSI RAID CHEAT SHEET:

To add a RAID 10:
./MegaCli64 -CfgSpanAdd -R10 -Array0[17:0,17:1] -Array1[17:2,17:3] -Array2[17:4,17:5] -Array3[17:6,17:7] -Array4[17:8,17:9] WB NORA  -strpsz64 -a0

(WB is write back, WT is write through)
(NORA is no read ahead, RA is read ahead)

To delete a Logical device:
./MegaCli64 -cfgLDDel -l0 -a0

To Start inilization:
./MegaCli64 -LDInit -Start -L0 -a0

TO Display the background initialization
./MegaCli64 -LDBI -ProgDsply -L0 -a0

To Display all the physical and logical devices on the array:

./MegaCli64 -LDPDInfo -aall

docs are here:

http://www.lsi.com/files/docs/techdocs/storage_stand_prod/sas/mr_sas_sw_ug.pdf

Wanted to note some weirdness in using sysbench’s rnrd tests on SLES 10.  Running some bencharks for a client on their new SAN this week and ran into an interesting issue.  Whenever I run sysbench with the following:

time sysbench –num-threads=16 –test=fileio –file-total-size=20G –file-test-mode=rndrd  run

The system CPU spikes to 90-100% of the machine and almost no io ends up going to the disk.  Seems like I am missing something, used the same version on ubuntu & centos without the same issues.  Tried local & san disk, the same pattern holds true.

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
5.40    0.00   86.66    0.00    0.00    7.95

Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda          0.00   1.80  0.00  1.90    0.00   31.20     0.00    15.60    16.42     0.00    0.00   0.00   0.00
sdb          0.00   0.30  0.00  1.00    0.00   11.20     0.00     5.60    11.20     0.00    0.00   0.00   0.00
sdc          0.00   0.00  0.00  0.00    0.00    0.00     0.00     0.00     0.00     0.00    0.00   0.00   0.00

The following disk tests are fine however:

time sysbench –num-threads=16 –test=fileio –file-total-size=20G –file-test-mode=rndwr  run
time sysbench –num-threads=16 –test=fileio –file-total-size=100G –file-test-mode=rndrw –file-rw-ratio=1 run

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
           0.01    0.00    6.94    5.78    0.00   87.25

Device:    rrqm/s wrqm/s   r/s   w/s  rsec/s  wsec/s    rkB/s    wkB/s avgrq-sz avgqu-sz   await  svctm  %util
sda          0.00   0.43  0.03  0.38    0.27    7.20     0.13     3.60    17.92     0.00    0.48   0.48   0.02
sdb          0.00   0.23  0.00  0.43    0.00    6.13     0.00     3.07    14.15     0.00    1.85   1.23   0.05
sdc          0.00 58386.72  0.08 556.98    0.67 470907.33     0.33 235453.67   845.34    67.17  120.58   1.15  64.19

So its only the read random test.  Not sure what is going on yet, but I thought I would through this out here to remind me to test & figure this out later.

Caught this little bit of info this morning, western digital is rumored to be building a 20K RPM drive.   It seems that all this flash/ssd love has made the rotational challenged vendors a little jealous.  After years of just 10K & 15K disks maybe the emergence of SSD will start a disk arms race.

A couple more articles I saw posted out on the web on Sun’s new SSD offerings.  In the The official press release…  their are very scant details on the sun offerings right now.  But the official release says:

“Sun is already shipping Solaris ZFS software optimized for SSD technologies through the OpenSolaris(TM) community and is the first major systems vendor to add an end-to-end Flash-based disk product line to its portfolio, leapfrogging competitors and giving customers 3x better performance at one-fifth the energy consumption of traditional spinning disk offerings.”

Hopefully I am misreading this… 3X performance seems slower then the other SSD I have looked at.

In another article, their is a quote on performance however:  “Fowler argues that flash is more cost-effective than disk in some cases because of its high IOPS rate. According to Sun, a 146GB disk drive with 15,000 RPM gets about 180 write IOPS and 320 read IOPS, while a 32GB flash drive gets 5,000 or more write IOPS and at least 30,000 read IOPS. “…  These are interesting … but because their is no indication if these numbers are random or sequential its hard to get too excited about them just yet.  But what is interesting is the press release say 3x and the qoute is 27x.

I just need to figure out how to get my hands on some test

A couple of noteworthy things…

First Dvnation’s price on memorite SSD’s has dropped ( a couple of weeks ago actually ).   32GB GT Models are only $845 dollars… which is about 20%.  This still look to be the fastest around.  I am debating on buying one to compare it to the mtron i tested earlier this year…  now if I can only figure out how to sneak $900 past my wife I will be set.

Found an interesting blurb it seems Sun is getting in the SSD market …  working for the company I hope that I can hook myself up with some test hardware.  Although I keep asking my boss for a SAN with 100+ drives for my basement and still have not gotten one

Anyone see the entry level samsung SSD drives?  supposed to be as fast the mtron’s only cheaper.

Ahh seems like a few people do take the time to read my blog:) Peter Z Commented here on my common disk performance mistakes post. He makes some great arguments, and you may want to give it a read. While he does not agree with everything I say it is interesting to see his views. Remember different folks have different experiences and a lot of times there are multiple roads on the path the performance nirvana.

Let me start off saying I wholly admit that saying “everything” is a disk issue is a dramatic exaggeration. And i did not specifically say disk, I said “The problem is always an IO problem”, more on that later. I have run into my far share of issues outside of this sphere ( network, context switching, cpu ), but I still find disk performance to be by far the most common issue effecting systems I deal with. In my opinion It is the reason memcache had to be invented, the reason for explosive growth in the amount of memory in servers, etc. If all of your disks performed even at half the speed of memory the urgent need to fit everything into memory would not be so urgent ( Now that sounds silly ). This is not a MySQL specific issue either I was dealing with disk performance issues since the mid 90’s in other database systems.

Read the rest of this entry »

Over the past several years whether I am working with a small company or a fortune 500 client I have seen lots of issues and mistakes made around the configuration, setup, and ongoing maintenance of disk on Unix and Linux Servers. This is not only an issue with MySQL shops, rather it can be an issue with all database setups whether it is Oracle, DB2, or Sybase. Neglecting the disk is setting yourself up for long term issues. These mistakes often force companies to throw more and more hardware at the problem… Lets look at some common mistakes around disk:

#1 The problem is always an IO problem, and remember spindles not capacity

In performance disk is everything. It makes you happy, it makes you sad. Learn to love it. Learn to hate it. Learn to understand it. Everything eventually comes back to disk. Take this example: Lets say you have a bad query that you fix with an index. Why was it slow? The easy answer : was a missing index. But really the answer is the missing index caused the query to read too much data from disk. The index merely reduced the number of ios that needed to be used to complete the query. In a perfect world the disk would be fast enough to return the query without you even realizing it was missing. But our world is not perfect. So what are some disk related performance tips?

Read the rest of this entry »

The messages going back and forth on the “close sourcing” or paying to make use of some “plugins” /addons within MySQL are flying still! And in my opinion this is getting way out of hand. < DISCLAIMER >I work for Sun/MySQL as a senior consultant but claim no special knowledge or agenda here. The comments here are simply my opinions. </ DISCLAIMER> I can not help but look at all the hubbub and laugh a little bit. The vast majority of what I have read seems way off base (more of a he/she posted this, fifth hand retelling instead of from official channels). But there is a lot of FUD floating around, and I keep seeing more and more legit news sources picking up on this “massive change to licensing”, which In my opinion it really is a non-story… because there is no massive change.

What is the issue?

Read the rest of this entry »

As I run into the same mistake over and over again I am going to throw them out here in order to serve as a warning and a learning tool to others. Some of these may seem like I am beating a dead horse because you can find examples of these on other sites, in other blogs, or even in the manual. Despite the large amounts of data on the subjects these things still pop up. This is not a complete list, and this list is not in any order… so please don’t dispare if I miss your favorite common performance gaff. As I encounter more I will post them to the common mistakes category here on bigdbahead.com. Hopefully this list will grow to include a large subset of issues and maybe one or two people will take note and fix their applications and databases.

#1.) MORE IS NOT ALWAYS BETTER

We often see configuration examples where folks believe in the old adage more is better. Case in point if a 2MB sort buffer size is good, a 32MB or a 128MB sort buffer size is even better. While it is true that larger key buffer and the innodb buffer;s can provide huge performance boosts, other settings can actually seriously hamper performance. Case in point the per thread buffers. The join buffer, read buffer, read rnd buffer, sort buffer, etc. These buffers set limits on memory per thread. Setting these too high can run the risk of some really large memory footprints in individual threads.

Most of the time when I see these values are set higher then 2MB, it is not that high because of careful consideration or benchmarking, but was set simply because bigger must be better. If your following general guidelines for a system running innodb you will already have a large portion of your system memory allocated to the innodb buffer pool. The larger innodb buffer pool with larger per thread memory seriously increases the chances of swapping. Another consideration is that setting certain buffers to higher values often will cause the MySQL engine to change how it is handling memory and can lead to inefficiencies. I am not going to rewrite a lot of detail that is already out in the wild on these. But the motto “Think before you tinker” is a good rule to live by.

Here is a a good list of required reading on setting the per thread buffers to high:

http://dev.mysql.com/doc/refman/5.1/en/memory-use.html
http://www.mysqlperformanceblog.com/2007/09/17/mysql-what-read_buffer_size-value-is-optimal/
http://www.mysqlperformanceblog.com/2006/06/06/are-larger-buffers-always-better/
http://mysql-ha.com/2007/09/06/read-buffer-performance-hit/
http://www.mysqlperformanceblog.com/2007/09/12/read-buffers-mmap-malloc-and-mysql-performance/
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html

Read the rest of this entry »

Ran into an interesting issue with a client today who was using innodb_file_per_table. This client had a lot of tables ( 300K+), they were using innodb_file_per_table. Today they had a problem that caused their db to crash. After fixing their issue, the database restart seemed to hang. The only thing in the error log was the mysql was started, their was no crash recovery messages. An strace revealed that the mysqld process was stating all the datafiles. It seems that during a recovery the first step is to just check to see if all the individual files are really their. The entire process to simply stat each datafile took over 45 minutes to complete after which the actual crash recovery started. Just a quick note to remind myself in the future if I see this again.