… if you have a small buffer pool and a very large number of rows from table2 that do not fit into… called the build step. After the hash table has been created, rows from table t2 are read and hash function is applied….5 is slow as compared to MySQL 5.6. Next interesting thing are the last two columns of the table above and…
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 3 Comments
… of memory for table cache, especially if you’re using large blobs. It is easy to check though. Run “FLUSH TABLES” and see… identify potential causes. Is it working with large blobs ? Using user variables ? Prepared Statements ? memory tables ? In a lot of cases you… good tools to detect memory leaks like valgrind are too slow to run in production. So the best thing to do…
Post: Why MySQL could be slow with large tables ?
June 9, 2006 By 139 Comments
… join with dimention tables being small it would not slow things down too much. On other hand join of few large tables, which is completely disk bound can be very slow. One…% or rows or less full table scan may be faster. Avoid joins to large tables Joining of large data sets using nested loops is…
Post: Slow DROP TABLE
June 16, 2009 By 21 Comments
… are stopped from accessing any table. Here is when the slow file removal on the ext3 …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: table_cache negative scalability
November 16, 2009 By 23 Comments
… fully cached which made updates to headers very slow. In the production systems with table headers not in OS cache you often… (opened_tables) to 10/sec or less by using large table_cache you should do so. However if you have so many tables you… Performance focused MySQL 5.4. As we can see large table_cache (or table_open_cache_ values indeed can cause significant performance problems…
Post: More on table_cache
November 26, 2009 By 7 Comments
… Table Cache of 2000 I got about 16400 selects/sec with Table Cache of 20000 13500 selects/sec. So there is some slow…) table cache misses it is best to size table_cache large enough – even in the best case scenario for misses hit from large table_cache is faster. There are also possible optimization for “hit” path with large table cache though this should…
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
… in a way it presents information: SELECT CONCAT(table_schema, ‘.’, table_name), CONCAT(ROUND(table_rows / 1000000, 2), ‘M’) rows, CONCAT(ROUND(data… also use it to see which tables may be worth to review in terms of indexes. Large index size compared to data… rather slow if you have a lot of large tables. On this instance it took 2.5 minutes to run for 450 tables. UPDATE…
Post: When Does InnoDB Update Table Statistics? (And When It Can Bite)
October 6, 2011 By 5 Comments
… a table‘s statistics or index cardinality becomes outdated, you might see queries which previously performed well suddenly show up on slow… or an application constantly executes SHOW [FULL] TABLES or SHOW TABLE STATUS on many a large tables, this can affect your server especially if… # Bytes_sent: 34187 Tmp_tables: 1 Tmp_disk_tables: 0 Tmp_table_sizes: 0 SET timestamp=1316767697; show table status from `db1`; As you…
Post: Quickly preloading Innodb tables in the buffer pool
May 1, 2008 By 9 Comments
…_col=0 This works relatively well (though can be slow for fragmented tables) but it does not preload indexes in memory neither… scan Clustered key Anyway. Now, say you have bunch of tables having few indexes – should you run multiple queries in parallel… to find out. If you just need to preload single large table you can chop it into several ranges and preload in…
Post: ORDER BY ... LIMIT Performance Optimization
September 1, 2006 By 93 Comments
… be the most efficient – If it is rare category large portion of table may be scanned to find 10 rows. So index… – it provides scary explain statements and may end up in slow query log as query which does not use indexes, even… should write another article about ORDER BY without limit and large tables soon.
