…How can I recover deleted rows? We’re going to follow the same example as in my previous post so we need to…to remove the innodb_table_monitor. C) Check…will connect to the database in order to examine the…to_date); Query OK, 3 rows affected (0.01 sec) Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 mysql…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…” or “Com_select” to get amount of IO per query or per Select. It is good to check it over certain…. How to get CPU consumption per query ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql#… 28us 35us # Rows sent 7.15M 0 56.36k 4.19 0.99 360.95 0.99 # Rows examine 33.77M…
Post: How FLUSH TABLES WITH READ LOCK works with Innodb Tables
March 23, 2012 By 20 Comments
…how it can look: mysql> show processlist; +——-+——+———–+———-+———+——+————————-+—————————–+———–+—————+———–+ | Id | User | Host | db | Command | Time | State | Info | Rows_sent | Rows_examined | Rows…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…rows affected (7.01 sec) The second one is 365x faster because it examines only the rows that changed. This exceptional ability to…how you can combine both types of views together. The complete method examples show how to…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… it for example to 300000 which is 0.3s! # Query_time: 0.503016 Lock_time: 0.000048 Rows_sent: 56 Rows_examined: 1113 LOGGING OF THE REPLICATED STATEMENTS Normally MySQL will not write into slow log… InnoDB_IO_r_wait you can see how much time (in seconds) it took InnoDB to actually read the data from storage…
Post: Profiling MySQL stored routines
January 19, 2009 By 4 Comments
…0.000695 Lock_time: 0.000284 Rows_sent: 1 Rows_examined: 0 Rows_affected: 0 Rows_read: 1 # QC_…you how this works: mysql> set profiling=1; Query OK, 0 rows affected (0.00 sec) mysql> SELECT whatstheweatherlike(…will ask my colleagues to check if it would be hard to implement configurable variable for…
Post: Why audit logging with triggers in MySQL is bad for replication
September 29, 2008 By 10 Comments
…databases mydb –sleep 1 He could then examine the checksum.checksum table and … -lt binary – Check out http://hackmysql.com/mysqlsla_filters for how to filter by …table: Foo Event_Manipulation: UPDATE *************************** 2. row *************************** trigger_name: Foo_Delete event_…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
…interest anyone to periodically check what data…to some newly purchased hardware. But how to…or which examine a lot of rows. …mysql> SHOW TABLE_STATISTICS LIKE ‘hosting2%’; +——————————–+———–+————–+————————-+ | Table | Rows_read | Rows_changed | Rows…
Post: Find and remove duplicate indexes
June 20, 2012 By 9 Comments
…MySQL needs to examine more query plans. The storage engine needs to…to explain the different types of duplicate indexes and how to…Checking the schemas periodically to catch this duplicates can help us to…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…row examined + 1 ticket per row deleted ALTER: (2 * rows in the table) – 1 As with any performance optimization effort, you will want to…to cause the 900-row SELECT statements to be subject to 899 concurrency checks. This means 899 potential opportunities to…
