You are here: Home / Archives for Technologies / Open Source

mysql.com and related sites are down

by

I tried to go to mysql.com and Planet MySQL over my lunch break at OSCON 2009 to find the websites are down. Seems from conversions with fellow Drizzle colleagues this has been down for some time.

What does your site look like when your system is unavailable or down?

This is a question I ask clients. What redundancy do you have in place for DNS, for a site unavailable page, for a static copy of content?

I learned my first personal lesson several years ago when at The Planet, my server and 9,000 others were unavailable at least 40 hours due to explosion, fire at a data center. While I had copies of my site, and shared hosting options elsewhere, all DNS was also in the same unavailable data center. This was definitely a shortcoming of the Host Provider at the time.

For any commercial site, it is important that at least your have geographical redundancy for DNS. Let’s use mysql.com as an example investigation.

Identify DNS records

$ dig mysql.com

; < <>> DiG 9.4.3-P1 < <>> mysql.com
;; global options:  printcmd
;; Got answer:
;; ->>HEADER< <- opcode: QUERY, status: NOERROR, id: 63421
;; flags: qr rd ra; QUERY: 1, ANSWER: 1, AUTHORITY: 4, ADDITIONAL: 0

;; QUESTION SECTION:
;mysql.com.			IN	A

;; ANSWER SECTION:
mysql.com.		2839	IN	A	213.136.52.29

;; AUTHORITY SECTION:
mysql.com.		72	IN	NS	ns7.sun.com.
mysql.com.		72	IN	NS	ns8.sun.com.
mysql.com.		72	IN	NS	ns1.sun.com.
mysql.com.		72	IN	NS	ns2.sun.com.

;; ADDITIONAL SECTION:
ns1.sun.com.		86045	IN	A	192.18.128.11
ns2.sun.com.		86075	IN	A	192.18.99.5
ns7.sun.com.		86085	IN	A	192.18.43.15
ns8.sun.com.		86093	IN	A	192.18.43.12

;; Query time: 2 msec
;; SERVER: 10.10.16.2#53(10.10.16.2)
;; WHEN: Wed Jul 22 14:18:11 2009
;; MSG SIZE  rcvd: 183

I am definitely no expert in networking, my understanding is your defined DNS server contain your primary information that is then delegated to servers worldwide.

These servers are up and running. Having no ping response is not an indicator the server not available.

mactaz:~ rbradfor$ ping -c 1 ns1.sun.com
PING ns1.sun.com (192.18.128.11): 56 data bytes
64 bytes from 192.18.128.11: icmp_seq=0 ttl=242 time=66.891 ms

--- ns1.sun.com ping statistics ---
1 packets transmitted, 1 packets received, 0% packet loss
round-trip min/avg/max/stddev = 66.891/66.891/66.891/0.000 ms
mactaz:~ rbradfor$ ping -c 1 ns2.sun.com
PING ns2.sun.com (192.18.99.5): 56 data bytes
64 bytes from 192.18.99.5: icmp_seq=0 ttl=239 time=58.879 ms

--- ns2.sun.com ping statistics ---
1 packets transmitted, 1 packets received, 0% packet loss
round-trip min/avg/max/stddev = 58.879/58.879/58.879/0.000 ms
mactaz:~ rbradfor$ ping -c 1 ns7.sun.com
PING ns7.sun.com (192.18.43.15): 56 data bytes
64 bytes from 192.18.43.15: icmp_seq=0 ttl=244 time=3.921 ms

--- ns7.sun.com ping statistics ---
1 packets transmitted, 1 packets received, 0% packet loss
round-trip min/avg/max/stddev = 3.921/3.921/3.921/0.000 ms
mactaz:~ rbradfor$ ping -c 1 ns8.sun.com
PING ns8.sun.com (192.18.43.12): 56 data bytes
64 bytes from 192.18.43.12: icmp_seq=0 ttl=244 time=4.076 ms

--- ns8.sun.com ping statistics ---
1 packets transmitted, 1 packets received, 0% packet loss
round-trip min/avg/max/stddev = 4.076/4.076/4.076/0.000 ms

They even appear to be in different locations which is good.

$ traceroute 192.18.128.11
traceroute to 192.18.128.11 (192.18.128.11), 64 hops max, 40 byte packets
 1  10.10.0.1 (10.10.0.1)  1.575 ms  0.882 ms  1.538 ms
 2  10.10.16.2 (10.10.16.2)  0.329 ms  0.366 ms  0.376 ms
 3  gateway.above.net (209.133.114.1)  1.567 ms  0.785 ms  0.863 ms
 4  ge-11-0-2.er1.sjc2.us.above.net (64.124.196.161)  1.386 ms  1.567 ms  1.214 ms
 5  xe-0-1-0.mpr4.sjc7.us.above.net (64.125.30.178)  2.177 ms  1.907 ms  1.873 ms
 6  above-att.sjc7.us.above.net (64.125.12.118)  5.361 ms  3.927 ms  3.717 ms
 7  cr2.sffca.ip.att.net (12.123.15.162)  66.434 ms  66.523 ms  66.694 ms
 8  cr2.la2ca.ip.att.net (12.122.31.133)  67.472 ms  66.008 ms  65.632 ms
 9  cr2.dlstx.ip.att.net (12.122.28.177)  66.003 ms  66.372 ms  66.723 ms
10  cr1.attga.ip.att.net (12.122.28.173)  66.472 ms  66.001 ms  66.908 ms
11  gar1.chlnc.ip.att.net (12.122.141.77)  66.139 ms  65.835 ms  65.892 ms
12  12.125.220.10 (12.125.220.10)  67.209 ms  66.569 ms  66.529 ms
13  cltea-ns-1.sun.com (192.18.128.11)  66.357 ms  66.756 ms  66.386 ms
mactaz:~ rbradfor$ traceroute 192.18.99.5
traceroute to 192.18.99.5 (192.18.99.5), 64 hops max, 40 byte packets
 1  10.10.0.1 (10.10.0.1)  1.159 ms  0.763 ms  0.704 ms
 2  10.10.16.2 (10.10.16.2)  0.298 ms  0.303 ms  0.290 ms
 3  gateway.above.net (209.133.114.1)  0.637 ms  0.784 ms  0.937 ms
 4  ge-11-0-2.er1.sjc2.us.above.net (64.124.196.161)  1.513 ms  1.743 ms  1.746 ms
 5  xe-0-1-0.mpr4.sjc7.us.above.net (64.125.30.178)  2.066 ms  1.417 ms  4.144 ms
 6  above-att.sjc7.us.above.net (64.125.12.118)  3.835 ms  3.374 ms  4.001 ms
 7  cr2.sffca.ip.att.net (12.123.15.162)  56.427 ms  56.191 ms  55.553 ms
 8  cr1.dvmco.ip.att.net (12.122.28.54)  55.819 ms  55.508 ms  55.442 ms
 9  gar1.dvmco.ip.att.net (12.122.144.37)  55.429 ms  55.406 ms  55.401 ms
10  12.125.159.146 (12.125.159.146)  59.293 ms  59.501 ms  59.237 ms
11  192.18.101.249 (192.18.101.249)  58.936 ms  59.099 ms  60.184 ms
12  brm-ea-ns-1.Sun.COM (192.18.99.5)  60.090 ms  59.285 ms  59.289 ms
mactaz:~ rbradfor$ traceroute 192.18.43.15
traceroute to 192.18.43.15 (192.18.43.15), 64 hops max, 40 byte packets
 1  10.10.0.1 (10.10.0.1)  1.070 ms  0.639 ms  0.639 ms
 2  10.10.16.2 (10.10.16.2)  0.323 ms  0.238 ms  0.242 ms
 3  gateway.above.net (209.133.114.1)  1.524 ms  2.697 ms  0.615 ms
 4  ge-11-0-2.er1.sjc2.us.above.net (64.124.196.161)  1.463 ms  1.510 ms  1.922 ms
 5  xe-0-1-0.mpr4.sjc7.us.above.net (64.125.30.178)  7.735 ms  2.136 ms  66.881 ms
 6  xe-0-0-0.mpr3.sjc7.us.above.net (64.125.27.85)  1.744 ms  3.131 ms  1.874 ms
 7  * above-level3.sjc7.us.above.net (64.125.13.242)  49.976 ms  2.078 ms
 8  ae-11-69.car1.SanJose1.Level3.net (4.68.18.3)  124.861 ms  206.837 ms  5.631 ms
 9  SUN-MICROSY.car1.SanJose1.Level3.net (4.53.16.50)  3.182 ms  3.579 ms  3.348 ms
10  192.18.44.18 (192.18.44.18)  4.168 ms  4.611 ms  4.146 ms
11  * * *
12  * * *
13  * *^C
mactaz:~ rbradfor$ traceroute 192.18.43.12
traceroute to 192.18.43.12 (192.18.43.12), 64 hops max, 40 byte packets
 1  10.10.0.1 (10.10.0.1)  1.206 ms  0.818 ms  0.879 ms
 2  10.10.16.2 (10.10.16.2)  0.348 ms  0.485 ms  0.465 ms
 3  gateway.above.net (209.133.114.1)  10.055 ms  1.911 ms  1.775 ms
 4  ge-11-0-2.er1.sjc2.us.above.net (64.124.196.161)  1.278 ms  0.963 ms  1.307 ms
 5  xe-0-1-0.mpr4.sjc7.us.above.net (64.125.30.178)  2.243 ms  2.004 ms  2.041 ms
 6  * xe-0-0-0.mpr3.sjc7.us.above.net (64.125.27.85)  2.016 ms  2.104 ms
 7  above-level3.sjc7.us.above.net (64.125.13.242)  2.143 ms  1.471 ms  2.106 ms
 8  ae-41-99.car1.SanJose1.Level3.net (4.68.18.195)  2.970 ms  3.103 ms ae-31-89.car1.SanJose1.Level3.net (4.68.18.131)  2.876 ms
 9  SUN-MICROSY.car1.SanJose1.Level3.net (4.53.16.50)  3.054 ms  3.414 ms  2.925 ms
10  192.18.44.18 (192.18.44.18)  3.721 ms  3.643 ms  3.622 ms
11  scaea-ns-1.sun.com (192.18.43.12)  4.350 ms  3.905 ms  4.188 ms

A traceroute of mysql.com shows it’s outside of the Sun network that at least the DNS servers are at.

$ traceroute 213.136.52.29
traceroute to 213.136.52.29 (213.136.52.29), 64 hops max, 40 byte packets
 1  10.10.0.1 (10.10.0.1)  1.243 ms  0.750 ms  0.844 ms
 2  10.10.16.2 (10.10.16.2)  0.397 ms  0.353 ms  0.413 ms
 3  gateway.above.net (209.133.114.1)  1.254 ms  1.021 ms  0.976 ms
 4  ge-11-0-2.er1.sjc2.us.above.net (64.124.196.161)  1.448 ms  0.933 ms  14.524 ms
 5  * xe-0-1-0.mpr4.sjc7.us.above.net (64.125.30.178)  1.734 ms  2.025 ms
 6  sjo-bb1-link.telia.net (213.248.94.29)  2.001 ms  1.942 ms  2.212 ms
 7  nyk-bb2-link.telia.net (80.91.254.176)  75.310 ms  81.628 ms  75.063 ms
 8  kbn-bb2-link.telia.net (80.91.254.90)  175.072 ms  175.445 ms  174.846 ms
 9  s-bb2-pos7-0-0.telia.net (213.248.65.30)  181.580 ms  181.930 ms  182.126 ms
10  s-b3-link.telia.net (80.91.253.226)  184.610 ms  198.216 ms  184.766 ms
11  bahnhof-110262-s-b3.c.telia.net (213.248.97.42)  182.919 ms  185.830 ms  184.827 ms
12  * * *
13  tsic2-gw.bahnhof.net (85.24.151.133)  186.588 ms  186.847 ms  188.352 ms
14  tsic3-gw.bahnhof.net (85.24.151.135)  183.782 ms  183.355 ms  184.660 ms
15  pio-dr1.pio-dr2.bahnhof.net (85.24.151.7)  186.142 ms  186.809 ms  186.723 ms
16  mysql-gw-sec-c.bahnhof.net (85.24.153.74)  183.821 ms  183.793 ms  183.597 ms
17  * * *
18  * * *
19  * * *
20  * * *
21  * * *

For such a significant open source product, I’m surprised that this level of complete unavailability without even a site unavailable page is surprising.

NOTE Further update. It’s been reported the site has been down now for 8+ hours.

Drizzle Query logging

by

Currently Drizzle offers three (3) separate query logging plugins. These plugins offer an extensible means of gathering all or selected queries and provide the foundation for a query analyser tool. Additional filtering includes selecting queries by execution time, result size, rows processed and by any given regular expression via PCRE.

During this tutorial I’ll be stepping though the various logging_query parameters which log SQL in a CSV format.

Confirm Logging Plugins

You can view the current ACTIVE plugins in Drizzle with the following SQL.

drizzle> select version();
+--------------+
| version()    |
+--------------+
| 2009.07.1097 |
+--------------+

drizzle> select * from information_schema.plugins where plugin_name like 'logging%';
+-----------------+----------------+---------------+--------------------------------------+---------------------------------+----------------+
| PLUGIN_NAME     | PLUGIN_VERSION | PLUGIN_STATUS | PLUGIN_AUTHOR                        | PLUGIN_DESCRIPTION              | PLUGIN_LICENSE |
+-----------------+----------------+---------------+--------------------------------------+---------------------------------+----------------+
| logging_gearman | 0.1            | ACTIVE        | Mark Atwood  mark @fallenpegasus.com | Log queries to a Gearman server | GPL            |
| logging_query   | 0.2            | ACTIVE        | Mark Atwood  mark @fallenpegasus.com | Log queries to a CSV file       | GPL            |
| logging_syslog  | 0.2            | ACTIVE        | Mark Atwood  mark @fallenpegasus.com | Log to syslog                   | GPL            |
+-----------------+----------------+---------------+--------------------------------------+---------------------------------+----------------+
3 rows in set (0.01 sec)

Logging all queries

You can define the following configuration variables to enable query logging.

/etc/drizzle/drizzled.cnf
[drizzled]
logging_query_enable=true
logging_query_filename=/var/log/drizzle/general.csv

You can confirm the settings with the following SHOW VARIABLES.

drizzle> show global variables like 'logging_query%';
+---------------------------------------+------------------------------+
| Variable_name                         | Value                        |
+---------------------------------------+------------------------------+
| logging_query_enable                  | ON                           |
| logging_query_filename                | /var/log/drizzle/general.csv |
| logging_query_pcre                    |                              |
| logging_query_threshold_big_examined  | 0                            |
| logging_query_threshold_big_resultset | 0                            |
| logging_query_threshold_slow          | 0                            |
+---------------------------------------+------------------------------+

This command showing queries to be logged.

$ cat /var/log/drizzle/general.csv
1248214561824590,1,1,"","select @@version_comment limit 1","Query",1248214561824590,1240,1240,1,00,0
1248214582588346,1,3,"","show global variables like 'logging_query%'","Query",1248214582588346,1958,1706,6,62,0

Unfortunately the log does not yet provide a header. You need to turn the source code to get a better description of the columns.

      snprintf(msgbuf, MAX_MSG_LEN,
               "%"PRIu64",%"PRIu64",%"PRIu64","%.*s","%s","%.*s","
               "%"PRIu64",%"PRIu64",%"PRIu64",%"PRIu64",%"PRIu64
               "%"PRIu32",%"PRIu32"n",
               t_mark,
               session->thread_id,
               session->query_id,
               // dont need to quote the db name, always CSV safe
               dbl, dbs,
               // do need to quote the query
               quotify((unsigned char *)session->query,
                       session->query_length, qs, sizeof(qs)),
               // command_name is defined in drizzled/sql_parse.cc
               // dont need to quote the command name, always CSV safe
               (int)command_name[session->command].length,
               command_name[session->command].str,
               // counters are at end, to make it easier to add more
               (t_mark - session->connect_utime),
               (t_mark - session->start_utime),
               (t_mark - session->utime_after_lock),
               session->sent_row_count,
               session->examined_row_count,
               session->tmp_table,
               session->total_warn_count);

The important parts of this information include:

  • getmicrotime – 1248214561824590
  • Session Id – 1
  • Query Id – 1
  • Schema
  • The Query: “show global variables like ‘logging_query%'”
  • The Query type “Query”
  • Time session connected – 1248214582588346
  • The total execution time – 1958
  • The execution time after necessary locks – 1706
  • The number of rows returned – 6
  • The number of rows examined – 6
  • The number of temporary tables used – 2
  • The total warning count – 0

I also found what I believe is a formatting problem logged as Bug #402831.

You can enable logging dynamically.

drizzle> select now();
+---------------------+
| now()               |
+---------------------+
| 2009-07-22 02:14:31 |
+---------------------+
1 row in set (0 sec)

drizzle> set global logging_query_enable=true;
Query OK, 0 rows affected (0 sec)

drizzle> select curdate();
+------------+
| curdate()  |
+------------+
| 2009-07-22 |
+------------+
1 row in set (0 sec)

drizzle> set global logging_query_enable=false;
Query OK, 0 rows affected (0 sec)

drizzle> select now();
+---------------------+
| now()               |
+---------------------+
| 2009-07-22 02:14:54 |
+---------------------+
1 row in set (0 sec)

1248228876381645,4,3,"","set global logging_query_enable=true","Query",1248228876381645,761,761,0,00,0
1248228886866882,4,4,"","select curdate()","Query",1248228886866882,105,105,1,00,0

I was not able to alter the logging_query_filename dynamically. Need to confirm with the development team about this functionality for the future.

drizzle> set global logging_query_filename='/tmp/general.csv';
ERROR 1238 (HY000): Variable 'logging_query_filename' is a read only variable

Logging slow queries

If you just wanted to emulate the MySQL slow query log, with a long_query_time of 1 second, you could use the following.

/etc/drizzle/drizzled.cnf
[drizzled]
logging_query_enable=true
logging_query_filename=/var/log/drizzle/slow.csv
logging_query_threshold_slow=1000000

Drizzle supports the ability to set a threshold in microseconds.

NOTE: I wanted to demonstrate this using the popular MySQL SLEEP() function, only to find this is currently not available in Drizzle. This is an ideal example of a simple UDF that can be written and added to Drizzle. One day if I ever have the time.

Here is some sample output using queries > 1 second.

1248216457856195,1,43,"test","insert into numbers   select...","Query",1248216457856195,2160680,2160620,0,26214420,0
1248216462738678,1,45,"test","insert into numbers   select...","Query",1248216462738678,4530327,4530263,0,52428821,0
1248216472430813,1,47,"test","insert into numbers   select...","Query",1248216472430813,8990965,8990890,0,104857622,0
1248216473592812,1,48,"test","select @counter := count(*) from numbers","Query",1248216473592812,1152319,1152257,1,104857622,0

Logging by threshold

Drizzle Query Logging provides the ability to return results by 2 thresholds, the number of rows in the result, and the number of rows examined by the storage engine.

/etc/drizzle/drizzled.cnf
[drizzled]
logging_query_enable=true
logging_query_filename=/var/log/drizzle/slow.csv
logging_query_threshold_big_resultset=100

1248216631322097,1,5,"test","select * from numbers limit 100","Query",1248216631322097,281,217,100,1002,0
1248216642763174,1,6,"test","select * from numbers limit 101","Query",1248216642763174,268,215,101,1012,0

/etc/drizzle/drizzled.cnf
[drizzled]
logging_query_enable=true
logging_query_filename=/var/log/drizzle/slow.csv
logging_query_threshold_big_examined=1000

1248216785430588,1,6,"test","select * from numbers limit 1000","Query",1248216785430588,8055,7983,1000,10002,0
1248216800327928,1,7,"test","select count(*) from numbers","Query",1248216800327928,1041322,1041222,1,10485762,0

Logging by pattern

The final option is to return queries that match a given pattern via a PCRE expression.



/etc/drizzle/drizzled.cnf
[drizzled]
logging_query_enable=true
logging_query_filename=/var/log/drizzle/slow.csv
logging_query_pcre=now



drizzle> select now();
+---------------------+
| now()               |
+---------------------+
| 2009-07-22 03:24:32 |
+---------------------+
1 row in set (0 sec)

drizzle> select curdate();
+------------+
| curdate()  |
+------------+
| 2009-07-22 |
+------------+
1 row in set (0 sec)

drizzle> select "now";
+-----+
| now |
+-----+
| now |
+-----+
1 row in set (0 sec)

drizzle> select "know how";
+----------+
| know how |
+----------+
| know how |
+----------+
1 row in set (0 sec)



1248233072792211,3,2,"","select now()","Query",1248233072792211,154,154,1,00,0
1248233085807520,3,4,"","select "now"","Query",1248233085807520,92,92,1,00,0
1248233096659018,3,5,"","select "know how"","Query",1248233096659018,75,75,1,00,0



Another example using a pattern.


/etc/drizzle/drizzled.cnf
[drizzled]
logging_query_enable=true
logging_query_filename=/var/log/drizzle/slow.csv
logging_query_pcre="[0-9][0-9][0-9]"



drizzle> select 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0 sec)

drizzle> select 11;
+----+
| 11 |
+----+
| 11 |
+----+
1 row in set (0 sec)

drizzle> select 111;
+-----+
| 111 |
+-----+
| 111 |
+-----+
1 row in set (0 sec)

drizzle> select 1111;
+------+
| 1111 |
+------+
| 1111 |
+------+
1 row in set (0 sec)

drizzle> select 11+22;
+-------+
| 11+22 |
+-------+
|    33 |
+-------+
1 row in set (0 sec)



1248233336460373,3,4,"","select 111","Query",1248233336460373,79,79,1,00,0
1248233339300429,3,5,"","select 1111","Query",1248233339300429,82,82,1,00,0



Unfortunately it seems that this variable is also not configurable dynamically at this time.


drizzle> set global logging_query_pcre="now";
ERROR 1238 (HY000): Variable 'logging_query_pcre' is a read only variable



This is definitely an improvement over current MySQL logging.


What's new in MySQL 5.4.1
 

 by   
Absolutely nothing?


5.4.0 was released with a change in the MySQL Binary distributions, delivering only 1 64bit Linux platform and two Sun Solaris platforms. This was officially announced on April 21 2009  however the 5.4.0 Release Notes state 05 April 2009.  So it’s not a big deal, but consistency would be nice.


I’ve seen in a few posts 5.4.1, so I decided to try it out. Spending the time to read what’s changed in 2 months with the 5.4.1 Release Notes before I go downloading and installing, you read.


This release does not differ from 5.4.0 except that binary distributions are available for all MySQL-supported platforms.


Is this going to be the new policy from Sun?  Release for Solaris platforms first, then later release for other platforms?


What to do at 3:25am
 

 by   
Look at MySQL bug reports of course?  Well actually I’m writing multiple blog posts, and I was confirming additional reference sources and links when I came across MySQL Bug #29847 – Large CPU usage of InnoDB crash recovery with a big buf pool.


Taking the time to actually read the information exchange I stumble upon.


[8 Jun 23:29] liz drachnik

Hello Heikki -

In order for us to continue the process of reviewing your contribution to MySQL - We need
you to review and sign the Sun|MySQL contributor agreement (the "SCA")

The process is explained here:

http://forge.mysql.com/wiki/Sun_Contributor_Agreement

Getting a signed/approved SCA on file will help us facilitate your contribution-- this
one, and others in the future.

Thank you !

Liz Drachnik  - Program Manager - MySQL



Oops. Well it made me laugh out loud for so many reasons. First your talking to the creator of InnoDB, a part of MySQL for at least 5+ years. Second, there is clearly an agreement between Oracle and MySQL already for the incorporation of InnoDB in the current builds, but mostly because at this late stage of probably Oracle acquisition (which side note has seemed too quite for too long) it seems rather a mute point to be chasing up paperwork.


Fortunately sanity obviously prevailed, unfortunately the public record persists. Still, humor is always good.


[9 Jun 18:04] Liz Drachnik

Please disregard the previous request for an SCA.
thank you



Never let your binlog directory fill up
 

 by   
Recently with a client while running a number of disaster recovery tests I came across a nasty situation which was not part of the original plan and provided a far worse disaster situation then expected.


I should preface this condition with some of the environment conditions.


    

  • MySQL 5.0 Enterprise 5.0.54
    • 

  • RHEL 5 64bit
    • 

  • Master and 2 Slaves
    • 

  • MySQL Data and MySQL Binary Logs/MySQL Error Logs are on separate disk partitions
    • 



While running stress tests under high load, we tested the filling of partition containing the logs. This partition included the binary log and MySQL error log.


The observed output was.


    

  • An error message was written to the MySQL error log. See below.
    • 

  • Application throughput dropped, but did not stop.
    • 

  • Binary logs stopped occurring.
    • 

  • MySQL proactively stopped logging but continued to process transactions.
    • 



The end result was:


    

  • The error log was ultimately truncated after reboot, so if the information was not captured while this was in action, this important messages would be lost.
    • 

  • The primary slave used for read scalability and the secondary slave used for backups are now unusable.
    • 

  • The backup and recovery process using slaves and point in time recovery via binary logs is not unusable.
    • 

  • The three backup methods in use for the client are ineffective. It was necessary to disable access to the Master, take a full backup, and then sync the slaves from this copy.
    • 



090710 19:01:25 [ERROR] /opt/mysql/bin/mysqld: Disk is full writing '/mysqllog/binlog/hostname-3306-bin.000020'
     (Errcode: 28). Waiting for someone to free space... Retry in 60 secs
090710 19:01:46 [ERROR] Error writing file '/mysqllog/slow_log/hostname_3306_slow_queries.log' (errno: 1)
090710 19:02:25 [ERROR] Error writing file '/mysqllog/binlog/hostname-3306-bin' (errno: 28)
090710 19:02:25 [ERROR] Could not use /mysqllog/binlog/hostname-3306-bin for logging (error 28).
    Turning logging off for the whole duration of the MySQL server process. 
    To turn it on again: fix the cause, shutdown the MySQL server and restart it.



Updated


I discuss in detail the options for the MySQL error log including recommendations for the MySQL error log file location in Monitoring MySQL – The error log


Getting wireless working on Ubuntu Macbook
 

 by   
I run Ubuntu 9.04 Januty on my Macbook. Previously installing Ubuntu 8.10, wireless worked automatically, for 9.04 it did not.


This is what I did to fix it.


    

  1. Verify your Macbook is seeing the Broadcom controller. See below for the lspci command, and expected output.
    2. 

  2. Goto System -> Administration -> Hardware Drivers.  The Broadcom STA wireless driver is activated, deactivate it.
    3. 

  3. Add to /etc/modules  a line with wl
    4. 

  4. Reboot
    5. 

  5. Goto System -> Administration -> Hardware Drivers.  Activate the Broadcom STA wireless driver.
    6. 

  6. Reboot
    7. 

  7. Wireless now operational.
    8. 



$ lspci
...
02:00.0 Network controller: Broadcom Corporation BCM4328 802.11a/b/g/n (rev 03)
03:00.0 Ethernet controller: Marvell Technology Group Ltd. Marvell Yukon 88E8058 PCI-E Gigabit Ethernet Controller (rev 13)
04:03.0 FireWire (IEEE 1394): Agere Systems FW323 (rev 61)



For reference, modprobe wl does not return any output in my environment, yet wireless works fine.


References:



Understanding InnoDB MVCC
 

 by   
Multi versioning concurrency control (MVCC) is a database design theory that enables relational databases to support concurrency, or more simply multiple user access to common data in your database.


In  MySQL the InnoDB storage engine provides  MVCC, row-level locking, full ACID compliance as well as other features.


In my understanding of database theory, access to modify independent sections of unique data (e.g. UPDATE) under MVCC should fully support concurrency.  I have however experienced a level of exclusive locking under Innodb.


I wanted to clearly document this situation so I could then seek the advice of the guru’s in InnoDB Internals such as Mark Callaghan, Percona and the Innodb development team for example.  I’m happy to say I’m not a MySQL expert in every aspect of MySQL, specifically internals where I have not had the detailed time to read the code, and understanding all internal workings.


The situation


Single table updates on a range of rows by primary keys are being blocked by other similar operations on the same table yet the set of data for each query is effectively unique.


Reproducing the problem


$ mysql -u -p test
drop table if exists numbers;
create table numbers (id int unsigned not null primary key, f1 int not null, f2 int not null) engine=innodb;

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,1,1);
  while counter < p_max
  do
      insert into numbers (id,f1, f2)
          select id + counter, counter + f1, id - f2
          from numbers;
      select count(*) into counter from numbers;
      select counter;
  end while;
end $$
delimiter ;

call fill_numbers(2000000);



In two separate threads I execute similar statements on different ranges of the primary key.


--thread 1
start transaction;
update numbers
set f2 = f2 +200
where id between 1 and 1000000;
commit;

--thread 2
start transaction;
update numbers
set f2 = f2 +300
where id between 1000001 and 2000000;
commit;



And in a third thread we can monitor the transactions inside Innodb.


-- thread 3
show engine innodb statusG



During the update process, the following error can be observed.


---TRANSACTION 0 7741, ACTIVE 20 sec, process no 2159, OS thread id 1188534592 fetching rows, thread declared inside InnoDB 275
mysql tables in use 1, locked 1
2007 lock struct(s), heap size 292848, 1001862 row lock(s), undo log entries 999858
MySQL thread id 918563, query id 16802707 localhost root Updating
update numbers set f2 = f2 +300 where id between 1000001 and 2000000
---TRANSACTION 0 7740, ACTIVE 21 sec, process no 2159, OS thread id 1178949952 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 2008 lock struct(s), heap size 292848, 1002005 row lock(s), undo log entries 1000000
MySQL thread id 918564, query id 16802694 localhost root Updating
update numbers set f2 = f2 +200 where id between 1 and 1000000
------- TRX HAS BEEN WAITING 3 SEC FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 16052 n bits 568 index `PRIMARY` of table `test`.`numbers` trx id 0 7740 lock_mode X waiting
Record lock, heap no 256 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 000f4241; asc   BA;; 1: len 6; hex 000000001e3d; asc      =;; 2: len 7; hex 00000033630110; asc    3c  ;; 3: len 4; hex 800f4241; asc   BA;; 4: len 4; hex 80050584; asc     ;;




The problem has been reproduced on various different MySQL versions and different hardware including, 5.0.67, 5.0.81 and 5.1.25.


What is causing the problem?


    

  • Is it a bug?  No.
    • 

  • Is my understanding of MVCC theory incorrect? Maybe.
    • 

  • Is it InnoDB’s implementation of MVCC incomplete. No. Heikki and his team have a far greater understanding of data theory then most database experts
    • 

  • Is it the MySQL kernel interfering with the InnoDB storage engine? No, this is not possible as the MySQL kernel has passed the queries to InnoDB, and InnoDB is handling these threads independently.
    • 

  • Is it a gap locking issue, a problem that can cause deadlocks when inserting data in a high concurrency situation?  Not likely as the data is inserted in primary key, i.e. auto increment order, and there are no gaps.
    • 

  • Is it related to InnoDB access method via the primary key, where InnoDB uses a clustered index to store the primary key.  Given the data is physically in primary key order, this clustered index would in theory reduce possible locking.
    • 

  • Is it related to the page size of indexes, e.g. the 16k index page, effectively causing a page level lock for overlapping index data? My understanding is that InnoDB supports row level locking, and MVCC should cater for this.
    • 

  • Is is related to the ranges of primary keys being adjacent, i.e. 1,000,000 and 1,000,001.  Not likely as I can reproduce the problem not using adjacent ranges.
    • 

  • Is it some weird interaction to managing the undo space of the transactions in the Innodb buffer pool?
    • 

  • Is it some weird interaction with marking/locking the dirty pages in the Innodb buffer pool of modified pages?
    • 

  • Is it some weird interaction with logging the successful Innodb transaction to the redo logs.
    • 



I’ve listed these points more as an information exercise for all those that have less understanding of the problem to see my though process.


Additional testing can definitely be performed. Additional analysis of InnoDB internals with SHOW ENGINE INNODB STATUS such as spin waits, OS waits (context switches), looking at Mutexes with SHOW ENGINE INNODB MUTEX can be undertaken.


My hope and request is that this has been observed by others and that a simple hybrid solution exists.


Killing my softly with QUERY
 

 by   
The MySQL KILL command as the name suggests kills queries that are running.


After identifying the Id using the SHOW PROCESSLIST command, the User of the connection/thread or a database user with SUPER privileges can execute KILL [id]; to remove the connection/thread.


However, there is an ability to kill just the query that is being executed rather the entire connection.  The default when not specified is to kill the connection, however you can optional specify the CONNECTION or QUERY keywords.


For example, below is an interactive test.


Thread 1:


mysql> select sleep(10);



Thread 2:


mysql> show processlist;
+----+------+-----------+------+---------+------+-----------+------------------+
| Id | User | Host      | db   | Command | Time | State     | Info             |
+----+------+-----------+------+---------+------+-----------+------------------+
| 23 | root | localhost | NULL | Query   |    2 | executing | select sleep(10) |
| 24 | root | localhost | NULL | Query   |    0 | NULL      | show processlist |
+----+------+-----------+------+---------+------+-----------+------------------+
2 rows in set (0.00 sec)

mysql> kill query 23;




Thread 1:

 Notice, the query exits, in less then 10 seconds, but the connection is still valid.


+-----------+
| sleep(10) |
+-----------+
|         1 |
+-----------+
1 row in set (7.27 sec)

mysql> select sleep(10);



Thread 2:


mysql> show processlist;
+----+------+-----------+------+---------+------+-----------+------------------+
| Id | User | Host      | db   | Command | Time | State     | Info             |
+----+------+-----------+------+---------+------+-----------+------------------+
| 23 | root | localhost | NULL | Query   |    4 | executing | select sleep(10) |
| 24 | root | localhost | NULL | Query   |    0 | NULL      | show processlist |
+----+------+-----------+------+---------+------+-----------+------------------+
2 rows in set (0.00 sec)

mysql> kill 23;
Query OK, 0 rows affected (0.00 sec)



Thread 1:

Query is killed and connection is lost.  An attempt to run the command again within the interactive client causes the system to re-get a connection.


ERROR 2013 (HY000): Lost connection to MySQL server during query
mysql> select sleep(10);
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    25
Current database: *** NONE ***

+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.00 sec)



Installing Perl RRD module – RRDs.pm
 

 by   
Perform a quick check if the module is available.


$ perl -MRRDs -le 'print q(ok!)'
Can't locate RRDs.pm in @INC (@INC contains: /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi /usr/lib64/perl5/site_perl/5.8.7/x86_64-linux-thread-multi /usr/lib64/perl5/site_perl/5.8.6/x86_64-linux-thread-multi /usr/lib64/perl5/site_perl/5.8.5/x86_64-linux-thread-multi /usr/lib/perl5/site_perl/5.8.8 /usr/lib/perl5/site_perl/5.8.7 /usr/lib/perl5/site_perl/5.8.6 /usr/lib/perl5/site_perl/5.8.5 /usr/lib/perl5/site_perl /usr/lib64/perl5/vendor_perl/5.8.8/x86_64-linux-thread-multi /usr/lib64/perl5/vendor_perl/5.8.7/x86_64-linux-thread-multi /usr/lib64/perl5/vendor_perl/5.8.6/x86_64-linux-thread-multi /usr/lib64/perl5/vendor_perl/5.8.5/x86_64-linux-thread-multi /usr/lib/perl5/vendor_perl/5.8.8 /usr/lib/perl5/vendor_perl/5.8.7 /usr/lib/perl5/vendor_perl/5.8.6 /usr/lib/perl5/vendor_perl/5.8.5 /usr/lib/perl5/vendor_perl /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi /usr/lib/perl5/5.8.8 .).
BEGIN failed--compilation aborted.



Check the installed packages on this CentOS 5 server.


$ rpm -qa | grep rrd
rrdtool-1.2.27-3.el5
rrdtool-1.2.27-3.el5



Do a search for related rrdtool modules.


$ sudo yum search rrdtool
Loading "fastestmirror" plugin
Loading mirror speeds from cached hostfile
 * epel: ftp.SURFnet.nl
 * base: mirrors.service.softlayer.com
 * updates: mirrors.service.softlayer.com
 * addons: mirrors.service.softlayer.com
 * extras: mirrors.service.softlayer.com
Excluding Packages in global exclude list
Finished
rrdtool.i386 : Round Robin Database Tool to store and display time-series data
queuegraph.noarch : A RRDtool frontend for Mail statistics
rrdtool-perl.x86_64 : Perl RRDtool bindings
rrdtool-tcl.x86_64 : Tcl RRDtool bindings
mailgraph-selinux.noarch : A RRDtool frontend for Mail statistics
collectd-rrdtool.x86_64 : RRDTool module for collectd
rrdtool.i386 : Round Robin Database Tool to store and display time-series data
munin.noarch : Network-wide graphing framework (grapher/gatherer)
rrdtool.x86_64 : Round Robin Database Tool to store and display time-series data
ruby-RRDtool.x86_64 : RRDTool for Ruby
munin-node.noarch : Network-wide graphing framework (node)
rrdtool-ruby.x86_64 : Ruby RRDtool bindings
sagator.noarch : Antivir/antispam gateway for smtp server
rrdtool-php.x86_64 : PHP RRDtool bindings
rrdtool-devel.x86_64 : RRDtool libraries and header files
rrdtool-python.x86_64 : Python RRDtool bindings
ganglia-gmetad.x86_64 : Ganglia Metadata collection daemon
rrdtool-doc.x86_64 : RRDtool documentation
cacti.noarch : An rrd based graphing tool
queuegraph-selinux.noarch : A RRDtool frontend for Mail statistics
rrdtool-devel.i386 : RRDtool libraries and header files
cacti.noarch : An rrd based graphing tool
sysusage.noarch : System monitoring based on perl, rrdtool, and sysstat
mailgraph.noarch : A RRDtool frontend for Mail statistics
rrdtool.x86_64 : Round Robin Database Tool to store and display time-series data



Install what looks like the right module.


$ sudo yum install rrdtool-perl
Loading "fastestmirror" plugin
Loading mirror speeds from cached hostfile
 * epel: mirrors.ircam.fr
 * base: mirrors.service.softlayer.com
 * updates: mirrors.service.softlayer.com
 * addons: mirrors.service.softlayer.com
 * extras: mirrors.service.softlayer.com
Excluding Packages in global exclude list
Finished
Setting up Install Process
Parsing package install arguments
Resolving Dependencies
--> Running transaction check
---> Package rrdtool-perl.x86_64 0:1.2.27-3.el5 set to be updated
--> Finished Dependency Resolution

Dependencies Resolved

=============================================================================
 Package                 Arch       Version          Repository        Size
=============================================================================
Installing:
 rrdtool-perl            x86_64     1.2.27-3.el5     epel               34 k

Transaction Summary
=============================================================================
Install      1 Package(s)
Update       0 Package(s)
Remove       0 Package(s)

Total download size: 34 k
Downloading Packages:
(1/1): rrdtool-perl-1.2.2 100% |=========================|  34 kB    00:00
Running rpm_check_debug
Running Transaction Test
Finished Transaction Test
Transaction Test Succeeded
Running Transaction
  Installing: rrdtool-perl                 ######################### [1/1]

Installed: rrdtool-perl.x86_64 0:1.2.27-3.el5
Complete!



Test shows it should now be available.


$ perl -MRRDs -le 'print q(ok!)'
ok!



An important Drizzle/MySQL difference
 

 by   
There are many features that are similar in MySQL and Drizzle. There are also many that are not.


I’ve previously discussed topics like Datatypes and tables, SQL_MODE and SHOW.


A key difference in Drizzle is the definition of utf8 as 4 bytes, not 3 bytes as in MySQL. This combined with no other character sets leads to an impact on the length in keys supported in Innodb.


During a recent test with a client, I was unable to successfully migrated the schema and provide the same schema due to unique indexes defined for utf8 VARHAR(255) fields.


Here is the problem.


mysql> create table t1(c1 int unsigned not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset latin1;
Query OK, 0 rows affected (0.05 sec)
mysql> create table t2(c1 int unsigned not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset utf8;
Query OK, 0 rows affected (0.03 sec)
mysql> create table t3(c1 int unsigned not null auto_increment primary key, c2 varchar(256) not null, unique key (c2)) engine=innodb default charset utf8;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes



drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb default charset latin1;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your Drizzle server version for the right syntax to use near 'charset latin1' at line 1
drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(255) not null, unique key (c2)) engine=innodb;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes



Only a maximum of 191 is now possible.


drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(191) not null, unique key (c2)) engine=innodb;
Query OK, 0 rows affected (0.03 sec)
drizzle> create table t1(c1 int not null auto_increment primary key, c2 varchar(192) not null, unique key (c2)) engine=innodb;
ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes



The confusion over global and session status
 

 by   
I was trying to demonstrate to a client how to monitor queries that generate internal temporary tables.  With an EXPLAIN plan you see ‘Creating temporary’.  Within MySQL you can use the SHOW STATUS to look at queries that create temporary tables.


There is the issue that the act of monitoring impacts the results, SHOW STATUS actually creates a temporary table. You can see in this example.


mysql> select version();
+-----------------+
| version()       |
+-----------------+
| 5.1.31-1ubuntu2 |
+-----------------+
1 row in set (0.00 sec)

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 48    |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 155   |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 48    |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 156   |
+-------------------------+-------+
3 rows in set (0.00 sec)



What has perplexed me in the past, and I can’t explain is that SHOW SESSION STATUS for this example does not increment.  It’s confusing to tell a client to use SHOW SESSION STATUS for SQL statements, but the behavior is different with SHOW GLOBAL STATUS. For example, no increment.


mysql> show session status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show session status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)



Let’s look at a query that creates a temporary table.


mysql> explain select t1.* from t1,t2 where t1.c1 = t2.c2 order by t2.c2, t1.c1;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | t1    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using temporary; Using filesort |
|  1 | SIMPLE      | t2    | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using join buffer  |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
2 rows in set (0.03 sec)



If we use session status we get an increment of 1.


mysql> show session status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> show session status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 2     |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> select SQL_NO_CACHE t1.* from t1,t2 where t1.c1 = t2.c2 order by t2.c2, t1.c1;
Empty set (0.00 sec)

mysql> show session status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 0     |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 3     |
+-------------------------+-------+
3 rows in set (0.00 sec)




If we use global status, in this case it’s and idle server so I know there is no other activity, however in a real world situation that isn’t possible.



mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 48    |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 171   |
+-------------------------+-------+
3 rows in set (0.00 sec)

mysql> select SQL_NO_CACHE t1.* from t1,t2 where t1.c1 = t2.c2 order by t2.c2, t1.c1;
Empty set (0.00 sec)

mysql> show global status like 'created_tmp%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Created_tmp_disk_tables | 48    |
| Created_tmp_files       | 5     |
| Created_tmp_tables      | 173   |
+-------------------------+-------+
3 rows in set (0.00 sec)



Benchmarking Drizzle with MyBench(DBD::drizzle)
 

 by   
With thanks to  Patrick Galbraith and his DBD::drizzle 0.200 I am now able to test client benchmarks side by side with MySQL and Drizzle.


For simple benchmarking with clients, generally when I have little time, I use a simple Perl framework mybench. I was able to change just the connection string and run tests.


The diff of my two scripts where:


---
> my $user      = $opt{u} || "appuser";
> my $pass      = $opt{p} || "password";
> my $port      = $opt{P} || 3306;
> my $dsn       = "DBI:mysql:$db:$host;port=$port";
---
< my $user      = $opt{u} || "root";
< my $pass      = $opt{p} || "";
< my $port      = $opt{P} || 4427;
< my $dsn       = "DBI:drizzle:$db:$host;port=$port";
---



It's too early to tell what improvement Drizzle will make. Just running my first test with single and multi thread tests shows an improvement in all figures in Drizzle via MySQL, however I will need to run this on various different versions of MySQL including the latest 5.0 to confirm.


Verifying MySQL Replication in action
 

 by   
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:




Using statpack with SHOW STATUS
 

 by   
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. 

  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. 

  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. 

  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. 

  5. Negative numbers. There are occurrences when negative numbers occur, due to the wrapping of status numbers. See Exhibit 4 for example.
    6. 

  6. Bug I’ve raised about divide by zero error when the same file (operator error) is specified.
    7. 



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
 

 by   
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?
 

 by   
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
 

 by   
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
 

 by   
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
 

 by   
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
 

 by   
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
 

 by   
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
 

 by   
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. 

  2. Confirm if the server is shared or dedicated.
    3. 

  3. Get SHOW GLOBAL VARIABLES output.
    4. 

  4. Get 2 x SHOW GLOBAL STATUS output, separated from some time, e.g. 1 minute, 10 minutes, 1 hour.
    5. 



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.


Free MySQL Book giveway – Current Progress
 

 by   
I’ve decided to give people two more days for a chance to win a free MySQL Book —  Sheeri Cabral’s MySQL Administrators Bible.


I have had five people so far provide recommendations for a simple MySQL configuration question as stated in For MySQL DBA fame and glory. Prize included.  Shlomi Noach the current front runner.


Try your MySQL Performance Tuning skills.  This is a good opportunity for new MySQL DBA’s and experienced DBA’s to provide basic input.


For MySQL DBA fame and glory. Prize included.
 

 by   
I came across the following configuration today on a Production MySQL system (5.0.67) running 30+ blogs using WordPress MU.


$ cat /etc/my.cnf
[mysqld]
set-variable = max_connections=500
safe-show-database



No I did not truncate the output.  I could say I’ve seen worse, but that’s a stretch.




So the quiz and a prize for the best response, for the next 48 hours I’ll accept your comments as responses to this post for the top 5 settings you would add, and additionally what information you may need to add these settings. Bonus points for giving a reason why you would add the settings as well.


For example, I’ll give you the most obvious.


key_buffer_size = ????



To determine a key_buffer_size to start with I would look at the size of all Indexes via I_S, and combine with some estimate of growth, say 2x-5x.


For the best answer with the top 5 settings, I’ll send you a copy of Sheeri Cabral’s book MySQL Administrators Bible which even I as a resident MySQL Expert has enjoyed scanning and learning something new.


Basic OS/MySQL Security
 

 by   
If you can do either of these on your MySQL production server, you need to correct immediately.


1. Login directly to your MySQL server as the ‘root’ Linux Operating System user. For example:


$ ssh root@server-name
Password:  ************



2. Connect to MySQL database as the ‘root’ MySQL user without a password.


$ mysql -uroot



Here are the 60 second fixes to address these major security flaws.

To disable direct root access to your server, first ensure you can login as a normal user, then su – or sudo su – appropriately. Then, disable ssh root access with the following configuration change.


$   vi /etc/ssh/sshd_config
    # ensure this is commented out and set to no
    PermitRootLogin no

$   /etc/init.d/sshd restart



This will stop any brute force attack on your server by automated bots and password generators.


Second, the default installation of MySQL *DOES NOT SET A PASSWORD*. Apart from being crazy, I’ve seen production systems without a MySQL ‘root’ user password. To set a password run:


$ mysqladmin -uroot password SOMEPASSWORD
$ mysqladmin -uroot -pSOMEPASSWORD -hlocalhost password SOMEPASSWORD



SHOW WARNINGS woes
 

 by   
Recently on a client site I had to fight the pain of having no way to confirm loss of data integrity when optimizing data types.  Due to MySQL’s ability to perform silent conversion of data, when converting a number of columns we enabled sql_mode to catch any truncations as errors.


sql_mode=STRICT_ALL_TABLES



This ensured that should any data truncations occur, an error is thrown not a warning.  The following shows an example case study for converting an INT to TINYINT UNSIGNED and shows that without sql_mode silent conversions occur.


mysql> drop schema if exists tmp;
Query OK, 25 rows affected (0.40 sec)

mysql> create schema tmp;
Query OK, 1 row affected (0.01 sec)

mysql> use tmp
Database changed
mysql> create table t1(i1 INT NULL);
Query OK, 0 rows affected (0.15 sec)

mysql> insert into t1 values(1),(2),(3),(256),(65536),(NULL);
Query OK, 6 rows affected (0.06 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> select * from t1;
+-------+
| i1    |
+-------+
|     1 |
|     2 |
|     3 |
|   256 |
| 65536 |
|  NULL |
+-------+
6 rows in set (0.03 sec)

mysql> set session sql_mode=STRICT_ALL_TABLES;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table t1 modify i1 TINYINT UNSIGNED NULL;
ERROR 1264 (22003): Out of range value for column 'i1' at row 4
mysql> set session sql_mode='';
Query OK, 0 rows affected (0.03 sec)

mysql> alter table t1 modify i1 TINYINT UNSIGNED NULL;
Query OK, 6 rows affected, 2 warnings (0.07 sec)
Records: 6  Duplicates: 0  Warnings: 2

mysql> show warnings;
+---------+------+---------------------------------------------+
| Level   | Code | Message                                     |
+---------+------+---------------------------------------------+
| Warning | 1264 | Out of range value for column 'i1' at row 4 |
| Warning | 1264 | Out of range value for column 'i1' at row 5 |
+---------+------+---------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+------+
| i1   |
+------+
|    1 |
|    2 |
|    3 |
|  255 |
|  255 |
| NULL |
+------+
6 rows in set (0.00 sec)



The process however for the client included converting a number of DATETIME columns to DATE columns. The input data specification called for the input values to be dates only so storing and using DATETIME was simply a waste of space.


Unfortunately the use of sql_mode didn’t help here, so the combination of changing multiple columns in one time simply meant it was impossible to determine if any truncations occurred.


This combined with the sheer data volume, tables between 10GB – 500GB, with 10 – 100 million rows.

When you see 14+ millions rows modified, 35+million rows and of course SHOW WARNINGS by default only shows a very small number of actual warnings


It is of course impossible to run individual ALTER statements on columns. Of the top 10 tables, excluding the largest, alter times were between 3 and 15 hrs.


The only solution is to run pre SQL checks on the data to search for the boundary conditions manually.  This is not the ideal solution for a customer.


The MySQL crystal ball says …
 

 by   
As the recipient of the 2009 MySQL Community Member of the Year award I received a MySQL crystal ball.  While it looks good in my bookcase, unfortunately the best advice I can offer during this time of uncertainty is “watch this space”.


A number of topics where information is still very much unknown and I’m either asked about, or am following includes:


    

  • The Oracle acquisition of Sun, owner of MySQL.
    • 

  • MySQL 5.4 Alpha release and schedule for production release
    • 

  • The end of MySQL 5.0 Community/Enterprise split
    • 

  • The future of Falcon in MySQL 6.0?
    • 



Thanks to Julian Cash of the Human Creativity Project of his photograph at the 2009 MySQL Conference.


Ronald Bradford - MySQL Community Member of the Year


View Photo.  View all 2009 MySQL Conference photos.


MySQL for the Oracle DBA Resources
 

 by   
The announcement last month of Oracle to acquire Sun  continues to warrant a lot of discussion over exactly what Oracle will do with MySQL.  Only time will tell what will happen with the official product, however it is important to remember that MySQL is GPL, there will always be a free version of MySQL available for popular LAMP stack products such as WordPress and Drupal and new and existing startup’s will continue to use MySQL.


This announcement will see Oracle resources begin to better understand and evaluate MySQL. As a resident MySQL Expert, I also have a strong background in Oracle having also worked for Oracle Corporation.  I have also delivered several successful one day and half day workshops on MySQL/Oracle related content including:


    

  • 2009 – Best Practices for Migrating to MySQL from Oracle and SQL Server environments. 4 hour workshop for the Federal Government – Washington DC

    • 

  • 2008 – MySQL for the Oracle DBA Bootcamp. 1 day workshop for the Federal Government – Washington DC

    • 

  • 2007 – MySQL for the Oracle DBA Bootcamp. 1 day workshop New York, NY & San Francisco, CA

    • 

  • 2007 – MySQL for Oracle DBA’s and Developers. 90 minute presentation at MySQL Conference and Expo, Santa Clara, CA

    • 

  • 2006 – MySQL for Oracle Developers. 45 minute presentation at MySQL Users Conference, Santa Clara, CA

    • 

  • 2006 – Know your Competitor. A MySQL developers guide to Oracle 10g express edition. 60 minutes presentation at MySQL Users Group, Brisbane, Australia

    • 



In addition I have proposed 2 MySQL related talks at Oracle Open World 2009


    

  • Integrating MySQL into your Oracle DBA management processes
    • 

  • An overview for evaluating migrating from Oracle to MySQL
    • 



For more information check out my MySQL for the Oracle DBA Resources.


Drizzle now available on Mosso
 

 by   
Mosso the Rackspace Cloud now has a Drizzle developer image much like the first Drizzle AMI on EC2.


The Mosso interface is definitely different, it’s a GUI, and I definitely prefer CLI, but it’s a simpler navigation for a new user. I suspect an API may be available.


I had an issue with the backup process, more the lack of feedback. The Knowledge Base didn’t help, so both calling and Live Chat directed me ultimately to the same person. I also found a bug in the backup process, that is being able to select an incomplete backup to try and launch a new server. I talked to Support about and apparently already known.


And in true open source form, the Drizzle version is actually one point higher then yesterday’s AWS image.


I don’t know how to *publish* this backup so others can try it. Something on the list of things to do, however I was able to verify my backup with a new instance.


$ drizzle
Welcome to the Drizzle client..  Commands end with ; or g.
Your Drizzle connection id is 2
Server version: 2009.04.998 Source distribution

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

drizzle>
drizzle> select version();
+-------------+
| version()   |
+-------------+
| 2009.04.998 |
+-------------+
1 row in set (0 sec)

drizzle> select count(*) from sakila.film;
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.18 sec)