…MySQL manual, especially the…row_lock_time_maxblogpercona.commanual Innodb_row_lock_waitsblogpercona.commanual Innodb_rows_deletedblogpercona.commanual Innodb_rows_insertedblogpercona.commanual Innodb_rows_readblogpercona.commanual Innodb_rows…unique_checksblogpercona.commanual updatable_views_with_…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
… slow query log until InnoDB again updates the statistics. But when does InnoDB perform the updates aside from the first opening of… / 16th of the table or 2Billion rows has been modified, whichever comes first. ./row/row0mysql.c:row_update_statistics_if_needed If you or… to update all the tables’ statistics. Luckily, this can be controlled with Percona Server with the variable innodb_stats_auto_update (from MySQL…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… to provide our estimates. If we’re running Innodb with MySQL we can use Innodb_data_reads , Innodb_data_writes, Innodb… ? You can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 (mysqld) S…_SENT: 336986112 BINLOG_BYTES_WRITTEN: 0 ROWS_FETCHED: 485139 ROWS_UPDATED: 0 TABLE_ROWS_READ: 610954 SELECT_COMMANDS: 181243 UPDATE_COMMANDS: 0 OTHER_COMMANDS: 0…
Post: Auditing login attempts in MySQL
December 28, 2012 By 4 Comments
… an example: Enable the log: general_log_file = /var/log/mysql/mysql.log general_log = 1 User correctly authenticated: 121227 8:31…_SENT: 14520 BINLOG_BYTES_WRITTEN: 0 ROWS_FETCHED: 783051 ROWS_UPDATED: 1017714 TABLE_ROWS_READ: 1484751 SELECT_COMMANDS: 14 UPDATE_COMMANDS: 103 OTHER_COMMANDS: 3556…
Post: Edge-case behavior of INSERT...ODKU
October 27, 2012 By 6 Comments
… always going to be considered as being defined first. So, MySQL checks our INSERT, sees that the next auto-inc value… row (which failed) and 1 request to update a row, which succeeded (this explains why, when there’s a row-update, that we have 2 rows affected and not 1. (root@localhost) [test]> show status like ‘handler%’; *** some rows omitted *** +—————————-+——-+ | Variable…
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… 829 Update_rows: table id 17 flags: STMT_END_F BINLOG ‘ L5cLURMBAAAALwAAAAUDAAAAABEAAAAAAAEABHRlc3QABHRlc3QAAwMD/gL+CQY= L5cLURgBAAAAOAAAAD0DAAAAABEAAAAAAAEAA///+AIAAAAAAAAAA2JhcvgCAAAAAAAAAANob3A= ‘/*!*/; ### UPDATE test….
Post: Full Text Search Webinar Questions Followup
August 24, 2012 By 5 Comments
… before declaring the `FTS_DOC_ID` primary key column properly, MySQL crashed with an out of memory fatal error, and the… data to the MySQL database or else periodically batch-insert data that has changed since last time you updated the index. You…-memory RT index type that supports direct row-by-row updates. But you would still have to update the RT index as data changes…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
…) node1 mysql> select * from autoinc; +—+——-+ | i | j | +—+——-+ | 1 | node2 | | 4 | node2 | | 7 | node2 | +—+——-+ 3 rows in set (0.00 sec) node1 mysql> update autoinc set j=”node1″ where i = 1; Query OK, 1 row affected (0.00 sec) Rows… rows in set (0.00 sec) node3 mysql> update autoinc set j=”node3″ where i=1; Query OK, 1 row affected (0.01 sec) Rows…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…_info_update,btr_cur_search_to_nth_level,btr_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row…_info_update,btr_cur_search_to_nth_level,btr_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row…_info_update,btr_cur_search_to_nth_level,btr_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row…
Post: Connecting orphaned .ibd files
May 13, 2011 By 19 Comments
…_FIELDS: 1 TYPE: 3 SPACE: 15 PAGE_NO: 3 *************************** 2. row *************************** TABLE_ID: 13 ID: 16 NAME: idx_actor_last_name… an .ibd file and updates the dictionary in ibdata1. # ./ibdconnect -o /var/lib/mysql/ibdata1 -f /var/lib/mysql/sakila/actor.ibd -d… by some other table. In this case if ibdata was updated MySQL will fail to start with error: InnoDB: Reading tablespace information…
