…long running SELECT queries to let backup to proceed, but resolving server gridlock one way or another. If you’re just using Innodb…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… InnoDB statistics (such as IO read operations, the number of unique pages the query accessed, the length of time query … as in MySQL. This is very helpful for measurement as otherwise we might not catch some long running ….6% 260 0.0002 1.00 0.00 INSERT poller_output # MISC 0xMISC 0.8137 10.0%…
Post: Avoiding auto-increment holes on InnoDB with INSERT IGNORE
November 29, 2011 By 7 Comments
… to the column. Prior to MySQL 5.1.22 InnoDB used a method to access that… time, so we lose concurrency and performance. The problems are even worse with long running queries like INSERT INTO… KEY `uniqname` (`name`) ) ENGINE=InnoDB; Insert a value using a LEFT OUTER JOIN: insert into foo(name) select…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 12 Comments
…dashboard_customer_sales’, ‘INCREMENTAL’); SET @mvid := LAST_INSERT_ID(); CALL flexviews.add_expr(@mvid… used to prevent wide innodb primary keys on the MV. mysql> select mview$pk …long time to refresh: mysql> call flexviews.refresh( -> flexviews.get_id(‘demo’,'complete_example2′),’BOTH’,NULL); Query…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
…this is naturally an INSERT-only workload. The insertions into the base table…of your database administrator. As long as all of the nodes…SQL, but right now only MySQL storage nodes are supported. Amdahl…InnoDB, Vectorwise and other databases, all at the same time, and transparently to the source query…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…insert_idblogpercona.commanual Last_query_costblogpercona.commanual lc_messagesblogpercona.commanual lc_messages_dirblogpercona.commanual lc_time…
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… sec, process no 3946, OS thread id 1151088992 inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock… size 368, undo log entries 1 MySQL thread id 9697561, query id 188161264 localhost root update insert into child values(2,2) Foreign… of IO submited by Innodb – pages to be flushed via LRU pages – dirty pages which were not accessed long time, flush list – old…
Post: High-Performance Click Analysis with MySQL
December 22, 2008 By 13 Comments
… my next point: Use InnoDB Assuming that you will use the stock MySQL server, InnoDB is usually your best bet…. getting bad data back for a long time. And every time you have some incremental job to update … If you do it on the master with INSERT..SELECT queries, it will propagate to the slaves and…
Post: Innodb undo segment size and transaction isolation
January 12, 2011 By 2 Comments
…long transaction looks: —TRANSACTION 17402D749, ACTIVE 15867 sec, process no 19349, OS thread id 1630148928 MySQL thread id 188790, query…over 4 hours so Innodb could be preventing purge from happening for long time and there … their impact on undo space usage. Inserts only have a small record in …
Post: High Rate insertion with MySQL and Innodb
January 7, 2011 By 16 Comments
…at that time and we could get over 200K of inserts/sec. This time I worked with Innodb tables… …long rows and bunch of indexes will see lower numbers. So what’s the deal ? First MySQL… select queries dramatically. The inserts in this case of course are bulk inserts… using single value inserts you …
