Questions tagged [percona-tools]
A collection of software designed for advanced MySQL/PerconaServer tasks, maintenance, and monitoring.
79 questions
0
votes
0
answers
10
views
Percona pt-query-digest vs. Performance Insights on AWS Aurora 3.x
Several years ago when I was running MySQL on-prem and then EC2, I found Percona pt-query-digest to be very useful to find poorly performing SQL in prod. It's been a while since I've used it. AWS ...
- 143
0
votes
0
answers
15
views
Percona pg_tde extension base backup failing
I am just asking here so that someone from Percona can check if possible.
I have followed below documentation to configure pg_tde in RHEL 8 with PG version 16.
https://percona.github.io/pg_tde/main/
...
- 143
0
votes
0
answers
26
views
Percona xtrabackup and percona toolkit for aarch64 Graviton Amazon Linux 2 machines
In my organisation, it was decided to move all the MySQL infrastructure (Amazon EC2 machines) to Graviton (aarch64) servers from AMD (x86_64).
All are MySQL Community Servers.
I need percona-...
- 226
0
votes
0
answers
55
views
Percona XtraBackup questions about full and incremental backups
I had a few questions on the Percona forum, and I'm unsure if I was being clear in my questions or not, however I'm curious how full and incremental backups work with XtraBackup.
When doing ...
- 1
10
votes
1
answer
2k
views
How to add index to a big table with 60M records without downtime?
we have been struggling with one issue in the past few days. We want to add an index to a huge table with 60M records. At first we tried adding it with basic mysql syntax. But it clogged our ...
- 53
4
votes
3
answers
644
views
failed pt-online-schema-change left behind triggers. How to delete?
We had a failed pt-osc - the server ran out of disk space. And now the triggers are left behind
SHOW TRIGGERS;
pt_osc_xxx_production_orders_ins
pt_osc_xxx_production_orders_upd
...
- 153
0
votes
1
answer
486
views
pt-online-schema-change drop_swap doesn't work, so what to do?
After waiting 24 hours for a ptosc this:
2023-05-29T11:29:40 Copied rows OK.
2023-05-29T11:29:40 Max rows for the rebuild_constraints method: 2710
Determining the method to update foreign keys...
2023-...
- 153
1
vote
1
answer
881
views
Run pt-online-schema-change with multiple ALTER queries synchronously
I want to run 3 ALTER queries with pt-online-schema-change tool:
--alter "ADD INDEX userid_sid_ts_fid (user_id, scorecard_id, timestamp, factor_id), DROP INDEX uidts, RENAME INDEX ...
- 113
0
votes
1
answer
67
views
Can one use a view as a source on pt-table-sync?
Any attempt to fill a table from a view results in an error, as the source-table does not exist:
Error getting table structure for TABLE_NAME on SOURCE_DSN_DATA doesnt handle CREATE TABLE without ...
- 177
0
votes
1
answer
387
views
Drop column with percona does not shrink table size
I have a table in Aurora MySQL 5.7. table has few partitions with 800m rows and weights 2tb.
Recently I dropped few column using percona. Surprisingly the table size did not change (looking in ...
- 533
-1
votes
1
answer
962
views
InnoDB: Assertion failure on executing select Query - MySQL 5.7.31
I am using pt-archiver for daily archiving of tables, but while selecting data from one tables I am getting following error and it restart mysql instance
2021-07-07 13:21:17 0x7fe0dffdc700 InnoDB: ...
- 149
1
vote
1
answer
253
views
Incremental encrypted per database backup and Partial restore policy for MySQL 5.7 in replication setup
I will run a replication cluster using MySQL 5.7 and I would like to set up for each database a backup policy that has a full once per day + incremental every N hours with data encrypted (based on the ...
0
votes
1
answer
763
views
Stuck pt-archiver query during purge
I am purging a table of size 1.2T using pt-archiver. In the show processlist i can see the query stuck for 650 secs in Sending data state. The query is quick when I execute independely. Any help will ...
- 97
2
votes
3
answers
4k
views
Can I disable binlogs to save space temporarily
I am using percona mysql 8.
I do not use any type of replication, but I read that binlogs are useful for data recovery. I would like to turn of binlogging and flush them while I run pt-online-schema-...
- 163
0
votes
1
answer
757
views
Downgrade MySQL 8.0.20 to 8.0.19 on Ubuntu for using XtraBackup
With MySQL 8.0.20 currently installed on Ubuntu, I am unable to use Percona XtraBackup 2.3.7 due to breaking changes introduced in MySQL 8.0.20.
How can we downgrade MySQL 8.0.20 to 8.0.19 on Ubuntu, ...
- 143
3
votes
2
answers
1k
views
Prometheus High Memory and CPU Usage in PMM
We are running PMM v1.17.0 and prometheus is causing huge cpu and mem usage (200% CPU and 100% RAM), and pmm went down because of this. We are running PMM on a VM with 2vCPUs and 7.5G RAM, and are ...
- 43
1
vote
2
answers
514
views
Copying MySQL table to another table with no missing new changes
I would like to recreate/copy a MySQL table (a actual new ibd file) - and most of the suggestions recommends the following :
CREATE TABLE mytbcopy LIKE mytb;
INSERT INTO mytbcopy SELECT * FROM mytb; ...
0
votes
1
answer
508
views
MariaDB throws error with documented 'alter user' ... 'password expire' option
Normally I use mysql-community on RHEL7, but today I installed MariaDB 10.3.20 on Ubuntu 19. In selectively cloning a database from the RHEL-mysql cluster to the Ubuntu-MariaDB cluster, I used Percona'...
- 654
0
votes
1
answer
1k
views
pt-table-sync: syncing slave from master that has different credentials?
I have a MySQL slave under my full control and a master server where I have only read-only login. Also, logins are different. At some point replication have stopped due to some kind of problem.
I ...
- 133
2
votes
1
answer
468
views
Is it safe to use pt-online-schema-change in a multimaster environment?
I have 2 MySQL servers with row-based replication between them. Both of them are masters and slaves for each other (active-active master-master setup).
If I understand it correctly pt-osc creates ...
- 121
0
votes
1
answer
3k
views
MariaDB defragmenting with new option innodb-defragment=1
I try to defragment table using innodb-defragment=1
https://mariadb.com/kb/en/library/defragmenting-innodb-tablespaces/
But it was a bad experience :
- locks on table
- no a good defragmentation ...
- 103
0
votes
1
answer
572
views
Percona Xtrabackup fails to backup InnoDB table with fulltext index
I'm trying to make a backup of a large InnoDB table with a fulltext index using Percona Xtrabackup (2.4.12).
I have innodb_file_per_table set.
To create the backup, I'm running:
innobackupex --...
- 172
2
votes
3
answers
490
views
Why pt-duplicate-key-checker suggests removing a composite index?
Here's a snippet from the output of the Percona Tool pt-duplicate-key-checker which searches for redundant indexes:
# Key myidx ends with a prefix of the clustered index
# Key definitions:
# KEY `...
- 1,334
1
vote
1
answer
573
views
how to setup percona pmm monitoring of percona 5.7 in docker container
I want to monitor percona running in docker with the percona monitoring and management client.
I have percona monitoring and management installed, running and monitoring other percona instances (...
- 111
2
votes
1
answer
2k
views
xb_stream_read_chunk(): wrong chunk magic at offset 0x0
I have taken a backup of mysql instance like this
innobackupex \
--user=$MYUSER \
--password=$MYPASS \
--no-timestamp \
--parallel=$DUMP_THREADS \
--stream=xbstream \
--slave-info \
...
- 121
0
votes
0
answers
767
views
pt-table-sync usage
There're Master (10.0.0.1, mysql 5.5.43) and only one Slave (10.0.0.2, mysql 5.5.55). Replication is set up, running and show all OK.
But pt-table-checksum found data differences in some tables ...
- 223
2
votes
1
answer
2k
views
Problem with aborted PT-online-schema change command
I aborted a pt-online-schema change command to change a table definition. Now, when I run pt-online-schema change again, I get this error:
The table <db name>.<table name> has triggers. ...
- 163
0
votes
1
answer
225
views
Help preparing for MySQL Master Slave Replication
I am not in the best situation. I inherited an Ubuntu 14.04 8 GB RAM, 8 CPU MySQL 5.5 database server with almost 400 GB of business-critical data (stored on external SSD) contained within several ...
- 33
0
votes
1
answer
921
views
Data drift on Row-Based-Replication
i've realized that one of my mysql slaves has missing rows.
select count on specific table on master:
9605010
on slave:
9593831
slave is completely sync,
no seconds_behind_master,
no errors,
no slow ...
- 63
1
vote
1
answer
302
views
PT-Kill not logging to file
Run command for pt-kill
/usr/bin/pt-kill --ignore-state User lock --rds --match-command Query --victims all --match-user phppoint --daemonize --busy-time 10 --kill --print --log /var/log/pt-kill.log ...
- 701
2
votes
1
answer
1k
views
pt-table-checksum and mysql 5.7 with binlog_format=ROW
How to use pt-table-checksum in mysql 5.7 where binlog_format=ROW is default
I also need binlog_format=ROW to be set this way in relation to other parameters in the mysql configuration
# pt-table-...
- 157
-1
votes
1
answer
307
views
innobackupex and creating slave
There's master mysql running 5.6.30. Engine InnoDB. innodb_file_per_table is used. Slave server is running 5.5.55
Doing next steps:
- (on master) innobackupex --no-timestamp /path/to/backup
- (on ...
- 223
0
votes
1
answer
3k
views
Pt-query-digest date ranges do not work as expected
Analyse Queries with Pt-query-digest
I am using pt-query-digest to analyse queries and would like to analyse a specific time range only, in order to measure the effect of adding new indices and ...
- 231
0
votes
1
answer
242
views
pt-online-schema-change's triggers fail with "DELETE command denied" on insert
We recently tried pt-online-schema-change to add a column to a table. It worked mostly as expected but one thing puzzles me: in case the account we use to do the migration doesn't have the DELETE ...
1
vote
2
answers
243
views
MYSQL DATABASE BACKUP
Currently my backup strategy invloves :-
1)I have one master server and two slaves (Data approximately 700 GBs and tables are of mixed innodb and myisam).
2)I take weekly and monthly backup from ...
- 47
2
votes
0
answers
479
views
why pt-table-checksum lock my whole table?
mysql> desc user_donate_request;
+-------------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------------------+--------...
- 321
6
votes
2
answers
1k
views
COMMIT on top of the pt-query-digest output
I've been optimizing my MySQL server and after tweaking a few queries, I ended up with the COMMIT item on top of the pt-query-digest output, as shown in the extract below:
# 322s user time, 770ms ...
- 81
2
votes
2
answers
334
views
pt-table-checksum not finding differences
I have a very simple setup: 1 master with 2 slaves. I stopped replication on one of the slaves (with the slave mysqld still running) and changed a row on the master. I have also kept all slaves and ...
- 23
5
votes
1
answer
5k
views
Percona: Backup preparation doesn't work ("cannot open ./xtrabackup_logfile")
I've created a MySQL backup with PerconaXtrabackup tool via (thanks to this guide):
innobackupex --user=backupmysqluser --password=xyz --compress --stream=xbstream ./ > /var/backups/db/firstBackup....
- 159
0
votes
1
answer
2k
views
pt-online-schema-change -- can't get it to stop quitting w/ "Threads_running=108 exceeds its critical threshold 100"
I've got an InnoDB table over 50GB with indexes. The query I'm running looks like this:
pt-online-schema-change h=localhost,D=db,t=table --critical-load="Threads_running=100" --check-slave-lag "h=XX....
- 203
2
votes
1
answer
2k
views
Is there a non-blocking operation to back up MyISAM tables using Percona XtraBackup?
I've been reading through the website of Percona XtraBackup and it claims that:
Percona XtraBackup can back up the following storage engines by briefly pausing writes at the end of the backup: ...
- 23
1
vote
2
answers
236
views
Running pt-table-checksum with replicate-do-db option on
I've read the documentation on percona page, but still a bit confused, especially about the --replicate option.
My setting is that the slave is only replicating certain databases from master, say db1 ...
- 13
2
votes
1
answer
136
views
Alter a large mysql table 700 gb
The production table I want to alter is around 700 Gb. I want to remove a FK from that table. Mysql version is 5.1.61-log.
I found following options:
pt-online-schema-change: alters a table’s ...
0
votes
1
answer
179
views
Changing Default Values of Percona Audit Log
Have I missed Something here? I am installing the Percona Audit Log Plugin (MySQL 5.5).
I can install it fine using:
INSTALL PLUGIN audit_log SONAME 'audit_log.so';
I can then see all the default ...
- 2,186
1
vote
1
answer
2k
views
Innobackupex: How to extract individual files from xbstream compressed backups
We are scripting a mix of full and incremental backup setup for our prodcut backend with sunday triggering full backup and incremental backup on all the remaining days.
In this process, we should be ...
- 814
4
votes
2
answers
3k
views
Is it possible to backup stored routines and functions with Innobackupex?
I am running a few restore tests on my Dev and I've noticed that the stored routines are not being restored with the innobackupx. Is that something achievable ? Am I doing something wrong ?
I am only ...
- 145
1
vote
0
answers
282
views
pt-online-schema-change with statement based replication in MYSQL
Is it possible/safe to use pt-online-schema-change in a Master slave environment where as the way replication is setup is statement based(SBR).
What is the better way of doing it as currently I have ...
- 367
2
votes
3
answers
849
views
"No database selected" or "Table 'DB.table' doesn't exist" failures in pt-upgrade run where queries from multiple DBs were collected with tcpdump
I collected queries on our Master using the following tcpdump command:
tcpdump -i any -s 65535 -x -n -nn -q -tttt 'port 3306' > tcpdump.tcp
The queries are executed amongst several databases.
I ...
- 359
4
votes
2
answers
2k
views
How pt-online-schema-change ensures consistency during data copy
I have read pt-online-schema-change documentation and understood that it works by creating triggers and copying data in chunks with in chunk-time seconds(0.5 seconds in default)
Let us consider this ...
- 814
7
votes
1
answer
8k
views
How much space is needed to reclaim space from a table in INNODB
I inherited a MySQL InnoDB table that has approximately 200 GB worth of data, as well as several unneeded indices that amount to approximately 500 GB. I've archived approximately 75 percent of the ...
- 173