October 24, 2014

Learning about MySQL Table Fragmentation

Recently I was working with the customer who need quick warmup – to get Innodb table fetched in memory as fast as possible to get good in memory access performance. To do it I run the query: “SELECT count(*) FROM tbl WHERE non_idx_col=0″ I use this particular form of query because it will do full […]

Long PRIMARY KEY for Innodb tables

I’ve written and spoke a lot about using short PRIMARY KEYs with Innodb tables due to the fact all other key will refer to the rows by primary key. I also recommended to use sequential primary keys so you do not end up having random primary key BTREE updates which can be very expensive. Today […]

MySQL upgrade best practices

MySQL upgrades are necessary tasks and we field a variety of questions here at Percona Support regarding MySQL upgrade best practices. This post highlights recommended ways to upgrade MySQL in different scenarios. Why are MySQL upgrades needed? The reasons are many and include: Access to new features, performance benefits, bug fixes…. However, MySQL upgrades can […]

MySQL Indexing Best Practices: Webinar Questions Followup

I had a lot of questions on my MySQL Indexing: Best Practices Webinar (both recording and slides are available now) We had lots of questions. I did not have time to answer some and others are better answered in writing anyway. Q: One developer on our team wants to replace longish (25-30) indexed varchars with […]

Improved InnoDB fast index creation

One of the serious limitations in the fast index creation feature introduced in the InnoDB plugin is that it only works when indexes are explicitly created using ALTER TABLE or CREATE INDEX. Peter has already blogged about it before, here I’ll just briefly reiterate other cases that might benefit from that feature: when ALTER TABLE […]

Tools

This page contains links to various tools we found helpful to use in practice. Some tools are written by us, others by third parties, yet another ones may be shipped with your operating system you just need to find they are there. Percona Toolkit Percona Toolkit is a great set of tools for MySQL Performance […]

Working with many files and file system fragmentation

Working on performance optimization project (not directly MySQL related) we did a test – creating 100 files writing 4K in the random file for some time and when checking the read speed on the files we end up with, compared to just writing the file sequentially and reading it back. The performance difference was huge […]

Heikki Tuuri answers to Innodb questions, Part II

I now got answers to the second portions of the questions you asked Heikki. If you have not seen the first part it can be found here. Same as during last time I will provide my comments for some of the answers under PZ and will use HT for original Heikkis answer. Q26: You also […]

MySQL: what read_buffer_size value is optimal ?

The more I work with MySQL Performance Optimization and Optimization for other applications the better I understand I have to less believe in common sense or common sense of documentation writers and do more benchmarks and performance research. I just recently wrote about rather surprising results with sort performance and today I’ve discovered even read_buffer_size […]

MySQL Storage Engines – PBXT

I promised to write little articles about all storage engines which sessions I attended during MySQL Users Conference but I did not get too far yet, too busy. So today it is time for PBXT. I was very interested about session about PBXT because this is storage engine does not target the same general purpose […]