Single host Cluster

A series of quick-start guides are now available to get you up and running with MySQL Cluster in as little time as possible; they are available for LINUX/Mac OS X, Windows and Solaris. The configuration is intentionally a simple one – 2 data nodes, 1 management node and 1 MySQL Server. Once you have this up and running, your next experiment may be to extend this over multiple hosts.

Download the Quick Start Guide for your platform below:

• MySQL Cluster Quick Start for Linux – also works for Mac OS X
• …

So running through some various tasks, I'm finally on the section of work where I can resurrect a script that finds inconsistent data between master-master pairs.

Let's get a quick summary to find our problems to target the script at.

dsh -ef 20 -w dbfacebook9b,dbfacebook9a -s ~/bin/execute_query.sh "FacebookShard \"CHECKSUM TABLE RollingUserLog\""
dbfacebook9b: *************************** 1. row ***************************
dbfacebook9b:    Table: FacebookShard.RollingUserLog
dbfacebook9b: Checksum: 538386033
dbfacebook9a: *************************** 1. row ***************************
dbfacebook9a:    Table: FacebookShard.RollingUserLog
dbfacebook9a: Checksum: 538386033

not bad.

CHECKSUM TABLE uses a ACCUM algorithm to determine the table checksum. This gives a good fast snapshot in a shared lock mode so writes do not stop while doing a …

Sheeri and others are organizing another incarnation of OpenSQL Camp in October in Boston. You ought to go! It’s relevant to MySQL, PostgreSQL, SQLite, and lots of the newer generation of databases — MongoDB, Cassandra, and so on.

Related posts:

1. There’s a European OpenSQL Camp coming up
2. Going to OpenSQL Camp US 2009
3. OpenSQL Camp events in 2009
4. The history of OpenSQL Camp
…

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 …