Verifying MySQL Replication in action

There is a very simple test to show MySQL replication in action and to also better understand the basics of MySQL Replication. With a configured MySQL environment we can run the following test on your MySQL master, and monitor the MySQL slave.

If you would like to try this, you can use MySQL SandBox which can launch a MySQL Master/Slave configuration in seconds. You can get started with MySQL Sandbox and Download 3.0.04.

Sandbox setup

$ make_replication_sandbox ~/mysql/mysql-5.1.35-osx10.5-x86.tar.gz
$ cd ~/sandboxes/rsandbox_5_1_35/

On the master

We will use a modified version of the numbers procedure found at Filling test tables quickly as our test program.

./m
create schema if not exists test;
use test
drop table if exists numbers;
create table numbers (id int unsigned not null primary key);

delimiter $$

drop procedure if exists fill_numbers $$
create procedure fill_numbers(in p_max int)
deterministic
begin
  declare counter int default 1;
  truncate table numbers;
  insert into numbers values (1);
  while counter < p_max
  do
      insert into numbers (id)
          select id + counter
          from numbers;
      select count(*) into counter from numbers;
      select counter;
  end while;
end $$
delimiter ;

call fill_numbers(2000000);

On the slave

$ watch -n 1 --differences './s2 -e "SHOW SLAVE STATUSG"'

This simple command monitors the replication process dynamically and gives a highlighted output during the process. You will notice different highlighted sections during various stages of the replication process.

The output will look similar to:

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 127.0.0.1
                  Master_User: msandbox
                  Master_Port: 23150
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000001
          Read_Master_Log_Pos: 4586
               Relay_Log_File: mysql_sandbox23152-relay-bin.000029
                Relay_Log_Pos: 251
        Relay_Master_Log_File: mysql-bin.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4586
              Relay_Log_Space: 564
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:

MiFi Installation woes

As I mentioned in MiFi Introduction I took the plunge and purchased a Verizon MiFi. I got this under a 2 year contract for $149.99 and $59.99 per month for 5GB of traffic.

While happy to have a new tech toy, the installation and use of, well that was a painful experience I’d rather not have to endure. Unfortunately I didn’t document all steps with screen shots so I will need to describe what I have.

While it stated the device worked with a Mac, it was clearly not as simple as the instructions stated.

  1. The first problem is, you can’t use the device until you activate it. You can’t activate it unless you already have Internet Access.
  2. The Tips, hits and shortcuts manual also states “VZAccess Manager Installation”, … connect device …, VZAccess installer auto-launches and the drivers will install automatically. You have to read one page further before you get “Mac customers are not required to use VZAccess Manager to use their device”. First, you do have to install the software to use the device (for the first time), there seems no way around that, and second, it does not auto-launch.
  3. Trying to install I read the instructions, “connect to a USB Port, … auto-launches”. This doesn’t happen. Opening in Finder, hoping for an attached device no luck? Now what.
  4. One page further, under Mac OS/X Users, the section that states “not required to use…” also includes a point stating that activation requires MAC OS X 10.4.0 or higher and WWAN Support Update 1.0. It would be nice if you included this as Pre-Requistes in your documentation so it reads chronologically. The docs state this is a free download.
  5. So I downloaded WWAN Support Update 1.0 from the apple web site, got to run it, and it states “Alert: This computer cannot use this update”. What the. I did between my first two attempts to connect to the device, and now run a Software Update, so guessing it was in this because I know no easy way to confirm installed software on a Mac, I truck on.
  6. The Important MiFI 2220 Connection Information
  7. page, shown below states you can access the MiFi Admin Portal at http://192.168.1.1. See Exhibit 1 below. I try that and I get the admin page, and it states “Not Activated”. Did the store not activate the device, or is my attempt to blunder through these steps not correct

  8. Being frustrated I try Verizon Phone Support, the number 1800 922 0204, conveniently absent from the docs FYI. The automated system asks are you a customer, yes I am, what is your number? Well my number is on AT&T, this is just a broadband device, not a phone, trying that doesn’t work. Then trying to get out of the automated system to get an operator an ordeal. Finally a real person.
  9. Real person asks, what is your number, I explain the same story, then I decide to grab the receipt from the store, and I find in small print, under the device name on the receipt a number? Is that my number? I give that and lone behold, that’s the Cell number for the device. Would be nice if they told you that.
  10. I explain my problem, and I’m put on hold, and hold a second time, and more hold a third time to talk to a more technical person.
  11. During this time, I go back over the steps I undertook and lone behold, there is now a device in my Finder. See Exhibit 2. Did it take like 10 mins to find it or what? Perplexing. So while waiting for a tech person, I move on.
  12. I see VZAccess Manager install package, I take this route, and following system reboot on the Mac, the software auto starts, and I’m prompted with the VZAccess Manager screen, See Exhibit 3.
  13. In fine print down the bottom, Activation required. Nice if that was in big letters on first usage of software. A few menu buttons on the top, and a Connect WWan button bottom right. Again, lack of clear UI here.
  14. Clicking Connect WWAN goes though a number of steps, which unfortunately I didn’t keep, but at the end, I finally get an I’m activated message.
  15. To get the device working however, there was also some pain. I had to disconnect my Internet connect, disable Wireless, and talk to the device directly via USB. I could then confirm I could access the Internet. I then had to remove the USB, enable Wireless and connect to get it to communicate via wireless. The activation process needed the USB connection.
  16. I’m finally online, now the need to plug this into my network. The device has an Admin portal at http://192.168.1.1. The first pain, I can’t access this because my Internal network, and multiple devices runs from my router at the same address. Seeing no way around this, I have to reconfigure my internal network, which was a pain. No I can access the Admin Portal. My goal is to change SSID, change password that’s printed on multiple pieces of paper, and also change the address.
  17. On the admin portal, an input box, and button for Login. See Exhibit 4. Nothing in the documentation on this login box, the Verizon tech didn’t know any information about this, so while they were researching I started randomly choosing logins. It was weird that the input was a password protected input (e.g. *****) and there was no username/password. I try the WiFi Password, seems to get truncated. I try admin and lone behold I’m logged in. That’s about as insecure as having nothing.
  18. After all this, I’m finally able to configure the device. See Exhibit 5. I trust it will work when I’m next on the road. Documentation get’s a fail, it’s unclear and incomplete. There needs to be clear and seperate sections for Windows and Mac OS/X, and it needs to detail the installation process correctly and clearly, and probably with some screen prints. I’m not a novice user, and I had difficulty with this process.

Exhibit 1 – Important Information Page

Exhibit 2 – VZAccess Manager Finder

Exhibit 3 – VZAccess Manager

Exhibit 4 – MiFi Administration Portal Login

Exhibit 5 – MiFi Administration Change Password

MiFi Introduction

My first news of the Verizon MiFi was via Twitter when @DonMacAskill said “Think my iPhone 3G via MiFi is faster than AT&T 3G. Should I carry both all the time now?”

A few reviews later such as Verizon Mifi: Personal Wi-Fi Coming this Month and Verizon MiFI a personal broadband bubble to believe in but this on my to buy tech list. I presently have a Generation 1 Day 1 iPhone, so no 3G there.

In summary, this device is the size of 5 credit cards, weighs not much more then 5 credit cards and enables 3G Wireless broadband access on the Verizon network, and also doubles as a WiFi router for up to 5 other devices.

I have been procrastinating about getting one, the combination to do urgent work at midnight on the AirTrain and NY Subway (the above ground part), and then the following day on a client site when the internet was flaky convinced me to get one.

The purchase process was relatively straightforward, except the usual credit check woes for an Australian living in this country. The installation and use, well that’s another painful story to share.

Using statpack with SHOW STATUS

Mark Leith, on of the MySQL Support Team managers wrote some time ago a very nice utility I use often called Statpack.

My use of Statpack is very simple. Take two snaphots of SHOW GLOBAL STATUS and compare to produce a text based version of the statistics.

Over time I’ve grown to love it’s simplicity, but notice a number of shortcomings. Being open source there is always the ability to modify, improve and give back. This post is more about detailing those little annoyances that I’d like to improve, or see improved. It is also a means to collate points into one location that I often forget about over time.

I welcome any input, and specifically help in this open source venture.

Here is my wish list that I can currently remember. I do plan to action, time permitting.

  1. In Statement Activity, the total percentages are great to determine Read/Write ratio, however the ratio is for the Total, not for the period. I’m not sure how best to present, but would be good to know. See Exhibit 1 for a good example.
  2. When sections are not used, e.g. all ZERO values, then suppress for ease of reading. See Exhibit 2 for examples of Prepared Statements and Query Cache, where a simple line like No Prepared Statements activity, or Query Cache not enabled.
  3. Incorporating SHOW VARIABLES output. If this is included in one file, then adding some information may be very valuable when reviewing these audit files. For example in the InnoDB Buffer Pool show innodb_buffer_pool_size. In InnoDB Log Files show innodb_log_file_size, innodb_log_files_in_group, innodb_log_buffer_size. This can be used in most sections. See Exhibit 3 for an example.
  4. Incorporate a Date/Time in the report output. Again for historical purposes, at worst it could be the time the output is generated, however this is only an approximation. With SHOW GLOBAL STATUS output from my hourly.sh monitoring I include the following line before each SHOW STATUS output. ‘| date_time | 090611.161511 |’. Note to Drizzle Development team, please add date/time output to SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES.
  5. Negative numbers. There are occurrences when negative numbers occur, due to the wrapping of status numbers. See Exhibit 4 for example.
  6. Bug I’ve raised about divide by zero error when the same file (operator error) is specified.

Exhibit 1 – Percentages on Period/Total

                     SELECT:           56,019                    28.11              281,456,428 (41.00%)
                     INSERT:          420,083                   210.78              325,218,748 (47.38%)
                     UPDATE:               46                     0.02                  138,206 (0.02%)
                     DELETE:           48,590                    24.38               79,170,553 (11.53%)

Exhibit 2 – Suppress sections

 ====================================================================================================
                                        Prepared Statements
====================================================================================================

   Prepared Statement Count:                0                     0.00                        0
                    PREPARE:                0                     0.00                        0
                    EXECUTE:                0                     0.00                        0
         DEALLOCATE PREPARE:                0                     0.00                        0
           Fetch Roundtrips:                0                     0.00                        0
             Send Long Data:                0                     0.00                        0

====================================================================================================
                                            Query Cache
====================================================================================================

       QCache Hits / SELECT:           0.00%
   QCache Hit/Qcache Insert:           0.00%
  Qcache Hits/Invalidations:           0.00%
                    SELECTs:       13,503,876                 3,798.56            4,298,170,239
           Query Cache Hits:                0                     0.00                        0
        Query Cache Inserts:                0                     0.00                        0
         Queries Not Cached:                0                     0.00                        0
    Cache Low Memory Prunes:                0                     0.00                        0
         Total Cache Blocks:                0                     0.00                        0
           Queries In Cache:                0                     0.00                        0
          Cache Free Blocks:                0                     0.00                        0

Exhibit 3 – Including Variables


====================================================================================================
                                         InnoDB Buffer Pool
====================================================================================================

Variables: innodb_buffer_pool_size = 10G, innodb_additional_mem_pool_size = 20M, innodb_file_per_table

Buffer Pool Read Efficiency:          99.89%
                  Data Read:              17G                                            4,892G
               Data Written:              29M                                            6,167G
...

====================================================================================================
                                          InnoDB Log Files
====================================================================================================

Variables: innodb_log_file_size=256M; innodb_log_files_in_group=2;innodb_log_buffer_size=1M;innodb_flush_log_at_trx_commit=2

           Log Data Written:               1G                                              327G
                 Log Writes:            4,756                     1.34                1,785,842
         Log Write Requests:        4,195,878                 1,180.28              773,981,459
                  Log Waits:                0                     0.00                       13

Exhibit 4- Negative Numbers

====================================================================================================
                                            Index Usage
====================================================================================================

           Index Efficiency:           51.09%
           Full Index Scans:            7,056                     0.08                  142,549
           Full Table Scans:          191,051                     2.21                4,739,205
            Full Join Scans:              374                     0.00                    9,601
         Handler_read_first:            7,056                     0.08                  142,549 (0.00%)
           Handler_read_key:      136,956,475                 1,585.20            3,698,727,203 (47.29%)
          Handler_read_next:       55,431,938                   641.60              290,189,865 (3.71%)
          Handler_read_prev:          210,418                     2.44                7,283,443 (0.09%)
           Handler_read_rnd:       58,241,824                   674.12            1,862,365,188 (23.81%)
      Handler_read_rnd_next:   -1,682,312,703               -19,471.89            1,963,342,385 (25.10%)

A Full output Example

====================================================================================================
              Uptime: 17 days 17 hours 22 mins Snapshot Period 1: 59 minute interval
====================================================================================================
                    Variable	Delta/Percentage 	    Per Second			  Total
====================================================================================================
                                         Database Activity
====================================================================================================

          Threads Connected:               -5                                               115
            Threads Running:               -4                                                51
                  Questions:       24,190,027                 6,804.51            7,366,339,778
             Bytes Recieved:               1G                     501K                     522G
                 Bytes Sent:              11G                       3M                   2,808G
            Aborted Clients:                0                     0.00                      237
           Aborted Connects:              240                     0.07                  102,090

====================================================================================================
                                         Statement Activity
====================================================================================================

                     SELECT:       13,503,876                 3,798.56            4,298,170,239 (94.25%)
                     INSERT:           91,101                    25.63               25,327,062 (0.56%)
                     UPDATE:          782,004                   219.97              220,640,296 (4.84%)
                     DELETE:            9,674                     2.72                2,485,643 (0.05%)
                    REPLACE:                0                     0.00                    4,980 (0.00%)
          INSERT ... SELECT:                0                     0.00                        0 (0.00%)
         REPLACE ... SELECT:                0                     0.00                        0 (0.00%)
               Multi UPDATE:                0                     0.00                        0 (0.00%)
               Multi DELETE:                0                     0.00                        0 (0.00%)
                     COMMIT:           46,422                    13.06               13,700,478 (0.30%)
                   ROLLBACK:                0                     0.00                       13 (0.00%)

====================================================================================================
                                        Prepared Statements
====================================================================================================

   Prepared Statement Count:                0                     0.00                        0
                    PREPARE:                0                     0.00                        0
                    EXECUTE:                0                     0.00                        0
         DEALLOCATE PREPARE:                0                     0.00                        0
           Fetch Roundtrips:                0                     0.00                        0
             Send Long Data:                0                     0.00                        0

====================================================================================================
                                           Admin Commands
====================================================================================================

                       KILL:                0                     0.00                       21
                      FLUSH:                0                     0.00                       34
              ANALYZE TABLE:                0                     0.00                        0
             OPTIMIZE TABLE:                0                     0.00                        0
                CHECK TABLE:                0                     0.00                       51
               REPAIR TABLE:                0                     0.00                        0

====================================================================================================
                                            Thread Cache
====================================================================================================

          Thread Efficiency:           100.00%
                Connections:        2,404,601                   676.40              691,324,391
            Threads Created:                0                     0.00                    1,015

====================================================================================================
                                            Table Cache
====================================================================================================

     table_cache Efficiency:           12.94%
                Open Tables:               18                     0.01                    1,100
              Opened Tables:               18                     0.01                    8,501

====================================================================================================
                                          MyISAM Key Cache
====================================================================================================

      Cache Read Efficiency:           75.56%
     Cache Write Efficiency:           2.50%
                Memory Used:               0B                                               24B
                Memory Free:               0B                                               13K
                  Key Reads:                0                     0.00                       44
          Key Read Requests:                0                     0.00                      179
                 Key Writes:                0                     0.00                       39
         Key Write Requests:                0                     0.00                       39
         Blocks Not Flushed:                0                     0.00                        0

====================================================================================================
                                         InnoDB Buffer Pool
====================================================================================================

Buffer Pool Read Efficiency:          99.89%
               Memory Total:               0B                                               11G
                Memory Free:               0B                                                0B
                Memory Data:     -12,910,592B                                               10G
               Memory Dirty:             245M                                                3G
                  Data Read:              17G                                            4,892G
               Data Written:              29M                                            6,167G
          Buffer Pool Reads:          864,166                   243.08              234,058,329
  Buffer Pool Read Requests:      674,921,081               189,851.22          213,974,254,498
 Buffer Pool Write Requests:       34,337,556                 9,658.95            5,907,928,452

====================================================================================================
                                          InnoDB Log Files
====================================================================================================

           Log Data Written:               1G                                              327G
                 Log Writes:            4,756                     1.34                1,785,842
         Log Write Requests:        4,195,878                 1,180.28              773,981,459
                  Log Waits:                0                     0.00                       13

====================================================================================================
                                            Query Cache
====================================================================================================

       QCache Hits / SELECT:           0.00%
   QCache Hit/Qcache Insert:           0.00%
  Qcache Hits/Invalidations:           0.00%
                    SELECTs:       13,503,876                 3,798.56            4,298,170,239
           Query Cache Hits:                0                     0.00                        0
        Query Cache Inserts:                0                     0.00                        0
         Queries Not Cached:                0                     0.00                        0
    Cache Low Memory Prunes:                0                     0.00                        0
         Total Cache Blocks:                0                     0.00                        0
           Queries In Cache:                0                     0.00                        0
          Cache Free Blocks:                0                     0.00                        0

====================================================================================================
                                            Index Usage
====================================================================================================

           Index Efficiency:           96.38%
           Full Index Scans:                0                     0.00                    3,685
           Full Table Scans:           26,468                     7.45                9,336,473
            Full Join Scans:                0                     0.00                        0
         Handler_read_first:                0                     0.00                    3,685 (0.00%)
           Handler_read_key:       60,313,973                16,965.96           18,840,285,973 (14.08%)
          Handler_read_next:      284,810,290                80,115.41          109,257,639,924 (81.68%)
          Handler_read_prev:        2,524,541                   710.14              822,756,210 (0.62%)
           Handler_read_rnd:       10,268,347                 2,888.42            3,230,517,410 (2.42%)
      Handler_read_rnd_next:          780,215                   219.47            1,612,883,644 (1.21%)

====================================================================================================
                                          Temporary Space
====================================================================================================

  tmp_table_size Efficiency:           2.10%
         Memory Temp Tables:           26,467                     7.45                9,335,030
           Disk Temp Tables:           25,951                     7.30                9,138,705
                 Temp Files:                2                     0.00                      601

====================================================================================================
                                          Lock Contention
====================================================================================================

    Percent of Locks Waited:            0.00%
         Table Locks Waited:                0                     0.00                      230
      Table Locks Immediate:       14,411,359                 4,053.83            4,555,498,747

====================================================================================================
                                              Sorting
====================================================================================================

                Rows Sorted:          790,892                   222.47              251,683,065
                 Sort Range:            3,767                     1.06                  983,825
                  Sort Scan:           25,952                     7.30                9,137,325
          Sort Merge Passes:                1                     0.00                      298
           Full Range Joins:                0                     0.00                        0

The value of multi insert values

Baron got a great amount of response from his 50 things to know before migrating Oracle to MySQL. I’m glad I invited him as a fellow MySQL colleague to my presentation to the Federal Government on Best Practices for Migrating to MySQL from Oracle and SQL Server for his inspiration.

Oracle will always be a more featured product then MySQL. There are however features that MySQL has that Oracle does not. While I’ve got a draft of a list of my own, I have several hundred incomplete drafts.

One of these features I was able to demonstrate to a client is the ability to have multiple VALUES clauses for a single INSERT statement. For example.

INSERT INTO t1(c1) VALUES (1), (2), (3), (4), (5);

Instead of

INSERT INTO t1(c1) VALUES(1);
INSERT INTO t1(c1) VALUES(2);
INSERT INTO t1(c1) VALUES(3);
INSERT INTO t1(c1) VALUES(4);
INSERT INTO t1(c1) VALUES(5);

Does it make a difference? What is the performance improvement?

The number one reason for an improvement in performance is the lack of network latency for each command. We ran a number of tests in a specific example for the client, taking multiple single insert statements, and combining into combined statements.

We ran tests across localhost and also a network test.

It was found that taking several thousand INSERT queries and combined into a maximum of 1M packets made sense. Overall this single test showed a 88% improvement from 11.4 seconds to 1.4 seconds.

real        0m11.403s
user        0m0.175s
sys         0m0.157s

real        0m1.415s
user        0m0.019s
sys         0m0.012s

In the real world example, differences in the volumes of query to combine and system load showed a reduction of 72%

What is max_tmp_tables?

Recently I came across another configuration option I’d not heard of before. I profess to not know them all, however I do know when I find something unusual. If you are a beginner DBA, learn what is normal and expected, and identify what is out of the normal, investigate, research and question if necessary.

I gave away a MySQL Administrator’s Book based on seeing a configuration with safe-show-database, an option I’d not seen before, and then requesting people giving basic configuration options in that situation.

The latest is max_tmp_tables. So, what does the manual say for this option. I quote:

The maximum number of temporary tables a client can keep open at the same time. (This option does not yet do anything.)

If this option doesn’t yet do anything, why is it there? Does it actually do something and the documentation is misrepresenting the option? Did it provide some feature or functionality before?

I know MySQL has a number of features where are not fully featured or even production strength in a production environment. This makes me wonder how many more parameters, options or features exist but don’t actual do anything or work as designed!

I then also question where organizations or people come up with using these options when the basic required options are not in place. Sometimes just using the basics is the best practice to start with.

O'Reilly Twitter Boot Camp a success

The first O’Reilly Twitter Boot Camp#OTBC was held in New York as a pre cursor to 140 Characters Conference#140conf on Monday 15th June, 2009.

With opening and closing keynotes were like matching bookends of The Twitter Book #twitterbook offered to all attendees and authored by the keynoters @timoreilly and @SarahM.

Attendees came from across the country. Just a few I spoke with coming from LA – @EricMueller of @FLWbooks, Texas – @marlaerwin , Vancouver – HootSuite, Las Vegas -zappos, Boston – @mvolpe , Philadelphia, @SBrownCCI from Cincinnati and @sticky_mommy from Vermont.

The demographics of attendees was a little different from my usual O’Reilly conferences of MySQL, OSCON and Web 2.0. There were less the half the attendees with laptops at hand for notes & twittering, offset by the high blackberry or should I say shaq-berry users (Thanks Ami @digitalroyalty), easily seen from the back of the steep and dark auditorium. A greater proportion of different industries and gender lead to many questions and discussions from users, not just technologists.

The morning panel sessions afforded no question time due to speakers providing good but overtime content. Over lunch Mike Volpe of HubSpot a corporate sponsor for the day set the standard by asking his panel of speakers to stick on time. This afforded almost 30 minutes of question time and a roar of approval from the crowd.

There is a lot of valuable information you can find by Twitter Search of #OTBC. A few examples include:

  • @archivesnext: Good advice: RT @mpedson RT @timoreilly: Twitter usage policy from @zappos at #OTBC: “Just be real and use your best judgement.”
  • @GeekGirlCamp: Hmmmm. Lots of conflicting views on following on Twitter here. What makes YOU follow someone? Would love to know… #OTBC
  • @CarriBugbee: ROI is a tricky thing on twitter; if you’re using it solely to generate revenue, you might be there for the wrong reason – @wholefoods #otbc
  • @mvolpe: “Driving ROI on Twitter” slides and video of my presentation later today for #OTBC – http://tinyurl.com/061509mvolpe
  • @ronaldbradford: Best Practices for Twitter – Build a commercial-grade profile. @CarriBugbee at #OTBC
  • @journalismgal: Ask questions within your tweets even something as simple as your fab apple #otbc
  • @ronaldbradford: Do stay tuned in. Nights, weekends, holidays are all twitter time. Maria Erwin @wholefoods at #OTBC
  • @harrybrelsford: Is Twitter the new Google? That is belief of @erictpeterson Twitter is creating entire new businesses (Flash Light books) #otbc #smbnation

My individual brands of @ronaldbradford and @MySQLExpert will certainly benefit from a wealth of knowledge of the day. If only I had my Twitter name on the tee shirt I was wearing for the event.

The only down sides to the venue the lack of power for attendees, flaky Internet and a basement auditorium with no cell phone service. Important things to re-consider for a online technology conference. In true form the attendees including myself @ronaldbradford, @SBrownCCI, @GeekGirlCamp, @14str8 used the medium of the conference and our voices were heard and some limited power made available. Thanks O’Reilly for listening.

Thank you to all speakers @katmeyer, @timoreilly, @steverubel, @zappos, @carribugbee, @twittermoms, @flwbooks, @davidjdeal, @bethharte, @dunkindonuts, @reggiebradford, @wholefoods, @tedmurphy, @adbroad, @digitalroyalty, @erictpeterson, @mvolpe, @laureltouby, @sarahm and to Zappos.com for the after event happy hour.

Wafflecloud with cream

I have been working recently with Matt Yonkovit to get Waffle Grid cloud enabled with Amazon Web Services (AWS).

An initial version of Waffle Grid Cream – Version 0.5 release is now available.

We have elected to create one AMI for now, that is ready to be configured as either a MySQL Server, a memcached server, or as in the following example both. For this first version, we have also not configured MySQL or memcache, but rather provide a virgin Waffle Grid ready server for developers to experiment and benchmark with.

Future releases will include custom AMI’s and the automated ability to register new memcached servers with the Waffle Grid enabled MySQL server.

Instance Creation

We assume you have created an EC2 account and are using one of the many tools available to launch images.

The AMI you want to launch is ami-0575936c. This is an Ubunut Intrepid 8.10 32bit small instance, and includes MySQL 5.4.0 beta and Memcache 1.4.0 RC1.

Configuration

$ ssh -i [key] ubuntu@ec2-[hostname]
$ ps -ef | grep -e "mysql" - "memcached"
$ memcached -m 1024 -p 11211 -u nobody -l 127.0.0.1 -d
$ memstat -s localhost
$ sudo /etc/init.d/mysql start

Verification

$ mysql -uroot -e "SELECT VERSION"
$ mysql -uroot -e "SHOW ENGINE INNODB STATUSG"

The Innodb Status shows a new section.

---------
MEMCACHED
---------
Memcached puts    0
Memcached hits    0
Memcached misses  0
Memcached Prefix:  3576

Testing

$ mysql -uroot -e "SELECT COUNT(*) FROM sakila.actor"
$ mysql -uroot -e "SHOW ENGINE INNODB STATUSG"
$ memcached -s localhost

Verification will show the change of information in the INNODB STATUS output.

---------
MEMCACHED
---------
Memcached puts    4
Memcached hits    0
Memcached misses  4
Memcached Prefix:  3576
Memcached Miss Total Lat 103 (us)
Memcached Miss Recent Lat 103 (us)
Memcached Set Total Lat 760 (us)
Memcached Set Recent Lat 760 (us)

You can also confirm stats in memcached.

$memstat -s localhost
Listing 1 Server

Server: localhost (11211)
        pid: 3453
        uptime: 575
        time: 1245013741
        version: 1.4.0-rc1
        pointer_size: 32
        rusage_user: 0.0
        rusage_system: 0.0
        curr_items: 5
        total_items: 5
        bytes: 82265
        curr_connections: 6
        total_connections: 9
        connection_structures: 7
        cmd_get: 4
        cmd_set: 5
        get_hits: 0
        get_misses: 4
        evictions: 0
        bytes_read: 82265
        bytes_written: 82265
        limit_maxbytes: 1073741824
        threads: 5

multi-threaded memcached

I discovered while compiling Wafflegrid today that by default, the Ubuntu binaries for memcached are not-multithreaded.

Following the installation of memcached from apt-get and libmemcached I ran memslap for:

$ memslap -s localhost
    Threads connecting to servers 1
    Took 1.633 seconds to load data

$ memstat -s localhost
Listing 1 Server

Server: localhost (11211)
     pid: 23868
     uptime: 54
     time: 1244575816
     version: 1.2.2
     pointer_size: 32
     rusage_user: 0.90000
     rusage_system: 0.120000
     curr_items: 10000
     total_items: 10000
     bytes: 5430000
     curr_connections: 1
     total_connections: 3
     connection_structures: 2
     cmd_get: 0
     cmd_set: 10000
     get_hits: 0
     get_misses: 0
     evictions: 0
     bytes_read: 5430000
     bytes_written: 5430000
     limit_maxbytes: 0
     threads: 1

By installed the Latest RC 1.4.0 we see.

memslap -s localhost
    Threads connecting to servers 1
    Took 0.866 seconds to load data

memstat -s localhost

Listing 1 Server

Server: localhost (11211)
     pid: 8651
     uptime: 375
     time: 1244577237
     version: 1.4.0-rc1
     pointer_size: 32
     rusage_user: 0.110000
     rusage_system: 0.130000
     curr_items: 10000
     total_items: 10000
     bytes: 5510000
     curr_connections: 5
     total_connections: 8
     connection_structures: 6
     cmd_get: 0
     cmd_set: 10000
     get_hits: 0
     get_misses: 0
     evictions: 0
     bytes_read: 5510000
     bytes_written: 5510000
     limit_maxbytes: 0
     threads: 5

Thanks Matt for pointing that one out.

Problems compiling MySQL 5.4

Seem’s the year Sun had for improving MySQL, and with an entire new 5.4 branch the development team could not fix the autoconf and compile dependencies that has been in MySQL for all the years I’ve been compiling MySQL. Drizzle has got it right, thanks to the great work of Monty Taylor.

I’m working on the Wafflegrid AWS EC2 AMI’s for Matt Yonkovit and while compiling 5.1 was straight forward under Ubuntu 8.10 Intrepid, compiling 5.4 was more complicated.

For MySQL 5.1 I needed only to do the following:

apt-get install -y build-essential
apt-get install libncurses5-dev
./configure
make
make install

For MySQL 5.4, I elected to use the BUILD scripts (based on Wafflegrid recommendations). That didn’t go far before I needed.

apt-get install -y automake libtool

You then have to go compiling MySQL 5.4 for 10+ minutes to get an abstract error, then you need to consider what dependencies may be missing.
I don’t like to do a blanket apt-get of a long list of proposed packages unless I know they are actually needed.

The error was:

make[1]: Entering directory `/src/mysql-5.4.0-beta/sql'
make[1]: warning: -jN forced in submake: disabling jobserver mode.
/bin/bash ../ylwrap sql_yacc.yy y.tab.c sql_yacc.cc y.tab.h sql_yacc.h y.output sql_yacc.output -- -d --verbose
make -j 6 gen_lex_hash
make[2]: Entering directory `/src/mysql-5.4.0-beta/sql'
rm -f mini_client_errors.c
/bin/ln -s ../libmysql/errmsg.c mini_client_errors.c
make[2]: warning: -jN forced in submake: disabling jobserver mode.
rm -f pack.c
../ylwrap: line 111: -d: command not found
/bin/ln -s ../sql-common/pack.c pack.c
....
make[1]: Leaving directory `/src/mysql-5.4.0-beta/sql'
make: *** [all-recursive] Error 1

What a lovely error ../ylwrap: line 111: -d: command not found

ylwrap is part of yacc, and by default in this instance it’s not even an installed package. I’ve compiled MySQL long enough that it requires yacc, and actually bison but to you think it would hurt if the configure told the user this.

It’s also been some time since I’ve compiled MySQL source, rather focusing on Drizzle. I had forgotten just how many compile warnings MySQL throws. Granted a warning is not an error, but you should not just ignore them in building a quality product.

Understanding your RAID Configuration

For any production MySQL Database system, running RAID is a given these days. Do you know what RAID your database is? Are you sure?. Ask for quantifiable reproducible output from your systems provider or your System Administrator.

As a consultant I don’t always know the specific tools for the clients deployed H/W, but I ask the question. On more the one occasion the actual result differed from the clients’ perspective or what they were told, and twice I’ve discovered that clients when asked if their RAID was running in a degraded mode, it actually was and they didn’t know.

You can read about various benchmarks at MySQL blogs such as BigDBAHead and MySQL Performance Blog however getting first hand experience of your actually RAID configuration, the H/W and S/W variables is critical to knowing how your technology works. You can then build on this to run your own benchmarks.

Over 50% of my clients run on DELL equipment, most using local storage or shared storage options such as Dell MD1000, Dell MD3000, NetApps or EMC. I’ve had the opportunity to spend a few days looking into the more details of RAID, specifically the DELL PERC 5/i Raid Controllers, and I’ve started a few MySQL Cheatsheets for my own reference that others may also benefit from.

Understanding PERC RAID Controllers gives an overview of using the MegaCLI tools to retrieve valuable information on the Adapter, Physical Drives, Logical Drives and the all important Battery Backed Cache.

There are several Google search results out there about finding the MegaCLI tools, I found them to be all outdated. There is of course other tools including Dell OpenManage Server Administrator (GUI and CLI) and an Open Source project called megactl.

Here is just a summary of a few lines of each that yields valuable information:

Adapter Details

$ /opt/MegaRAID/MegaCli/MegaCli64 -AdpAllInfo -aALL

RAID Level Supported             : RAID0, RAID1, RAID5, RAID10, RAID50
Max Stripe Size         : 128kB
Stripe Size                    : 64kB

Physical Details

$ /opt/MegaRAID/MegaCli/MegaCli64 -LDPDInfo -aall

Adapter #0
Number of Virtual Disks: 1

RAID Level: Primary-5, Secondary-0, RAID Level Qualifier-3
Size:208128MB
Stripe Size: 64kB
Number Of Drives:4
Default Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU
Current Cache Policy: WriteBack, ReadAheadNone, Direct, No Write Cache if Bad BBU

Raw Size: 70007MB [0x88bb93a Sectors]
Inquiry Data: FUJITSU MAY2073RC       D108B363P7305KAU
Inquiry Data: FUJITSU MAY2073RC       D108B363P7305KAJ
Inquiry Data: FUJITSU MAY2073RC       D108B363P7305JSW
Inquiry Data: FUJITSU MAY2073RC       D108B363P7305KB1

Battery

$ MegaCli -AdpBbuCmd -aALL
  Fully Charged           : Yes
  Discharging             : Yes

BBU Capacity Info for Adapter: 0
Relative State of Charge: 100 %
Absolute State of charge: 88 %
Run time to empty: 65535 Min

BBU Properties for Adapter: 0

Auto Learn Period: 7776000 Sec
Next Learn time: 304978518 Sec

The big detail that was missing was the details in this ouput of the drive speed, such as 7.2K, 10K, 15K. What is the impact? Well that’s the purpose of the next step.

Following this investigation, testing of the RAID configuration with Bonnie++ was performed to determine the likely performance of various configurations, and to test RAID0, RAID1, RAID5 and RAID10.

Further testing that would be nice would include for example RAID 5 with 3 drives verses 4 drives. The speed of the drives, the performance in a degraded situation, and the performance during a disk rebuild.

This still leaves the question about how to test the performance with and without the Battery Backed Cache. You can easily disable this via CLI tools, but testing an actually database test, and pulling the power plug for example with and without would yield some interesting results. More concerning is when Dell specifically discharges the batters, and it takes like 8 hours to recharge. In your production environment you are then running in degraded mode. Disaster always happens at the worse time.

MySQL Book winner – for 5 configuration options

If you had to configure a WordPress MU installation without access to any details of your MySQL Configuration, what would you do?

What top five configuration settings would you use?

I asked the community this question, see For MySQL DBA fame and glory. Prize included and a number of brave soles responded for a chance to win a free copy of MySQL Administrators Bible by MySQL Community She-BA Sheeri Cabral.

There is no perfect answer and of course you would want to set more then five options, however the purpose of the competition was to seek what people would do with limited information and a limited choice of actions to enable people to focus on what is important.

Part of choosing the winner was for those that accurately read the question. There were a number of triggers. These included:

  • Give an answer with five options. That was the question.
  • WordPress MU gives you an indication of the schema size (about 300 tables), the default storage engine of MyISAM, and the technology stack includes PHP.
  • The current MySQL configuration included just two existing options.

What would I do?

If I was asked this question, here is what I’d do.

  1. Demand more information about the H/W the Mysql server running on. I also would do my own physical verification as client description != actual implementation.
  2. Confirm if the server is shared or dedicated.
  3. Get SHOW GLOBAL VARIABLES output.
  4. Get 2 x SHOW GLOBAL STATUS output, separated from some time, e.g. 1 minute, 10 minutes, 1 hour.

Without the above information I’m not really prepared to tune a system in isolation. I’ll spend more time at a later time explaining why.

So my answers for reference.

key_buffer_size

We have as a pre-cursor to the top 5, the key_buffer_size. The key_buffer_size is used to store MyISAM Indexes, and is assigned as MySQL server startup. Setting this value to large is a waste of space, setting this too small will increase the amount of disk I/O. In this example, the total index space was around 100MB, so setting the value to 384-512M supports adequate growth. The default value for 5.0.67 is 8M. When you read on, see point 5, you may well consider lowering this value.

1. log-bin=/path/to/dir/file-prefix

If you are storing data in a database I’m sure you want to keep it. log-bin is necessary for point in time recovery. Period. You should also specify the actually path, and store this outside of the default MySQL data directory. The default is NOT enabled.

2. myisam_recover = force,backup

MyISAM is not the most optimal storage engine for online systems. This is a much larger discussion however for the stock default WP and general LAMP products we start with MyISAM. Setting myisam_recover will improve one disadvantage of MyISAM and that is the recovery on startup. The default is NOT enabled.

3. query_cache_size = 64M

In absence of greater information, enabling the Query Cache in a generally high read environment is a good starting point. You have the ability to set and unset this dynamically on a running server, so it is easy to remove if performance is not the optimal. You should also not go overboard with the size of the query cache. Start with a modest amount, such as 64M, assess the impact of the QC and re-assess the value. The default is NOT enabled. The Query Cache also uses the variables query_cache_type, which defaults to ON, and query_cache_limit which defaults to 1M.

4. table_cache=1024

The MU part of the WordPress is a trigger that the system will have several hundred tables. Monitoring Opened_tables, and Open_tables is as easy check to determine a more appropriate table_cache value. The default value on 5.0.67 is 64.

5. max_connections=150

This was a difficult choice, the first 4 are obvious for the current environment.

I have to question the reason why max_connections=500. Given that there is no other configuration settings, why would this value be so large. Start with a more realistic figure, such as the default of 150, and monitor max_used_connections, threads_running for a while. A review of the H/W confirmed this is unrealistic, the system has 16GB of RAM, but only a 32bit OS.

Moving forward

Tuning MySQL is not a trivial or simple task. It takes a certain amount of knowledge about the system. Recently I have seen some very unusual MySQL configurations, and I have to pass on this information for all readers and DBA’s.

Gather information about your environment, and the setting in question before making a change. Document this on an internal wiki. It is important, when in a year’s time somebody asks, why did you change this?

The bonus prize was for anybody that questioned the current configuration which only included two parameters.

I had to look up –safe-show-database. No wonder I didn’t know it, Deprecated since 4.0.2. I wonder sometimes when people add uncommon options. Shlomi the only person to at least raise a ? on that on.

And the winner, I had to consider 3 people that had 3 of my 5. Shlomi Noach made such a plea I have to give it to him, and I’ll throw in international shipping.

(Mis)Understanding RAID configuration

Update 8 June 2009
Be sure you to check out some of my research including Understanding your RAID Configuration and Understanding PERC RAID Controllers.

Last week I got caught thinking a MySQL production system wasn’t configured with RAID when it actually was. The following is the Linux dmesg output of 3 different client MySQL servers, where the H/W is all the same, a Dell 2950.

In the first two cases you will see individual references to the internal disks, however in the third you do not. This threw me, but as Matt Yonkovit correctly pointed out to me, the sizes are not standard drive sizes.

A deeper level of understanding via CLI tools, or actually console output would appear necessary to understand the disk configuration in the future.


Nov 2007 – Dell 2950 Running RHEL 4. Note the individual SEAGATE drive details and single sda (70G) and sdb(300G)

scsi0 : LSI Logic SAS based MegaRAID driver
  Vendor: SEAGATE   Model: ST973451SS        Rev: SM04
  Type:   Direct-Access                      ANSI SCSI revision: 05
  Vendor: SEAGATE   Model: ST973451SS        Rev: SM04
  Type:   Direct-Access                      ANSI SCSI revision: 05
  Vendor: SEAGATE   Model: ST973451SS        Rev: SM04
  Type:   Direct-Access                      ANSI SCSI revision: 05
  Vendor: SEAGATE   Model: ST973451SS        Rev: SM04
  Type:   Direct-Access                      ANSI SCSI revision: 05
usb 4-1: new high speed USB device using ehci_hcd and address 2
usb 4-1: configuration #1 chosen from 1 choice
hub 4-1:1.0: USB hub found
hub 4-1:1.0: 2 ports detected
  Vendor: SEAGATE   Model: ST973451SS        Rev: SM04
  Type:   Direct-Access                      ANSI SCSI revision: 05
  Vendor: SEAGATE   Model: ST973451SS        Rev: SM04
  Type:   Direct-Access                      ANSI SCSI revision: 05
  Vendor: SEAGATE   Model: ST973451SS        Rev: SM04
  Type:   Direct-Access                      ANSI SCSI revision: 05
  Vendor: DP        Model: BACKPLANE         Rev: 1.05
  Type:   Enclosure                          ANSI SCSI revision: 05
  Vendor: DELL      Model: PERC 5/i          Rev: 1.03
  Type:   Direct-Access                      ANSI SCSI revision: 05
SCSI device sda: 142082048 512-byte hdwr sectors (72746 MB)
sda: Write Protect is off
sda: Mode Sense: 1f 00 10 08
SCSI device sda: drive cache: write through w/ FUA
SCSI device sda: 142082048 512-byte hdwr sectors (72746 MB)
sda: Write Protect is off
sda: Mode Sense: 1f 00 10 08
SCSI device sda: drive cache: write through w/ FUA
 sda: sda1 sda2 sda3 sda4 < sda5 >
sd 0:2:0:0: Attached scsi disk sda
  Vendor: DELL      Model: PERC 5/i          Rev: 1.03
  Type:   Direct-Access                      ANSI SCSI revision: 05
SCSI device sdb: 568328192 512-byte hdwr sectors (290984 MB)
sdb: Write Protect is off
sdb: Mode Sense: 1f 00 10 08
SCSI device sdb: drive cache: write through w/ FUA
SCSI device sdb: 568328192 512-byte hdwr sectors (290984 MB)
sdb: Write Protect is off
sdb: Mode Sense: 1f 00 10 08
SCSI device sdb: drive cache: write through w/ FUA

Oct 2008 – Dell 2950 running Ubuntu 8.04. Note the individual SEAGATE drive details and single sda (600GB)

[   88.069326] scsi0 : LSI Logic SAS based MegaRAID driver
[   88.087755] scsi 0:0:0:0: Direct-Access     SEAGATE  ST3300656SS      HS09 PQ: 0 ANSI: 5
[   88.104931] scsi 0:0:1:0: Direct-Access     SEAGATE  ST3300656SS      HS09 PQ: 0 ANSI: 5
[   88.122079] scsi 0:0:2:0: Direct-Access     SEAGATE  ST3300656SS      HS09 PQ: 0 ANSI: 5
[   88.139064] scsi 0:0:3:0: Direct-Access     SEAGATE  ST3300656SS      HS09 PQ: 0 ANSI: 5
[   88.205346] scsi1 : ata_piix
[   88.205387] scsi2 : ata_piix
[   88.205409] ata1: PATA max UDMA/100 cmd 0x1f0 ctl 0x3f6 bmdma 0xfc00 irq 14
[   88.205411] ata2: PATA max UDMA/100 cmd 0x170 ctl 0x376 bmdma 0xfc08 irq 15
[   88.240207] scsi 0:0:32:0: Enclosure         DP       BACKPLANE        1.05 PQ: 0 ANSI: 5
[   88.252370] scsi 0:2:0:0: Direct-Access     DELL     PERC 6/i         1.21 PQ: 0 ANSI: 5
[   88.263532] scsi 0:0:32:0: Attached scsi generic sg0 type 13
[   88.263547] scsi 0:2:0:0: Attached scsi generic sg1 type 0
[   88.265390] Driver 'sd' needs updating - please use bus_type methods
[   88.265671] sd 0:2:0:0: [sda] 1169686528 512-byte hardware sectors (598880 MB)
[   88.265700] sd 0:2:0:0: [sda] Write Protect is off
[   88.265702] sd 0:2:0:0: [sda] Mode Sense: 1f 00 10 08
[   88.265771] sd 0:2:0:0: [sda] Write cache: disabled, read cache: enabled, supports DPO and FUA
[   88.266135] sd 0:2:0:0: [sda] 1169686528 512-byte hardware sectors (598880 MB)
[   88.266173] sd 0:2:0:0: [sda] Write Protect is off
[   88.266175] sd 0:2:0:0: [sda] Mode Sense: 1f 00 10 08
[   88.266232] sd 0:2:0:0: [sda] Write cache: disabled, read cache: enabled, supports DPO and FUA
[   88.266240]  sda: sda1 sda2 sda3
[   88.270085] sd 0:2:0:0: [sda] Attached SCSI disk

May 2009 – Dell 2950 running RHEL 5. Note no individual drive details, only partition sizes for sda (300G) and sdb (600G).

scsi0 : LSI Logic SAS based MegaRAID driver
  Vendor: DP        Model: BACKPLANE         Rev: 1.05
  Type:   Enclosure                          ANSI SCSI revision: 05
  Vendor: DELL      Model: PERC 5/i          Rev: 1.03
  Type:   Direct-Access                      ANSI SCSI revision: 05
SCSI device sda: 584843264 512-byte hdwr sectors (299440 MB)
SCSI device sda: drive cache: write through
SCSI device sda: 584843264 512-byte hdwr sectors (299440 MB)
SCSI device sda: drive cache: write through
 sda: sda1 sda2 sda3 sda4 < sda5 sda6 sda7 sda8 sda9 sda10 sda11 >
Attached scsi disk sda at scsi0, channel 2, id 0, lun 0
  Vendor: DELL      Model: PERC 5/i          Rev: 1.03
  Type:   Direct-Access                      ANSI SCSI revision: 05
SCSI device sdb: 1169686528 512-byte hdwr sectors (598880 MB)
SCSI device sdb: drive cache: write through
SCSI device sdb: 1169686528 512-byte hdwr sectors (598880 MB)
SCSI device sdb: drive cache: write through
 sdb: sdb1 sdb2 sdb3

Leveraging the power of Twitter

Last week I posted the following twitter request“Can somebody loan me (or buy me) a Dell 2950 decked out so I can run and publish some benchmarks. Please!”

In a same day response I was offered access to use 2 x Dell 1950’s, and today I’m now actually using these machines for my own testing. I would like to thank cafemom (Barry, Anthony & Dan) for the loan of hardware.

And now the chance to better understand the RAID configuration of the DELL PERC Controllers, trying out some different RAID types, LVM configurations and disk tests. When I’m done with my System Administrator refresher, I’m then be trying some different MySQL Benchmarks to test various MySQL configuration settings including using the new Juice benchmark.