How important a primary key design can be for MySQL performance? The answer is: Extremely! If tables use InnoDB storage engine, that is.

It all begins with the specific way InnoDB organizes data internally. There are two major pieces of information that anyone should know:

1. It physically stores rows together with and in the order of primary key values. It means that a primary key does not only uniquely identify a row, it is also part of it. Or perhaps rather, a physical row is part of table’s primary key.
2. A secondary index entry does not point to the actual row position, which is how it works in MyISAM. Instead, every single index entry is concatenated with a value of the corresponding primary key. When a query reads a row through a secondary index, this added value is used in additional implicit lookup by the primary key, to locate the actual row.

What could be a “rule of the thumb” for …

If for any reason you need to change the size of InnoDB log files (also known as transaction logs), but not sure how to do it, this post will guide you through the steps.

Step 1: Preflight checks Something to keep in mind

Database restart is needed as part of this process.

Locate your MySQL configuration file

If you don’t know where the configuration file is, you can follow one of my previous posts on “How to find MySQL configuration file?”.

Find the existing logs and check their size

If database is running, you can simply use a tool called lsof:

db01 ~ # lsof -c mysqld | grep ib_logfile
mysqld    15153 mysql    9uW     REG                8,3 5242880 19350809 /var/lib/mysql/ib_logfile0
mysqld    15153 mysql   10uW     REG                8,3 5242880 …

Have you ever wanted to get a list of indexes and their columns for all tables in a MySQL database without having to iterate over SHOW INDEXES FROM ‘[table]’? Here are a couple ways…

The following query using the INFORMATION_SCHEMA STATISTICS table will work prior to MySQL GA 5.6 and Percona Server 5.5.

SELECT table_name AS `Table`,
       index_name AS `Index`,
       GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics
WHERE table_schema = 'sakila'
GROUP BY 1,2;

This query uses the INNODB_SYS_TABLES, …

COUNT() function returns a number of rows returned by a query. In a popular opinion COUNT(*) should not be used on InnoDB tables, but this is only half true.

If a query performs filtering on any column, there is no relevant difference in how COUNT(*) will be executed regardless of the storage engine. In any such case MySQL has to look for matching rows and then count them.

In the following queries COUNT(*) can be used without any negative impact on performance:

SELECT COUNT(*) FROM mytable WHERE id = 12345
SELECT COUNT(*) FROM mytable WHERE is_enabled = 1
SELECT COUNT(*) FROM mytable WHERE username LIKE 'a%' AND is_enabled = 1

The real difference is when no filter is specified in WHERE clause, i.e. when query counts all rows in a table. MyISAM maintains cached row count for each table, so it can always return the value …

I have an internal project that is generating a materialized view of some pretty important data, using 96 application server cores against a 12 core database with 192g of memory and a buttload of SSD, good for about 250 MB/sec peak write rate in synthetic file creation.

The project was underperforming, even with MySQL 5.5-current. We never had more than 15mb/s sustained write rate, and never more than 24G of dirty pages in innodb. Looking at the appropriate (self-made) merlin graphs showed redo log contention and excessive checkpointing.

Install MySQL 5.6.4-m7, which can do large redo logs. A quick back-of-the-napkin calculation shows that around 32GB of redo log are just fine for our worklog, most likely. So I configure 2 ib_logfile of 16000M each, and we turn on the load.

The result is a write rate of 100mb/sec sustained, until the redo log reaches about 24G in size. Then queries stall, the internal …

This is the second blog post in the series of blog posts leading up to the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. This blog post is aimed at the optimizer enhancement Multi Range Read (MRR). Its available in both MySQL 5.6 and MariaDB 5.5

Now let’s take a look at what this optimization actually is and what benefits it brings.

Multi Range Read

With traditional secondary index lookups, if the columns that are being fetched do not belong to the secondary index definition (and hence covering index optimization is not used), then primary key lookups have to be performed for each secondary key entry fetched. This means that secondary key lookups for column values that do not belong to the secondary index definition can result in a lot of Random I/O. The …

This is another, in-case-you-missed-it, but it is definitely worth mentioning again:

MariaDB 5.3 is now GA

So, if you’re using MariaDB 5.1 or 5.2, I’d definitely recommend upgrading to the new MariaDB 5.3.

Also, if you’re a Windows user using MySQL 5.1, I’d highly consider making the switch to MariaDB 5.3 due to all of the Windows-specific improvements in it.

MariaDB 5.3 Enhancements:

• Subquery optimizations
• Semi-join subquery optimizations
• Non-semi-join optimizations
• Subquery Cache
• Subquery Optimizations Map
• Optimizations for derived tables and views
• Disk access optimization
• Join optimizations
• Index Merge improvements
• Optimizer control
• NoSQL-style interfaces
• Group commit for the binary log
• Replication and binary logging enhancements …

I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5

The post Index Condition Pushdown in MySQL 5.6 and MariaDB 5.5 and its performance impact appeared first on ovais.tariq.

I have been working with Peter in preparation for the talk comparing the optimizer enhancements in MySQL 5.6 and MariaDB 5.5. We are taking a look at and benchmarking optimizer enhancements one by one. So in the same way this blog post is aimed at a new optimizer enhancement Index Condition Pushdown (ICP). Its available in both MySQL 5.6 and MariaDB 5.5

Now let’s take a look briefly at what this enhancement actually is, and what is it aimed at.

Index Condition Pushdown

Traditional B-Tree index lookups have some limitations in cases such as range scans, where index parts after the part on which range condition is applied cannot be used for filtering records. For example, suppose you have a key defined as:

KEY `i_l_partkey` …

I was again reminded why setting SQL_MODE is so important in any new MySQL environment. While performing benchmark tests on parallel backup features with a common InnoDB tablespace and per file tablespace, I inadvertently missed an important step in the data migration. The result was the subsequent test that performed data population worked without any issues however there was no data in any InnoDB tables.

These are the steps used in the migration of InnoDB tables from a common tablespace model to a per-table tablespace model.

1. Dump all InnoDB tables
2. Drop all InnoDB tables
3. Shutdown MySQL
4. Change the my.cnf to include innodb-file-per-table
5. Remove the InnoDB ibdata1 tablespace file
6. Remove the InnoDB transactional log files
7. Start MySQL
8. Verify the error log
9. Create and load new InnoDB tables

However, step 6 was not …