Hot answers tagged

30

When a Slave is read-only, it is not 100% shielded from the world. According to MySQL Documentation on read-only This variable is off by default. When it is enabled, the server permits no updates except from users that have the SUPER privilege or (on a slave server) from updates performed by slave threads. In replication setups, it can be useful to ...


18

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


16

rsync is just a brute force byte-by-byte copy of data from place to another. You would have perform multiple rsyncs until the last rsync, which is very quick. Then, you would do a full shutdown of MySQL and perform one more rsync. XtraBackup is a comprehensive tool that behaves like rsync but has directed purpose in life. It can start off copying all InnoDB ...


14

Here is the first thing I thought about Setup 3 DB servers (identical HW/OS configs) each installed with MySQL Percona MariaDB Get a Fourth Server installed with MONyog (eval version lasts 30 days) Register the 3 DB servers in MONyog Use the Performance Metrics Charts in MONyog or Set Up Your Own Charts in MONyog Use SysBench against all 3 DB Servers at ...


9

In this instance, you actually have two choices CHOICE #1 : Percona XtraDB Cluster I am currently evaluating it and I think it is brilliantly designed for MultiMaster writes. It can use mysqldump (default), rsync, and xtrabackup (preferred) for initializing new Cluster node. You have total freedom and power. This may be the greatest cliche of all time but ...


8

Think about it: You are storing data in the database as latin1 You are data is handled internally by mysqld as latin1 If data coming from the OS or from the connection is utf8, how is mysqld going to treat it? Rather than guessing or hoping for the best, you could change the incoming character set behavior. With the exception of information_schema and ...


7

This should be very helpful to you as this is the defacto industry benchmark for databases, TPC-C, http://www.tpc.org/tpcc/spec/tpcc_current.pdf Even if you do not use execute this benchmark, the information contained within the test definition document should provide you with tons of insight into setting up your own benchmark for testing your database ...


7

This looks like you might have hit a bug logged against Percona Server 5.5: Concurrent duplicate inserts can violate a unique key constraint in InnoDB tables. There is no fix and no reproducible test case for this bug yet. It has only been observed in a production environment. The pattern described is: INSERT a value into a column with a unique constraint....


6

but I have difficulties to see the added value compared to Cloud SQL instances with replication and backup. I am not very familiar in scaling heavy load systems. Galera-based clusters like Percona XtraDB Cluster support true active/active multi-master, so failover is seamless because you can actually be writing on any node at any time (tho it's ...


5

For a Wordpress blog it should be fine to set query_cache_type = 1. See, the major problems with the query cache are: It invalidates very easily (any update on some table invalidates all queries related to said table) It has a single mutex on which any incoming or outgoing query must go through. The query cache was fine in the days where machines had one ...


5

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


5

Yes, you can do this. pt-table-checksum --config FILENAME Where FILENAME looks like tables = table1,table2,table3 ignore-tables-regex = ^tmp chunk-time = .2 max-load = Threads_running=10 chunk-size = 100000 user = myuser pass = mypass If --config is not ...


5

Unfortunately this is not documented in the MySQL manual - the page would be this one. I didn't know the answer, but I did manage to debug it from the 5.5 source: It is queries to the server that do not produce SQL queries. i.e. laptop1113-2:~ morgant$ mysql -e "SHOW GLOBAL status like 'Com_empty_query';" +-----------------+-------+ | Variable_name | ...


5

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


5

In order to perform an upgrade with (almost) no downtime, you need to add some extra infrastructure for high availability. The most common way to perform that is with the help of MySQL built-in replication: Setup a slave with a clone of the data of the original master. It is technically possible to setup a master-slave replication within the same node by ...


5

Something close to what you're asking can be done with mysqldump. You can stream its output directly to the slave. # mysqldump -h master -A --master-data --single-transaction --quick | mysql -h slave mysqldump will add CHANGE MASTER TO to the output. However, it doesn't include MASTER_HOST, MASTER_USER and MASTER_PASSWORD. It has to be configured ...


5

Your assumption is incorrect. The hidden clustered index does nothing to help, here, because the server can't use that index to find rows in a way that replication needs. This causes some problems when the master is logging in STATEMENT mode, different problems in ROW mode, and all of the problems, combined, in MIXED mode. Here's the tl;dr on the biggest ...


5

The EAV pattern that is used in Wordpress' meta table leads to queries that need composite indexes. I suggest these three indexes: ****_postmeta (meta_key, meta_value(30), post_id) -- for meta ****_postmeta (meta_key, post_id, meta_value(30)) -- for meta2 ****_posts (post_type, id, post_status) -- for posts Column meta_value ...


5

MySQL like any other RDBMS do not sort results by default. You have to add ORDER BY clause to get an ordered result. Otherwise the order rows are returned is undefined. In some degenerate cases you have a chance to get an ordered result without ORDER BY - e.g. when you have bulk-inserted preordered rows into the newly created table, and if you do not ...


5

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


5

Actually, you can now. Since MySQL 8.0.1, while expire_log_days still works, the canonical way to setup expiration is by changing binlog_expire_logs_seconds, which, as you may guess, has second resolution. The default value since is 8.0.11 is 2592000 (= 30 days), but can be configured as usual on the configuration file to any other period in seconds. If you ...


4

The usual cause for this problem is that pt-table-sync is using CRC32 as a hash algorithm, because it's fast and cheap, but it can also give collisions. "codding" and "gnu" have the same crc32, for example. I recommend trying again with MD5 as the function.


4

Take a look the queries in your question /* Execution time 7.0095062E-5 */ SELECT t.*, tt.* FROM wp_terms AS t INNER JOIN wp_term_taxonomy AS tt ON t.term_id = tt.term_id WHERE tt.taxonomy IN ('category') AND tt.count > 0 ORDER BY t.name ASC /* Execution time 5.3167343E-5 */ SELECT post_id, meta_key, meta_value FROM wp_postmeta WHERE post_id IN (77375,...


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

The chief reason to use --apply-log until the time of restore is to support incremental backups. If you make incremental backups, you would use --apply-log --redo-only each incremental backup, and then just before you restore, you do a final prepare with --apply-log. Otherwise if you don't make incremental backups, there's no reason not to do --apply-log ...


4

You might check your value for the innodb_stats_sample_pages parameter. It controls how many index dives MySQL performs on a table when updating index statistics, which in turn are used to calculate the cost of a candidate join plan. The default value was 8 for the version we were using. We changed it to 128, and observed fewer unexpected join plans.


4

The two queries are not logically equivalent. FROM contacts c LEFT JOIN `dnc_contacts` as B on c.`contact` <> B.`contact` This does not do what you think it does. This expression joins every row in c to every row in b except where contact is equal. 1000 rows in each table means ~999000 rows are being generated and compared. FROM contacts c LEFT ...


4

This is not a hack. It is the intention of them: Use auto_increment_increment and auto_increment_offset http://dev.mysql.com/doc/refman/5.6/en/replication-options-master.html#sysvar_auto_increment_increment


4

You appear to be missing the distinction between "Percona XtraDB" and "Percona XtraDB Cluster," also known as PXC. XtraDB is Percona's compatible drop-in replacement rewrite of the InnoDB storage engine, which is included in Percona Server and MariaDB. Percona XtraDB Cluster and MariaDB Galera Cluster both use XtraDB as their storage engine, but the ...


4

You have not decompressed the Xtrabackup files if they still have the .qp extension. Try using this inside the current folder: sudo innobackeupex --decompress --parallel=4 where 4 stands for the number of threads you would like to use. Also, for clean-up afterwards: sudo find ${DEST_DIR}/ -name "*.qp" -delete


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