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:
[Read more]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:
[Read more]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:
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 …
[Read more]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 …
[Read more]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.
…
[Read more]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 …[Read more]
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 …
[Read more]A couple of extremely informative recent blog posts have gone to either Planet MySQL or Planet PostgreSQL, but not both, and I think everyone on both aggregators who cares about database internals should be interested in them. Here they are:
Happy cross-pollination.
Related posts:
[Read more]