In one of my previous posts, “How to resize InnoDB logs?”, I gave the advice on how to safely change the size of transaction logs. This time, I will explain why doing it may become necessary.

A brief introduction to InnoDB transaction logs

The transaction logs handle REDO logging, which means they keep the record of all recent modifications performed by queries in any InnoDB table. But they are a lot more than just an archive of transactions. The logs play important part in the process of handling writes. When a transaction commits, InnoDB synchronously makes a note of any changes into the log, while updating the actual table files happens asynchronously and may take place much later. Each log entry is assigned a Log Sequence Number – an incremental value that always uniquely identifies a change.

…

The objective of this benchmark is to measure the performance improvement achieved when enabling the Multi-Threaded Slave enhancement delivered as a part MySQL 5.6.

As the results demonstrate, Multi-Threaded Slaves delivers 5x higher replication performance based on a configuration with 10 databases/schemas. For real-world deployments, higher replication performance directly translates to:

· Improved consistency of reads from slaves (i.e. reduced risk of reading "stale" data)

· Reduced risk of data loss should the master fail before replicating all events in its binary log (binlog)

The multi-threaded slave splits processing between worker threads based on schema, allowing updates to be applied in parallel, rather than sequentially. This delivers benefits to those workloads that isolate application …

This week we talk with Martin Farach-Colton of Tokutek about what's new with TokuDB.

News/Events/Feedback
Oracle announced the MySQL Connect Conference to be held in San Francisco on Saturday, September 29 and Sunday, September 30th. Call for papers and registration will open on April 16th.

There is a free webcast on Efficiently deploying new MySQL applications on Windows .

read more

MySQL performance is largely defined by keys and how efficiently queries can use them. As you scale, at certain point it isn’t enough anymore to just have any indexes and still get a good performance in return. You have to really figure them out and allow your queries to do less work, as little work as possible.

The approach presented in this article can sometimes help designing such good, efficient indexes. As a consultant, I have to rely on it myself from time to time, having to optimize a query that works in a database I know nothing about.

Let’s assume there is an application, which collects user activity in various places. The application uses a poorly indexed database, so there are plenty of examples to choose from. Our example query performs a full table scan, which means it reads all rows from the table it uses. It is also among the most popular statements executed by application.

mysql> EXPLAIN …

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 …

Swapping occurs when system moves some data between memory and a special area on disk called swap space. The process is called swapping in or swapping out depending on the direction in which it happens. System swaps out when it makes a decision to free up some physical memory (RAM) and pushes data out to disk. It swaps in when an application needs to access data that was swapped out. MySQL is like any other application and any memory it holds can also be sent to disk. It may have severe negative impact on performance.

The foremost step to prevent swapping is ensuring that not database, not any other application can either independently or collectively use up all available memory. The peak usage may not exceed a threshold that still leaves comfortable buffer for any remaining system activity. If this condition is not met, nothing can help and swapping may occur.

Further tuning can be done …

Official MySQL documentation provides information that using regular expressions is “powerful way of specifying a pattern for a complex search”. Is it really such a powerful way of filtering and should be used, or is it a solution that should be avoided? As it usually happens in real life, there are many opinions and no universal answer. Unfortunately, it often turns out that the truth lies somewhere in the middle.

One of my clients asked me yesterday for a little help with a query badly hitting performance of their production server. They were complaining about performance of REGEXP powered query and asked for advice on how to make it efficient.

Original query used in customer’s application:

 SELECT id FROM list WHERE user_name REGEXP '^bulba[0-9]+$';

Of course `list` table was properly indexed.

Explain:

mysql> explain SELECT SQL_NO_CACHE id FROM list …

When I published the MariaDB-5.3.4 sysbench results I said “if your workload includes complex (sub)queries, then you will probably benefit more from MariaDBs new optimizer features”. Today I will present some benchmark results for complex workload.

The benchmark is DBT3, an implementation of the TPC-H specification. DBT3 is written in C and hosted at Sourceforge.

The DBT3 benchmark can run at different scale factors – defining the size of the database. I used a scale factor of 30 which yields ~30GB of raw data and ~48GB of disk footprint. The machine running the benchmark had 16G of memory.

InnoDB has the problem of fluctuating table statistics, leading to rather unpredictable query plans. For this reason the benchmark tables were created with the …

I have written a 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

The post Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5 appeared first on ovais.tariq.

MariaDB-5.5.21-beta is the first MariaDB release featuring the new thread pool. Oracle offers a commercial thread pool plugin for MySQL Enterprise, but now MariaDB brings a thread pool implementation to the community!

If you are not familiar with the term, please read the Knowledge Base article about it.

The main design goal of the thread pool is to increase the scalability of the MariaDB server with many concurrent connections. In order to test and demonstrate this, I have run the sysbench OLTP RO benchmark with up to 4096 threads to compare the new pool-of-threads and the traditional thread-per-connection scheduler:

Benchmark description:

• sysbench multi table OLTP, readonly
• 16 tables, totaling 40 mio …