Apr
26
2012
--

Percona Server 5.1.62-13.3 released!

Percona is glad to announce the release of Percona Server 5.1.62-13.3 on April 25, 2012 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.1.62, including all the bug fixes in it, Percona Server 5.1.62-13.3 is now the current stable release in the 5.1 series. All of Percona‘s software is open-source and free, all the details of the release can be found in the 5.1.62-13.3 milestone at Launchpad.

New option rewrite-db has been added to the mysqlbinlog utility that allows the changing names of the used databases in both Row-Based and Statement-Based replication. This was possible before by using tools like grep, awk and sed but only for SBR, because with RBR database name is encoded within the BINLOG ‘….’ statement.

Release notes for Percona Server 5.1.62-13.3 are available in our online documentation.

Apr
26
2012
--

Percona Server 5.5.22-25.2 released!

Percona is glad to announce the release of Percona Server 5.5.22-25.2 on April 25, 2012 (Downloads are available here and from the Percona Software Repositories).

Based on MySQL 5.5.22, including all the bug fixes in it, Percona Server 5.5.22-25.2 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 can be found in the 5.5.22-25.1 milestone at Launchpad.

Bugs Fixed:

  • While running the test case found that MEMORY engine may return rows in non-deterministic order for equal keys. Bug fixed #892951 (Laurynas Biveinis).

Release notes for Percona Server 5.5.22-25.2 are available in our online documentation.

Apr
25
2012
--

Testing STEC SSD MACH16 200GB SLC

Following my previous benchmark of Samsung 830, today I want to show results for STEC MACH16 SATA card, 200GB size, this card is based on SLC, and regarding STEC website, it is an enterprise grade storage.

For tests I use sysbench fileio, 16KiB block size (to match workload from InnoDB, as this is primary usage for me), and recently I switched to use async IO mode. There are two reasons for that. First, MySQL/InnoDB uses async writes, so this will emulate database load, and second, async mode allows to see maximal possible throughput, it does not show reliable latency though, as it appears there is no a reliable way in the Linux asynchronous IO library to get time metrics for particular IO block.

so my testing command line looks like:

sysbench --test=fileio --file-total-size=${size}G --file-test-mode=rndwr --max-time=18000 --max-requests=0 --num-threads=$numthreads --rand-init=on --file-num=64 --file-io-mode=async --file-extra-flags=direct --file-fsync-freq=0 --file-block-size=16384 --report-interval=10 run

You may see I gather metrics every 10 sec to see how stable the performance is, and it really helps to observe some artifacts, as you will see in following graphs.

Hardware for tests: HP ProLiant DL380 G6, filesystem: ext4, mounted with nobarrier.

The results for random write case (8 async IO threads):

In general it shows stable throughput topping to 148 MiB/sec, but every 20 min, there is small drop to 87 MiB/sec, which I guess is related to internal garbage collector activity.

The results for random read case:

Very stable throughput on line 222 MiB/sec

To understand better what kind of response time we should expect, I ran random read sync IO mode, now for 1-64 threads.

The throughput:

We are getting to the peak throughput at 8 threads.

And response time:

For 8 threads, we may expect 0.62ms response time.

In general I have very good experience with this card, and it seems suitable to work with MySQL. I will publish sysbench oltp benchmarks running MySQL on RAID10 over 4 STEC MACH16 cards.

If you are interested more in SSD and MySQL questions – I will be giving a webinary “MySQL and SSD” on May-9. It will be the same as my talk on Percona Live MySQL Conference 2012, if you did not attend my talk – you are welcome to join the webinar.

Disclaimer: This benchmark is done as part of consulting work for STEC, but this post is totally independent and fully reflects our opinion.


Apr
25
2012
--

Testing Samsung SSD SATA 256GB 830 – not all SSD created equal

I personally like PCIe based Flash, but from a pricing point our customers are looking for cheaper alternatives. SATA SSD is an options. There is many products based on MLC technology, and Intel 320 I would say is the most popular. I do not particularly like its write performance – I wrote about it before, that’s why I am looking for comparable alternatives. Samsung 830 256GB looked like a good product, that’s why I decided to test it.

For tests I use sysbench fileio, 16KiB block size (to match workload from InnoDB, as this is primary usage for me), and recently I switched to use async IO mode. There are two reasons for that. First, MySQL/InnoDB uses async writes, so this will emulate database load, and second, async mode allows to see maximal possible throughput, it does not show reliable latency though, as it appears there is no a reliable way in the Linux asynchronous IO library to get time metrics for particular IO block.

so my testing command line looks like:

sysbench --test=fileio --file-total-size=${size}G --file-test-mode=rndwr --max-time=18000 --max-requests=0 --num-threads=$numthreads --rand-init=on --file-num=64 --file-io-mode=async --file-extra-flags=direct --file-fsync-freq=0 --file-block-size=16384 --report-interval=10 run

You may see I gather metrics every 10 sec to see how stable the performance is, and it really helps to observe some artifacts, as you will see in following graphs.

Hardware for tests: HP ProLiant DL380 G6, filesystem: ext4, mounted with nobarrier.

The results for random write case (8 async IO threads):

It seems that InnoDB is not alone with its flashing problems. You can see there periodical stalls in throughput (0 throughput for 20-30 sec period of time). When there is no drops, the drive keep write throughput on 323 MiB/sec level.

I really thought that these stalls are related, so I was totally surprised them in random reads also.
The results for random read case:

I do not have a good explanation for this. When there is no drop, the drive keeps 375 MiB/sec throughput. I may do a wild guess about drops – the drive periodically cleans an internal cache or something.

To understand better what kind of response time we should expect, I ran random read sync IO mode, now for 1-64 threads.

The throughput:

We are getting to the peak throughput at 16-32 threads.

And response time:

For 16 threads, we may expect 0.96ms response time, which increases to 1.62ms under 32 threads.

The periodic drops that I observe for both random reads and random writes do not allow me to recommend this drive for a database server usage, even in general this drive provides much better throughput than Intel 320 (some results for Intel 320).

If you are interested more in SSD and MySQL questions – I will be giving a webinary “MySQL and SSD” on May-9. It will be the same as my talk on Percona Live MySQL Conference 2012, if you did not attend my talk – you are welcome to join the webinar.


Apr
20
2012
--

Joining many tables in MySQL – optimizer_search_depth

Working on customer case today I ran into interesting problem – query joining about 20 tables (thank you ORM by joining all tables connected with foreign keys just in case) which would take 5 seconds even though in the read less than 1000 rows and doing it completely in memory. The plan optimizer picked was very good one, yet you could notice EXPLAIN itself was taking same 5 seconds, which points to problem with optimizer performance. Note though if you have subqueries these might need to be executed during EXPLAIN phase yet making it unusable to check the optimizer performance.

Solution for this problem was to use set optimizer_search_depth=0, rarely used option which as per manual will chose best value automatically. Making this change I could bring optimization, and full query execution time to less than 50ms. Low values, such as 3,4 provided a bit better performance but I decided against using this as I did not want to risk likehood of execution plans changing for some over queries joining less number of tables.

I was wondering if 0 is automatic selection why do we have value of 62 being default in MySQL 5.5 which can produce very expensive plan selections ? Investigating this further I found the following explanation from Timour Katchaounov in MySQL mailing list archives

I have some recollection that there were few main reasons for the
decision to keep exhaustive search as the default:
- backwards compatibility,
- the hypothesis that most users have joins with few tables,
- it is not clear how far from optimal plans do we get by using a greedy
search.

From the same discussion we can learn how automatic selection works – it picks value of min(number of tables, 7) essentially limiting search depth to no more than 7 at which complexity is reasonable. This makes Timour explanation somewhat conflicting though as if we assume MySQL users do not join lots of tables (less than 7) when using 0 as default value would not impact them.
For people who have more than 7 tables in join I think faster execution plan computation would be more important than backward compatibility.

In MySQL 5.6 things are likely to get even better handling joins of many tables as optimizer heuristics are improved so much higher search depths are feasible now.

Apr
20
2012
--

Benchmarks challenges of XtraDB Cluster

We are running internally a lot of benchmarks on our recently announced Percona XtraDB Cluster, and I am going to publish these results soon.
But before that I wanted to mention that proper benchmark of distributed system comes with a lot of challenges.
I am saying that not to complain, but to make sure, if you are going to benchmark XtraDB Cluster yourself, there is a lot of things to take into account.

And it seems that one component, which was not much important before, now appears as critical peace, which easily can became bottleneck in the benchmarks – this is network.

In case of simple client-server setup, the network is not fully utilized.

But as we start testing a cluster setup, the 1Gb network between client and switch is getting fully utilized by sysbench communication with 3 nodes.

In this setup it does not make sense to increase number of nodes, as we will not be able to load them properly.

The solution would be to increase network capacity or add additional client boxes.

Now take into account that there is an internal network communication between nodes also, and that makes a network tuning as the critical part of a cluster setup. This is not something we paid much attention before.

The main conclusion of this post is that if you are going to benchmark a Percona XtraDB Cluster or just use it under intensive communication workload, pay an attention to network component. It is very easy that a client or a client network becomes bottleneck.


Apr
19
2012
--

PLMCE High Availability Deep Dive slides and Document

The slides and accompanying document of the High availability deep dive tutorial have all been uploaded and can be downloaded from the link below:

http://box.com/perconalive2012

For the slides, you’ll find a PDF and a pptx version, the gdocs animations works somewhat OK with pptx. Also, the VMs will stay available from a at least few months from here:

Virtualbox: http://ubuntuone.com/01mJtfGDc2QbxB9eaOqcWc
Xen and VMWare: http://ubuntuone.com/3nikxumH483slq8rfP73iZ

It has been a pleasure to be part of the tutorial, if you have any question, feel free to contact either Florian or myself.

Regards,

Yves

Written by in: MySQL,Zend Developer |
Apr
19
2012
--

Percona is Speaking At Open Source Data Center Conference in Nuremberg, Germany

Percona has 2 talks lined up at OSDC in Nuremberg, Germany:
- Taking hot backups with XtraBackup by Alexey Kopytov
- Expert Troubleshooting: Resolving MySQL Problems Quickly  by Kenny Gryp

Feel free to come and say Hi!

On April 25 & 26, 2012 the Open Source Data Center Conference will invite experienced administrators and architects for an exchange with international open source experts.

OSDC is about simplifying complex IT infrastructures with Open Source and offers an ideal opportunity to meet with open source professionals and insiders, gather and share expertise and benefit from their comprehensive experience in open source data center solutions. The conference is especially adapted to experienced administrators and architects.

An essential aspect of the OSDC is the opportunity for social networking and community building. Beside the comprehensive conference program, there will be much space to get together, to exchange experiences with the open source community as well as for exciting discussions around open source data center solutions.

With focus on the main topic “Agile Infrastructures”, open source experts like Kenny Gryp (Percona), Mark Burgess (CFEnigine), Kris Buytaert (inuits) or Kristian Köhntopp (booking.com) will cover the entire scope of application of open source software in data centers and huge IT environments.

The bilingual conference program is divided into the three sessions, „Devops and Methods“, „Databases“ and „Scalability and Infrastructure“ and thus provides the latest know-how for the daily practice. Additionally there is the possibility to deepen knowledge in one of the following three in-depth workshops „Open Nebula“, „IPv6“ und „Puppet Configuration Management“, taking place on the day before the conference (24th April 2012).

For more information about Open Source Data Conference 2012, please visit the website: www.netways.de/osdc

Apr
18
2012
--

Percona MySQL Training Schedule for May, June, & July

Over the coming three months, Percona will be holding our highly acclaimed MySQL courses in the following cities:

If you have never attended Percona Training, or want to refine your skills, now is the time to register! All training dates are open for early registration. Register today and ensure your place! With 98% of attendees willing to recommend Percona’s MySQL courses to a friend you know the training is going to be good.

If you do not see a date or location that works for you and/or your organization, we can arrange a private onsite course at your location.

Apr
17
2012
--

Version 1.0 of Percona Monitoring Plugins Released

I’m happy to announce that version 1.0 of Percona Monitoring Plugins is now available. The Percona Monitoring Plugins are high-quality plugins, templates, and add-ons for Nagios and Cacti, so you can add world-class MySQL monitoring to your existing enterprise open-source monitoring products.

Version 1.0 fixes a variety of bugs that users found in version 0.9.0. Here are the usual links: changelog, documentation, downloads, source code, bug reports, mailing list, and of course, you should purchase a support contract.

Powered by WordPress | Theme: Aeros 2.0 by TheBuckmaker.com