… last webinar, but did not have time to analyze it in-depth. If you try to do something like this in MySQL…, 0 rows affected (0.22 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected (0…, 0 rows affected (0.36 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> ALTER TABLE test ADD INDEX (col2); Query OK, 0 rows affected, 1…
Post: Follow these basics when migrating to Percona XtraDB Cluster for MySQL
May 1, 2013 By 10 Comments
… (PXC) for MySQL is a hot thing right now and …though if you can leave them behind. Lastly, once that bug above is fixed, and… good if your async master can use ROW based binlog format as well to achieve … whole cluster and your async replication being affected. Be Prepared for some Latency PXC can …
Post: Is Synchronous Replication right for your app?
May 14, 2013 By 18 Comments
… it isn’t always obvious how they will affect your database workload. For example: Transaction commit takes…long as they don’t touch the same row. Row locks are held for modifications until the …sync MySQL replication? It’s actually much worse than Galera. As I illustrated in a blog post last …
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
…: sbtest Last_errno: 0 Killed: 0 # Query_time: 9.031233 Lock_time: 0.000086 Rows_sent: 0 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 0… only actual rows which are found and returned up to the top level MySQL part for processing are counted the Rows_examined remains…: sbtest Last_errno: 0 Killed: 0 # Query_time: 5.391612 Lock_time: 0.000121 Rows_sent: 2 Rows_examined: 10000000 Rows_affected: 0 Rows_read: 2…
Post: Implications of Metadata Locking Changes in MySQL 5.5
February 1, 2013 By 9 Comments
… to MySQL 5.5.3 Metadata Locking behavior prior to MySQL 5.5.3 Prior to MySQL…; Query OK, 0 rows affected (0.00 sec) session1 > start transaction; Query OK, 0 rows affected (0.00 sec) session1 >…last step when its about to make changes to table metadata. Let’s see another interesting side-affect…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (mysqld…99 3.53k 0.99 # Rows affecte 0 0 0 0 0 0 0 # Rows read 5.25M 0 507…. trxID 3BBF3B55 (1/0%), 3BBF3B5A (1/0%)… 1753695 more # Last errno 0 # Users user # Query_time distribution # 1us # 10us # …
Post: How to recover deleted rows from an InnoDB Tablespace
February 20, 2012 By 4 Comments
… table row format from the Information Schema: mysql (information_schema) > SELECT ROW_FORMAT from TABLES WHERE TABLE_SCHEMA=’employees’ AND TABLE_NAME=’salaries’; +————+ | ROW…” 4- Import the rows With the data in our hands the last step is to import it in our database: mysql (employees) > LOAD…, to_date); Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql (employees) > select * from salaries…
Post: MySQL 5.6.10 Optimizer Limitations: Index Condition Pushdown
March 14, 2013 By 2 Comments
…DEFAULT CHARSET=utf8; The table had 22 million rows, with approximately 8 million of them having role_…the contents of the buffer pool did not affect the results. What was happening? Well, in …the MySQL 5.6 release, and get advantage of the latest integrated tools that MySQL provides with its last…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
… flexviews.create(‘demo’, ‘dashboard_customer_sales’, ‘INCREMENTAL’); SET @mvid := LAST_INSERT_ID(); CALL flexviews.add_expr(@mvid,’GROUP’,'customer_… 0 rows affected (42 min 42.14 sec) mysql> call flexviews.enable( -> flexviews.get_id(‘demo’,'dashboard_customer_sales’)); Query OK, 0 rows affected (…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… update last_modified=NOW(); Query OK, 2 rows affected (0.00 sec) (root@localhost) [test]> select * from update_test; +—-+———-+———+———————+ | id | username | host_id | last… always going to be considered as being defined first. So, MySQL checks our INSERT, sees that the next auto-inc value…_id=7, last_modified=NOW(); Query OK, 2 rows affected (0.00 sec) Looks fine, right? 2 rows affected, so obviously, the row that we wanted…
