Tag Info

Hot answers tagged

11

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


8

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

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


7

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


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


5

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


4

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


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


4

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


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

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


4

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


3

it seems the binlog doesn't include the new inserts I'm not sure whether you're saying the binlog actually doesn't include them, and you have confirmed this with mysqlbinlog, or that it "seems" like it doesn't, because they don't replicate. PXC needs log_slave_updates turned on at the node serving as master to the asynchronous slave, otherwise, not ...


3

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


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

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


3

Taking a backup from a slave is exactly how I do it, and it works very well. I can't imagine a better way to take a backup without causing downtime of the master and your application. I am intrigued by your statement about not being able to restore a backup onto a mac osx machine (this could probably be a different question). An alternative is to setup a ...


3

Big Percona fan here. I'm really excited about a lot of 5.6 features (although many seem to be things Percona already provided). At the same time there's no way i'm rolling out an alpha into production and no way I'm going back to stock and loosing thing I get from percona 5.5 Largely I suppose those "things" are related to information schema (which is ...


3

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.


3

solved by upgrading server from 5.5.13 to 5.5.20


3

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


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

Have you simply tried "mysql> show processlist;"? TokuDB displays the progress of optimize operations in plain view.


2

You should be able to shell script such automation I posted a shell script I use to automate mk-table-checksum and mk-table-sync : Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working (Dec 5, 2011) The script does the following: accepts the IP addresses of Master and Slave as command-line parameters retrieve the list ...


2

I think you want wsrep_sst_method=skip not wsrep_sst_mode=skip mysql> show variables like 'wsrep_sst%'; +---------------------------+------------+ | Variable_name | Value | +---------------------------+------------+ | wsrep_sst_auth | | | wsrep_sst_donor | | | wsrep_sst_method | ...


2

Percona xtrabackup documentation indicates that you should point xtrabackup_55 to the backup-my.cnf file taken with your backup, rather than your live my.cnf file. If you're doing the prep on the same server as the backup was taken, I'm not sure if it will make a difference.


2

I recently ran into this benchmark results: https://blog.mariadb.org/benchmarking-mariadb-5-3-4/ The conclusion here was that MySQL-5.5 is the mos performing among others. The tested editions here are : MariaDB-5.3.4 – the Monty Program release candidate, both with XtraDB and the InnoDB plugin Percona-Server 5.1.61 because it is based on the same XtraDB ...


2

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


2

First of all: This is not because of Percona, the differences you are getting are because of the upgrade to 5.6. Percona Server rarely modifies the SQL optimiser from upstream. The changes come from the new optimiser in MySQL 5.6, which Percona Server 5.6 uses and that was heavily optimised between the two version (usually, for the best). The reason why the ...


2

The binlog doesn't contain different structures when the storage engine is XtraDB vs InnoDB. In the sense of interoperability, XtraDB is not an "extension" of InnoDB -- it's a fully-compatible replacement, that handles some internal operations differently but is still very much "InnoDB" wherever it is exposed outside the server core. For what it's worth, ...



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