April 16, 2014

Indexes in MySQL

MySQL does not always make a right decision about indexes usage. Condsider a simple table:

; 250001 (V1)

; 83036 (V2) (execution time = 110 ms) That is index selectivity by condition (ID1=1) is V2/V1 = 0.3321 or 33.21% It is said (e.g. book “SQL Tuning”) if selectivity over 20% then a full table […]

InnoDB memory usage

There are many questions how InnoDB allocates memory. I’ll try to give some explanation about the memory allocation at startup. Some important constants: NBLOCKS=count of block in innodb_buffer_pool = innodb_buffer_pool_size / 16384 OS_THREADS= if (innodb_buffer_pool_size >= 1000Mb) = 50000 else if (innodb_buffer_pool_size >= 8Mb) = 10000 else = 1000 (it’s true for *nixes, for Windows […]

ORDER BY … LIMIT Performance Optimization

Suboptimal ORDER BY implementation, especially together with LIMIT is often the cause of MySQL Performance problems. Here is what you need to know about ORDER BY … LIMIT optimization to avoid these problems ORDER BY with LIMIT is most common use of ORDER BY in interactive applications with large data sets being sorted. On many […]

Percona XtraDB Cluster 5.6.15-25.5 is now available

Percona is glad to announce the new release of Percona XtraDB Cluster 5.6 on March 20th 2014. Binaries are available from downloads area or from our software repositories. Based on Percona Server 5.6.15-63.0 including all the bug fixes in it, Galera Replicator 3.4 and on Codership wsrep API 25.5 is now the current General Availability […]

Percona Server 5.5.36-34.0 is now available

Percona is glad to announce the release of Percona Server 5.5.36-34.0 on March 10th, 2014 (Downloads are available here and from the Percona Software Repositories). Based on MySQL 5.5.36, including all the bug fixes in it, Percona Server 5.5.36-34.0 is now the current stable release in the 5.5 series. All of Percona‘s software is open-source and free, all the details of the release […]

The MySQL ARCHIVE storage engine – Alternatives

In my previous post I pointed out that the existing ARCHIVE storage engine in MySQL may not be the one that will satisfy your needs when it comes to effectively storing large and/or old data. But are there any good alternatives? As the primary purpose of this engine is to store rarely accessed data in disk […]

WITHer Recursive Queries?

Over the past few years, we’ve seen MySQL technology advance in leaps and bounds, especially when it comes to scalability. But by focusing on the internals of the storage engine for so long, MySQL has fallen behind regarding support for advanced SQL features. SQLite, another popular open-source SQL database, just released version 3.8.3, including support […]

InnoDB scalability issues due to tables without primary keys

Each day there is probably work done to improve performance of the InnoDB storage engine and remove bottlenecks and scalability issues. Hence there was another one I wanted to highlight: Scalability issues due to tables without primary keys This scalability issue is caused by the usage of tables without primary keys. This issue typically shows […]

Using keepalived for HA on top of Percona XtraDB Cluster

Percona XtraDB Cluster (PXC) itself manages quorum and node failure.  Minorities of nodes in a network partition situation will move themselves into a Non-primary state and not allow any DB activity.  Nodes in such a state will be easily detectable via SHOW GLOBAL STATUS variables. It’s common to use HAproxy with PXC for load balancing purposes, but […]

How can we bring query to the data?

Baron recently wrote about sending the query to the data looking at distributed systems like Cassandra. I want to take a look at more simple systems like MySQL and see how we’re doing in this space. It is obvious getting computations as closer to the data as possible is the most efficient as we will […]