I learn more and more about Galera every day. As I learn more, I try to keep my myq_gadgets toolkit up to date with what I consider is important to keep any eye on on a PXC node. In that spirit, I just today pushed some changes to the ‘wsrep’ report, and I thought I’d go over [...]
Realtime stats to pay attention to in Percona XtraDB Cluster and Galera
November 26, 2012 By 4 Comments
Concatenating MyISAM files
November 19, 2012 By 5 Comments
Recently, I found myself involved in the migration of a large read-only InnoDB database to MyISAM (eventually packed). The only issue was that for one of the table, we were talking of 5 TB of data, 23B rows. Not small… I calculated that with something like insert into MyISAM_table… select * from Innodb_table… would take [...]
Knowing what pt-online-schema-change will do
November 1, 2012 By 1 Comment
pt-online-schema-change is simple to use, but internally it is complex. Baron’s webinar about pt-online-schema-change hinted at several of the tool’s complexities. Consequently, users often want to know before making changes what pt-online-schema-change will do when it runs. The tool has two options to help answer this question: –dry-run and –print. When ran with –dry-run and –print, pt-online-schema-change changes nothing [...]
Edge-case behavior of INSERT…ODKU
October 27, 2012 By 6 Comments
A few weeks back, I was working on a customer issue wherein they were observing database performance that dropped through the floor (to the point of an outage) roughly every 4 weeks or so. Nothing special about the environment, the hardware, or the queries; really, the majority of the database was a single table with [...]
Recovering from a bad UPDATE statement
October 19, 2012 By 5 Comments
Did you just run an UPDATE against your 10 million row users table without a WHERE clause? Did you know that in MySQL 5.5 that sometimes you can recover from a bad UPDATE statement? This is possible if you are running in binlog_format=ROW ! Imagine this scenario:
1 2 3 4 5 6 | CREATE TABLE `t1` ( `c1` int(11) NOT NULL AUTO_INCREMENT, `c2` varchar(10) NOT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB; INSERT INTO `t1` (`c2`) VALUES ('michael'), ('peter'), ('aamina'); |
We run an accidental UPDATE statement that [...]
Tools and Techniques for Index Design Webinar Questions Followup
October 4, 2012 By 5 Comments
I presented a webinar this week to give an overview of Tools and Techniques for Index Design. Even if you missed the webinar, you can register for it, and you’ll be emailed a link to the recording. I’d like to invite folks who are interested in tools for query optimization to attend the new Percona [...]
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 [...]
Recovery deleted ibdata1
August 10, 2012 By 9 Comments
Recently I had a case when a customer deleted the InnoDB main table space – ibdata1 – and redo logs – ib_logfile*. MySQL keeps InnoDB files open all the time. The following recovery technique is based on this fact and it allowed to salvage the database. Actually, the files were deleted long time ago – [...]
Find unused indexes
June 30, 2012 By Leave a Comment
I wrote one week ago about how to find duplicate indexes. This time we’ll learn how to find unused indexes to continue improving our schema and the overall performance. There are different possibilites and we’ll explore the two most common here. User Statistics from Percona Server and pt-index-usage. User Statistics User Statistics is an improvement [...]
