Percona Server is a fork of MySQL that came into being about 7 years ago. The Percona employees are mostly former MySQL people. They care about InnoDB performance so much that they inject 30,000 lines of code into its version of MySQL to make those enhancements possible. They have been doing this since MySQL 5.0 and are still at it today.
MySQL (eh Oracle [...
Morgan gives a hint in his comment that InnoDB is constantly checking for corrupted pages by doing checksums on the pages it reads. If InnoDB finds a checksum mismatch, it will crash stop the server.
If you want to speed that process up (instead of waiting for InnoDB to read the corrupted page), you can use innochecksum:
Because checksum mismatches will ...
What catches my eye is the Query recorded in the general log
150629 9:17:14 76946 Query UPDATE `api_dictionaryitem` SET `name` = '', `group_id` = 4, `count` = 2248844, `last_occurence` = NULL WHERE `api_dictionaryitem`.`id` = 8
when you got signal 11
09:17:14 UTC - mysqld got signal 11 ;
I would suspect you hit some weird bug when running the table ...
You need to tune your InnoDB settings in the following areas:
Make InnoDB access all your cores
Increase innodb_buffer_pool_size to 12G
Increase innodb_buffer_pool_instances to 2 (First run numactl --hardware to determine the number of Physical CPUs. What every number of CPUs it reports, use that number. I learned this recently in Jeremy Cole's Blog)
According to MySQL 5.0 Certification Study Guide, Page 443,444 Section 30.4:
You can check InnoDB tables by using the CHECK TABLE command or using
a client program to issue the statement for you. However, if an InnoDB
table has problems, you cannot fix it by using REPAIR TABLE because
that statement applies only to MyISAM.
If a table check ...
WARNING: before trying any of these instructions it is strongly recommended to verify that there is a healthy backup of your database in hands, just in case. (thanks to @Nick for the warning)
Try to use the mysqlcheck command. On a terminal:
mysqlcheck -u username -p --databases database1 database2
This command will output a list of all tables and a ...
Since your buffer pool is 6G (6144M), the innodb_log_file_size should be 1536M (25% of 6G)
You have sync_binlog set to 1. This provides the safest ACID compliant setup. It can also slow things down dramatically. You say it seems as though its writing each row 1 at a time to disk. That's the case because each completed DML (...
Unlike InnoDB, TokuDB historically did not automatically compute cardinality statistics. As a user you were required to manually run ANALYZE TABLE in order to calculate these values.
All tables and indices created prior to 5.6.27-76.0 would also not maintain accurate row counts. After 5.6.27-76.0, new tables and indices, and tables that had RECOUNT ROWS ...
As well as RolandoMySQLDBA's answer, my observation is that Percona offers features that
come only with "paid for" stock mySQL
make it more that a "grown up" RDBMS
Percona XtraDB Cluster acts more like a proper HA solution as per the CAP theorem
More knobs and levers and monitoring
Assuming like for like performance, the extra ...
Forget about mysqltuner and check for human advice. This tool tells you general recommendations that may be useless and even hurtful in some cases. Optimize table is probably going to be useless, but it locks your tables for writes. A consultant may save you time and money in the long run.
Swapping should be a no-go for MySQL. Make sure to tune your ...
I hope this links can help you to decide your fine tunning. A little later my answer, sorry about.
I could resolve some problems in 5.5 MySQL version using this information.
Here: you can check how to calculate your table_open_cache
Find current value of open_tables and opened_tables
mysql> show global status like 'open%';
The number of open ...
I think that Percona's pt-online-schema-change might be a better solution for you as it can do an ALTER with very little downtime. It does, however, require adding a TRIGGER to the table.
Runnig DDL on your table will lead you into rebuilding your table, mysql is a row based engine so you will suffer the index rebuild this can be quite expensive depending ...
Here's a recent blog post (2013-07-01) that shows how to use tpcc-mysql and graph results:
This is written by Michael Rikmas, who is a super nice guy, and a long time Percona support engineer.
The solution is to drastically decrease the number of files. I see 3 approaches, depending on your version:
Plan A (the only option for 5.6 and earlier):
Set innodb_file_per_table=OFF, restart your client, then do ALTER TABLE ... ENGINE=InnoDB all but the 1000 largest tables. This will shovel all the non-huge tables into ibdata1.
Plan B (if you have at ...
mysql_upgrade will generate a file informing if it was run on the datadir. If it was run for the specific version you have, the mysql_upgrade_info file would contain the current version string and will prevent a new run of mysql_upgrade unless --force is used. Cat $datadir/mysql_upgrade_info file to know which version was ran on last.
Even if for some ...
When I head the word 'Percona Server', the first thing that jumps at me is : "Does the DB Server have multiple CPUs?" These days, that is taken for granted. What is unfortunate is that most people forget to configure MySQL or Percona Server to summon those multiple CPUs.
MySQL 5.1 with InnoDB 5.1 Plugin, MySQL 5.5 and Percona Server have options to increase ...
Morgan is correct. Unless you are running an extremely fast device with very low access latency, the I/O access will dwarf the checksum time, and turning on fast checksums will make only a negligible difference.
The wording you pasted is very specific:
The original algorithm is checked after the new one, so you can have data pages with old checksums and data pages with new checksums. However in this case, you may experience slow reads from pages having old checksums.
This means that it can still read the old checksum, but from now on in, only Percona Server ...
Your query does not do what you think it does. It results in much more rows than you want to because of the cross join. The only reason you haven't noticed it, is that the excessive number of rows produced are not really inserted in the 3rd table due to duplicate keys which trigger either the INSERT IGNORE or the ON DUPLICATE KEY UPDATE
(And you should ...
A quick 101 on Percona Xtrabackup:
Basically, what the tool does is copying the data live in an inconsistent way, but making sure it gets all the necessary changes that are happening at copy time, so it can revert it to a proper state. In other words, it does an "unclean full copy" and then a "controlled crash recovery" with a custom InnoDB instance. That ...
This kind of sounds like poor flushing behavior, ideally you'd be seeing consistent IO not stretches of 90 and 25.
What is the adaptive flushing method in use (or is it off)? keep_average?
Is flushing neighbor pages enabled? Sometimes this helps, sometimes this really isn't efficient to do. Generally, its good on spinning disks so probably not the issue ...
In the latest Percona XtraDB cluster version it is possible to make WAN segments so that there is more efficient communication between nodes. If you didn't do that, all nodes would try to replicate to all nodes, which is highly inefficient for Galera. With Galera 3/PXC 5.6, you can establish those so that only one node at a time will replicate to the other ...
This is taken from the MySQL documentation:
Changes in MySQL 5.7.1 (2013-04-23, Milestone 11)
If a column is declared as NOT NULL, it is not permitted to insert NULL into the column or update it to NULL. However, this constraint was enforced even if there was a BEFORE INSERT (or BEFORE UPDATE trigger) that set the column to a non-NULL value. ...
There is a known issue when certain checkpointing / flushing configuration results in stall. Here is a quote from Percona 5.5 documentation Improved InnoDB I/O Scalability:
Normally, the checkpoint is done passively at the current oldest page
modification (this is called “fuzzy checkpointing”). When the
checkpoint age nears the maximum checkpoint age (...
The root cause of this issue is Oracle MySQL regression in 5.5.46 / 5.6.27 caused by fix for http://bugs.mysql.com/bug.php?id=76135 and Percona Server will have a fix released ASAP (use https://bugs.launchpad.net/bugs/1519094 for tracking).
Edit: the Oracle bug for this is https://bugs.mysql.com/bug.php?id=79185
Adaptive flushing setting is not at fault, ...
To install Percona TokuBackup:
Run ps_tokudb_admin --enable-backup to add the preload-hotbackup option into [mysqld_safe] section of my.cnf.
$ sudo ps_tokudb_admin --enable-backup
Checking SELinux status...
INFO: SELinux is disabled.
Checking if preload-hotbackup option is already set in config file...
INFO: Option preload-hotbackup is not set in the ...
I wonder what happens if anyone uses InnoDB data created via InnoDB Plugin and then switch to another version of InnoDB. That could create possible page corruption in the eyes of mysqld.
Note what the MySQL Documentation on InnoDB File Format says about this possibility :
In general, a newer version of InnoDB may create a table or index that