This was the title of an article back in 2002 by Shelley Doll and other articles with more or less the same concerns including the secretary of the ANSI database committee. In my recent blog post I made the point that I do hope that MySQL AB tries to follow the standards as much as possible (while retaining the freedom to add things as deemed necessary .. LIMIT and friends). In a chat conversation with Jan, he asked me what my thoughts are in regards to the SQL standard in particular. Most people will for example agree that standards compliant SQL routines are no fun to write. Unfortunately I had to agree with him that SQL today isn't what it should be.

Since I am not sure if everybody is aware of this so just let …

mysqlsla v1.6 is ready. It has four new options to allow better isolation/filtering of queries: –only-databases, –only-users, –only-hosts, –only-ids. Each option limits the queries under analysis to their respective properties (i.e., “–only-users root” only analyzes queries executed by root). See the documentation for which options work with which kinds of logs.

One of MMM users reported that they’re experiencing really weird memory leaks in checker processes used by MMM. After a deep investigation I’ve found out that Perl part of the checker and checker modules does not leak (at least I didn’t found these leaks), so I think it could be caused by some problems in MySQL DBD module (client uses Ubuntu server).

So, I’d like to ask all users to check if their checker processes use more memory than expected and if yes, what OS, MySQL libraries versions and Perl version used on their servers.

Thanks in advance for any help.

Some small (but meaningful) updates in HEAD at http://consoleninja.net/code/dpm/dpm.git :

- You can now actually specify a username/password when connecting to a server. A few other similar spots were fixed.
- You can get the remote connection ID from a connection object.
- New myp.close() command for closing connections ;)

I've tested kill -9'ing clients, servers, in various states of transit with no obvious bugs. It's probably still unstable but these changes make a big difference.

I'm not an API designer by any stretch. I'm stuck wondering:

Should connobj:remote_id() (or just remote()) return the object id, or the full object?

On that same line, all callbacks have the affected connection ID as an argument. Should that just be a reference to the actual connection object, or continue to …

Some small (but meaningful) updates in HEAD at http://consoleninja.net/code/dpm/dpm.git :

- You can now actually specify a username/password when connecting to a server. A few other similar spots were fixed.
- You can get the remote connection ID from a connection object.
- New myp.close() command for closing connections ;)

I've tested kill -9'ing clients, servers, in various states of transit with no obvious bugs. It's probably still unstable but these changes make a big difference.

I'm not an API designer by any stretch. I'm stuck wondering:

Should connobj:remote_id() (or just remote()) return the object id, or the full object?

On that same line, all callbacks have the affected connection ID as an argument. Should that just be a reference to the actual connection object, or continue to …

I’ve been trying to [circle back]() and clean up things I left for later in several chapters of High Performance MySQL, second edition. This includes a lot of material in chapter 4, Schema Optimization and Indexing. At some point I’ll write more about the process of writing this book, and what we’ve done well and what we’ve learned to do better, but for right now I wanted to complete the picture of what material we have on schema, index, and query optimization.

Backcountry.com has been using open source for years. Last week, it finally gave something back. Unlike other web properties that have built their businesses on open source but acknowledge little need to contribute back, Backcountry.com has also given little back...but at least takes no pride in it. In fact, its press release announcing the code contribution is hilariously self-flagellating:

After mooching off the open source world for 10 years and making millions, Backcountry.com finally threw the Postgres community a bone in the form of a database synching tool curiously called Bucardo....

...

sysbench - Linux test bench. Easy as pie to test CPU, memory, threads, mysql, and disk performance.

Full description is available here: http://sysbench.sourceforge.net/docs/

install mysql, mysql-devel
wget http://superb-west.dl.sourceforge.net/sourceforge/sysbench/sysbench-0.4.8.tar.gz
tar xvzf sysbench*gz
cd sysbench*
./configure && make install

mysql tests

This will run 10 separate consecutive mysql tests using an InnoDB table type, each with 100 mysql threads, doing a total of 1000 various SQL operations per test. Then it will print the total time they took to finish:

sysbench --test=oltp --mysql-user=USER --mysql-password=PASS --mysql-db=test \\
  --mysql-host='HOST' --mysql-table-engine=innodb prepare
 
time perl -e "foreach(1..10){print \`sysbench --max-requests=1000 --test=oltp \\
  --mysql-user=USER …

The question I often get is how far MySQL may fall behind and how to keep replication from lagging.

The lag you will see will vary a lot from application to the application and from load to load. Plus what is the most important within same application the lag will likely have spikes - most of applications would have typical lag within few milliseconds while there will be rare cases when replication lags behind several seconds or even longer.

There are multiple reasons why application falls behind and why we see those lag spikes:

Slave Server Load - MySQL Replication goes in single thread so it is really vulnerable to the server load. If you get 100 active queries running on the slave overloading it, slave thread will most likely will not get CPU or Disk resources it needs in order to keep going. So if you want to keep replication lag under control you need to keep Slave load under control and avoid load …