15
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/...
- 76.1k
15
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 ...
- 14.7k
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 ...
- 124
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 ...
- 61
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 ...
- 4,258
5
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....
- 181k
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
...
- 4,947
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 ...
- 66
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 ...
- 22.5k
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 ...
- 96.8k
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 ...
- 76.1k
5
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 ...
- 321
4
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 ...
- 171
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 ...
- 22.5k
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....
- 4,947
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 ...
- 76.1k
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-...
- 163
4
votes
Accepted
Percona - Unable to start mysql after restart
Yes Rick is right. Because all of nodes are down you have to bootstrap Cluster again.
You stopped all nodes gracefully.
If you know What is the last one (node) stopped, choice this node too ...
- 617
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 ...
- 22.5k
4
votes
Accepted
what is MDL? (e.g. 'MDL semantics' or 'MDL locks') Probably Galera related
It stands for "MetaData Locking" seeing the sources of MySQL https://dev.mysql.com/doc/dev/mysql-server/8.0.0/mdl_8h.html
It's a way to keep an ACID database by avoiding concurrent access to the same ...
- 195
4
votes
How to avoid replication lag in case all writes on master and reads on replica?
Replication always has lag, if only a very small amount, because your data changes are not even written to the binary log until you commit a transaction. Then the replica has to download those events ...
- 13k
3
votes
MySQL Slave database size >> Master Database
From the details, it looks like the 'massive' difference is ~70MB in the dataplay schema. You can adjust your query to find out exactly which tables are different.
Two potential reasons for this ...
- 23.3k
3
votes
Scaling for MariaDB / Percona / Galera Scaling
Rolando may be right, or I may be right; it depends on the situation...
A single Galera cluster (PXC or MariaDB or hand-rolled) can have more than 3 nodes, but probably should not have a lot more ...
- 76.1k
3
votes
Accepted
How to recover performance_schema after restoring from partial innobackupex backup?
The solution requires running mysql_upgrade on the server that has been restored. As per the documentation on https://dev.mysql.com/doc/refman/5.5/en/performance-schema-build-configuration.html
If ...
- 83
3
votes
Accepted
Of the multiple cnf files in /etc/mysql/conf.d/ dir, which would be the last one to be read (and will eventually take precedence)?
If I understand it correctly, there can be only one config file. But mine ends with
#
# * IMPORTANT: Additional settings that can override those from this file!
# The files must end with '.cnf', ...
- 5,070
3
votes
Accepted
Split brain scenario - RAC and PXC
I can see why Oracle RAC can handle split brain and PXC cannot.
What separates them in their architecture and data storage. In what ways ???
Oracle RAC
All RAC Instances deal with only one set of ...
- 181k
3
votes
Between optimizing a 1-billion rows table and dump-restoring it, which would be faster?
OPTIMIZE is probably better because what it does is
CREATE TABLE ...
Copy all the existing rows over
RENAME ...
(Some time during or after step 2, the indexes are rebuilt.)
Dump and reload:
Read ...
- 76.1k
3
votes
How to make TokuDB engine on Percona delete faster
If your table has 1 or more secondary indexes than each delete requires a read on the primary key index to get the fields necessary for the deletes in each secondary, which eliminates much of the ...
- 739
3
votes
Accepted
How password can be prompted in percona xtrabackup
I donot think there is --ask-pass in xtrabackup! You might want to write a wrapper shell script which will
echo enter mysql password
read -s pw
and then pass it as --password=$pw.
- 1,922
3
votes
Accepted
MySQL Backups - Full vs incremental - innobackupex
No, you don't need to prep every backup. You only need to create the full backup on Sunday and then create the incrementals from Monday to Saturday.
Full:
innobackupex --user=USER --password=...
- 692
Only top scored, non community-wiki answers of a minimum length are eligible