… just slow accessing information_schema can often impact server performance dramatically. The cause of majority of this slowness is not opening and closing tables, which can be solved with decent table cache size, and which is… table first time it opens it. Here are some numbers from my test box: mysql> select count(*),sum(data_length) from information_schema.tables…
Post: Scaling problems still exist in MySQL 5.5 and Percona Server 5.5
May 20, 2011 By 11 Comments
…. The query looks something like this: SELECT * FROM table WHERE deleted = 0 AND group_id IN (62715996, 62716592…_info_update_slow,btr_search_info_update,btr_cur_search_to_nth_level,btr_p cur_open_with_…btr_p cur_open_with_no_init_func,row_sel_get_clust_rec_for_mysql,row_search_for_mysql,ha_…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… Default config for MySQL 5.5 and MySQL 5.6, no tuning at all Config 2: MySQL 5.6 with …data and the IO is slow (when the FS cache is cold, IO to slow IO subsystem happens) Repeated …| 13.027019 | 4979 | 21399 | 0 | 0 | 90 | 90 | | 5 | Opening tables | 0.270664 | 0.295955 | 0.025996 | 0.024996 | 34 |…
Post: Percona XtraDB Cluster: Multi-node writing and Unexpected deadlocks
August 17, 2012 By 17 Comments
… extra network traffic would really slow down transactions. You may have …) node2 mysql> show create table autoinc\G *************************** 1. row *************************** Table: autoinc Create Table: CREATE TABLE `autoinc` ( `i… have an open transaction on node1 updating row 1 in our table. …
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
… a table‘s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow… the first opening of the table or manually running ANALYZE TABLE on it? The 2 instances below are documented from the MySQL and InnoDB plugin’s manual: Metadata commands like SHOW INDEX, SHOW TABLE STATUS and SHOW [FULL] TABLES (or their…
Post: What to tune in MySQL Server after installation
September 29, 2006 By 118 Comments
… case MySQL Server crashes. Value 2 only cause data loss with full OS crash. table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use… help. Do not set it too large as it may slow things down as its maintenance may get expensive. Values from…
Post: Write contentions on the query cache
September 5, 2012 By 5 Comments
… know more about how MySQL executes this query….permissions | 0.000004 | | checking permissions | 0.000004 | | Opening tables | 0.000021 | | System lock | 0.000009 | | …0.000004 | | closing tables | 0.000032 | | freeing items | 0.000035 | | logging slow query | 0.000004 …
Post: Figuring out what limits MySQL Replication
March 7, 2007 By Leave a Comment
… limiting MySQL Replication speed. My first idea was to check it based on slow query log, happily the server was running MySQL with slow… restart MySQL but this would make replication to be delayed few more hours because of the time Innodb takes to open tables first… to mysqlsla which I remembered parses all kinds of MySQL Logs. Too bad MySQL Binary log is the only one which it…
Post: The perils of uniform hardware and RAID auto-learn cycles
November 16, 2010 By 16 Comments
… of quite uniform database servers. Some of the servers were slowing down in a very puzzling way over a short time… system metrics (++customer), and the symptoms we observed were that Open_tables went sharply up, the buffer pool got more dirty pages… default (these are Dells, with PERC / LSI MegaRAID controllers.) I opened my quick-reference text file and found the command we…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
…tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table…-lt slow /path/to/slow….openings in chess — the great chess players study openings…
