Stoner's World

A bit of advice to anyone wanting to write an article on MySQL that includes setting up users: familiarize yourself with the concept of Least Privileges. That is, only grant those privileges absolutely necessary to do a job and nothing more. I just finished reading an article on how to set up RSyslog to log to a MySQL database. Halfway through the article is a listing showing the grant statement. I’ll share just the fun part:

grant ALL ON Syslog.* …

My first reaction when I see a “grant all” is to ask: why? Why does an application need every database privilege? Well, I finished the article, then went to the RSyslog web site and spent all of 4 minutes researching why the app needs so much privilege. As it turns out, it doesn’t. Right there in blank and white:

“It is sufficient to grant it INSERT privileges to the systemevents table, only.”

Whew. Okay. The app doesn’t need all those privileges. When I checked the author’s credentials, he presents himself as someone who manages firewalls, antispam and antivirus systems – someone you would hope would be security-conscious and aware of privileges. Apparently not – or at least, not when it comes to databases. I couldn’t find an email address to write to the author nor does the web site have a form to post feedback on articles so I can’t point this out nor the fact that he completely left out how to configure RSyslog to connect to MySQL.

<sigh>

The Redhat Summit was a success for me. I got a lot of useful information from the presentations I attended, especially the “Performance tuning Jboss Enterprise Application Platform” session given by Andy Miller. Andy talked about tuning many of the pools in Jboss, like the thread pool and database connection pools. The juiciest piece of info was running Jboss with HugeTLB. We’re going to hit that one hard in terms of testing, validating and deploying in our production environments.

The “Optimizing the SOA Enterprise: Using Jboss and Redhat Enterprise Linux virtualization” session, given by Isaac Christofersen of Booz Allen Hamilton, was also very interesting. The built a cluster of Xen VMs, then build a cluster of Jboss instances in it. They used GFS on top of iSCSI LUNs for shared mounts. This got them away from HBA cards and deploying a separate SAN fabric. The trickiest part they encountered was setting up VLANs on the VM bridges. They had to modify a script or two to get it to work correctly. The benefit they got was being able to provision a Jboss app server in minutes on a VM infrastructure. Everything was highly available (a cluster in a cluster) and they actually realized greater performance then Jboss app servers sitting on real hardware.

The “What’s the fuss about fastboot and the new kernel crash dumping?” session, given by Vivek Goyal of Redhat, was another very interesting session. It talked about kdump and kexec, booting another kernel without rebooting the hardware and more. The ability to jump from one kernel into another (kexec) provides some really good benefits. kdump is built on kexec and provides a great framework for kernel developers and dealing with some really tricky kernel crashes. If you have unexplained kernel crashe, look into this as it stands to provide more and better information for resolving issues.

Finally, the “Augeas: A Linux configuration API” session, given by David Lutterkort of Redhat, was good. Augeas will fill a hole in Linux nicely, if they can come up a way to effectively manage “complex” configuration files, like DHCP, Apache and any XML-formatted file. Augeas uses regular language to pick apart and piece back together a config file and those mentioned do not lend themselves to simple regular expressions.

I went to other sessions but they were either a wash (mostly things I already knew but was deceived by a poorly written description) or a dog & pony show trying to sell me products/services.

Other highlights of the Summit:

• IBM sponsored Wednesday night’s dinner at Fenway Park. Fenway is a great baseball stadium. I got some grainy photos on my cell phone and sat on the Green Monster.
• Redhat provided lunch during the conference, which meant I could spend more on dinner.
• Schwag I got: a Redhat backpack, a baseball with Redhat and IBM’s logo on it, a plushTux penguin from Trusted Computer Systems, Inc., a tee shirt from QLogic, a foam Tux penguin rom R1Soft, a 1 GB USB drive from the Fedora Project, a cheesy monitor duster from BakBone Software
• The seafood in Boston is great. I had several varieties from chowder, which were all better than anythig you’ll get from a can. Oyster bars rule! Free wifi rules over $10.95/day hotel wired connections (always use a VPN for any network connections, regardless.)

I’ve been reading Codebreakers: The Inside Story of Bletchley Park and I have to say, it is an absolutely fascinating read. Even if you don’t give a hoot about encryption or how the Allies won World War II, it has great stories from the people who were there. You learn about their thoughts and feelings when it comes to events like being the first non-Nazis to read the message about the death of Hitler, mere hours after it happened, to deciphering the message that lead to the Allies shooting down Admiral Yamamoto Ishiroku. The stress of the work and the round-the-clock shift-work that went into the effort is amazing. The pressures put on these people, the tolls it took and how they coped with it all is simply astounding.

• Household generator + installation = $6,000
• Propane tank upgrades = $3,000
• Power outage going on 24 hours = ???
• Being the only house in the subdivision with electricity for refrigeration, cooking and hot water = Priceless

Major thunderstorms blew through yesterday, knocking out power all over the place. Our power company’s web site is still down so we can’t get updates that way (have to check from work since cable is still out.)

I was curious about some of the databases I get to work with. “How do my clients store their data? What data types are most prevalent?” Well, a few keystrokes later, I had my answers:

SELECT c.data_type, count(c.data_type) AS frequency
FROM information_schema.columns AS c
INNER JOIN information_schema.tables AS t
ON c.table_schema = t.table_schema AND
c.table_name = t.table_name
WHERE c.table_schema NOT IN ('information_schema','mysql') AND
t.table_type = 'base table'
GROUP BY data_type;

Which gave me a nice “data type distribution” table:

Not content with just that, I whipped up another statement to show me the top 10 tables that used a particular data type (replace the WHATEVER with the data type you’re interested in) :

SELECT c.table_schema, c.table_name, count(c.data_type) AS count
FROM information_schema.columns c
INNER JOIN information_schema.tables AS t
ON c.table_schema = t.table_schema AND
c.table_name = t.table_name
WHERE c.table_schema NOT IN ('information_schema','mysql') AND
t.table_type = 'base table' AND
c.data_type = 'WHATEVER'
GROUP BY c.table_schema, c.table_name
ORDER BY count DESC
LIMIT 10;


Which showed me:

Finally, I wrote them up into stored procedure form (and included a 3rd that gives the data type distribution for specified databases) and committed them to my project on Sourceforge.

I downloaded Maatkit 1877 and installed it on a data archive server, then did a parallel dump of the data. I also did a regular dump of the same data using mysqldump. This isn’t a benchmark or even a real comparison, just me playing with Maatkit to get a feel for it and see how it can make my life easier. First, some stats about the database:

mysql> select table_name, table_rows from information_schema.tables where table_schema = 'somedb';
+------------+------------+
| table_name | table_rows |
+------------+------------+
| table1 | 24758808 |
| table2 | 33713412 |
| table3 | 221633443 |
| table4 | 56869734 |
| table5 | 14017081 |
| table6 | 77559278 |
| table7 | 48453337 |
+--------+------------+

mysql> call database_size_profile('somedb')\G
*************************** 1. row ***************************
myisam_index_size: 15603748864
 myisam_data_size: 14895580924
innodb_index_size: NULL
 innodb_data_size: NULL
 total_index_size: 15603748864
  total_data_size: 14895580924
  database_size: 30499329788
1 row in set (0.00 sec)

The dump using maatkit

$ mk-parallel-dump --tab --databases somedb --no-gzip
 default: 7 tables, 7 chunks, 7 successes, 0 failures, 1239.59 wall-clock time, 3558.11 dump time

$ ls -l default/somedb/
-rw-rw-rw- 1286 Mar 18 18:25 table1.000000.sql
-rw-rw-rw- 847081157 Mar 18 18:28 table1.000000.txt
-rw-rw-rw- 1274 Mar 18 18:25 table2.000000.sql
-rw-rw-rw- 1131602870 Mar 18 18:29 table2.000000.txt
-rw-rw-rw- 1304 Mar 18 18:25 table3.000000.sql
-rw-rw-rw- 8508494974 Mar 18 18:45 table3.000000.txt
-rw-rw-rw- 1318 Mar 18 18:25 table4.000000.sql
-rw-rw-rw- 2327666926 Mar 18 18:32 table4.000000.txt
-rw-rw-rw- 1213 Mar 18 18:28 table5.000000.sql
-rw-rw-rw- 441408946 Mar 18 18:29 table5.000000.txt
-rw-rw-rw- 1431 Mar 18 18:29 table6.000000.sql
-rw-rw-rw- 4001690723 Mar 18 18:45 table6.000000.txt
-rw-rw-rw- 1339 Mar 18 18:29 table7.000000.sql
-rw-rw-rw- 1625718296 Mar 18 18:35 table7.000000.txt

$ du -sh default/
18G default/

The dump using mysqldump.

$ mysqldump --user=joe -p --routines --trigger --quick --complete-insert --master-data=2 somedb > somedb.18mar08.sql

Elapsed time to dump: 2676

$ ls -l
-rw-r--r-- 21905573763 Mar 18 19:49 somedb.18mar08.sql

The server has two dual-core athlons and 8 gigs of ram, reading from a fiber-attached SAN and writing to local SCSI drives (RAID-1.) I haven’t performed a restore yet. That’s next but not tonight. I have a code push to deal with first.

I learned something new about myisamchk. When doing a recovery of the data file, it creates not one but two temporary files. I knew about the TMD it creates in $datadir when rebuilding the MYD file but it creates a second temporary file using an old and rather annoying technique (which involves creating a file using the standard C ‘open()’ call, then deleting the file without closing the file descriptor.) I discovered this when myisamchk told me “Disk is full writing ‘/tmp/STQ3p9UF’ (Errcode: 28).” I looked in /tmp and didn’t see that file. So I did a

# lsof | grep delete

and low-and-behold, there it was. You know, as a sys admin, that really annoys me. I don’t care that it creates temporary files to do its thing. In fact, I expect it, but I at least want to know about it so I can plan accordingly. My /tmp partition only had 2.5 gigs of space and the table’s MYD was over 4 gigs in size. If I had known about this extra temp file, I would have used –tmpdir=/some/place/else before I started and not had myisamchk crap out on me in the first place.

At $WORK we typically use Redhat Cluster to make MySQL highly available. We have a number of 2-node clusters (active/passive) and it works nicely, for the most part. One thing I find very annoying is RHCS has no notification framework to notify you when a service is relocated to another node (because of failure or otherwise.)

In a perfect world, the only reason MySQL would pass between nodes is for server maintenance, under the control of a human. In reality, crap happens. Redhat Cluster manages all this, but I still want to know that a failover happened (is hardware going bad? did a software bug cause a failure? etc.)

Most monitoring tools weren’t designed to report on a clustered service. I can add a check that connects to MySQL using the cluster-managed IP – but that only tells me if MySQL has failed completely. The failover between nodes generally takes under a minute so it’s easy for that type of monitoring check to miss a failover. Parsing syslog output is less than optimal because syslog is unstructured text. Blech. Process and disk checks can’t be used on each node because they’ll only exist on one node, the active node.

My current solution is a bit of a kludge but it works. We configure MySQL to use standard names for binary logs and error logs (they never change, regardless of which node the database is running on.) The only file name that isn’t standardized is the pid file, which defaults to the hostname of the server running MySQL. So, with that bit of info, I wrote a script that connects to MySQL using the cluster-managed IP and issues the query “show variables like ‘pid_file’” and parses the result. It then compares the hostname against a stored value in a file, which contains the hostname of the current active node. If the hostname changed, then MySQL has changed nodes (whether due to failure or manual relocation.) This changing of hostnames is considered an error condition and the monitoring system sends alerts appropriately. The NOC staff consult procedures to determine the level of failure and call people if necessary. Clearing the error requires editing the file and setting the current hostname as the active node.

What would be nice would be some informational functions in MySQL to give a bit more data about the server itself, things like hostname, IP address MySQL is bound to, port, etc. Then I could use “select hostname()” or similar instead of relying on a file name. Before someone says “you connected to the database so you know all that already,” not all of this information is known to a database connection, particularly if your application uses a database connection pool or the connection goes through a proxy, masking the real database.

I always get a chuckle when I see a new product released and within hours/days/weeks, there is a Slashdot article about someone porting the Linux kernel to it. What is this fascination with running Linux on everything?

“Hey John, I just picked up this safety pin at Worst Buy!”

“Cool, Bob. Let’s put Linux on it!”

Personally, I don’t care that you can install Linux on a Roomba or some other device, complete with Asterisk, and control a Roomba to vacuum your house with your cell phone (yes, someone actually did this.) I don’t foresee myself in a hotel room halfway around the world thinking to myself “I wish there were a way I could vacuum my house right now so it’d be clean when I get home.”

The way I feel about this is:

1. Just because you can does not mean you should.
2. If you do, then don’t complain if hardware doesn’t work right and the vendor doesn’t give you any help.

Jay Pipes blogged about this earlier this month. I read his post, nodded a knowing nod and finished my cup of coffee. It wasn’t until yesterday when it really struck me. I was looking through the database schema of a popular forum software package (won’t say which) when I came across this column definition in a central table (by ‘central’ I mean one that typically holds a lot of rows):

ip varchar(100),

One hundred characters to store a at-most 15-character IPv4 string? I know what you’re about to say, “But, it’s a varchar so you’re not really using 100 bytes.” Well, you *might* not be. Jay’s discussion on how MySQL uses temporary tables illustrates how you can use all 100 bytes in memory – not a good thing.

So, the minimum number of bytes you will be using is 7 if each octet is a single digit (don’t forget the ‘.’ separators.) At most, you use 15 bytes. If you store the IP as an unsigned integer, you use, at most, 4 bytes per row. Those bytes really start to add up, especially when pulling the data into memory to satisfy queries.

But wait…what if it stores the fully qualified domain name? I didn’t dive into the PHP to see if it stores IP addresses or hostname or both in this column. Is 100 characters enough? Probably. Some of the hostnames from the major ISPs are decently long but I don’t think I’ve seen one that’s 100 characters. I did set up a site for a client that was 61 characters long (thank goodness for bookmarks.)

This begs the question: is it better to store the IP address or hostname…or both? Resolving an IP to hostname or vice versa is an “expensive” process, whether you do it in your code at the time of storage or as a batch process later on. This is a question for another day.

Where does all this lead? You need to clearly define the data you want to store, then be frugal with the data type used to store it.