Hot answers tagged

32

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


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


7

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


7

With just MySQL/MariaDB, I would employ: High speed ingestion: http://mysql.rjweb.org/doc.php/staging_table Summary tables (to make fetching the data much faster): http://mysql.rjweb.org/doc.php/summarytables I would even consider not storing the raw data; instead summarize the data, then toss it. If this is practical, it avoids most of the questions you ...


7

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


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


6

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

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

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

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

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


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

pt-online-schema-change takes advantage of the consistency nature of relational databases at table level. Let's see what happens when we write to the original table, one of three cases: The chunk has already been copied. No problem, the trigger will overwrite the values of the accessory table within the transaction The chunk has not yet been copied. The ...


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

If there is a need to run OPTIMIZE TABLE in PXC, you could achieve this by denying its recording into binary logs into one of three(3) ways: METHOD #1 SET sql_log_bin = 0; OPTIMIZE TABLE mydb.mytable; SET sql_log_bin = 1; METHOD #2 OPTIMIZE NO_WRITE_TO_BINLOG TABLE mydb.mytable; METHOD #3 OPTIMIZE LOCAL TABLE mydb.mytable; SUGGESTION Run the one of ...


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


4

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


4

What indexes? You have no indexes! So any query will scan the entire table -- all partitions. Once the entire table is bigger than innodb_buffer_pool_size, a table scan will not finish without having to hit the disk. And the next table scan will have reread everything from disk. An index does not need to be kept in memory. It acts just like a table -- ...


4

When Percona Online Schema Change runs, it creates triggers which synchronize the new table with updates that happen to the old table (with the previous table definition). Before you run pt-online-schema change again, you have to delete these triggers. In MySQL you can find the triggers by writing show triggers like '<table name>'\G. And you can ...


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


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