September 23, 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 […]

The MySQL optimizer, the OS cache, and sequential versus random I/O

In my post on estimating query completion time, I wrote about how I measured the performance on a join between a few tables in a typical star schema data warehousing scenario. In short, a query that could take several days to run with one join order takes an hour with another, and the optimizer chose […]

MySQL Optimizer team comments on TPC-H Results

Yesterday I had a chance to speak to Igor – head of MySQL optimizer team and Timur – both of them expressed concern with TPC-H run results I posted and notes about little gains in MySQL 6.0. Do not get this post wrong. I’m not saying MySQL 6.0 SubQuery optimizations are non existent or priorities […]

TPC-H Run on MySQL 5.1 and 6.0

We were doing MySQL Performance evaluation on TPC-H queries for the client and they kindly allowed us to publish results which are very interesting. This is obviously not audited TPC-H run, and it can’t be because we used MyISAM tables which are not ACID complaint. Plus we only measured Power to keep things simple. We […]

MySQL Slow query log in the table

As of MySQL 5.1 get MySQL slow query log logged in mysql.slow_log table instead of the file as you had in previous versions. We rarely would use this feature as it is incompatible with our slow query analyses patch and tools Fixing this is not trivial while staying 100% compatible to standard format as TIME […]

Is it query which needs to be optimized ?

Last few days I had a lot of a lot of questions at MySQL Performance Forum as well as from our customers regarding query optimization… which had one thing in common – It is not query which needed to be optimized. Way too frequently people design schema first and then think how the queries they […]

Why MySQL could be slow with large tables ?

If you’ve been reading enough database related forums, mailing lists or blogs you probably heard complains about MySQL being unable to handle more than 1.000.000 (or select any other number) rows by some of the users. On other hand it is well known with customers like Google, Yahoo, LiveJournal,Technocarati MySQL has installations with many billions […]

InnoDB Full-text Search in MySQL 5.6 (part 1)

I’ve never been a very big fan of MyISAM; I would argue that in most situations, any possible advantages to using MyISAM are far outweighed by the potential disadvantages and the strengths of InnoDB. However, up until MySQL 5.6, MyISAM was the only storage engine with support for full-text search (FTS). And I’ve encountered many […]

Trying Archive Storage Engine

Today I noticed one of server used for web request profiling stats logging is taking about 2GB per day for logs, which are written in MyISAM table without indexes. So I thought it is great to try how much archive storage engine could help me in this case.

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 […]