Hi Everyone,

I would like to personally invite you to a GreenSQL Express Webinar,
I’ll be demonstrating GreenSQL Express, the free and simple way to keep your information private and safe.

On Wednesday, March 16th (just 2 weeks from now),
It’s called “How to Protect Sensitive Information in Minutes: Setting up GreenSQL Express with Basic Security Rules”

If you’re serious about protecting your data, you need to hear and see how it’s done. I’ll talk about:

1. Why you need a Database firewall / security solution
2. Where and How to install GreenSQL Express in your infrastructure
3. How to use GreenSQL Express to protect you database
4. How to create the security polices you need in minutes
5. How to protect your database from SQL injection attacks
6. How to implement a separation of duties in your database access
7. How to maintain business continuity …

Here’s a nice challenge for you. What does the following do (or error out on?):

CREATE TABLE t1 (a int);
CREATE TABLE t2 (b int);
ALTER TABLE t1 RENAME t3, RENAME t2, RENAME t4;

I’d be interested to know what a) you think it does and then b) if you were surprised when you went and typed it into your RDBMS of choice.

The call for papers for the MySQL conference’s Ignite sessions is open. These are a lot of fun: 5 minutes, 20 slides, period. Last year I spoke way too fast — if I do it again this year, I’ll try not to cram so much into so little time. Either that, or I’ll deliberately talk like Busta Rhymes.

Related posts:

1. Submit your proposals for MySQL conference 2010
2. I’m a MySQL Conference and Expo advocate again
3. …

I often need to watch a server that’s very minimally configured, e.g. has no Perl DBI libraries installed, and I shouldn’t install anything. The following snippet is a quick way to do that:

watch 'mysqladmin proc | grep -v Sleep | cut -b0-130'

Replace 130 by the width of your terminal, naturally.

(Of course, innotop is much more featureful than mytop, but mytop is the essential functionality we’re going for here!)

Related posts:

1. An easy way to run many tasks in parallel
2. How to read Linux’s /proc/diskstats easily
3. …

I’m not sure how many of this blog’s readers are likely to be aware of VoltDB. It is one of the systems that I think could be poised to dispel the notion that SQL (or the relational model) is somehow inherently unscalable. Here’s a blog post explaining how VoltDB does transaction ordering and replication.

Related posts:

1. Why MySQL replication is better than mysqlbinlog for recovery
2. What are your favorite MySQL replication filtering rules?
3. …

PostgreSQL 9.0 High Performance

PostgreSQL 9.0 High Performance. By Gregory Smith, Packt 2010. About 420 pages. (Here’s a link to the publisher’s page for this book.)

I enjoyed this book a lot and recommend it to everyone who uses PostgreSQL or MySQL. MySQL users should benefit from understanding PostgreSQL. Beyond that, I learned a lot from this book that I can apply directly to MySQL. In particular, the book begins with a few chapters on hardware performance, benchmarking, and configuration. This material is database-agnostic and very well done. There is about 70 pages of it — it goes into a lot of details. It is more detailed than the similar material in my own book …

Today, I had a task of modifying a slave to exclude (or if you want to say specifically include) tables. The situation is that this slave need only contain tables used for reporting. The added benefit being that with fewer tables to replicate there will be less replication traffic, less disk I/O and less disk space used hence better performance for queries that digest statistical data.

In the past, I would manually edit files, but today I decided to turn over a new leaf and be lazy — and let the database do the work for me! I’m posting this article because I feel obliged to share with you my laziness.

The client provided me a list of tables in a spreadsheet which I cut-n-pasted and used the magical regex powers of vim to give me a quoted, comma-separated list of tables. I love information_schema, because as opposed to ‘SHOW’ commands which may be easy to remember, don’t give me the ability to produce an output which I …

I’ve seen this a handful of times: someone has trouble with their database performance, and they have heard that XtraDB is much faster than InnoDB. They build a custom-compiled server with XtraDB.

This is unfortunately missing the point a bit. If you have a server that is the same as normal MySQL, but you’ve replaced InnoDB by XtraDB, what do you have? Depending on the version of MySQL you’re using, you have somewhere between, say, 1.5x and 15x performance improvement, at best. Compared to what you could be getting, that is not much, because you’re missing the most important improvement in Percona Server: the ability to measure the server’s activity. In other words, with a faster server that you still can’t measure and diagnose easily, you have just painted yourself into a faster corner. Your application’s workload is likely to grow 1.5x in no time; you have barely put off needing to diagnose why it is slow.

…

What is wrong with the following?

mysql> select 'hello world';
+-------------+
| hello world |
+-------------+
| hello world | 
+-------------+
1 row in set (0.00 sec)

Centisecond resolution for query times belongs to the last century. I want at least millisecond resolution — microsecond is better. Fortunately, this is as simple as changing a printf format specifier in the mysql client program.

Edit: I thought that maybe I could fix this by changing the printf format specifier with sed, but it looks like I was wrong:

$ sed -e 's/%\.2f sec/%.6f sec/' bin/mysql > bin/mysql-precision

Now when I enter commands, I actually do see 6 digits after the decimal point, but it looks like I still get only 2 digits of precision:

mysql> select sleep(.009);
+-------------+
| sleep(.009) |
+-------------+
|           0 |
+-------------+
1 row in set (0.010000 sec)
mysql> select …

I’ve just committed some changes to diskstats, an I/O analysis tool in Aspersa that’s actually been in the Subversion repository for a long time, but in a barely usable fashion and with no documentation. Now it’s usable and documented.

It is basically a reimplementation of iostat in awk. Why on earth would I reinvent that wheel? Because I spend a lot of time gathering and analyzing raw data from /proc/diskstats, which is vital to really understanding what the storage subsystem is doing. The iostat tool hides important details. Seeing that detail has immediately solved many a disk performance problem and proven SAN vendors wrong, for instance. (I used to do this the old-fashioned way.) Disk performance, of course, is one of the most important …