You are here

Feed aggregator

mysql errors on node 2 and 3 but not on node 1

Lastest Forum Posts - April 6, 2015 - 9:33am

I am using XtraDB cluster 5.5 and have a cluster of 3 nodes, with node 1 as the primary node. When I look at my mysql error log, I see the same errors on node 2 and node 3 but not in node 1 (no errors at all). These are duplicate column/can't drop columns errors.

I have an application that uses the the cluster and I believe that users are trying to create/drop tables with the same name more than once, therefore causing the db errors.

I was wondering if anyone knows why these errors are occurring on node 2 and 3 but not on node 1.

Thanks a

The cost of not properly managing your databases

Latest MySQL Performance Blog posts - April 6, 2015 - 3:00am

Every day hundreds of millions of dollars are wasted by allowing improperly tuned or misconfigured systems, misunderstood infrastructure, and inefficient IT operations to live and thrive in data centers around the globe. There are both direct and indirect costs associated with allowing these unhealthy systems to continue to exist. Let’s look at some.

The setup:

Let us start by using a small example. We will start by looking at a small database setup. This setup will have a single master-slave, with a database size of lets say 500GB. Traffic is steady and let’s say this translates into 500 IOPS on the master. You have chosen to host this on Amazon’s AWS. A common way of ensuring backups occur in AWS is to setup ebs snapshots of the slave. In terms of usage, let us assume your CPU is about 50% used and you have about 20GB of hot data that needs to stay in the memory for the database.

If we look at what this would take to support in EC2 you are looking roughly at this:

  • 2 c3.4xlarge servers (16 vcpu, 30GB of memory )
  • Master-Slave Set
  • with 1TB of Provisioned IOPS SSD, over 2 volumes
  • with 500 IOPS on the master, 125 iops on the slave
  • estimated 7TB of storage for snapshots

This calculator gives us an estimated cost of $3,144.28 per month, or roughly $38,000 a year in hosting fees.  Note that you can choose other tiers of service, or reserved or spot servers to get different pricing.

Regular, steady growth:

Now let’s assume your database is growing along with its traffic at about 5% per month (these are rough numbers I know). After a year your database server would be out of steam using 86% CPU, 34GB of hot data (so relying more heavily on disk), and be consuming just about 850GB of storage space. Moving up to the next tier of servers and with additional iops you will see your spend per month jump to around $4,771.32 per month ($57,000 per year).

When tuning and auditing an environment like the above we been able to give some customers up to 50% or more improvement in performance, and often see 20-25% reduction in space. Let’s be conservative and say we can get a 25% boost in your performance, reduce your 5% monthly growth to 4%, and shrink your database by 10%. Based on that you can stave off upgrading your servers an additional 9 months, saving you almost $15,000 in that first year alone. Over 4 years this customer would end up saving an estimated $75,000 in total spend in AWS costs just based on smaller data and performance enhancements.


In this case performance enhancements are not the only place to save costs. Moving from EBS Snapshots to regular MySQL backups using Percona XtraBackup, keeping one copy on disk and sending those backups to s3, the cost of the environment drops to $2,043.87 per month ( from $3144.28).  This means a simple switch of backup methodology can net you about $1,100 a month or $13,200 a year off your hosting bill.

These numbers are are based on only two servers, the saving over dozens or even hundreds of servers can be huge. Take a look at this 10 server environment:

Often we are not only reducing the resources needed, but we can also reduce the number of servers needed to run your application through tuning. We had a recent client who was able to see a 90% reduction in their read heavy workload and actually turn off servers that used to be used to serve their application. Here is what their savings over the next couple of years would look like:

Here we helped cut this customers direct costs by two thirds.

Handling spikes:

 The one thing to keep in mind is this assumes a linear growth in terms of application and database usage.  This would mean you can predict when you will need hardware.  If your user base is growing and feature adds are controlled it is possible, however in most environments you will not see that linear growth. You will probably see something like this:


Understanding this pattern and the spikes are vital to keeping your costs down. See that giant spike up to 2,500? The first reaction for many is upgrade their hardware, then tune. Inevitably any tuning benefit is offset by the already sunk cost of the hardware upgrade which after the tuning they may not have needed. Getting in front of that spike and preventing it could have saved tens or even hundreds of thousands of dollars.

 Spikes kill performance and cost real dollars. Those spikes may not be easy to find. A few years ago I was working with a Fortune 500 client who had one of these spikes. They had been running perfectly fine with steady but controllable growth for 7 or 8 months, then the 9th month things went very wrong very quick.

A critical component of their company was to certify professionals through a testing application. During their peak time of season these certifications had stopped completely – delaying certifications for thousands of employees and clients for 2 weeks. I was flown out to help control the bleeding and hopefully fix the problem.

The number of users using the application was the same, the number of page views on the web was steady, but the number of queries to the database skyrocketed. None of the queries had hit their threshold of 1 second to be flagged as problem queries. It turned out to be one query that took 250ms to execute that was causing this company to grind to a halt. That one query ended up being executed 25,000 times per page when certain conditions were met, and those conditions were not met until the 9th month after this application was re-released.

This query lay like a trojan horse waiting to destroy this company’s ability to deliver to its customers.

 Two lessons can be learned from this. The first is even a seemingly well-tuned system may not be. Second, small things matter. In this case fixing the code is the correct solution, however, proper indexing of the tables dropped the query time from 250ms down to 50ms.  This was enough of a relief to allow the certification process to start up again until the code could be fixed. A seemingly small impacting query still should be optimized.

 Another source for these performance spikes is a company’s application release cycle. Applications are very a living entity in today’s world. They grow and expand and change on a regular basis. In order to stay ahead of any problems you need to have a process and resources in place that can proactively monitor and tune. Every release of new code should be going through a rigorous performance review to prevent trojan problems that may cause problems and extra costs down the road.

Indirect and hard to calculate costs:

All of this discussion so far has been around direct hosting costs. There is also a cost to your reputation and your ability to deliver services that meet a customer’s expectations. Customers who come to your site or are using your application can leave in droves due to poor performance. We have seen several customers who lost 50% or more of their user base due to performance problems with the application.

Lost revenue and profits are often much more difficult to quantify, and vary greatly from company to company. This cost, however, is very real. Silicon Valley is littered with the remnants of companies that did not plan to address scale or simply missed important problems in their IT infrastructure. Unfortunately I have worked directly with numerous companies that learned this lesson the hard way. These hidden costs can kill a customer quicker than any competitor or market shift.

 One of the biggest hidden costs companies needlessly pay is the cost of downtime.

The cost of downtime:

I was reading a gartner study where they estimated that the cost per minute of downtime was $5,600 dollars; other studies, like this one, have pegged the cost per minute of downtime at $7,900.

Anyway you slice it being down for even a minute costs you money. If we are conservative in our estimates, the cost of an hour of downtime can easily top $100,000. It’s amazing the number of well-established companies that don’t have a solid plan for dealing with downtime.

Let’s look at some common disaster recovery policies:

Restore from backup:

How quickly can your DBAs get alerted to an outage, then login to look at the outage, and finally make a call whether or not to restore? I submit that most people are going to take a few minutes to get an alert (let’s say 2). They will then take a few minutes to get to the computer and into the system (let’s say 5 minutes). Then they will take at least 10 minutes to try and figure out what’s going on. Fast-forward 17 minutes later…. minimum has gone by with nothing to show for it.

Restoring the backup itself could take a few minutes or several hours. Let’s just say 40 minutes total. If we use that $7,900 number, you could have just lost $316,000. That’s a huge amount that could have easily been avoided. Maybe you know that you’re not losing $7,900 a minute, maybe it’s only $1,000. That’s still $40,000!

Manual failover to a slave:

The time for getting, reacting and taking action does not change in this equation. The original 17 minutes of time (minimum) to react and start fixing just potentially cost you $134,300.

Automated failover:

Not all automated failovers are created equal. Some solutions can take several minutes to even hours to restore proper service (passive cold slaves warm up time). Just because you think you are protected does not mean you are. Having the right automated solution can mean you minimize your downtime risks to $10K or less, having the wrong one can be worse than having none at all.

It’s important to understand the cost of downtime and pick the proper solution to mitigate it.

Cost of being wrong is high:

These are just a few of the costs that companies can incur by having the improper database and infrastructure setup. Mitigating these costs requires a solid process, a high-level of expertise, and the right resources in place.

The post The cost of not properly managing your databases appeared first on MySQL Performance Blog.

use innobackupex for backup faild, code /usr/bin/innobackupex line 3003,1539,2987

Lastest Forum Posts - April 5, 2015 - 6:35am
Hi Masters

I'm a new guy just begin to study mysql, but recently i met a issue but do know how to fix it, appreciated if somebody could
help me about it, maybe a stupid mistake, but i can find out, pls see below

[root@nemrry ~]# innobackupex --user=root --password=mysql /root/backup/

InnoDB Backup Utility v1.5.1-xtrabackup; Copyright 2003, 2009 Innobase Oy
and Percona LLC and/or its affiliates 2009-2013. All Rights Reserved.

This software is published under
the GNU GENERAL PUBLIC LICENSE Version 2, June 1991.

Get the latest version of Percona XtraBackup, documentation, and help resources:

150405 19:55:23 innobackupex: Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root' (using password: YES).
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3003.
main::mysql_connect('abort_on_error', 1) called at /usr/bin/innobackupex line 1539
innobackupex: Error: Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','ro ot',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /usr/bin/innobackupex line 2987.
[root@nemrry ~]# rpm -qa |grep xtrabackup
[root@nemrry ~]# cat /etc/centos-release
CentOS Linux release 7.0.1406 (Core)

i have already install perl-DBD-MySQL-4.023-5.el7.x86_64, i have searched around, but still note find the solution,
pls help to give some advices

Thanks & best regards

newbie nemrry

pt-online-schema-change filter on data moved to new table

Lastest Forum Posts - April 4, 2015 - 1:50am

The problem is I have a big table and have to delete a substantial part of data from it, say 60%.

It would be nice to do it the way pt-online-schema-change makes DDL changes, i.e. create a new table, and move all the records I want to be left in the table to it, then swap the tables.

The gains are:
1) The data is moved in small chunks automatically, I don't need to care about replication lag;
2) Only 40% of the data is modified (rewritten to a new table) instead of 60%;
3) All the unused disk space goes back to OS, the new table consumes disk space only for those 40% of the records, which were actually moved.

The solution could be to add an option that adds WHERE condition to every chunk of data moved to the new table. So every chunk would be limited by pk id (or any other column if --chunk-index is used) and this arbitrary where condition.

It might seem strange, that this use case of pt-online-schema-change does not suppose a schema change at all, but why not, if the algorithm is almost the same? =)

Illustrating Primary Key models in InnoDB and their impact on disk usage

Latest MySQL Performance Blog posts - April 3, 2015 - 3:00am

On a recent engagement I worked with a customer who makes extensive use of UUID() values for their Primary Key and stores it as char(36), and their row count on this example table has grown to over 1 billion rows.

The table is INSERT-only (no UPDATEs or DELETEs), and the bulk of their retrieval are PK lookups. Lookups by PK were performing acceptably, but they were concerned with the space usage by the table as we were approaching 1TB (running with innodb_file_per_table=1 and Percona Server 5.5).

This schema model presents an increasing burden for backups since they use Percona XtraBackup, and so the question was asked: does their choice of an effectively random Primary Key based on UUID() impact their on-disk storage, and to what extent? And as a neat trick I show towards the end of this post how you can calculate the rate of fragmentation in your table on a regular basis if you’re so inclined. So read on!

For background, the more common approach for a Primary Key in InnoDB is one that uses an integer AUTO_INCREMENT value. One of the benefits of a PK AUTO_INCREMENT is that it allows InnoDB to add new entries at the end of the table, and keeps the BTREE index from having to be split at any point.  More on this splitting reference in a moment.  Note also that this blog post isn’t intended to promote one type of model over another, my goal is really to illustrate the impact your choice of PK will have on the data on disk.

A Primary Key serves multiple purposes in InnoDB:

  • Ensures uniqueness between rows
  • InnoDB saves row data on disk clustered by the Primary Key
  • Depending on the type used and INSERT / UPDATE pattern used, either provides for a unfragmented or severely fragmented Primary Key

I wanted to profile three different Primary Key types:

  1. integer AUTO_INCREMENT – this key will consume 4 bytes
  2. binary(16) using Ordered UUID() – as per Karthik’s UUID()-optimised blog post
  3. char(36) using UUID() – what my customer was used

I then used the powerful tool innodb_space’s function space-lsn-age-illustrate  (from Jeremy Cole’s innodb_ruby project) to plot the LSN (InnoDB’s Log Sequence Number, an always-incrementing value) pages from each table that uses the different Primary Keys via ASCII colour (so hot, right?  Thanks Jeremy!!).  For reference, the legend indicates that the darker the colour, the “older” the page’s updated version is (the LSN), while as you move across the colour spectrum to pink you’re looking at the most recently written LSN values.  What I’m trying to illustrate is that when you use AUTO_INCREMENT or UUID() that has been modified to insert in an ascending order, you get virtually no page splits, and thus consume the minimal amount of database pages.  On the left side you’re looking at the page IDs for this table, and the lower the number of pages consumed, the more efficiently packed the table’s data is within those pages.

This is an example of INSERT-only based on a Primary Key of AUTO_INCREMENT.  Notice how the darker colours are heavy at the earliest pages and lighter as we get to writing out the higher number pages.  Further this table finishes writing somewhere around 700 pages consumed.

Primary Key integer AUTO_INCREMENT

As we look at the optimised-UUID() INSERT pattern we see  that it too has a very evenly distributed pattern with oldest pages at the start (lowest page IDs) and newest written pages at the end of the table.  More pages are consumed however because the Primary Key is wider, so we end somewhere around 1,100 pages consumed.

Ordered UUID()-based Primary Key

Finally we arrive at the UUID() INSERT pattern, and as we expected, the fragmentation is extreme and has caused many page splits — this is the behaviour in InnoDB when a record needs to be written into an existing page (since it falls between two existing values) and InnoDB realises that if this additional value is written that the capacity of the page will be overcommitted, so it then “splits” the page into two pages and writes them both out.  The rash of pink in the image below shows us that UUID() causes significant fragmentation because it is causing pages to be split all throughout the table.  This is deemed “expensive” since the ibd file now is more than 2x greater than the UUID()-optimised method, and about 3x greater than a Primary Key with AUTO_INCREMENT.

UUID() Primary Key

Based on this investigation we determined that the true size of the 1 billion row table was about half the size as reported by Linux when examining the .ibd file.  We happened to have an opportunity to dump and load the table (mysqldump | mysql) and found that on restore the table consumed 450GB of disk — so our estimate was pretty good!

I also wanted to highlight that you can determine for yourself the statistics for data / pages split. As you can see below, the first two PK distributions are very tight, with pages packed up to 90%, however the UUID model leaves you with just slightly higher than 50%.  You can run this against your prepared backups if you use Percona XtraBackup since at least version 2.1 by using the –stats option.

[root@mysql]# xtrabackup --stats --datadir=/data/backups/mysql --target-dir=/data/backups/mysql | grep -A5 test | grep -A5 PRIMARY table: test/t1, index: PRIMARY, space id: 13, root page: 3, zip size: 0 estimated statistics in dictionary: key vals: 8, leaf pages: 99, size pages: 161 real statistics: level 1 pages: pages=1, data=1287 bytes, data/pages=7% leaf pages: recs=60881, pages=99, data=1461144 bytes, data/pages=90% -- table: test/t2_uuid_ordered, index: PRIMARY, space id: 14, root page: 3, zip size: 0 estimated statistics in dictionary: key vals: 8, leaf pages: 147, size pages: 161 real statistics: level 1 pages: pages=1, data=3675 bytes, data/pages=22% leaf pages: recs=60882, pages=147, data=2191752 bytes, data/pages=91% -- table: test/t3_uuid, index: PRIMARY, space id: 15, root page: 3, zip size: 0 estimated statistics in dictionary: key vals: 8, leaf pages: 399, size pages: 483 real statistics: level 2 pages: pages=1, data=92 bytes, data/pages=0% level 1 pages: pages=2, data=18354 bytes, data/pages=56%

Below are the table definitions along with the scripts I used to generate the data for this post.

mysql> show create table t1G *************************** 1. row *************************** Table: t1 Create Table: CREATE TABLE `t1` ( `c1` int(10) unsigned NOT NULL AUTO_INCREMENT, `c2` char(1) NOT NULL DEFAULT 'a', PRIMARY KEY (`c1`), KEY `c2` (`c2`) ) ENGINE=InnoDB AUTO_INCREMENT=363876 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> show create table t2_uuid_orderedG *************************** 1. row *************************** Table: t2_uuid_ordered Create Table: CREATE TABLE `t2_uuid_ordered` ( `pk` binary(16) NOT NULL, `c2` char(1) NOT NULL DEFAULT 'a', PRIMARY KEY (`pk`), KEY `c2` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> show create table t3_uuidG *************************** 1. row *************************** Table: t3_uuid Create Table: CREATE TABLE `t3_uuid` ( `pk` char(36) NOT NULL, `c2` char(1) NOT NULL DEFAULT 'a', PRIMARY KEY (`pk`), KEY `c2` (`c2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 1 row in set (0.00 sec)

[root@mysql]# cat make_rows.sh #!/bin/bash while [ 1 ] ; do mysql -D test -e "insert into t1 (c2) values ('d')" ; mysql -D test -e "insert into t2_uuid_ordered (pk, c2) values (ordered_uuid(uuid()), 'a')" ; mysql -D test -e "insert into t3_uuid (pk, c2) values (uuid(), 'a')" ; done

[root@mysql]# cat space_lsn_age_illustrate.sh #!/bin/bash cd /var/lib/mysql echo "T1 - AUTO_INCREMENT" innodb_space -f test/t1.ibd space-lsn-age-illustrate echo "T2 - UUID() ORDERED" innodb_space -f test/t2_uuid_ordered.ibd space-lsn-age-illustrate echo "T3 - UUID()" innodb_space -f test/t3_uuid.ibd space-lsn-age-illustrate

I hope that this post helps you to better understand the impact of random vs ordered Primary Key selection! Please share with me your thoughts on this post in the comments, thanks for reading!

Note to those attentive readers seeking more information: I plan to write a follow-up post that deals with these same models but from a performance perspective. In this post I tried to be as specific as possible with regards to the disk consumption and fragmentation concerns – I feel it addressed the first part and allude to this mysterious “fragmentation” beast but only teases at what that could mean for query response time… Just sit tight, I’m hopeful to get a tag-along to this one post-PLMCE!

By the way, come see me speak at the Percona Live MySQL Conference and Expo in Santa Clara, CA the week of April 13th – I’ll be delivering 5 talks and moderating one Keynote Panel. I hope to see you there!  If you are at PLMCE, attend one my talks or stop me in the hallway and say “Hi Michael, I read your post, now where’s my beer?” – and I’ll buy you a cold one of your choice

The post Illustrating Primary Key models in InnoDB and their impact on disk usage appeared first on MySQL Performance Blog.

More on (transactional) metadata locks

Latest MySQL Performance Blog posts - April 2, 2015 - 9:18pm

Two years ago Ovais Tariq had explained in details what kinds of problems existed before MySQL introduced metadata locks in 5.5.3 and how these locks help to prevent them. Still, some implications of metadata locking in MySQL remain unclear for both users, DBAs and even software developers that target recent MySQL versions. I’ve decided to include a slide or two into the presentation about InnoDB locks and deadlocks I plan to make (with my colleague Nilnandan Joshi) on April 16 at Percona Live 2015.

I decided to do this as recently I’ve got an issue to work on where it was claimed that the behavior of SELECT blocking TRUNCATE TABLE is wrong, just because transaction isolation level was set to READ COMMITTED and thus there should be no locks set by SELECT and transaction should not even start no matter what the value of autocommit is (it was explicitly set to 0 by smart software).

MySQL manual clearly says:

“To ensure transaction serializability, the server must not permit one session to perform a data definition language (DDL) statement on a table that is used in an uncompleted explicitly or implicitly started transaction in another session. The server achieves this by acquiring metadata locks on tables used within a transaction and deferring release of those locks until the transaction ends. A metadata lock on a table prevents changes to the table’s structure. This locking approach has the implication that a table that is being used by a transaction within one session cannot be used in DDL statements by other sessions until the transaction ends.”

So, the real challenge was to show these metadata locks still set in a transaction that started implicitly, by SELECT immediately following SET autocommit=0 in a session. It was a good chance to check how metadata locks are exposed in MySQL 5.7 via Performance Schema, so I’ve set up a simple test.

First of all, I’ve enabled instrumentation for metadata locks:

[openxs@centos 5.7]$ bin/mysql --no-defaults -uroot -proot mysql: [Warning] Using a password on the command line interface can be insecure. Welcome to the MySQL monitor. Commands end with ; or g. Your MySQL connection id is 4 Server version: 5.7.6-m16 MySQL Community Server (GPL) Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or 'h' for help. Type 'c' to clear the current input statement. mysql> UPDATE performance_schema.setup_consumers SET ENABLED = 'YES' WHERE NAME = 'global_instrumentation'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME = 'wait/lock/metadata/sql/mdl'; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0

Then I’ve set up a simple test based on the details from the issue (I’ve create the InnoDB table, t, and added a row to it before this):

mysql> set session autocommit=0; Query OK, 0 rows affected (0.00 sec) mysql> select @@autocommit, @@tx_isolation; +--------------+----------------+ | @@autocommit | @@tx_isolation | +--------------+----------------+ | 0 | READ-COMMITTED | +--------------+----------------+ 1 row in set (0.00 sec) mysql> select * from t limit 1; +----+------+ | id | val | +----+------+ | 1 | 1 | +----+------+ 1 row in set (0.00 sec)

Now, from another session I tried to TRUNCATE the table before the fist session got a chance to do explicit or implicit COMMIT (In the issue I mentioned software used just had not cared to do this, assuming transaction had not started. It worked with MySQL 5.1 really well that way.)

mysql> truncate table t;

I was not surprised that TRUNCATE hung. Manual clearly says that until transaction is committed we do not release metadata locks. But let’s check them in Performance Schema (from the first session, where we executed SELECT):

mysql> select * from performance_schema.metadata_locksG *************************** 1. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: test OBJECT_NAME: t OBJECT_INSTANCE_BEGIN: 140450128308592 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_parse.cc:5585 OWNER_THREAD_ID: 27 OWNER_EVENT_ID: 17 *************************** 2. row *************************** OBJECT_TYPE: GLOBAL OBJECT_SCHEMA: NULL OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 140450195436144 LOCK_TYPE: INTENTION_EXCLUSIVE LOCK_DURATION: STATEMENT LOCK_STATUS: GRANTED SOURCE: sql_base.cc:5224 OWNER_THREAD_ID: 30 OWNER_EVENT_ID: 8 *************************** 3. row *************************** OBJECT_TYPE: SCHEMA OBJECT_SCHEMA: test OBJECT_NAME: NULL OBJECT_INSTANCE_BEGIN: 140450195434272 LOCK_TYPE: INTENTION_EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_base.cc:5209 OWNER_THREAD_ID: 30 OWNER_EVENT_ID: 8 *************************** 4. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: test OBJECT_NAME: t OBJECT_INSTANCE_BEGIN: 140450195434368 LOCK_TYPE: EXCLUSIVE LOCK_DURATION: TRANSACTION LOCK_STATUS: PENDING SOURCE: sql_parse.cc:5585 OWNER_THREAD_ID: 30 OWNER_EVENT_ID: 8 *************************** 5. row *************************** OBJECT_TYPE: TABLE OBJECT_SCHEMA: performance_schema OBJECT_NAME: metadata_locks OBJECT_INSTANCE_BEGIN: 140450128262384 LOCK_TYPE: SHARED_READ LOCK_DURATION: TRANSACTION LOCK_STATUS: GRANTED SOURCE: sql_parse.cc:5585 OWNER_THREAD_ID: 27 OWNER_EVENT_ID: 18 5 rows in set (0.00 sec)

Note SHARED_READ lock set on table t and EXCLUSIVE lock is pending on the same table t above. TRUNCATE is blocked (as DDL).

Note also locks related  to out SELECT from the metadata_locks table in the output. Yes, access to Performance Schema is also protected with metadata locks!

We can get a nice view of all metadata locks from other sessions, excluding our current one, and check also all we could get about them before MySQL 5.7 (just a thread state in the SHOW PROCESSLIST output):

mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO FROM performance_schema.metadata_locks INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID WHERE PROCESSLIST_ID <> CONNECTION_ID(); +-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------+ | OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | LOCK_TYPE | LOCK_STATUS | THREAD_ID | PROCESSLIST_ID | PROCESSLIST_INFO | +-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------+ | GLOBAL | NULL | NULL | INTENTION_EXCLUSIVE | GRANTED | 30 | 8 | truncate table t | | SCHEMA | test | NULL | INTENTION_EXCLUSIVE | GRANTED | 30 | 8 | truncate table t | | TABLE | test | t | EXCLUSIVE | PENDING | 30 | 8 | truncate table t | +-------------+---------------+-------------+---------------------+-------------+-----------+----------------+------------------+ 3 rows in set (0.00 sec) mysql> show processlist; +----+------+-----------+------+---------+------+---------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+---------------------------------+------------------+ | 5 | root | localhost | test | Query | 0 | starting | show processlist | | 8 | root | localhost | test | Query | 50 | Waiting for table metadata lock | truncate table t | +----+------+-----------+------+---------+------+---------------------------------+------------------+ 2 rows in set (0.00 sec)

As soon as I complete transaction where SELECT was executed, TRUNCATE completes and we see no pending metadata locks:

mysql> commit; Query OK, 0 rows affected (0.00 sec) mysql> SELECT OBJECT_TYPE, OBJECT_SCHEMA, OBJECT_NAME, LOCK_TYPE, LOCK_STATUS, THREAD_ID, PROCESSLIST_ID, PROCESSLIST_INFO FROM performance_schema.metadata_locks INNER JOIN performance_schema.threads ON THREAD_ID = OWNER_THREAD_ID WHERE PROCESSLIST_ID <> CONNECTION_ID(); Empty set (0.01 sec) mysql> select * from t; Empty set (0.00 sec)

To summarize, MySQL 5.7 allows you to study all metadata locks in details. They are set for both transactional and non-transactional tables, but remember that if you use autocommit=0 or start transaction explicitly they are released only when commit happens, implicit or explicit. If you want single statement SELECT to not block any DDL after it is completed, make sure to COMMIT immediately or use autocommit=1.

We can surely call the behavior of metadata locks for this case a “bug” or file a “feature request” to change it, but for now any software that is supposed to work with MySQL 5.5.3+ should just take all implications of metadata locks into account.

The post More on (transactional) metadata locks appeared first on MySQL Performance Blog.

Recover from deleted ibdata1 file

Lastest Forum Posts - April 2, 2015 - 6:17am
is it possible to recover from a deleted ibdata1 after the database has been restarted? I am able to start the database by setting innodb_force_recovery = 1 and display the tables but when I try and run a select or dump on the data I get the following error.
mysqldump: Got error: 1146: Table 'database.table' doesn't exist when using LOCK TABLES
i get the same with skip lock tables as well

The value of MySQL Support

Latest MySQL Performance Blog posts - April 2, 2015 - 6:00am

Years ago when I worked for the MySQL Support organization at the original MySQL AB, we spoke about MySQL Support as insurance and focused on a value proposition similar to that of car insurance. For your car to be fully covered, you must purchase car insurance before the incident happens – in fact most places around the world require automobile insurance. Similarly with many organizations, any production-use technology might be mandated to have its own insurance in the way of 24/7 support.

I think however this is a very one-sided view that does not capture the full value (and ROI) that a MySQL Support contract with Percona provides. Let’s look at the different dimensions of value it provides based on the different support cases we have received throughout the years.

Reduce and Prevent Downtime
If your database goes down, the time to recover will be significantly shorter with a support agreement than without it. Cost of downtime varies widely between organizations. Gartner estimates the average cost of downtime is $5,000 per minute.

With most of our clients, we have found that the cost of preventing or rapidly reducing even one significant downtime event a year more than pays for the cost of support. Even when the client’s in-house team is very experienced, our help is often invaluable as we are exposed to a great variety of incidents from hundreds of companies, so it is much more likely we have encountered the same incident before and have a solution ready. Helping to recover from downtime quickly is a reactive part of support – you can realize even more value by proactively working with support to get advice on your HA options as well as ensure that you’re following the best database backup and security practices.

Better Security
Having a MySQL Support contract by itself is not enough to prevent all security incidents. MySQL will be only one of the components for a possible attack vector and it takes a lot of everyday work to stay secure. There is nothing that can guarantee complete security. MySQL Support, however, can be an invaluable resource for your security team to learn how to apply security and compliance practices to your MySQL environment and how to avoid typical mistakes.

The cost of data breaches can be phenomenal and also impact business reputations much more than downtime or performance issues. Depending on the company size and market, costs will vary. Different studies estimate costs ranging in average from $640K  in direct costs to $3.5M. What everyone seems to agree upon is that security risks and security costs are on the rise and you can’t afford to leave this areas unchecked.

Fix Database Software Bugs
While you might have great DBAs on your team who are comfortable with best practices and downtime recovery, most likely you do not have a development team comfortable with fixing bugs in the database kernel or supporting tools. Being able get software fixes contributes to downtime reduction as well as all kinds of other things, such as ensuring efficient development and operation teams, avoiding using complex workarounds, etc.

Reduce Resources
A large number of questions we get are performance-related which, when addressed, provide a better experience for users, saves costs, and minimizes environmental impact by using less resources.

Savings vary depending on your application scale and how much it is already optimized. In the best cases, our support team has helped customers make applications more than 10x more efficient. In most cases though, we are able to help make things at least 30% more efficient. If you’re spending $100K or more on your database environment, this benefit alone will make a support agreement well worth it.

Efficient Developers
This is important one. Way too often customers do not even give their developers access to support, even though these developers are critical in realizing the the full value of their application. Developers working with databases make many decisions about schema design, query writing, and the use of MySQL features such as stored procedures, triggers or foreign keys. Without a MySQL Support contract, developers often have resort to “Google” to find an answer – and often end up with inapplicable, outdated or simply wrong information. Combined with this, they often apply or resort to time-consuming trial and error.

With help of the Percona Support team, developers can learn the proven practices that apply to their specific situation, save a lot of time and get a better application to the market faster. Even with a single US-based developer intensively working with MySQL, a support agreement might be well worth the cost based on increased developer efficiency alone. Larger development teams simply cannot afford to not have support.

Efficient Operations
Your operations staff (DBAs, DevOps, Sysadmins) are in the same boat – if your database environment is significant, chances are you are always looking for ways to save time, make operations more efficient and reduce mistakes. Our Support team can provide you with specific actionable advice for the challenges you’re experiencing.

Chances are we have seen environments similar to yours and know which software, approaches and practices work well and which do not. This all of course contributes to downtime prevention and reduction, but also helps with team efficiency. With the Percona Support team’s help, you will be able to handle operations with a smaller team or be able to have stuff done with less experienced staff members.

Better Applications
Percona Support access helps developers not only be more productive, but also results in better application quality because best practices in application database interface design, schema, queries, etc. are followed. The Percona team has supported many applications for many years, so we often will think about problems before you might think about them, such as:

  • “How will this design play with replication or sharding?”
  • “Will it scale with large amounts of users or data?”
  • “How flexible is such a design when the  application will inevitably be evolving over years?”

While a better application is hard to quantify, it really is quite important.

Faster Time to Market
Yet another benefit that comes from developers having access to a MySQL Support team is faster time to market. For many agile applications, being able to launch new features faster is even more important than cost savings – this is how businesses can succeed against the competition. At Percona, we love helping businesses succeed.

As you see, there are a lot of ways Percona Support can contribute to the success of your business. Support is much more than “insurance” that you should consider purchasing for compliance reasons. If you’re using MySQL for your applications, Percona Support will provide a great return on investment, allowing you to minimize risks and costs while delivering the highest quality of application or service possible.

The post The value of MySQL Support appeared first on MySQL Performance Blog.


Lastest Forum Posts - April 2, 2015 - 1:45am
I use xtrabackup on a Mariadb 10.0.15 running on Centos 6.7
It seems something changed in innobackupex. Yesterdays backup using the previous version 2.2.9 had no problems.
Any hints on how to solve this?

Thanks, Michiel

I include the log here:

innobackupex: Using server version 10.0.15-MariaDB-log

innobackupex: Created backup directory /var/backups/mysql/incr/2015-04-01_03-21-01/2015-04-02_03-21-01

150402 03:21:01 innobackupex: Starting ibbackup with command: xtrabackup --defaults-group="mysqld" --backup --suspend-at-end --target-dir=/var/backups/mys
ql/incr/2015-04-01_03-21-01/2015-04-02_03-21-01 --innodb_data_file_path="ibdata1:12M:autoextend" --tmpdir=/mydata/data --compress --compress-threads=4 --tab
les='.*[.].*' --extra-lsndir='/mydata/data' --incremental-basedir='/var/backups/mysql/base/2015-04-01_03-21-01' --suspend-at-start --parallel=4
innobackupex: Waiting for ibbackup (pid=29526) to suspend
innobackupex: Suspend file '/var/backups/mysql/incr/2015-04-01_03-21-01/2015-04-02_03-21-01/xtrabackup_suspended_1'

xtrabackup version 2.2.10 based on MySQL server 5.6.22 Linux (x86_64) (revision id: )
incremental backup from 2555712792976 is enabled.
xtrabackup: uses posix_fadvise().
xtrabackup: cd to /mydata/data
xtrabackup: open files limit requested 65535, set to 16384
xtrabackup: using the following InnoDB configuration:
xtrabackup: innodb_data_home_dir = ./
xtrabackup: innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup: innodb_log_group_home_dir = ./
xtrabackup: innodb_log_files_in_group = 2
xtrabackup: innodb_log_file_size = 536870912
xtrabackup: using O_DIRECT
>> log scanned up to (2560735523319)
xtrabackup: Generating a list of tablespaces
xtrabackup: Creating suspend file '/var/backups/mysql/incr/2015-04-01_03-21-01/2015-04-02_03-21-01/xtrabackup_suspended_1' with pid '29526'

150402 03:21:02 innobackupex: Continuing after ibbackup has suspended
DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHANGED_PAGE_BITMAPS' at line 1 at /usr/bin/innobackupex line 3044.
innobackupex: got a fatal error with the following stacktrace: at /usr/bin/innobackupex line 3047
main::mysql_query('HASH(0x24ec2f8)', 'FLUSH NO_WRITE_TO_BINLOG CHANGED_PAGE_BITMAPS') called at /usr/bin/innobackupex line 1970
main::backup() called at /usr/bin/innobackupex line 1601
innobackupex: Error:
Error executing 'FLUSH NO_WRITE_TO_BINLOG CHANGED_PAGE_BITMAPS': DBD::mysql::db do failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'CHANGED_PAGE_BITMAPS' at line 1 at /usr/bin/innobackupex line 3044.
150402 03:21:02 innobackupex: Waiting for ibbackup (pid=29526) to finish

How to avoid this error Lock wait timeout exceeded; try restarting transaction

Lastest Forum Posts - April 1, 2015 - 8:42pm

I am checking innobackupex for backing up the database server but I get error Error `1205, HY000, Lock wait timeout exceeded; try restarting transaction`

How can I avoid it , i tried setting lock-wait-timeout=300 and lock-wait-threshold=50 , still error persists.

How to avoid this error Lock wait timeout exceeded; try restarting transaction

Lastest Forum Posts - April 1, 2015 - 8:30pm

I am checking innobackupex for backing up the database server but I get error Error `1205, HY000, Lock wait timeout exceeded; try restarting transaction`

How can I avoid it , i tried setting lock-wait-timeout=300 and lock-wait-threshold=50 , still error persists.

MySQL shell prompt vs MongoDB shell prompt

Latest MySQL Performance Blog posts - April 1, 2015 - 12:00am

Recently Todd Farmer shared an interesting story about the mysql command line prompt in MySQL 5.7: how it was changed to provide more context and why the change was finally reverted. This made me think that after using the command line client for MongoDB for awhile, I would love seeing a much more modern mysql shell prompt. Here are a few examples of what a modern command line client can do.

Add dynamic information to the prompt

If you use replication with MongoDB, you have probably noticed a nice feature of the prompt: it is replication aware. What I mean is that for a standalone instance, the prompt is simply:


When you configure this instance to be the primary of a replica set named RS, the prompt automatically becomes:


and for secondaries, you will see:


And of course if an election is triggered and roles are switched, the prompt is updated accordingly to reflect the change.

Such a feature may not be easily transposed to MySQL as the allowed replication topologies are more flexible: for instance with master-master replication or for chained replication, some servers may be both a master and a slave.

However if you look a bit deeper how the MongoDB shell prompt can be customized, it is very flexible as you can set the prompt variable to the result of any Javascript function. So for instance if you write this:

var prompt=function() { return db.getName()+"/"+ISODate().toLocaleTimeString()+"> "; }

Your prompt will be changed to something like:


And the change can be persisted by defining the prompt variable in a ~/.mongorc.js file.

If I could do something similar with MySQL, I would for instance be able to know the replication lag of a slave or the uptime of MySQL and so on and so forth. That would be incredibly useful in some situations.

Better help system

Let’s assume I want to manually fail over to a secondary in a MongoDB replica set. I can simply instruct the master that it should no longer be the master. But what is the exact command?

I know that it is rs.xxx because all replica sets functions have this format, so let’s simply open the shell and type rs. and all the available options show up:

> rs. rs.add( rs.constructor rs.propertyIsEnumerable( rs.syncFrom( rs.addArb( rs.debug rs.prototype rs.toLocaleString( rs.apply( rs.freeze( rs.reconfig( rs.toString( rs.bind( rs.hasOwnProperty( rs.remove( rs.valueOf( rs.call( rs.help( rs.slaveOk( rs.conf( rs.initiate( rs.status( rs.config( rs.isMaster( rs.stepDown(

Here again, I’d love to see something similar with MySQL. For instance each time I need to convert a timestamp to a human readable date, I have to look at the documentation to find the exact function name.

Actually I could have used the built-in help system to find the name of the function, but it is much more convenient to look at the documentation.


What’s your opinion on which new features a modern mysql shell should provide? If many people agree on a set of features, it could be worth filing a feature request to get a better mysql command line client in a future version of MySQL.

The post MySQL shell prompt vs MongoDB shell prompt appeared first on MySQL Performance Blog.

Percona XtraBackup 2.2.10 for MySQL hot backups is now available (free!)

Latest MySQL Performance Blog posts - March 31, 2015 - 8:34am

Percona is glad to announce the release of Percona XtraBackup 2.2.10 on March 31, 2015. Downloads are available from our download site or Percona Software Repositories.

Percona XtraBackup enables MySQL backups without blocking user queries, making it ideal for companies with large data sets and mission-critical applications that cannot tolerate long periods of downtime. Offered free as an open source solution, Percona XtraBackup drives down backup costs while providing unique features for MySQL backups.

Bugs Fixed:

  • Decrypting backup with the wrong key would make the backup unusable and unrecoverable. innobackupex doesn’t automatically delete the *.qp and *.xbcrypt files anymore, after --decrypt and --decompress are used. Bug fixed #1413044.
  • XtraDB Changed Page Tracking wasn’t working with innobackupex. Bug fixed #1436793.
  • Fixed Percona XtraBackup assertion caused by dirty pages remaining in the buffer pool after the log was fully applied. Bug fixed #1368846.
  • Backup will not be prepared and innobackupex will stop with an error if the transaction log file is corrupted and it wasn’t applied to the intended LSN. Previously this was showing only as a warning. Bug fixed #1414221.
  • New status log-applied is introduced for backup prepared with --redo-only to avoid making the backup unusable by preparing full or incremental backup without --redo-only and then applying next incremental on top of it. Incremental backup now can be applied only to backup in log-applied state, but not to full-prepared as it was earlier. Bug fixed #1436790.

Release notes with all the bugfixes for Percona XtraBackup 2.2.10 are available in our online documentation. Bugs can be reported on the launchpad bug tracker. Percona XtraBackup is an open source, free MySQL hot backup software that performs non-blocking backups for InnoDB and XtraDB databases.

The post Percona XtraBackup 2.2.10 for MySQL hot backups is now available (free!) appeared first on MySQL Performance Blog.

Error_code: 1677 on Rolling upgrade

Lastest Forum Posts - March 31, 2015 - 1:11am

I have a percona cluster of three servers and i am trying to perform rolling schema upgrade with RSU + node dropping method.
Basicaly what you do is:

1.remove the a server from the cluster,
2.change OSU method to RSU,
3.perform the schema change
4. Add server back to cluster

I did the above method on a small table and it all worked well how ever when i tried on a much bigger table (took like 5 hours to complete) when added the server back to cluster i got the following error:

150331 9:05:09 [Note] WSREP: Receiving IST: 278908 writesets, seqnos 42240-321148
150331 9:05:10 [ERROR] Slave SQL: Column 3 of table 'main_db.allRecords' cannot be converted from type 'varchar(120)' to type 'varchar(100)', Error_code: 1677
150331 9:05:10 [Warning] WSREP: RBR event 2 Write_rows apply warning: 3, 42266
150331 9:05:10 [Warning] WSREP: Failed to apply app buffer: seqno: 42266, status:1
at galera/src/replicator_smm.cpp:apply_wscoll():55
Weird thing is that the change i made was indeed on relevant table on Column 3 from VARCAHR(40) to VARCHAR(100)...
Any ideas?


Percona XtraDB Cluster 5.5.41-25.11 is now available

Latest MySQL Performance Blog posts - March 30, 2015 - 9:22am

Percona is glad to announce the new release of Percona XtraDB Cluster 5.5 on March 30th 2015. Binaries are available from downloads area or from our software repositories.

Based on Percona Server 5.5.41-37.0 including all the bug fixes in it, Galera Replicator 2.11, and on Codership wsrep API 25.11, Percona XtraDB Cluster 5.5.41-25.11 is now the current 5.5 General Availability release. All of Percona‘s software is open-source and free, and all the details of the release can be found in the 5.5.41-25.11 milestone at Launchpad.

Bugs Fixed:

  • XtraBackup SST wouldn’t stop when MySQL was SIGKILLed. This would prevent MySQL to initiate a new transfer as port 4444 was already utilized. Bug fixed #1380697.
  • garbd was returning incorrect return code, ie. when garbd was already started, return code was 0. Bugs fixed #1308103 and #1422863.
  • wsrep_sst_xtrabackup-v2 script was causing innobackupex to print a false positive stack trace into the log. Bug fixed #1407599.
  • MyISAM DDL (CREATE TABLE only) isn’t replicated anymore when wsrep_replicate_myisam is OFF. Note, for older nodes in the cluster, wsrep_replicate_myisam should work since the TOI decision (for MyISAM DDL) is done on origin node. Mixing of non-MyISAM and MyISAM tables in the same DDL statement is not recommended with wsrep_replicate_myisam OFF since if any table in list is MyISAM, the whole DDL statement is not put under TOI (total order isolation). This also doesn’t work if default_storage_engine variable is set to MyISAM (which is not recommended for Percona XtraDB Cluster) and a table is created without the ENGINE option. Bug fixed #1402338.
  • Percona XtraDB Cluster now shows a warning in case additional utilities, like pv which may not affect critical path of SST, are not installed. Bug fixed #1248688.
  • wsrep_causal_reads variable was not honored when declared as global. Bug fixed #1361859.
  • garbd would not work when cluster address was specified without the port. Bug fixed #1365193.
  • garbd was running as root user on Debian. Bug fixed #1392388.
  • Errors in garbd init script stop/start functions have been fixed. Bug fixed #1367956.
  • If mysqld gets killed during the SST it will leave an unclean data directory behind. This would cause Percona XtraDB Cluster to fail when the server would be started next time because the data directory would be corrupted. This was fixed by resuming the startup in case wsrep-recover failed to recover due to corrupted data directory. The old behavior is still achievable through --exit-on-recover-fail command line parameter to mysqld_safe or exit-on-recover-fail under [mysqld_safe] in my.cnf. Bug fixed #1378578.
  • gvwstate.dat file was removed on joiner when XtraBackup SST method was used. Bug fixed #1388059.
  • xtrabackup-v2 SST did not clean the undo log directory. Bug fixed #1394836.
  • stderr of SST/Innobackupex is logged to syslog with appropriate tags if sst-syslog is in [sst] or [mysqld_safe] has syslog in my.cnf. This can be overridden by setting the sst-syslog to -1 in [sst]. Bug fixed #1399134.
  • clustercheck can now check if the node is PRIMARY or not, to allow for synced nodes which go out of PRIMARY not to take any writes/reads. Bug fixed #1403566.
  • Race condition between donor and joiner in Xtrabackup SST Configuration has been fixed. This caused XtraBackup SST to fail when joiner took longer to spawn the second listener for SST. Bug fixed #1405668.
  • SST will now fail early if the xtrabackup_checkpoints file is missing on the joiner side. Bug fixed #1405985.
  • socat utility was not properly terminated after a timeout. Bug fixed #1409710.
  • 10 seconds timeout in Xtrabackup SST Configuration script was not enough for the joiner to delete existing files before it started the socat receiver on systems with big datadir. Bug fixed #1413879.
  • Conflict between enforce_storage_engine and wsrep_replicate_myisam for CREATE TABLE has been fixed. Bug fixed #1435482.
  • SST processes are now spawned with fork/exec instead of posix_spawn to allow for better cleanup of child processes in event of non-graceful termination (SIGKILL or a crash etc.). Bug fixed #1382797.
  • Variable length arrays in WSREP code were causing debug builds to fail. Bug fixed #1409042.
  • Signal handling in mysqld has been fixed for SST processes. Bug fixed #1399175.
  • Inserts to a table with autoincrement primary key could result in duplicate key error if another node joined or dropped from the cluster during the insert processing. Bug fixed #1366997.

Other bugs fixed: #1391634 and #1396757.

Release notes for Percona XtraDB Cluster 5.5.41-25.11 are available in our online documentation along with the installation instructions.

Help us improve our software quality by reporting any bugs you encounter using our bug tracking system. As always, thanks for your continued support of Percona!

Please also note that Percona XtraDB Cluster 5.6 series is the latest General Availability series and current GA release is 5.6.22-25.8.

The post Percona XtraDB Cluster 5.5.41-25.11 is now available appeared first on MySQL Performance Blog.

TokuBD 7.5.6 Insert Performance

Lastest Forum Posts - March 30, 2015 - 6:02am

1. Is this TokuDB insert performance normal or slow?
2. It it possible to increase TokuDB insert performance?

Thank you

: num-threads=4, tokudb_commit_sync = ON, innodb_flush_log_at_trx_commit = 1 * MyISAM 16684 per sec * InnoDB 3379 per sec * TokuDB 1763 per sec num-threads=4, tokudb_commit_sync = OFF, innodb_flush_log_at_trx_commit = 2 * MyISAM 15485 per sec * InnoDB 22085 per sec * TokuDB 14956 per sec --- num-threads=2, tokudb_commit_sync = ON, innodb_flush_log_at_trx_commit = 1 * MyISAM 11848 per sec * InnoDB 2695 per sec * TokuDB 1251 per sec num-threads=2, tokudb_commit_sync = OFF, innodb_flush_log_at_trx_commit = 2 * MyISAM 14377 per sec * InnoDB 12283 per sec * TokuDB 9079 per sec --- num-threads=1, tokudb_commit_sync = ON, innodb_flush_log_at_trx_commit = 1 * MyISAM 6181 per sec * InnoDB 1618 per sec * TokuDB 922 per sec num-threads=1, tokudb_commit_sync = OFF, innodb_flush_log_at_trx_commit = 2 * MyISAM 6507 per sec * InnoDB 6518 per sec * TokuDB 5598 per sec ----------------------------------------------------------------- CPU: AMD A8-5600K (4 core) RAM: 8 Gb HDD: SATA 7200 rpm CentOS 7 x86_64 Percona 5.6.23-rel72.1.el7.x86_64 (tokudb 7.5.6) Sysbench 0.5 (snapshot 20150126) ----------------------------------------------------------------- my.cnf: [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock key_buffer_size = 2048M tokudb_cache_size = 2048M #tokudb_commit_sync = OFF innodb_buffer_pool_size = 2048M #innodb_flush_log_at_trx_commit = 2 [mysqld_safe] thp-setting=never log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid ----------------------------------------------------------------- sysbench.sh: #!/bin/bash function bench { engine=$1 shift sysbench \ --test=/usr/share/doc/sysbench/tests/db/insert.lua \ \ --db-driver=mysql \ --mysql-password= \ --mysql-host=localhost \ --mysql-table-engine=$engine \ --mysql-user=root \ \ --num-threads=4 \ --oltp-table-size=1000000 \ $* } function run { echo "=======================================================" echo " $1" echo "=======================================================" mysql -u root --password= -e "drop database sbtest" 2>/dev/null mysql -u root --password= -e "create database sbtest" 2>/dev/null bench $1 prepare > /dev/null bench $1 --max-time=60 --max-requests=0 run | grep 'requests:' } run 'MyISAM' run 'InnoDB' run 'TokuDB'

Fresh installation on Vagrant fails

Lastest Forum Posts - March 29, 2015 - 11:49am
Hi I did a clean installation on ubuntu64 via vagrant and every time i start it keeps failing. I'm not a pro so I would appreciate some noob friendly guidelines. I followed the guidelines from here https://www.digitalocean.com/communi...-replace-mysql
vagrant@precise64:/$ sudo service mysql stop
* Stopping MySQL (Percona Server) mysqld [ OK ]
vagrant@precise64:/$ sudo service mysql start
* Starting MySQL (Percona Server) database server mysqld [fail]

Has Shape-Shifting Spikes and Teen Angst

Lastest Forum Posts - March 29, 2015 - 8:12am
Cook and Other Tech CEOs Blast Indiana Religious https://www.reddit.com/r/ioposlas/comments/30p4n7/

OPTIMIZE, CHECK or REPAIR TABLE crashes tables and frequently server

Lastest Forum Posts - March 28, 2015 - 5:50am
We are using Percona 5.6.23-72.1 on Centos 7.0.1406 64bit (with TokuDB plugin) and migrated the tablespace from MySQL 5.5.27.

( 1 )

We see frequent table marked as crashed after OPTIMIZE, CHECK, REPAIR command (MyISAM)

Log entry:

2015-03-28 05:03:28 123161 [ERROR] Got an error from thread_id=7373, /mnt/workspace/percona-server-5.6-redhat-binary/label_exp/centos7-64/rpmbuild/BUILD/percona-server-5.6.23-72.1/storage/myisam/ha_myisam.cc:910
2015-03-28 05:03:29 123161 [ERROR] MySQL thread id 7373, OS thread handle 0x7f4f663bd700, query id 653385 192.168.0.*. * Checking table *** multiple tables ***
2015-03-28 05:03:30 123161 [ERROR] /usr/sbin/mysqld: Table '***' is marked as crashed and should be repaired

Fixing the table with REPAIR TABLE works for most cases - in some cases REPAIR TABLE fails and we are using myisamcheck, which works well.

( 2 )

In some cases we don't see the behaviour in (1) , but we see a server crash (only for multiple tables in CHECK TABLE or REPAIR TABLE). Error log:

05:06:41 UTC - mysqld got signal 11 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed,
something is definitely wrong and this may fail.
Please help us make Percona Server better by reporting any
bugs at http://bugs.percona.com/

It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 2641195005 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f4edb468000
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 7f4f23dcad40 thread_stack 0x40000
/usr/sbin/mysqld(_ZNK7handler22ha_statistic_incrementEM17sys tem_status_vary+0xc)[0x59a6ac]
/usr/sbin/mysqld(_Z20open_and_lock_tablesP3THDP10TABLE_LISTb jP19Prelocking_strategy+0xa2)[0x696e52]
/usr/sbin/mysqld(_ZN20Sql_cmd_repair_table7executeEP3THD+0xc 8)[0x8140b8]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x5e 8)[0x6e1618]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3 THDPcj+0xfc8)[0x6e2d78]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f4ede41d010): is an invalid pointer
Connection ID (thread ID): 7367

You may download the Percona Server operations manual by visiting
http://www.percona.com/software/percona-server/. You may find information
in the manual which will help you identify the cause of the crash.
150328 06:06:42 mysqld_safe Transparent huge pages are already set to: never.
150328 06:06:42 mysqld_safe Starting mysqld daemon with databases from /var/lib/mysql
2015-03-28 06:06:43 0 [Warning] The syntax 'pre-4.1 password hash' is deprecated and will be removed in a future release. Please use post-4.1 password hash instead.
2015-03-28 06:06:43 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2015-03-28 06:06:44 124453 [Note] Plugin 'FEDERATED' is disabled.
2015-03-28 06:06:44 124453 [Note] InnoDB: Using atomics to ref count buffer pool pages
2015-03-28 06:06:44 124453 [Note] InnoDB: The InnoDB memory heap is disabled
2015-03-28 06:06:44 124453 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins
2015-03-28 06:06:44 124453 [Note] InnoDB: Memory barrier is not used
2015-03-28 06:06:44 124453 [Note] InnoDB: Compressed tables use zlib 1.2.3
2015-03-28 06:06:44 124453 [Note] InnoDB: Using Linux native AIO
2015-03-28 06:06:44 124453 [Note] InnoDB: Using CPU crc32 instructions
2015-03-28 06:06:44 124453 [Note] InnoDB: Initializing buffer pool, size = 128.0M
2015-03-28 06:06:44 124453 [Note] InnoDB: Completed initialization of buffer pool
2015-03-28 06:06:44 124453 [Note] InnoDB: Highest supported file format is Barracuda.
2015-03-28 06:06:44 124453 [Note] InnoDB: The log sequence numbers 1626265 and 1626265 in ibdata files do not match the log sequence number 1626285 in the ib_logfiles!
2015-03-28 06:06:44 124453 [Note] InnoDB: Database was not shutdown normally!
2015-03-28 06:06:44 124453 [Note] InnoDB: Starting crash recovery.
2015-03-28 06:06:44 124453 [Note] InnoDB: Reading tablespace information from the .ibd files...
2015-03-28 06:06:51 124453 [Note] InnoDB: Restoring possible half-written data pages
2015-03-28 06:06:51 124453 [Note] InnoDB: from the doublewrite buffer...
2015-03-28 06:06:51 124453 [Note] InnoDB: 128 rollback segment(s) are active.
2015-03-28 06:06:51 124453 [Note] InnoDB: Waiting for purge to start
2015-03-28 06:06:51 124453 [Note] InnoDB: Percona XtraDB (http://www.percona.com) 5.6.23-72.1 started; log sequence number 1626285
Sat Mar 28 06:06:51 2015 TokuFT recovery starting in env /var/lib/mysql/
Sat Mar 28 06:06:51 2015 TokuFT recovery scanning backward from 140611
Sat Mar 28 06:06:51 2015 TokuFT recovery bw_end_checkpoint at 140611 timestamp 1427519174964095 xid 140607 (bw_newer)
Sat Mar 28 06:06:51 2015 TokuFT recovery bw_begin_checkpoint at 140607 timestamp 1427519174964044 (bw_between)
Sat Mar 28 06:06:51 2015 TokuFT recovery turning around at begin checkpoint 140607 time 51
Sat Mar 28 06:06:51 2015 TokuFT recovery starts scanning forward to 140611 from 140607 left 4 (fw_between)
Sat Mar 28 06:06:51 2015 TokuFT recovery closing 2 dictionaries
Sat Mar 28 06:06:51 2015 TokuFT recovery making a checkpoint
Sat Mar 28 06:06:51 2015 TokuFT recovery done
2015-03-28 06:06:51 124453 [Note] Recovering after a crash using mysql-bin
2015-03-28 06:06:52 124453 [Note] Starting crash recovery...
2015-03-28 06:06:52 124453 [Note] Crash recovery finished.
2015-03-28 06:06:52 124453 [Note] RSA private key file not found: /var/lib/mysql//private_key.pem. Some authentication plugins will not work.
2015-03-28 06:06:52 124453 [Note] RSA public key file not found: /var/lib/mysql//public_key.pem. Some authentication plugins will not work.
2015-03-28 06:06:52 124453 [Note] Server hostname (bind-address): ''; port: 3306
2015-03-28 06:06:52 124453 [Note] - '' resolves to '';
2015-03-28 06:06:52 124453 [Note] Server socket created on IP: ''.
2015-03-28 06:06:52 124453 [Note] Event Scheduler: Loaded 0 events
2015-03-28 06:06:52 124453 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.6.23-72.1-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Percona Server (GPL), Release 72.1, Revision 0503478

Any suggestions?



‘Woz on your mind?’ Share your questions for Steve Wozniak during his Percona Live keynote!

Latest MySQL Performance Blog posts - March 27, 2015 - 2:34pm

Here’s your chance to get on stage with Woz! Sort of. Apple co-founder and Silicon Valley icon and philanthropist Steve Wozniak will participate in a moderated Q&A on creativity and innovation April 14 during the Percona Live MySQL Conference and Expo in Santa Clara, California.

Woz once said that he never intended to change the world. That was the other Steve, Steve Jobs.

“I didn’t want to start this company,” Woz told the Seattle Times of Apple’s beginnings in a 2006 interview. “My goal wasn’t to make a ton of money. It was to build good computers. I only started the company when I realized I could be an engineer forever.”

What would you ask Woz if given the opportunity?

“Woz, what first sparked your interest in engineering?”
“Hey Woz, how did you come up with the design for the first Apple?”
“Woz, what do you see as the next big thing in personal computers?”
“Hi Woz, what’s the deal with your giant vacuum tube watch?”

Now it’s your turn! Ask a question in the comments below and be sure to include your Twitter handle – or your Facebook page or LinkedIn profile. If we use your question, then your profile and question will be displayed on the giant screen behind Woz on stage as it’s being asked during his big keynote! How cool is that?

Want to be there in person? See Woz speak for just $5! That’s $70 off the regular admission price! Just use the promo code “KEY” at registration under the “Expo Hall and Keynote Pass” selection. Following Woz’s keynote, be sure to stop by the Percona booth, say “hello, Tom,” and I’ll give you a limited-edition Percona t-shirt.

In the meantime, help spread the word! Please share this tweet:

“Woz on your mind?” Tweet @Percona your questions for Apple’s Steve Wozniak who speaks April 14 at #PerconaLive! http://ow.ly/KTmES

Do that, then follow @Percona and I’ll send a DM for your address and will ship a t-shirt right to your door. See you at the conference!

The post ‘Woz on your mind?’ Share your questions for Steve Wozniak during his Percona Live keynote! appeared first on MySQL Performance Blog.


Subscribe to Percona aggregator