…because operationally working with large table and data sizes in MySQL can be painful, lacking online operations to alter table, defragmentation etc. Yes there… yet as MySQL is getting better and better handling large scale systems (both in terms of performance and operationally) and as MySQL is …
Post: Is there room for more MySQL IO Optimization?
January 3, 2013 By 11 Comments
… cached — system files, binary log, FRM files, MySQL MyISAM system tables etc. Starting MySQL 5.5 MySQL uses asynchronous IO which should allow it…. I run CHECK TABLE on this table which essentially performs data and indexes scan which will be reflective to large full table scan and index…
Post: Drop table performance
April 20, 2011 By 22 Comments
…TABLE performance in InnoDB. (You can refer to Peter’s post http://www.mysqlperformanceblog.com/2011/02/03/performance-problem-with-innodb-and-drop-table/ and these bug reports: http://bugs.mysql.com/bug.php?id=51325 and http://bugs.mysql… for a server with a large amount of memory, like …
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 7 Comments
…InnoDB perform the updates aside from the first opening of the table or manually running ANALYZE TABLE on it? The 2 instances below are documented from the MySQL…constantly executes SHOW [FULL] TABLES or SHOW TABLE STATUS on many a large tables, this can affect …
Post: How Much memory do you use to run MySQL
November 2, 2012 By 8 Comments
… issues with MySQL Server related to amount of memory you have in the system – these range from problems with large size Query Cache to bad drop table performance with large Innodb Buffer Pool size. As such I wonder how much memory do we really use to run MySQL Server…
Post: table_cache negative scalability
November 16, 2009 By 23 Comments
… apples to apples. It performs same as MySQL 5.0 and MySQL 5.1 with small table cache. With table_open_cache increased to 16384… even in Performance focused MySQL 5.4. As we can see large table_cache (or table_open_cache_ values indeed can cause significant performance problems. Interesting…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 100 Comments
… implementation, especially together with LIMIT is often the cause of MySQL Performance problems. Here is what you need to know about ORDER… we stick to simple MySQL using multiple indexes on most selective columns would be good idea for performance of such queries. For… should write another article about ORDER BY without limit and large tables soon.
Post: MySQL: what read_buffer_size value is optimal ?
September 17, 2007 By 25 Comments
… fast full table scans for large table you should set this variable to some high value. Sample my.cnf values on large memory sizes recommend 1M settings and MySQL… query to perform full table scans, with 3 runs and averaged results. MySQL 5.1.21-beta was used for tests. mysql> select count…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
…MySQL into removing smaller files with DROP TABLE to minimize the effect, such as: TRUNCATE TABLE large_table; ALTER TABLE large_table ENGINE=…; DROP TABLE large_table; TRUNCATE TABLE large_table; OPTIMIZE TABLE large_table; DROP TABLE large_table…
Post: Ultimate MySQL variable and status reference list
January 8, 2011 By 4 Comments
…Performance_schema_rwlock_classes_lostblogpercona.commanual Performance_schema_rwlock_instances_lostblogpercona.commanual Performance_schema_table_handles_lostblogpercona.commanual Performance_schema_table_instances_lostblogpercona.commanual Performance…
