This post is a step-by-step guide to set up Percona XtraDB Cluster (PXC) in a virtualized test sandbox. I used Amazon EC2 micro instances, but the content here is applicable for any kind of virtualization technology (for example VirtualBox). The goal is to give step by step instructions, so the setup process is understandable and [...]
A case for MariaDB’s Hash Joins
May 31, 2012 By Leave a Comment
MariaDB 5.3/5.5 has introduced a new join type “Hash Joins” which is an implementation of a Classic Block-based Hash Join Algorithm. In this post we will see what the Hash Join is, how it works and for what types of queries would it be the right choice. I will show the results of executing benchmarks [...]
Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5
March 21, 2012 By 10 Comments
This is the 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 Now let’s take a look at [...]
MySQL performance on EC2/EBS versus RDS
August 4, 2011 By 11 Comments
A while ago I started a series of posts showing benchmark results on Amazon EC2 servers with RAID’ed EBS volumes and MySQL, versus RDS machines. For reasons that won’t add anything to this discussion, I got sidetracked, and then time passed, and I no longer think it’s a good idea to publish those blog posts [...]
Should we give a MySQL Query Cache a second chance ?
April 10, 2011 By 64 Comments
Over last few years I’ve been suggesting more people to disable Query Cache than to enable it. It can cause contention problems as well as stalls and due to coarse invalidation is not as efficient as it could be. These are however mostly due to neglect Query Cache received over almost 10 years, with very [...]
InnoDB Flushing: a lot of memory and slow disk
March 31, 2011 By 57 Comments
You may have seen in the last couple of weekly news posts that Baron mentioned we are working on a new adaptive flushing algorithm in InnoDB. In fact, we already have three such algorithms in Percona Server (reflex, estimate, keep_average). Why do we need one more? Okay, first let me start by showing the current [...]
Is VoltDB really as scalable as they claim?
February 28, 2011 By 3 Comments
Before I begin, a disclaimer. VoltDB is not a customer, and did not pay Percona or me to investigate VoltDB’s scalability or publish this blog post. More disclaimers at the end. Short version: VoltDB is very scalable; it should scale to 120 partitions, 39 servers, and 1.6 million complex transactions per second at over 300 [...]
How well does your table fits in innodb buffer pool ?
December 9, 2010 By 12 Comments
Understanding how well your tables and indexes fit to buffer pool are often very helpful to understand why some queries are IO bound and others not – it may be because the tables and indexes they are accessing are not in cache, for example being washed away by other queries. MySQL Server does not provide [...]
Moving from MyISAM to Innodb or XtraDB. Basics
November 22, 2010 By 2 Comments
I do not know if it is because we’re hosting a free webinar on migrating MyISAM to Innodb or some other reason but recently I see a lot of questions about migration from MyISAM to Innodb. Webinar will cover the process in a lot more details though I would like to go over basics in [...]
How well do your tables fit in buffer pool
March 26, 2010 By 4 Comments
In XtraDB we have the table INNODB_BUFFER_POOL_PAGES_INDEX which shows which pages belong to which indexes in which tables. Using thing information and standard TABLES table we can see how well different tables fit in buffer pool.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 | SELECT d.*, ROUND(100 * cnt * 16384 / ( data_length + index_length ), 2) fit FROM (SELECT schema_name, table_name, COUNT(*) cnt, SUM(dirty), SUM(hashed) FROM INNODB_BUFFER_POOL_PAGES_INDEX GROUP BY schema_name, table_name ORDER BY cnt DESC LIMIT 20) d JOIN TABLES ON ( TABLES.table_schema = d.schema_name AND TABLES.table_name = d.table_name ); +-------------+---------------------+---------+------------+-------------+--------+ | schema_name | table_name | cnt | sum(dirty) | sum(hashed) | fit | +-------------+---------------------+---------+------------+-------------+--------+ | db | table1 | 1699133 | 13296 | 385841 | 87.49 | | db | table2 | 1173272 | 17399 | 11099 | 98.42 | | db | table3 | 916641 | 7849 | 15316 | 94.77 | | db | table4 | 86999 | 1555 | 75554 | 87.42 | | db | table5 | 32701 | 7997 | 30082 | 91.61 | | db | table6 | 31990 | 4495 | 25681 | 102.97 | | db | table7 | 1 | 0 | 0 | 100.00 | +-------------+---------------------+---------+------------+-------------+--------+ 7 rows in set (26.45 sec) |
You can also see in one of the cases the value shown is a bit over 100% – [...]
