I posted a simple INFORMATION_SCHEMA query to find largest tables last month and it got a good response. Today I needed little modifications to that query to look into few more aspects of data sizes so here it goes:

Find total number of tables, rows, total data in index size for given MySQL Instance

PLAIN TEXT SQL:

1. mysql> SELECT count(*) TABLES,
2.     ->        concat(round(sum(table_rows)/1000000,2),'M') rows,
3.     ->        concat(round(sum(data_length)/(1024*1024*1024),2),'G') DATA,
4.     ->    concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx,
5.     ->    …

In the past few months I’ve not had to look for a job, they look for me. If your a well qualified MySQL DBA there are many opportunities, I’ve had so many of these offers in the past 12 months I’ve lost count. However a DBA job would not offer the job satisfaction I’m seeking in my career goals now in 2008.

I’ve been able to consider 4 genuine positions in just the past few weeks, and indeed my new employer Primebase Technologies was someone I’d never considered working for, and indeed they had never considered me until a chance lunch meeting less then one month ago.

I did seriously consider working with Paul Vallee and the team at Pythian. Pythian looks for the best people from their respective levels of technology expertise, whether a college graduate or a 2 decade RDBMS veteran. This company has a great work …

Tim Bunce, the author of the Perl DBI, the database library that has influenced similar works in most languages, has published a presentation about Perl myths. The essential list of myths goes:

• Perl is dead. No, it's alive and kicking (look at the numbers)
• Perl is difficult to read. So are Java and C++, when written by bad programmers.
• Perl 6 is killing Perl 5. Actually, the opposite is true. While Perl 6 is taking way too long to be implemented, its analysis has advantaged Perl 5, which has grown new features and performance in 5.10.

Highly educational stuff, as it is this other article: Good Perl code is the best form of evangelism.
As for me, Perl is my main tool of the trade, not only with …

Sun Microsystems, Inc. and Zmanda, the open source leader in backup and recovery software, today announced they are partnering to deliver a comprehensive, global data backup and recovery solution for MySQL Enterprise subscribers. A 30-day trial of Zmanda Recovery Manager Enterprise Edition -- the first open-source backup and recovery solution designed specifically for MySQL™ databases -- is available now at www.mysql.com/zrm. Starting April 1, MySQL Enterprise customers will be able to purchase ZRM for MySQL directly from Sun worldwide. Zmanda and Sun have also agreed to joint-selling and co-marketing activities in support of MySQL, the world's most popular open source database software.

Typically, when choosing a composite index structure, your favorite DBA will tell you to put the most selective columns first. Now, when I tell people that I tend to get a few blank stares, so first, what does selective really mean?

It means, that for the given set of columns, the columns with the more UNIQUE values are more selective than columns with less UNIQUE values. In general, putting more selective values before less selective values in a composite index will result in faster access. This assumes however that comparison operations involve the EQUALS (=) operator.

To demonstrate selectivity:

Table
Col1  Col2
1     1
2     1
3     1

Query
select * from Table where Col1 = 1 and Col2 = 1

In this first example `Col1` is more selective than `Col2`. Think of selectivity as the number of rows that need to be read to make a comparison against a single …

As an independent consultant and trainer , I found myself doing a lot of existing code analysis and enhancement, mostly for stored procedures and ad-hoc SQL statements. I suspect a lot of people do the same thing as well, so I am really interested in learning how you do it. That’s the main purpose of this post. This post is tagged with Oracle, Sql Server, and MySQL, as the principals should be the same for all platforms.

Let me share with you how I do it. Notice that I look at table/column statistics and indexes in almost all the steps below. Therefore I purposely left them out in the discussion.

1. I always talk to the original author or the current owner of the code, asking him/her to walk me through it. I listen mostly, trying to understand why s/he writes code this way. I may ask a few questions, just to help me understand. I almost never do any lecturing at this stage. If the code is a stored procedure, I ask the author or …

A

All in Melbourne, keep the evening of Thursday, 20 March 2008, free. Why? We’re having a Meetup Mashup, right here in Melbourne, Australia. This is the Sun and MySQL tour around the world!

Details:
Date: 20th March
Time: 7.30pm
Venue: RMIT University (Swanston St)
Room: 10.08.04

Contact Tristan on 0422 501 726 for directions to the venue. (or me, at 0412 593 292 if you have any questions, etc.)

After we’re done at RMIT, discussing the recent acquisition (you’re guaranteed to see Support Engineer Gary Pendergast, and me speak, and answer questions), we’ll head over to the bar nearby, and grab some grub. Its a great opportunity to come and get your questions answered!

Are you already part of the MySQL Meetup in Melbourne? If not, check out the …

We’ve been running into a problem with one client:

SELECT COUNT(*) FROM tbl;

takes 0.25 seconds on one db, and 0.06 seconds on another.

Consistently. That’s a fourfold difference.

There aren’t any significant configuration differences (like query cache, etc.), the software versions are the same, and the table fits into memory. This has been looked at by at least 3 in-house MySQL experts, and the only thing we can determine is that it’s a hardware difference.

The table fits into memory so it’s not a disk issue, and the only other difference among the hardware is that the slower machine has Solaris virtualization in place in the form of “containers” (cpu is the same, etc). Is this something that’s known to cause issues with speed? The “tbl” in question is an InnoDB table, if that means anything. Is there something like the “speed” of RAM?

Note that Sun has already been …

I have created a MySQL Professionals Group for networking with others in the space, in the tradition of the Oracle Professionals group and the SQL Server Professionals groups that I already participate in.

This is a great way to network with other professionals in your field of work. I hope you join us.

To join, please follow this invitation link.

Paul