… feature provides better concurrency under heavy write load but needs additional precautions to avoid … only occur if you do UPDATE or DELETE or SELECT FOR UPDATE. InnoDB provides REPEATABLE READ… 1 MySQL thread id 3, OS thread handle 0x7f84a78ba700, query id 163 localhost msandbox TABLE LOCK table `…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
… amazing MySQL manual, especially the option and variable reference table. But …Slave_heartbeat_periodblogpercona.commanual slave_load_tmpdirblogpercona.commanual slave_net_…isolationblogpercona.commanual unique_checksblogpercona.commanual updatable_views_with_limitblogpercona.commanual …
Post: Implementing efficient counters with MySQL
July 1, 2007 By 16 Comments
…table as counter table (can well work if you do not run into table locks bottleneck) or implement delayed table update…table” trick – use two tables insert into one and process and truncate another. MySQL offers atomic RENAME TABLE…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
…flight. Some data hand updated.’; — – Table structure for table `ontime_fact` — CREATE TABLE `ontime_fact` ( `date_… 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact… provide. I have not loaded InnoDB compressed tables yet but since InnoDB compression…
Post: MySQL Query Cache
July 27, 2006 By 69 Comments
… Query Cache on server which runs this blog. Updates are rather rare so per-table granularity is not the problem, I have… memory – Queries are constantly being invalidated from query cache by table updates, this means number of queries in cache and memory used… it works with MySQL to add caching but you can enable query cache so it works faster. Low load applications – If you…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
… can watch the load growth and …updated: 240 Select_commands: 1759 Update_commands: 254 Other_commands: 85 Commit_transactions: 0 Rollback_transactions: 0 mysql> SHOW TABLE_STATISTICS LIKE ‘hosting2%’; +——————————–+———–+————–+————————-+ | Table…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
… a question how much load a given system can handle when load is low and we …can take a look at procfs for MySQL process: root@ubuntu:/var/log/mysql# cat /proc/19018/stat 19018 … 0 ROWS_FETCHED: 485139 ROWS_UPDATED: 0 TABLE_ROWS_READ: 610954 SELECT_COMMANDS: 181243 UPDATE_COMMANDS: 0 OTHER_COMMANDS…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…2.4% 89 0.0022 1.00 0.01 UPDATE wp_options # 7 0x7AEDF19FDD3A33F1 0.1381 1.7% 909…time which typically (not always) shows queries causing highest load on the server. As we can see here …more data about the underlying tables involved and the query execution plan used by MySQL. The end result might be…
Post: Filtered MySQL Replication
November 7, 2007 By 7 Comments
… the tables are not taken into account. The traditional pre MySQL 5.0 way to break such replication is also using multi-table update or delete statements, if you happen to replicate one table… and would like to save on traffic or if write load is high and it would be waste to pull binary…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
… pattern responsible for large portion of the load in the application by aggregating query log … The query looks something like this: SELECT * FROM table WHERE deleted = 0 AND group_id IN (62715996,…update,btr_cur_search_to_nth_level,btr_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql…
