I got the message in the morning today about the bug being fixed in MySQL 5.6.6…. which I reported in Early 2006 (while still being with MySQL) and running MySQL 4.1 I honestly thought this issue was fixed long ago as it was indeed pretty annoying. I must say I’m very impressed with Oracle team [...]
Differences between READ-COMMITTED and REPEATABLE-READ transaction isolation levels
August 28, 2012 By 3 Comments
As an instructor with Percona I’m sometimes asked about the differences between the READ COMMITTED and REPEATABLE READ transaction isolation levels. There are a few differences between READ-COMMITTED and REPEATABLE-READ, and they are all related to locking.
Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
I presented a webinar this week to give an overview of several Full Text Search solutions and compare their performance. Even if you missed the webinar, you can register for it, and you’ll be emailed a link to the recording. During my webinar, a number of attendees asked some good questions. Here are their questions and my [...]
Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
Percona XtraDB Cluster (PXC) and the technology it uses (Galera) is an exciting alternative to traditional MySQL replication. For those who don’t know, it gives you: Fully Synchronous replication with a write latency increase equivalent to a ping RTT to the furthest node Automatic cluster synchronization, both incremental and full restores The ability to read [...]
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:
1 2 3 4 5 | ---TRANSACTION 12303, ACTIVE 26 sec mysql tables in use 2, locked 0 MySQL thread id 53038, OS thread handle 0x7ff759b22700, query id 3918786 localhost root Sending data select count(*) from sbtest,sbtest x Trx read view will not see trx with id >= 12304, sees < 12301 |
As you can [...]
SQL Injection Questions Followup
July 26, 2012 By 2 Comments
I presented a webinar today about SQL Injection, to try to clear up some of the misconceptions that many other blogs and articles have about this security risk. You can register for the webinar even now that I’ve presented it, and you’ll be emailed a link to the recording, which will be available soon. During [...]
MySQL Upgrade Webinar Questions Followup
June 28, 2012 By 7 Comments
I did a Webinar about MySQL Upgrade – Best Practices Yesterday and there were some questions we could not answer during Webinar, following Jay’s Lead I decided to post them as a Blog Post. Q: Can you go directly MySQL 5.0 to 5.5 for MyISAM tables? MyISAM have not been getting any significant development since [...]
Find and remove duplicate indexes
June 20, 2012 By 9 Comments
Having duplicate keys in our schemas can hurt the performance of our database: They make the optimizer phase slower because MySQL needs to examine more query plans. The storage engine needs to maintain, calculate and update more index statistics DML and even read queries can be slower because MySQL needs update fetch more data to [...]
A case for MariaDB’s Hash Joins
May 31, 2012 By Leave a Comment
MariaDB 5.3/5.5 has introduced a new join type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post we will see what the Hash Join is, how it works and for what types of queries would it be the right choice. I will show the results of executing benchmarks [...]
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, [...]
