Hot answers tagged

13

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 [...


11

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 ...


8

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) ...


6

OBSERVATION #1 Since your buffer pool is 6G (6144M), the innodb_log_file_size should be 1536M (25% of 6G) OBSERVATION #2 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 (...


5

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 ...


4

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 ...


4

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 ...


4

XtraDB replaces InnoDB (it's compatible and a drop in replacement), so just add ENGINE = InnoDB However, it should be the default in MySQL 5.5.x onwards


4

Here's a recent blog post (2013-07-01) that shows how to use tpcc-mysql and graph results: http://www.mysqlperformanceblog.com/2013/07/01/tpcc-mysql-simple-usage-steps-and-how-to-build-graphs-with-gnuplot/ This is written by Michael Rikmas, who is a super nice guy, and a long time Percona support engineer.


4

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 ...


4

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 ...


3

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 ...


3

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 ...


3

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 Examples: Percona XtraDB Cluster acts more like a proper HA solution as per the CAP theorem Online backups More knobs and levers and monitoring Assuming like for like performance, the ...


3

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 ...


3

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.


3

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 ...


3

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 ...


3

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 ...


3

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. ...


3

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 ...


3

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 ...


2

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 cannot ...


2

I think you're looking for the "show profiles" query. See http://dev.mysql.com/doc/refman/5.5/en/show-profiles.html


2

You need to rebuild the table in order to shrink the ibd file. This command will do it: optimize table abc.test;


2

Symlinking InnoDB is definitely not a good idea for regular maintenance of InnoDB. I wrote posts on why not to do this: Aug 07, 2011 : MySQL InnoDB data file directly on a disk slice (fixed-size)? Mar 12, 2012 : How to set a specific directory location for individual MySQL database? However, if this strictly for the purpose of shrinking a large table, I ...


2

Transaction isolation levels and database locking can make it very difficult to handle the problem you've described. Hopefully ypercube's suggestion does the trick. If it's valid to have multiple (campaign_id, user_id) combinations, just not within seconds of each other, you might want to consider adding a column with the user's web/application session id,...


2

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 line ...


2

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 ...


2

It seems to be fixed (see percona manual): Feature has been removed because the upstream DROP TABLE implementation has been improved.



Only top voted, non community-wiki answers of a minimum length are eligible