Percona is pleased to officially (and belatedly) welcome Sasha Pachev to our team of consultants.
Before joining Percona, Sasha worked as an independent MySQL consultant. Sasha was the original implementer and maintainer of MySQL replication from 3.23 to 4.02. He is also the author of MySQL Enterprise Solutions and Understanding MySQL Internals.
Sasha, a big welcome – we are fortunate indeed you’re working with us!
Entry posted by Ryan Lowe |
No comment
Add to: 
| 
| 
| 
| 
Percona Server version 5.1.53-12.4 is now available for download. It is now the current stable release version.
The 
Release Notes for this and previous releases can be found in our Wiki.
Downloads are available here and from the Percona Software Repositories. The latest source code for Percona Server, including the development branch, can be found on Launchpad.
Please report any bugs found at Bugs in Percona Server.
For general questions, use our Percona Discussions Group, and for development questions our Percona Development Group.
For support, commercial, and sponsorship inquiries, contact Percona.
Entry posted by Fred Linhoss |
No comment
Add to: | | | |
Inspired by Baron’s earlier post, here is one I hear quite frequently –
“If you enable innodb_file_per_table, each table is it’s own .ibd file. You can then relocate the heavy hit tables to a different location and create symlinks to the original location.”
There are a few things wrong with this advice:
Another common claim with this recommendation is that it allows you to quickly add space when running out. LVM actually allows you to add physical volumes, and increase the size of logical volumes 
This is much easier to do than more one large table around.
Entry posted by Morgan Tocker |
22 comments
Add to: | | | |
In my previous posts I highlighted that one of improvements in Percona Server is support of innodb_log_file_size > 4G. This test was done using Percona Server 5.5.7, but the same performance expected for InnoDB-plugin and MySQL 5.5.
The valid question how long is recovery in this case, so let’s test it. I took the same tpcc-mysql 1000W workload with 52GB and 144GB innodb_buffer_pool_size with data located on Virident tachIOn card and killed mysqld after 30 mins of work.
The recovery time after start is:
for 52GB innodb_buffer_pool_size:
that is 7min 51sec
for 144GB innodb_buffer_pool_size:
that is 9min 23sec
and
for 144GB innodb_buffer_pool_size with data stored on RAID10:
that is 14min 57sec
I think this time is acceptable as trade-off for performance.
However it should be taken into account if you use HA solution like DRBD, as it basically means this is time for fail-over period, and your system will be down during this time.
Entry posted by Vadim |
11 comments
Add to: | | | |
This is to follow up on my previous post and show the results for MySQL 5.5.8 and Percona Server on the fastest hardware I have in our lab: a Cisco UCS C250 server with 384GB of RAM, powered by a Virident tachIOn 400GB SLC card.
To see different I/O patterns, I used different innodb_buffer_pool_size settings: 13G, 52G, an 144G on a tpcc-mysql workload with 1000W (around 100GB of data). This combination of buffer pool sizes gives us different data/memory ratios (for 13G – an I/O intensive workload, for 52G – half of the data fits into the buffer pool, for 144G – the data all fits into memory). For the cases when the data fits into memory, it is especially important to have big transactional log files, as in these cases the main I/O pressure comes from checkpoint activity, and the smaller the log size, the more I/O per second InnoDB needs to perform.
So let me point out the optimizations I used for Percona Server:
For MySQL 5.5.8, I used:
For both servers I used:
The raw results, config, and script are in our Benchmarks Wiki.
Here are the graphs:
13G innodb_buffer_pool_size:
In this case, both servers show a straight line, and it seems having 8 innodb_buffer_pool_instances was helpful.
52G innodb_buffer_pool_size:
144G innodb_buffer_pool_size:
The final graph shows the difference between different settings of innodb_io_capacity for MySQL 5.5.8.
Small innodb_io_capacity values are really bad, while 20000 allows us to get a more stable line.
In summary, if we take the average NOTPM for the final 30 minutes of the runs (to avoid the warmup stage), we get the following results:
This is actually the first case where I’ve seen NOTPM greater than 100,000 for a tpcc-mysql workload with 1000W.
The main factors that allow us to get a 1.4x improvement in Percona Server are:
We recognize that hardware like the Cisco UCS C250 and the Virident tachIOn card may not be for the mass market yet, but
it is a good choice for if you are looking for high MySQL performance, and we tune Percona Server to get the most from such hardware. Actually, from my benchmarks, I see that the Virident card is not fully loaded, and we may benefit from running two separate instances of MySQL on a single card. This is a topic for another round.
(Edited by: Fred Linhoss)
Entry posted by Vadim |
10 comments
Add to: | | | |
As we can see, MySQL 5.5.8 comes with great improvements and scalability fixes. Adding up all the new features, you have a great release. However, there is one area I want to touch on in this post. At Percona, we consider it important not only to have the best peak performance, but also stable and predictable performance. I refer you to Peter’s post, Performance Optimization and Six Sigma.
In Percona Server (and actually even before that, in percona-patches builds for 5.0), we added adaptive checkpoint algorithms, and later the InnoDB-plugin included an implementation of “adaptive flushing”. This post shows the differences between them and MySQL.
The post also answers the question of whether we are going to have releases of Percona Server/XtraDB based on the MySQL 5.5 code line. The answer: Yes, we are. My benchmarks here are based on Percona Server 5.5.7. (You can get the source code from lp:~percona-dev/percona-server/5.5.7 , but it is very beta quality at the moment.)
For this post, I made tpcc-runs on our Dell PowerEdge R900 box, using RAID10 over 8 disks and a FusionIO 320GB MLC card.
First, the results for tpcc-mysql, 500w (around 50GB of data) on RAID10. I used innodb_buffer_pool_size=24G, innodb_log_file_size=2000M (innodb_log_files_in_group=2), and innodb_flush_log_at_trx_commit=2. Also, innodb_adaptive_flushing (ON) / innodb_adaptive_checkpoint (estimate) were the default values.
The raw results, full config files, and scripts are in our Benchmarks Wiki.
The graphical result below shows the throughput on the server over 8 hours. (Yes, 8 hours, to show MySQL performance over a long time period. It is not a short, 5-minute exercise.)
Although it takes a decent time for the Percona Server results to stabilize, for MySQL 5.5.8 we have regular dips (3 times per hour) from 24900 NOTPM to 17700 NOTPM (dips of around 30%).
Next, the second run on the FusionIO card. There I should say that we were not able to get stable results with the existing adaptive_checkpoint or adaptive_flushing algorithms. So, Yasufumi invested a lot of research time and came up with the new innodb_adaptive_checkpoint=”keep_average” method. This method requires setting innodb_flush_neighbor_pages=0 , to disable flushing of neighborhood pages (not available in MySQL 5.5.8). The problem with flushing neighborhood pages is that it makes an exact calculation of how many pages were handled impossible. The flushing neighborhoods feature was created as an optimization for hard drives, since InnoDB tries to combine writing as many pages as possible into a single sequential write, which means that a single I/O may have a size of 32K, 64K, 96K, …, etc. And again, that makes a prediction of how many I/O operations there are impossible. Furthermore, this optimization is not needed for flash devices, like FusionIO or Virident cards.
An additional optimization we have for SSDs is big log files. For this run, I used innodb_log_file_size=4G (innodb_log_files_in_group=2) for Percona Server. That gave 8GB in total size for log files (MySQL 5.5.8 has a 4GB limit). In additional to increasing log_size we added option innodb_log_block_size which allows to change IO block size for logs files. Default is 512 bytes, in test with FusionIO I use 4096 bytes, to align IO with internal FusionIO size.
You can see that MySQL 5.5.8 has periodic drops here, too. The margin between Percona Server and MySQL is about 2500-2800 NOTPM (~15% difference).
MySQL 5.5.8 now has features related to having several buffer pool instances that are supposed to fix the buffer pool scalability issue. Let’s see how MySQL performance changes for the last workload if we set innodb_buffer_pool_instances=8 or 16.
As you see, having several buffer pools makes the dips deeper and longer. It seems that for Percona Server the best choice is innodb_buffer_pool_instances=1, as we implemented buffer pool scalability in a different way.
UPDATE
By request from commenter I put also results with different innodb_io_capacity for MySQL 5.5.8. It is 500 ( which I used in benchmarks above), 4000 and 20000.
As you see there is no improvements from bigger innodb_io_capacity, and it also concurs with my previous experience, that with bigger io_capacity you rather getting worse results.
For reference, here is the config file used for benchmarks on FusionIO:
(post edited by Fred Linhoss)
Entry posted by Vadim |
23 comments
Add to: | | | |
Last Friday I published results of DBT2 performance while varying the number of idle connections here, but I had compiled MySQL with the debugging code enabled. That completely screw up my results, be aware… debug options have a huge performance impact. So, I recompiled Percona-Server 11.2 without the debug options and did another benchmark run. The result is shown below:
As you can see, the impact is more moderate and far less shocking. The performance loss is approximately of 1% per 1000 of idle connections. Although it is something to keep in mind, there is no big stress with these idle connections.
Entry posted by Yves Trudeau |
5 comments
Add to: | | | |
Be careful with my findings, I appear to have compile in debug mode, I am redoing the benchmarks. Updated version here.
I recently had to work with many customers having large number of connections opened in MySQL and although I told them this was not optimal, I had no solid arguments to present. More than once, I heard: it is not a problem, those connections are Sleeping! In order to remedy to this situation, I decided to run a series of DBT2 benchmarks while the number of idle connections was varied from 0 to 1500. I was expecting an influence, because those idle threads are presents in internal MySQL list objects that need to be scanned and also the socket SELECT call needs to build a FD bitmap after every network activity and the size of the bitmap is proportional to the number of active threads. What I found is not a small impact at all.
For my daily work, I use 2 computers linked with a gigabit switch so I decided to use them for benchmarking. On the desktop, I installed Percona-Server-11.2 which I configured with the following Innodb settings:
The desktop is running Ubuntu 10.04, has 8GB of RAM and a Core i5 CPU (dual core with HT enabled). Since my goal is to test concurrency, I decided to use only one warehouse for DBT2 which easily fits inside the buffer pool. Then, with innodb_flush_log_at_trx_commit=0, I ensured I was not benchmarking my disk.
On the laptop, I ran the following script:
Where 10.2.2.129 is the IP of the Desktop. This script uses a PHP script, make_conn.php, to generate the idle connections. This script is the following:
Before each benchmark, the database is reinitialized to ensure consistent benchmarks. During all the benchmarks, the CPU load on the laptop was never above 10%. I also hacked a bit the “run_mysql.sh” script to allow more processing threads (100) instead of the max of 20. This is required for another series of benchmarks I’ll present soon, this time looking at the number active connections. For these benchmarks, I use pools of 4 connections, basically to match the number of available computing threads the Core i5 allows. I know by experience that this is about the max DBT2 NOTPM for small number of threads.
So, here are the results:
As you can see, the performance drop is shocking. At 1500 idle connections, the performance is 1.3% of the one with 0 idle. Even for as few as 20 idle connections, the drop is already of 40%. I also verified the laptop (running dbt2) was not slowed down by handling all those connections. I tried generating the idle connections from a third box and the results were the exact same. The conclusion is quite straightforward, idle connections hurts performance a lot!!! I am curious as to where MySQL is spending its time, I am planning to use profiling tools to identify the culprit but I had no time yet to do that. I am afraid the same scalability issues affects actives connections although these are also affected by concurrency issues. With new servers having 24+ cores, this phenomenon is seriously affecting performances.
Entry posted by Yves Trudeau |
18 comments
Add to: | | | |
Percona Server version 5.1.53-11.7 is now available for download.
The main purpose of this release is to update the current Percona stable release to the latest version of MySQL 5.1.
Release Notes for this and previous releases can be found in our Wiki.
Downloads are available here and from the Percona release repositories. The latest source code for Percona Server, including the development branch, can be found on Launchpad.
Please report any bugs found at Bugs in Percona Server.
For general questions, use our Percona Discussions Group, and for development questions our Percona Development Group.
For support, commercial, and sponsorship inquiries, contact Percona.
Entry posted by Fred Linhoss |
2 comments
Add to: | | | |
Just yesterday we released Percona Server 5.1.52-12.3 which includes HandlerSocket. This is third-party plugin, developed Inada Naoki, DeNA Co., Ltd and explained in Yoshinori Matsunobu’s blog post.
What is so special about it:
How it can be used:
So give it a try and provide us feedback.
Entry posted by Vadim |
18 comments
Add to: | | | |
Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com