A high-performance application that has producers and consumers of some resource, such as a queue of messages, needs an efficient way to notify the consumers when the producer has inserted into the queue. Polling the queue for changes is not a good option. MySQL’s GET_LOCK() and RELEASE_LOCK() functions can provide both mutual exclusivity and notifications. This post was prompted by a message to the MySQL general emailing list some time ago, but I’m finally getting around to actually testing the theoretical solution I mentioned then (I can never just think my way through anything that involves locking and waiting… I have to test it).

Having switched from Oracle to MySQL we were chugging along with development when we realized that in MySQL, autocommit is set to 1 by default. Ie, if you don’t explicitly start and end a transaction, each statement will commit as it executes. Type “delete from user” in the MySQL client, hit return, think “oops”, and it’s too late. “rollback” won’t help you now.

So I used “init_connect” to set autocommit=0 for incoming connections. (In my.cnf, I added init_connect=’set autocommit=0′.) Seemed to work great. (init_connect can be used to set session-level variables for connections.)

Then we moved this change from dev and QA into production. Suddenly, our UI wasn’t working. We were scrambling to figure out what was different between QA and production. I was told that the permissions must be different. Being a real newbie, I compared all the grants for ‘UI’ in the information_schema database and said they …

When we optimize clients' SQL queries I pretty often see a queries with SQL_CALC_FOUND_ROWS option used. Many people think, that it is faster to use this option than run two separate queries: one - to get a result set, another - to count total number of rows. In this post I'll try to check, is this true or not and when it is better to run two separate queries.

For my tests I've created following simple table:

PLAIN TEXT SQL:

1. CREATE TABLE `count_test` (
2.   `a` int(10) NOT NULL AUTO_INCREMENT,
3.   `b` int(10) NOT NULL,
4.   `c` int(10) NOT NULL,
5.   `d` varchar(32) NOT NULL,
6.   PRIMARY KEY  (`a`),
7.   KEY `bc` (`b`,`c`)
8. ) ENGINE=MyISAM

Test data has been created with following script (which creates 10M records):

PLAIN TEXT PHP:

1. …

In the Enterprise Team we had the need to track how many connections to the MySQL server in parallel. By "used" I mean running queries and not just idling around.

And the make it more complicated we needed the information not just every few seconds (while true; do echo "SHOW PROCESSLIST" | mysql; sleep 1;done) but when it happens.

Proxy to the rescue we now have a script which tracks the global state of all connections going through the proxy and dump them to stdout when their state changes:

2007-08-28 21:39:55
  #connections: 20, #active trx: 3, max(active trx): 4
  [792] (merlin@merlin) (COM_QUERY) "SET autocommit=0" (state=started)
  [794] (merlin@merlin) (COM_QUERY) "SET autocommit=1" (state=started)
  [795] (merlin@merlin) (COM_QUERY) "SET autocommit=1" (state=started)

• when the query comes in, we add it with its thread-id to the global table
• when the query is done, we …

I believe we wrote about this before, but this topic popups again and again.
Today I've read opinion that if we have clause WHERE has_something=1 we should have index on column `has_something` (the column has two values 0 and 1).

In reality the right answer is not so simple.

Let's look next table

PLAIN TEXT SQL:

1. CREATE TABLE `testr` (
2.   `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3.   `name` varchar(32) NOT NULL,
4.   `has_something` tinyint(3) UNSIGNED NOT NULL,
5.   PRIMARY KEY  (`id`),
6.   KEY `has_something` (`has_something`)
7. ) ENGINE=MyISAM

with 20.000.000 records.

And in first case has_something=0 for 90% of rows (with random distribution)

PLAIN TEXT SQL:

1. mysql> SELECT cnt0/cnt FROM (SELECT count(*) cnt0 FROM …

Dana Blankenhorn over at sister publication ZDNet has a great analysis of the looming Microsoft Sharepoint threat. As Dana points out, it's a threat that open source is well-suited to meet and beat, but doing so will require some project coordination.

Interestingly, it's a threat that the proprietary Enterprise Content Management vendors have rejected as credible, even as Sharepoint boots them out of customer accounts. While ECM, operating system, database, etc. vendors sleep, Sharepoint is gaining ground.

If only open source could get its act together:

...

Where can you get access to some of the most informed people from MySQL and the community, for free?

The answer: at MySQL Camp. And then throw in lunch and breakfast for free, being able to influence the session topics and you have quite a package deal.

So it is strange why so few people took up the offer in New York this year!?

My talk was about the BLOB Streaming engine, MyBS, and I have posted the slides: Presentation - MySQL Camp 2007: The BLOB Streaming Project.

OK, so I got pretty much ragged about the name, MyBS. Why, I was asked, did I name it that? Jay, even suggested a session to find a new name for the engine! Thanks, Jay, very considerate of you... :)

But it was quite unnecessary, because I really can't see what the …

About year ago Peter wrote about redundant indexes and mentioned sometimes it is good to leave two indexes, even one is first part of another. I'm speaking about BTREE indexes, for example, KEY (A), and KEY (A,B). From SQL point of view KEY(A) is not needed, as for queries like WHERE A=5 the index (A,B) also can be used.

But there is case when for performance it would be good to have both

Let we have the table

PLAIN TEXT SQL:

1. CREATE TABLE `userinfo` (
2.   `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
3.   `name` varchar(64) NOT NULL DEFAULT '',
4.   `email` varchar(64) NOT NULL DEFAULT '',
5.   `password` varchar(64) NOT NULL DEFAULT '',
6.   `dob` date DEFAULT NULL,
7.   `address` …

ActiveGrid, Inc., a leading provider of Enterprise Web 2.0 solutions, has announced it is working with MySQL to simplify the migration of legacy applications to modern web architectures. Last week, the companies published the "Microsoft Access to MySQL Migration Guide," a quick guide to migrating MS Access applications to a CIO Safe, Enterprise Web 2.0 deployment architecture. Next month, the two companies will be offering a free webinar covering Application Migration to Enterprise Web 2.0.

When I started, I found the section on backups in the manual a bit confusing. I wanted to know, what’s available for InnoDB backups that would correspond to “exports” and “hot backups” for Oracle. I had trouble extracting a clear answer from the manual.

Here’s what I’ve found over time, to the best of my knowledge.

Assuming you don’t want to buy extra software (eg, Zmanda), you can use mysqldump (”export”) and/or LVM (”hot backup”) for your backup needs. (mysqlhotcopy is not for InnoDB.) Replication is nice, too. Here’s a bit about each method.

1. mysqldump (think “export”)
You can get a full backup, even for point-in-time recovery, using mysqldump. Mysqldump is sort of like Oracle’s export. Dumps the database, and you can use it to import it back. It creates a bunch of text commands that you can redirect into a mysql client to do the import. And mysql has a tool for …