Recently I was discussing with some colleagues the possibility of consolidating some MySQL servers. While the servers are not heavily loading (averaging less than 1,000 queries a second) they are pretty large in terms of storage requirements. Each server has roughly 200 databases on each with approximatley 50 tables. Thats 10,000 tables per server.  Each server contains up to 1 terabyte of data so if you consolidated servers at a 10:1 ratio you would have 10 terabytes of data, 2,000 databases and 100,000 tables with 10,000 queries per second average load.

Alright, that's a lot. And without testing I don't know if it would work. It probably wouldn't.  But it might. And if it did, it would save the company a significant amount of money.  But, while discussing this,  someone brought up that open files limit might be a problem. Open files limit is the maximum number of files the …

Read the original article at Anatomy of a Performance Review

A lot of firms come to us with a specific scalability problem. “Our user base is growing rapidly and the website is falling over!” Or they’re selling more widgets, “Our shopping cart is slowing down and we’re seeing users abandon their purchases”. These are real startup growing pains, so what to do?

We like to take a measured approach with these types of challenges, so we thought it would be helpful to run through a hypothetical scenario and see how we work.

Having trouble with scalability? Check out our 5 things toxic to scalability piece. …

- Future-Proofing MySQL for the World-Wide Data Revolution by Robert Hodges- Solving Large-Scale Database Administration with Continuent Tungsten by Robert Hodges and Neil Armitage  - MySQL Backup and Recovery Essentials by Ronald Bradford  - Consolidate Your Sharded Database Indexes in Real-time by Jeff Mace   Get 10% discount from your conference pass by using discount code Continuent

GenieDB is building a database with global distribution as its core thesis. It is no secret customers demand near-instantaneous and highly reliable service, and that they are becoming more globally dispersed than ever before. We believe that data custodianship must ultimately be moved to the “edge of the web” where it can be dynamically managed in order to improve user experience, optimize network/hardware utilization and reduce TCO. A single datacenter hosted database and application stack runs afoul of this fundamental thesis in a number of ways. In this article we will focus on the issue of improving response time for users even when they are globally distributed. This is simply a matter of physics and how long it takes to transmit a packet between the two locations. No amount of application tuning can overcome this obstacle.

The obvious solution is to have multiple copies of the …

The SkySQL® Cloud Data Suite Agreement is, like any set of terms and conditions, written in legalese. But what does it really say, in broad strokes?   While the legalese terms and conditions constitute the valid agreement, I think it's a good idea to condense some key thoughts into plain English. So here goes:

1. We give you the right to remotely access our SkySQL Cloud Data Suite and use it to configure and download the MariaDB database for your use on your own Amazon AWS account.
2. We provide you with a 30-day trial license to use MONyog.
3. Although you need to have your own AWS account, we don’t need access to any of your data.
4. If you ask for the SkySQL Cloud Data Suite to automatically download your configured MariaDB dbms, we won’t store your AWS credentials.
5. Downloaded software is made …

Since Fractal Tree indexes turn random writes into sequential writes, it’s easy to see why they offer a big advantage for maintaining indexes on rotating disks. It turns out that that Fractal Tree indexing also offers signficant advantages on SSD. Here are three ways that Fractal Trees improve your life if you use SSDs.

Advantage 1: Index maintenence performance.

The results below show the insertion of 1 billion rows into a table while maintaining three multicolumn secondary indexes. At the end of the test, TokuDB’s insertion rate remained at 14,532 inserts/second whereas InnoDB had dropped to 1,607 inserts/second. That’s a difference of over 9x.

Even on flash, I/O performance costs something. Since TokuDB employs Fractal Tree write-optimized …

John Sellens presents Nagios and Another Layer of Indirection at the Nagios World Conference. PDF slides are here.

“All problems in computer science can be solved by another level of indirection” – David Wheeler

Nagios Constitution: Separation of Core and State

There are separate components and interfaces, and they’re well-defined. This separation allows us to subvert how they’re supposed to be used and do whatever we want.

Nagios if well-documented, that’s one of the major strengths!

Where is there indirection in Nagios?

Favorite plugin is the negate plugin – in the official nagios plugins.

Remote checking adds another layer – between a local plugin and the nagios server – check_by_ssh, check_nrpe, …

John Sellens presents Nagios and Another Layer of Indirection at the Nagios World Conference. PDF slides are here.
“All problems in computer science can be solved by another level of indirection” – David Wheeler
Nagios Constitution: Separation of Core and State
There are separate components and interfaces, and they’re well-defined. This separation allows us to subvert how they’re supposed to be used and do whatever we want.
Nagios if well-documented, that’s one of the major strengths!
Where is there indirection in Nagios?
Favorite plugin is the negate plugin – in the official nagios plugins.
Remote checking adds another layer – between a local plugin and the nagios server – check_by_ssh, check_nrpe, check_snmp
Another …

In the last blog post I wrote about how to log deadlock errors using Percona Toolkit. Foreign key errors have the same problems. InnoDB only logs the last error in the output of SHOW ENGINE INNODB STATUS, so we need another similar tool in order to have historical data.

pt-fk-error-logger

This is a tool very similar to pt-deadlock-logger that will help us to log those errors. It can log them to a table, file or just show them on STDOUT. Let’s see how to use it:

1- Create the table

First we create the table where we are going to store the information:

CREATE TABLE foreign_key_errors (
  ts datetime NOT NULL,
  error text NOT NULL,
  PRIMARY KEY (ts)
);

2- Run the tool as a daemon and store it on that …

There is a great quote from Simon that explains why some test cases are not getting published. I am happy that someone took the time to explain the issue to the community. I still hope that all test cases will eventually be shared. While reading his post I was amused by a quote from the MariaDB blog on how things were done in the good old days at MySQL.

We had pretty strict policies about it in MySQL AB (and, later, Sun Microsystems) — every new bug fix always had to come with a test case for the bug. 

Was that ever true? I know a lot of buggy behavior never had …