I was recently helping some colleagues check what was happening with their MySQL queries, and wrote a DTrace script to do it. Time to share that script.

First of all, a look at some output from the script:

mashie[bash]# ./querystat.d -p `pgrep mysqld`
Tracing started at 2009 Sep 17 16:28:35
2009 Sep 17 16:28:38   throughput 3 queries/sec
2009 Sep 17 16:28:41   throughput 4 queries/sec
2009 Sep 17 16:28:44   throughput 528 queries/sec
2009 Sep 17 16:28:47   throughput 1603 queries/sec
2009 Sep 17 16:28:50   throughput 1676 queries/sec
\^C
Tracing ended   at 2009 Sep 17 16:28:51
Average latency, all queries: 107 us
Latency distribution, all queries (us): 
           value  ------------- Distribution ------------- count    
              16 |                                         0        
              32 |@@                                       170      
              64 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ …

I was recently helping some colleagues check what was happening with their MySQL queries, and wrote a DTrace script to do it. Time to share that script.

First of all, a look at some output from the script:

mashie[bash]# ./querystat.d -p `pgrep mysqld`
Tracing started at 2009 Sep 17 16:28:35
2009 Sep 17 16:28:38   throughput 3 queries/sec
2009 Sep 17 16:28:41   throughput 4 queries/sec
2009 Sep 17 16:28:44   throughput 528 queries/sec
2009 Sep 17 16:28:47   throughput 1603 queries/sec
2009 Sep 17 16:28:50   throughput 1676 queries/sec
\^C
Tracing ended   at 2009 Sep 17 16:28:51
Average latency, all queries: 107 us
Latency distribution, all queries (us): 
           value  ------------- Distribution ------------- count    
              16 |                                         0        
              32 |@@                                       170      
              64 |@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@ …

Installing Cherokee With PHP5 And MySQL Support On Fedora 11

Cherokee is a very fast, flexible and easy to configure Web Server. It supports the widespread technologies nowadays: FastCGI, SCGI, PHP, CGI, TLS and SSL encrypted connections, virtual hosts, authentication, on the fly encoding, load balancing, Apache compatible log files, and much more. This tutorial shows how you can install Cherokee on a Fedora 11 server with PHP5 support (through FastCGI) and MySQL support.

Devart, a vendor of native connectivity solutions and development tools for Oracle, SQL Server, MySQL, PostgreSQL, InterBase, Firebird, and SQLite databases, has announced the release of dbForge Schema Compare for SQL Server 1.50, a sophisticated tool specially designed to meet a diversity of comparison tasks, help analyze schema differences at a glance, and synchronize them correctly, saving time and efforts.

With the new release, Devart continues its dedication to providing a line of safe as well as powerful tools for SQL Server database synchronization.

The highlights of Schema Compare for SQL Server 1.50 include:

* Table data verification after synchronization

dbForge Schema Compare for SQL Server 1.50 moves forward in delivering safe synchronization. The present-day market, saturated with all sorts of …

I am publishing my MySQL related bookmark collection http://www.mysqlpreacher.com/bookmarks/.

Feel free to send me links you think might be good to add in order to help others.

Remember, SHARING IS CARING!!! …. we get so much for free, why shouldn’t we give some back?

Cheers,
Darren

After Chris' great post, What's faster than ALTER, I got thinking about REPAIR. REPAIR TABLE can be slow, but may seem necessary if your table is corrupt.

A little background:
MyISAM tables are prone to corruption if they are receiving updates/inserts/deletes/etc and your server crashes. Doing a REPAIR TABLE can generally "fix" them, but it can take time. The larger the table, the longer it takes. How long? Well, a rough approximation that I use is:

So a …

rsnapshot is a filesystem snapshot utility for making backups of local and remote systems, based on rsync. Rather than just doing a complete copy every time, it uses hardlinks to create incrementals (which are from a local perspective a full backup also). You can specify how long to keep old backups, and all the other usual jazz. You’d generally have it connect over ssh. You’ll want/need to run it on a filesystem that supports hardlinks, so that precludes NTFS.

In the context of MySQL, you can’t just do a filesystem copy of your MySQL data/logs, that would be inconsistent and broken. (amazingly, I still see people insisting/arguing on this – but heck it’s your business/data to gamble with, right?)

Anyway, if you do a local mysqldump also, or for instance use …

This Friday (Sept 18th 2009) will be my last day at Sun. I found an opportunity that I could not refuse. It been great working at Sun for the past 10 years. I have nothing but great things to say about Sun (and PAE - the organization I worked for). In case you need to reach me, you can try emailing me at realneel at gmaildotcom or on Linkedin

I will continue blogging on my personal site at http://realneel.blogspot.com. Hope to see you there.

I'm pleased to announce the release of DBD::mysql 4.013. I've been busy with so many interesting tasks at NorthScale which include keeping my open source projects moving along. I made some time this week to conglomerate some patches that have been submitted and close tickets which this release consists of.

Changes from the changelog:

* #49484: PATCH add support for MYSQL_INIT_COMMAND to
DBD::mysql - Thanks Peter John Edwards!
* #48242: 'mysql_bind_type_guessing' doesn't work correctly
with minus sign - Thanks Serguei Trouchelle!
* #45616: t/40blobs.t cannot pass without database access -
ServerError() not declared - Thanks ysth http://ysth.livejournal.com/

Thanks to all who helped with patches on this release!

The files:

file: $CPAN/authors/id/C/CA/CAPTTOFU/DBD-mysql-4.013.tar.gz
…

Most of the tests in the MySQL regression test suite are hardwired to use MyISAM or InnoDB. This has made it difficult for new storage engines to run these tests. PBXT has spent too much time modifying these tests and maintaining a huge patch for that modification. Guess what? Now MySQL needs a storage-engine independent test suite.

For MySQL 5.1.38 there are 75 tests for built-in InnoDB in mysql-test/t and 8 tests for plug-in InnoDB in mysql-test/suite/innodb/t. I am interested in using the plug-in but only when it has been tested. I will figure out how to get the existing tests to run with the plug-in, but that will require code changes. Others might want to be able to do that without modifying the distribution.

A better long-term solution is a test suite that works for any storage engine.