Hot answers tagged

13 votes
Accepted

What is the optimal solution for storing real time time-series in MariaDB / MySQL?

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/...
user avatar
13 votes
Accepted

How to set MySQL binlog retention in hours?

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 ...
user avatar
  • 14k
7 votes
Accepted

Percona MySQL 5.5 Unique key is duplicated

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 ...
user avatar
  • 9,871
7 votes
Accepted

What should I set my table_definition_cache to?

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 ...
user avatar
  • 106
6 votes

Galera cluster into Google cloud platform

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 ...
user avatar
6 votes

MySQL not using PRIMARY KEY to sort by default

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 ...
user avatar
  • 4,154
5 votes

How to migrate from MySQL to MariaDB or Percona with minimum downtime?

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 ...
user avatar
  • 14k
5 votes
Accepted

Is there a way to create a MySQL/Percona Slave without using a dump?

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 ...
user avatar
  • 4,837
5 votes
Accepted

Percona: Backup preparation doesn't work ("cannot open ./xtrabackup_logfile")

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 ...
user avatar
5 votes

MySQL Replication query - Primary key need

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 ...
user avatar
5 votes
Accepted

How to optimize this specific MySQL query which examines 2.84M rows and 29.49k InnoDB distinct pages

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 ...
user avatar
5 votes

How does MySQL manage its memory with regards to indexes?

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 ...
user avatar
4 votes
Accepted

HA - MySQL Master Master replication - Duplicate entry errors

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#...
user avatar
4 votes

How to resolve: Specified key was too long; max key length is 767 bytes

I suspect the difference is a Unicode length issue. As you are not using a single-byte-per-character collation the total length of the fields in your key is 530 character (75+200+255) and not 530 ...
user avatar
4 votes
Accepted

How pt-online-schema-change ensures consistency during data copy

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 ...
user avatar
  • 14k
4 votes
Accepted

Active-active Cluster In MySQL

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 ...
user avatar
4 votes
Accepted

How to run OPTIMIZE TABLE on a 3-node cluster?

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....
user avatar
4 votes

Routines backup in Percona xtrabackup

Procedures, functions are stored in mysql database, triggers in respective database directories. XtraBackup makes a copy of all of that. It however doesn't make a copy of MySQL config file (/etc/my....
user avatar
  • 4,837
4 votes

MySQL can take more than an hour to start

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 ...
user avatar
4 votes
Accepted

Problem with aborted PT-online-schema change command

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-...
user avatar
4 votes

What is the optimal solution for storing real time time-series in MariaDB / MySQL?

There are some big questions there that probably need closer examination than can be achieved here, as there are so many dependencies (realize that you know this!). There are a number of slide sets ...
user avatar
4 votes

How to set MySQL binlog retention in hours?

Percona Server supports max_binlog_files, which used in conjunction with max_binlog_size can keep the number of files (and their aggregate size) within a target range. Note that max_binlog_size is ...
user avatar
3 votes
Accepted

What is the Best way to set Disaster recovery setup for percona Xtra DB Cluster?

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 ...
user avatar
  • 14k
3 votes

Is it necessary to run apply-log immediately after innobackupexec has run?

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 ...
user avatar
  • 14k
3 votes
Accepted

Any way to force "garbage collection" in TokuDB?

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

MySQL slave taking too long on "Queueing master event to the relay log"

If the large table was reload on the Master using LOAD DATA INFILE, then here's what's happening: The relay logs are being used to transport the entire input file. The input file has to be extracted ...
user avatar
3 votes

Should a MySQL replication slave be set to read only?

Should a MySQL replication slave be set to read only? Yes, you'd better run slave servers with R/O modes available. Previously privileged users could modify such a slave's data anyways but later then ...
user avatar
  • 161
3 votes

OOM in mysql-percona

I would suggest that even though your innodb_buffer_pool_size is undoubtedly too large for the available memory (192GB would be the theoretical max "sane" value) your thread-cache-size is likely to be ...
user avatar
3 votes
Accepted

MySQL 5.5 Replication to Galera/Percona XtraDB Cluster

Yes, it is very possible. As the Documentation says, you should have a minimum of three nodes in a Percona XtraDB Cluster. You only need one node out of the three nodes. Make sure all nodes in the ...
user avatar
3 votes

9 node percona xtradb cluster

PXC and Galera Cluster do not write scale very well. I mentioned this in my answer to the old post Transaction speed benchmarks for mySQL v5.6 replication - seems very slow where I said the following: ...
user avatar

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