I used PMP this week to find bottlenecks in MySQL while running two benchmarks and describe the first bottleneck in this note. Changing the code as described here doubled the peak QPS. Pardon the hype in the title. This doubles peak QPS at 64 and 128 concurrent threads. To double throughput in all cases you might need faster CPU cores and a faster network. I created feature request 54790 for this.

I ran sysbench with concurrent threads that fetch one row by primary key per statement. HANDLER was used instead of SELECT. This is a workload for which some NoSQL servers are very fast. The sysbench client ran on one host and the mysqld server on another. Throughput saturated at less than 80,000 QPS for more than 32 concurrent threads. On previous tests with the …

I pushed more patches to the Facebook patch for MySQL 5.1 at Launchpad. Alas, I have yet to publish release notes. The patches include:

• Merged to MySQL 5.1.47
• Backports from future 5.1 releases of bug fixes
• More background disk IO counted in information_schema.table_statistics
• Add the innodb_fast_checksums option that uses crc32 and is 10X faster
• Provide an option to do fuzzy checkpoint IO in the main background thread. On some servers we get many stalls for fuzzy checkpoint IO in user threads. This can be a …

MyTOP is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL.

UPDATE – I just fond Jeremy did update MyTOP in 2009 and released it on GitHub.  He fixed the 64x and 5.x bugs. He also incremented the version number to 1.7.  So, I’m bumping my number to 1.8.

Jeremy D. Zawodny <Jeremy@Zawodny.com> wrote the original in 2000 and has continued to update it until 2007. The 1.6 version works on MySQL up to version 4.x.

For weeks now and I’m been working on bringing it up to date.  When I started using version 1.6 it worked but didn’t return some data fields.  After …

Due to firewalls, virus scans, corporate security restrictions, or just plain bad luck; there are times when MySQL just won’t install on a specific Windows server.  Here’s a sure-fire install method.   Relax, I won’t have you run the msi installer again.

The first step is to remove directories from your previous install attempts.  Uninstall from the control panel.  Manually, rename or delete “C:\Program Files\MySQL\MySQL Server 5.1″.  Next, delete (or rename) the data directory.  Warning! If you have previously entered data into MySQL, deleting the data directory will delete data.  The directory is located at “C:\Documents and Settings\All Users\Application Data\MySQL\MySQL Server 5.1\data”.   You may need to unhide the data directory.

Let’s get started.

#1.   …

The On Time Flight Data (see previous entry) has 93 rows per flight and the first line of the CSV file has the names of the columns. Use head -1 on that file to obtain that information and to build a schema by piping the output to a file (and do not forget to remove that top line before loading the data).

Strip out the quotation marks, add CREATE TABLE flight ( at the front and ) ENGINE=InfiniDB; at the end. Year is the title of the first column and it has been renamed to FlightYear to avoid confusion with the function of the same name. The next part is to assign a data type to each column and a quick perusal of the data will show which fields are numeric, which ones are alphanumeric, and the one column of type DATE. Personal …

One of the long-time continuing complaints against using MySQL in an enterprise or large scale environment is it's logging system. The query log, slow query log, and error log all get written to the server's local filesystem, which is difficult and awkward to hook up to enterprise log collection and analysis systems. A recurring feature request is to be able to use the UNIX syslog instead.

One of the first things I did to Drizzle was tear out the existing logging code. This also removed a number of mutex locks, one of which was completely unnecessary, and one that got constantly hit even if logging was turned off.

In it's place I put two of the first Drizzle plugin interfaces, one for the query logging, which combined the features of the old MySQL query log and the slow query log, and one for error message reporting. I then wrote a plugin that took the query log, and sent it to the syslog.

At the last …

The first public mysqlnd plugin adds client side query result caching to all MySQL extensions of PHP (ext/mysql, ext/mysqli, PDO_MySQL). The cache is written in C. It does not change any of the PHP MySQL APIs and works with any PHP application using MySQL. Query results are stored on the client. Cached data can be stored in main memory, APC, Memcache, SQLite (theoretically - via SQLite …

The first public mysqlnd plugin adds client side query result caching to all MySQL extensions of PHP (ext/mysql, ext/mysqli, PDO_MySQL). The cache is written in C. It does not change any of the PHP MySQL APIs and works with any PHP application using MySQL. Query results are stored on the client. Cached data can be stored in main memory, APC, Memcache, SQLite (theoretically - via SQLite …

There's a lot of buzz lately about Hadoop. If you're completely new to Hadoop, I recommend the free videos from Cloudera (http://www.cloudera.com/resources/?type=Training). If you have a vague idea and want to play around, it's easy!
First, download Cloudera's training VM which has a small Hadoop cluster already installed and running: http://www.cloudera.com/developers/downloads/virtual-machine/
Second, you need to put some data into Hadoop. Fortunately for database folks, there's a tool to import data into Hadoop from MySQL called "Sqoop". It's already installed on the VM and there are instructions for using Sqoop to import some MySQL tables into Hadoop (see Desktop/instructions/exercises/SqoopExercise.html inside the VM). FYI, it's not uncommon to "Sqoop" data …

In MySQL, even though the name read_buffer_size implies that the variable controls only read buffering, but it actually does dual purpose by providing  sequential IO buffering for both reads and[...]