This month on Database Journal we talk about multi-master MySQL using circular replication to achieve high availability.

Read more at DatabaseJournal – Intro to Multi-Master MySQL

Mat Keep and I will be presenting this free webinar on Wednesday 14 July.

The MEMORY storage engine has been widely adopted by MySQL users to provide near-instant responsiveness with use cases such as caching and web session management. As these services evolve to support more users, so the scalability and availability demands can start to exceed the capabilities of the MEMORY storage engine.

The MySQL Cluster database, which itself can be implemented as a MySQL storage engine, is a viable alternative to address these evolving web service demands. MySQL Cluster can be configured and run in the same way as the MEMORY storage engine (ie on a single host with no replication and no persistence). As web services evolve, any of these attributes can then be added in any combination to deliver higher levels of scalability, …

Thanks to Martin Loschwitz, official Linux cluster stack Debian packages are now available for Debian lenny. Check out the the lenny-backports repository on backports.org (and your favorite local mirror).

When upgrading from Heartbeat 2.1.3, this webinar recording may come in handy. The webinar covers an upgrade to squeeze, but the upgrade procedure is identical if you’re staying on lenny.

…

When examining the execution plan of troublesome queries in MySQL, most users are aware of using EXPLAIN. However, an often overlooked, yet very helpful extension of EXPLAIN, is EXPLAIN EXTENDED coupled with the SHOW WARNINGS command.

The reason being is because it provides a little more information about how the optimizer processes the query, and thus it could help to quickly identify a problem that you might not otherwise recognize with just EXPLAIN.

For instance, here is a common query which could be inefficient:

SELECT id FROM t WHERE id='1';

And here is the CREATE TABLE output:

mysql> show create table tG
*************************** 1. row …

Senior Database Administrator Position: Senior Database Administrator
Job Type: Full-Time
Department: Engineering
Location: San Jose, CA
Responsibilities

• Manage MySQL in production/QA/development environments including installation, configuration, backup, recovery, upgrades, schema changes, etc.

• Perform database health monitoring and diagnostics

• Implement monitoring, auditing and alert systems for MySQL databases

• Troubleshoot and resolve problems with MySQL

• Design, implement, maintain and automate the appropriate backup and recovery architecture required

• Work with various teams for performance tuning and optimization, query optimization, index tuning, caching, buffer tuning, etc.

• Perform capacity planning exercises to properly identify required hardware, software, database configuration/architecture necessary to support …

Working on a new set of replication APIs in MariaDB, I have given some thought to the generation of replication events on the master server.

But there is another side of the equation: to apply the generated events on a slave server. This is something that most replication setups will need (unless they replicate to non-MySQL/MariaDB slaves). So it will be good to provide a generic interface for this, otherwise every binlog-like plugin implementation will have to re-invent this themselves.

A central idea in the current design for generating events is that we do not enforce a specific content of events. Instead, the API provides accessors for a lot of different information related to each event, allowing the plugin flexibility in choosing what to include in a …

Every month or so a node randomly dies

EQX root@cass01:/opt/cassandra/bin# ./nodeprobe -host localhost -port 8181 ring
Address       Status     Load          Range                                      Ring
                                       facebook_1301003235_1301003235             
10.129.28.22  Down       15.77 GB      9ZehBzpHHwnxiPJU                           |<--|
10.129.28.23  Up         7.59 GB       facebook_100000471858343_1514390063        |   |
10.129.28.14  Up         4.59 GB       facebook_100000846936312                   |   |
10.129.28.20  Up         12.94 GB      facebook_1301003235_1301003235             |-->|

Trying to get info from the host, the reads timeout.
java.net.SocketTimeoutException: Read timed out


Doing an lsof -p on the java proc I see that it is holding open a bunch of sockets. So the node itself is hanging on …

The most efficient performance optimization of a SQL statement is to eliminate it. Cary Millsap’s recent Kaleidoscope presentation again highlighted that improving performance is function of code path. Removing code will improve performance.

You may think that it could be hard to eliminate SQL, however when you know every SQL statement that is executed in your code path obvious improvements may be possible. In the sequence SQL was implemented sometimes easy observations can lead to great gains. Let me provide some actual client examples that were discovered by using the MySQL General Log.

Example 1

5 Query   SELECT *  FROM `artist`
5 Query   SELECT *  FROM `artist`
5 Query …

If you haven't already heard PBMS is now part of the Drizzle tree.

Getting it there was a fair bit of work but not as much as I had thought it would be. The process of getting it to work with Drizzle and running it thorough Hudson has improved the code a lot. It is amazing what some compilers will catch that others will let by. I am now a firm believer in treating all compiler warnings as errors.

I am just in the process of updating the PBMS plugin so that it will build and install the PBMS client library (libpbmscl.so) as well as the plugin. The PBMS client library is a standalone library that can be used to access the PBMS daemon weather it is running as part of MySQL or Drizzle. So a PBMS client library built with Drizzle can be used to access a PBMS daemon running as part of MySQL and vice-versa.

There is also PHP extension for PBMS that is basically just a wrapper for the library. Currently this is …

One role of a MySQL consultant is to review an existing production system. Sometimes you have sufficient time and access, and other times you don’t. If I am given a limited time here is a general list of things I look at.

1. Review Server architecture, OS, Memory, Disks (including raid and partition type), Network etc
2. Review server load and identify physical bottleneck
3. Look at all running processes
4. Look specifically at MySQL processes
5. Review MySQL Error Log
6. Determine MySQL version
7. Look at MySQL configuration (e.g. /etc/my.cnf)
8. Look at running MySQL Variables
9. Look at running MySQL status (x n times)
10. Look at running MySQL INNODB status (x n times) if used
11. Get Database and Schema Sizes
12. Get Database Schema
13. Review Slow Query Log
14. Capture query sample via SHOW FULL PROCESSLIST (locked …