Tag Info

Hot answers tagged

10

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


7

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


5

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


4

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


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

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

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


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

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.


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


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

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


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

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


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


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

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

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


2

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


1

I was having the exact same problem on an AWS t1.micro that has about .6GB of memory. It would go through the entire setup process then fail on initializing mysqld. As it turns out, that instance did not have enough memory to get percona server 5.6 going. I brought up a faster instance running 3.75GB of memory and it started without issue. If I were to ...


1

Please look at the following view of the InnoDB/XtraDB Architecture Please notice the Percona Server Documentation on This Performance Impact of the Doublewrite Buffer In usual workloads the performance impact is low-5% or so. As a consequence, you should always enable the doublewrite buffer because the strong guarantee against data ...


1

I searched around Google, and I'll admit, it's tough to find any documentation. You can try these links Download Site Clustrix tpcc-mysql Benchmark Full Search within Percona's Website I also recommend reading the README file when you download and install it. Perhaps the README file will give you some guidance on getting proper documentation.


1

Depending on how many rows are in your table, your problem may stem from the id_customer and id_operating_system indexes. Try: DROP INDEX `id_customer` ON `ps_guest`; DROP INDEX `id_operating_system` on `ps_guest`; CREATE INDEX `id_customer` USING BTREE ON `ps_guest` (`id_customer`); CREATE INDEX `id_operating_system` USING BTREE ON `ps_guest` ...


1

You should enable slow-query-log and log-queries-not-using-indexes to in order to manifest what queries are skipping on the indexes. This is just a personal preference, but I usually the --processlist option of mk-query-digest. I wrote a automation script for it long ago : MySQL general query log performance effects based on Percona's video about ...


1

Luckily there is a small fix for this if you do not have the luxury of stopping traffic on the DB. By using --ibbackup xtrabackup_55 the recovery recognizes the pending transactions and handles them accordingly. Our script now looks like this: nice -n 19 innobackupex-1.5.1 --stream=tar ./ --user XXXX --password 'YYYYY' \ --ibbackup xtrabackup_55 | \ ...


1

This is being done in MySQL v5.6.5. You have to enable Performance_Schema that's it. It has a table called events_statements_summary_by_digest Which gives detailed information about query which can be helpful for a DBA/Developer to debug it. mysql> select * from events_statements_summary_by_digest order by count_star desc limit 1 \G ...



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