May 25, 2013

Innodb Table Locks

July 31, 2012 By 4 Comments

Innodb uses row level locks right ? So if you see locked tables reported in SHOW ENGINE INNODB STATUS you might be confused and rightfully so as Innodb table locking is a bit more complicated than traditional MyISAM table locks. Let me start with some examples. First lets run SELECT Query:

As you can [...]

Filed Under: Insight for DBAs, MySQL

DROP TABLE and stalls: Lazy Drop Table in Percona Server and the new fixes in MySQL

June 22, 2012 By 33 Comments

Suppose you have turned on innodb_file_per_table (which means that each table has its own tablespace), and you have to drop tables in a background every hour or every day. If its once every day then you can probably schedule the table dropping process to run during off-peak hours. But I have seen cases where the [...]

Filed Under: Benchmarks, Insight for DBAs, MySQL

Benchmarking single-row insert performance on Amazon EC2

May 16, 2012 By 17 Comments

I have been working for a customer benchmarking insert performance on Amazon EC2, and I have some interesting results that I wanted to share. I used a nice and effective tool iiBench which has been developed by Tokutek. Though the “1 billion row insert challenge” for which this tool was originally built is long over, [...]

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , , , ,

Troubleshooting MySQL Memory Usage

March 21, 2012 By 5 Comments

One of the most painful troubleshooting tasks with MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use reasonable global buffers, such as innodb_buffer_size, key_buffer_size etc, you have reasonable amount of connections but yet MySQL takes much more memory than you would expect, causing swapping or other [...]

Filed Under: Insight for DBAs, MySQL

Optimize Your SQL With Percona’s Online Query Advisor!

February 15, 2012 By 23 Comments

Wouldn’t it be nice if you could get expert advice on your SQL queries to find problems in them, the same way that programmers can use lint-check tools to warn about bugs in their C?

Such a simple mistake, but it’s the kind of thing that James Bond movies are made of, isn’t it? [...]

Filed Under: Insight for DBAs, Insight for Developers, MySQL

How to recover a single InnoDB table from a Full Backup

January 25, 2012 By 10 Comments

Sometimes we need to restore only some tables from a full backup maybe because your data loss affect a small number of your tables. In this particular scenario is faster to recover single tables than a full backup. This is easy with MyISAM but if your tables are InnoDB the process is a little bit [...]

Filed Under: Insight for DBAs, MySQL, Percona Software

MySQL performance on EC2/EBS versus RDS

August 4, 2011 By 11 Comments

A while ago I started a series of posts showing benchmark results on Amazon EC2 servers with RAID’ed EBS volumes and MySQL, versus RDS machines. For reasons that won’t add anything to this discussion, I got sidetracked, and then time passed, and I no longer think it’s a good idea to publish those blog posts [...]

Filed Under: Benchmarks, Cloud and NoSQL, MySQL

Distributed Set Processing with Shard-Query

May 14, 2011 By 27 Comments

Can Shard-Query scale to 20 nodes? Peter asked this question in comments to to my previous Shard-Query benchmark. Actually he asked if it could scale to 50, but testing 20 was all I could due to to EC2 and time limits. I think the results at 20 nodes are very useful to understand the performance: [...]

Filed Under: MySQL

More on MySQL Error Messages

April 6, 2011 By 9 Comments

I wrote about MySQL Error Messages before and as you might guess I’m not very happy with quality for error messages it produces. Now I’m revisiting this subject with couple of more annoying examples I ran into during last couple of days.

Filed Under: Insight for DBAs, MySQL

MySQL on Amazon RDS part 1: insert performance

March 15, 2011 By 37 Comments

Amazon’s Relational Database Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance limitations on standard EC2 servers with EBS volumes (see SSD versus EBS death match), and one of them wanted to evaluate RDS as a replacement. It is built on the same technologies, but the hardware and networking are supposed [...]

Filed Under: Cloud and NoSQL, MySQL
« Older Posts
Newer Posts »