Hot answers tagged percona
9
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 ...
6
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 ...
5
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 ...
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
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 ...
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 ...
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
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
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 ...
2
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 ...
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.
1
Your backup solution for MySQL User Grants is fine.
I have been advocating doing that for years:
Mar 24, 2013 : MySQL export user with semi-colon ";" on the end
Dec 24, 2012 : Backup and restore "mysql" database
Jun 13, 2012 : Fastest way to move a database from one server to another
Apr 12, 2012 : Cannot GRANT privileges as root
Apr ...
1
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 ...
1
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 ...
1
If the table reportingdb.norep_zonebannertmp_bk does not exist on one of the nodes in the Cluster, this could be the source of your problem. Why? TRUNCATE TABLE is DDL. That will close the current transaction PXC has during WriteSet Operations. This cannot be rolled back. I would expect a PXC communication problem at COMMIT time because TRUNCATE TABLE causes ...
1
Luckily there is a small fix for this if you do not have the luxury of stopping traffic on the DB. By using --ibbackup xtrabackup_55 the recovery recognizes the pending transactions and handles them accordingly.
Our script now looks like this:
nice -n 19 innobackupex-1.5.1 --stream=tar ./ --user XXXX --password 'YYYYY' \
--ibbackup xtrabackup_55 | \
...
1
You should enable slow-query-log and log-queries-not-using-indexes to in order to manifest what queries are skipping on the indexes.
This is just a personal preference, but I usually the --processlist option of mk-query-digest. I wrote a automation script for it long ago : MySQL general query log performance effects based on Percona's video about ...
1
Anaylzing binlogs may not give you an accurate picture because binlogs contains queries that were completed and are inserted in the binary logs like a FIFO queue.
What you really need to look for is what does the historgram of actively running queries looks like. In other words, you need to catch the processlist in the act of revealing what queries and what ...
1
As Rolando points out, the CLIENT_STATISTICS table is not a MySQL table.
However, I'm fairly sure it would be mapped to the 'Com_empty_query' status variable:
mysql> SHOW STATUS LIKE '%empty%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Com_empty_query | 0 |
+-----------------+-------+
1 row in set (0.00 ...
Only top voted, non community-wiki answers of a minimum length are eligible
