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 ...
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 tables ...
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 ...
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 ...
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.
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 ...
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 ...
This is a guess, but please note that the option server-id cannot be activated in a hot way. In my experience, this is a common cause of problems when setting up the replication for the first time, specially because the master accepts the following syntax without complaining:
mysql> SET GLOBAL server_id = 1;
Query OK, 0 rows affected (0.04 sec)
And the ...
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 ...
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. ...
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 ...
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 ...
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 ...
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 ...
Personally, I cringe when I hear about upgrades gone wrong due to human error. Most have success stories when upgrading MySQL in succession (5.0, 5.1, 5.5, 5.6) Some create their own disaster movies by moving datadir, uninstalling old MySQL, installing new MySQL, putting back the datadir and think everything's OK ... NOT !!!
MySQL created ...
I believe Percona already has that option built-in.
Inside /etc/init.d/mysql, somewhere I saw something like
# numactl --interleave=all
When I discovered this, every time I installed Oracle's MySQL, I added that command to /etc/init.d/mysql.
If you want to be sure, just run this:
grep -n "interleave" < /etc/init.d/mysql
and you will see which ...
This turned out to not be a mysql issue at all. The network team recently installed a new security device that will block packets on certain rules. A legitimate database write contained a sequence of characters the device deemed nefarious.
The overall connection handshake for replication was able to make it fine but then it just sat there asking the ...
I seem to have found an answer... at least it works.
After a few hours of trial and error, i saw that the problem seemed to be my transactions in my slow log. I could not find any documentation to support this, so i am still not sure if i might just be using the tool wrong.
I commented all:
Lines from ...
Unless your production database is mission-critical and restore time is of the utmost importance, I would generally suggest that you wait to run the apply-log until you need the backup.
Advantages of running apply-log immediately after taking a backup:
Your backup is ready for use immediately (with --move-back / --copy-back), saving you precious time in ...
The MySQL Documentation suggests it : See Setting Up Replication Using GTIDs
Keep in mind that MySQL won't even start up with it. Why ? From the Documentation:
Step 3: Restart both servers with GTIDs enabled. To enable binary logging with global transaction identifiers, each server must be started with GTID mode, binary logging, slave update logging ...
It appears to be a change in the optimizer. I haven't tracked down the exact setting that caused this yet but the explain on the 5.5 version was showing it using the Primary Key. On 5.6 It was using the secondary char1(8) index.
Adding force index (primary) got it back to it's 3-5 minute count time.
MattU's comment above about caching was enough to help me find a solution. Whilst reading about those settings mentioned, I came across innodb-flush-log-at-trx-commit which was set to 1 but I've now set to 2. There are a couple of tables which we're using like a queue (refactoring this into a different architecture more suited is on our TODO list) and thus ...
From your status lists, here is what I see
96GB Buffer Pool
Buffer is 85.8345% Full (88477548544 / 1030792151040);
2.699% of the whole buffer pool (2782134272 / 103079215104)
3.1445% of the data in the buffer pool (2782134272 / 88477548544);
If there are so little dirty pages, why all the extra write activity ?
First, look at InnoDB's ...
You have to look in my.cnf for the variable innodb_parallel_doublewrite_path
You should also set this
Then, restart mysql
After restarting mysql, you can then delete /test/mysql/xb_doublewrite
What is the purpose of this feature ?
According to Percona Server 5.7 performance improvements
Parallel doublewrite buffer. For ...
shutdown the node while maintaining quorum. so that there will be no downtime for application. manually move those all gcache files to some other location(don't delete them for safer side).Restart the server. You will get new gcache file.