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 .
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 …[Read more]
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:
What could be a “rule of the thumb” for …
[Read more]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 …
[Read more]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 …[Read more]
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 …
[Read more]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:
MariaDB announced their 5.3 GA and 5.5 alpha releases.
Oracle will be at DrupalCon in Denver, CO from Monday, March 19th through Friday, March 23rd.
Percona's XtraDB cluster announcement
XtraDB Cluster at the San Francisco MySQL Meetup
on Wednesday, March 21st
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.