… in innobackupex). He also included this helpful script to generate all of those statements for steps 7 and 10 in two scripts: mysql -N -B <<'EOF' > discard-ddl.sql SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` DISCARD… TABLE_SCHEMA=’orig’ AND ENGINE=’InnoDB’; EOF mysql -N -B <<'EOF' > import-ddl.sql SELECT CONCAT(‘ALTER TABLE `’, table_name, ‘` IMPORT TABLESPACE…
Post: Replication in MySQL 5.6: GTIDs benefits and limitations - Part 2
May 30, 2013 By 6 Comments
… s4 are also lagging behind). s2> select count(*) from t; +———-+ | count(*) | +———-+ | 2 | +———-+ # s1 is behind s1> select count(*) from t; +———-+ | count(*) | +———-+ | 0…; mysql> change master to master_port=10002; mysql> start slave; # And then check the number of records from the t table s1> select… mysqlbinlog? There is a solution of course, but very poor in my opinion: look for the binlog file/position of the…
Post: MySQL Query Patterns, Optimized - Webinar questions followup
June 3, 2013 By 2 Comments
… just running the query in a suboptimal form. Q: Doesn’t the primary key solution for random selection only work when the… was more efficient in this case to force MySQL to scan the `title` table first, grouping by kind_id in index order. This… the Percona Live MySQL Conference and Expo in Santa Clara, California starting March 31, 2014. Watch more webinars from Percona in the future!
Post: Choosing a MySQL HA Solution - MySQL Webinar: June 5
May 28, 2013 By 1 Comment
Selecting the most appropriate solution for a MySQL HA infrastructure is as much a business and philosophical decision … a webinar entitled, Choosing a MySQL HA Solution, in which we’ll explore the topic of MySQL HA from each of these perspectives… deployment. This webinar will be both technical and non-technical in nature, beginning with a discussion of some general HA principles…
Comment: INSERT INTO ... SELECT Performance with Innodb tables.
May 22, 2013 By 22 Comments
…mathew: Wrong, it’s still the same in 5.5. Here is the related output from show engine … InnoDB 334 mysql tables in use 4, locked 4 3264 lock struct(s), heap size 440760, 799203 row lock(s) MySQL thread id 38, OS thread handle 0x2b3c1c776940, query id 16838101 localhost root Copying to tmp table create table t as SELECT…
Comment: Experiences with the McAfee MySQL Audit Plugin
May 23, 2013 By 4 Comments
Can different filters be combined? For example, all the select queries in mysql.user from a particular user.
Post: Webinar: SQL Query Patterns, Optimized
May 20, 2013 By Leave a Comment
… query objective, and show how you can use MySQL 5.6 built-in methods to analyze them for optimal query efficiency. The… measure performance. The query patterns will include: Exclusion Join Random Selection Greatest-Per-Group Dynamic Pivot Relational Division Please register for…
Post: Rotating MySQL slow logs safely
April 18, 2013 By 7 Comments
… that I’ll be at the Percona Live MySQL Conference and Expo next week in Santa Clara, California so look for me…: /var/mysql/slow_query.log { nocompress create 660 mysql mysql size 1G dateext missingok notifempty sharedscripts postrotate /usr/local/bin/mysql -e ‘select @@global.long_query_time into @lqt_save; set global long_query_time=2000; select sleep(2); FLUSH LOGS; select sleep…
Post: MySQL 5.6 - InnoDB Memcached Plugin as a caching layer
March 29, 2013 By 10 Comments
… object built from several queries) Infrequently changing data (configurations, etc) In pseudo-code, here is the basic approach: data = fetchCache(key… ms/op) MySQL Select showed 27% increase over InnoDB fetch (~.09 ms/op) This replaced $cache->load() with $db->query(“SELECT * FROM memcached…
Post: Percona XtraDB Cluster 5.5.30-23.7.4 for MySQL now available
April 17, 2013 By 2 Comments
… fixed #1104977 (Raghavendra D Prabhu). Clustercheck script would hang in case the MySQL server on a node is hung. As a consequence… would fail if the tmpdir was specified more than once in the MySQL configuration file (my.cnf). Bugs fixed #1160047 and #1086978… D Prabhu). In case CREATE TABLE AS SELECT statement was running in parallel with the DDL statement on the selected table, in some cases first…
