… plan either by updating statistics (ANALYZE TABLE) or disabling some of the optimization/statistics related features in new version. Innodb statistics can especially be…
Post: Tuning for heavy writing workloads
October 14, 2009 By 9 Comments
… to tune the contention. innodb_stats_update_need_lock = 0 (default 1) It skip the updating statistics which needs the lock. (it only… the throughput stabilize, it is not enough still for heavy update workloads. A single purge_thread on one CPU is not enough for updates from user threads on the all of other CPUs. XtraDB…
Post: Quickly finding unused indexes (and estimating their size)
December 5, 2012 By 2 Comments
…’s quite easy to collect statistics about index usage in Percona Server (and others) using the User Statistics patch. By enabling ‘userstat…_total_size from information_schema.tables t join information_schema.statistics i using (table_schema, table_name) join information_schema.innodb…: 27 index_total_size: 442368 … Now we just need to update our droppable_indexes view to use that information: mysql> drop…
Post: The perils of InnoDB with Debian and startup scripts
January 28, 2009 By 13 Comments
… | statistics | select … | 6334 | user | statistics | select … | 6337 | user | | NULL | 6340 | user | Sending data | select … | 6342 | user | statistics | select … | 6344 | user | | NULL | 6345 | user | Updating | update … | 6346 | user | Sorting result | insert … | 6351 | user | | NULL | 6355 | user | | NULL | 6356 | user | statistics | select … | 6357 | user | statistics | select … | 6358…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
… new set of commands such as SHOW TABLE_STATISTICS, SHOW INDEX_STATISTICS and SHOW USER_STATISTICS. The first two can interest anyone to… Rows_updated: 240 Select_commands: 1759 Update_commands: 254 Other_commands: 85 Commit_transactions: 0 Rollback_transactions: 0 mysql> SHOW TABLE_STATISTICS LIKE…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
…-tables support to sysbench, it is interesting to see what statistic we can get from workload that produces some disk IO…_TIMER_INSERT: 57121140547 COUNT_UPDATE: 8584 SUM_TIMER_UPDATE: 3453842535354 MIN_TIMER_UPDATE: 7587746 AVG_TIMER_UPDATE: 402357649 MAX_TIMER_UPDATE: 78153671549 COUNT_DELETE: 4292…
Comment: About This Blog
October 7, 2008 By 48 Comments
… SHOW STATISTICS Com_select, Questions, Com_update FROM conuser WHERE Questions > 10000; +—————————-+————-+———–+————+ | conuser (user@host) | Com_select | Questions | Com_update | +——— ——————+————-+———–+————+ | bandala…
Comment: Four ways to optimize paginated displays
October 3, 2008 By 27 Comments
… for the forum: ———————————————– 1) I have a statistics table that is updated by triggers on inserts: statistics_table(serviceid, count, lastinsert): +——–+———+—————-+ | SERVICE| COUNTER… the same statistics per item in the forum-category table. Again, this table is updated with the same trigger that updates the statistics_table…
Post: Investigating MySQL Replication Latency in Percona XtraDB Cluster
March 3, 2013 By 13 Comments
…: sysbench –test=oltp –oltp-test-mode=nontrx –oltp-nontrx-mode=update_key –mysql-user=root –mysql-password=”" –oltp-table-size=1000000…=on –max-requests=0 –max-time=300 run …. per-request statistics: min: 0.68ms avg: 0.88ms max: 306.80ms approx… run again now running the same update statement on the different cluster node: per-request statistics: min: 0.69ms avg: 1.12ms…
Post: Auditing login attempts in MySQL
December 28, 2012 By 4 Comments
…information about a particular user: mysql> select * from user_statistics where user=’root’\G *************************** 1. row *************************** USER: root … 0 ROWS_FETCHED: 783051 ROWS_UPDATED: 1017714 TABLE_ROWS_READ: 1484751 SELECT_COMMANDS: 14 UPDATE_COMMANDS: 103 OTHER_COMMANDS: …
