… prior to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL 5…. You can see that no table open operation was performed and hence no wait. Does the fact that … 0 rows affected (0.00 sec) session1 > select * from t1 where i=1 for update; +—+ | i | +—+ | 1 | +—+ 1 row in set…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…incremental method) can be updated much more efficiently,…mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select…response time. Refresh method performance comparison For demonstration purposes, …
Post: InnoDB's gap locks
March 27, 2012 By 15 Comments
…SELECT. They only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ for read-only SELECT… transactions affecting the concurrency and the performance you can disable them in two different…en/innodb-locks-set.html. Conclusion MySQL uses REPEATABLE READ as the default…
Post: Using GROUP BY WITH ROLLUP for Reporting Performance Optimization
September 17, 2007 By 14 Comments
… can get grouped result set together with total value for the groups: mysql> select grp, count(*) cnt from dt where slack like “a… from single query ? Sure. Here is Way Number Three: mysql> select * from (select grp, count(*) cnt from dt where slack like “a%” group… possible though performance is still far from our goal of real time reporting on 10.000.000+ recorded events. UPDATE: Looking a…
Post: Upgrading MySQL
January 5, 2010 By 24 Comments
…”. For some this is 15 minutes job for others it is many month projects. Why is that ? Performing MySQL …some slaves with this version (or selecting to use version only for new shard and measuring its capacity… is the same. It is possible some update statements worked differently in the new version …
Post: Auditing login attempts in MySQL
December 28, 2012 By 4 Comments
…so it can cause performance degradation and you will…:21 [Warning] Access denied for user ‘root’@'localhost’ (using …information about a particular user: mysql> select * from user_statistics where user=’root’…783051 ROWS_UPDATED: 1017714 TABLE_ROWS_READ: 1484751 SELECT_COMMANDS: 14 UPDATE_COMMANDS…
Post: Percona Live MySQL Conference and Expo 2013 – News from the Committee – Tutorial Selection Complete
December 3, 2012 By 1 Comment
…updates on the committee’s progress in selecting the program for the conference. As many people know, the selections and schedule for…for high performance…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Performance_schema_mutex_classes_lostblogpercona.commanual Performance_schema_mutex_instances_lostblogpercona.commanual Performance_schema_rwlock_classes_lostblogpercona.commanual Performance_schema_rwlock_instances_lostblogpercona.commanual Performance…
Post: Paul McCullagh answers your questions about PBXT
November 20, 2009 By 2 Comments
…performed InnoDB with INSERTS and UPDATES (but not SELECTS). That would make PBXT very interesting for non-SELECT…updated by another user. Now PBXT does acquire locks for 2 reasons: to support SELECT FOR UPDATE…
Post: Test Drive of Solid
September 22, 2006 By 5 Comments
…perform consistent read even if rows are locked or even modified unless you require locking read, in which case SELECT FOR UPDATE or SELECT… 2: SELECT FOR UPDATE: mysql> begin; Query OK, 0 rows affected (0.00 sec) mysql> select * from test2 where id=2 for update; +—-+———+ | id | names | …
