… your hard drive sequential read rate or you would see MySQL becoming CPU bound if IO subsystem is too fast. In… table is not logically fragmented and Innodb is reading data in large blocks – this can happen in case Innodb table file is itself fragmented… fragmentation is also the issue. Interesting enough the “cure” for both fragmentation issues is the same – OPTIMIZE TABLE tbl – this command recreates the table…
Post: Pretty-formatted index fragmentation with xtrabackup
March 15, 2011 By 4 Comments
… InnoDB tables and indexes. This can be useful to examine whether you’d benefit from “defragmenting” your MySQL database with OPTIMIZE TABLE, although… formats the stats output nicely to give an overview of fragmentation. It’s an initial draft, and if you find issues… top of the code listing. The output looks like this: TABLE INDEX TOT_PAGES FREE_PAGES PCT_FULL art.link_out104…
Post: Make your file system error resilient
August 24, 2011 By 8 Comments
… block: 0 Block size: 4096 Fragment size: 4096 Reserved GDT blocks: 316 Blocks per group: 32768 Fragments per group: 32768 Inodes per… is probably not needed for partition you store MySQL data on, as chances are MySQL server is only one doing writes on… as needed, same as you would every so often check MySQL tables for corruption. To change those options you can run tune2fs…
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
… the values from MySQL 5.5.30 and MySQL 5.6.10 to the different tables and ran the query: mysql [localhost] {msandbox} (test…_THREAD_INSTANCES | 1000 | 224 | | SOCKET | /tmp/mysql_sandbox5530.sock | /tmp/mysql_sandbox5610.sock | | INNODB_FILE_PER_TABLE | OFF | ON | | SYNC_MASTER_INFO | 0… now 64MB instead of 8MB which should help to reduce fragmentation and make file growth more rare event. max_connect_errors…
Post: MySQL Indexing Best Practices: Webinar Questions Followup
August 16, 2012 By 4 Comments
… your inserts and make primary key significantly fragmented. I also would note there are some MySQL optimizer restrictions in how well it… of indexes you can have, which is 64 per table in recent MySQL versions. However it is often too many. Instead of…. Q: how mysql use index for group by? A: If you have Index on the column MySQL can avoid temporary table or filesort…
Post: MySQL File System Fragmentation Benchmarks
March 21, 2008 By 28 Comments
…I found: The fragmentation we speak in this article is filesystem fragmentation or internal table fragmentation which affects performance of full table scan. Not … amount of tables and does specified number of inserts going to random tables. I used default MySQL settings for MyISAM (table_cache=…
Post: InnoDB: look after fragmentation
November 5, 2009 By 12 Comments
…problem at first. The table is CREATE TABLE `c` ( `tracker_id` int(…+—————————+ 1 row in set (2 min 8.92 sec) mysql> SHOW STATUS LIKE ‘Innodb_scan_pages%’; +——————————+——-+ | Variable_name…So notes to highlight: InnoDB fragmentation may hurt your query …
Post: MySQL Query Cache
July 27, 2006 By 70 Comments
… great feature called “Query Cache” which is quite helpful for MySQL Performance optimization tasks but there are number of things you… from this table are invalidated at once. Most of them quite likely would not have change their result set but MySQL has… cache on table modifications – if there are a lot of queries being cached this might reduce update speed a bit. Fragmentation over…
Post: Working with many files and file system fragmentation
March 18, 2008 By 11 Comments
… to repeat the test with MySQL MyISAM tables and see how bad the difference would be for MySQL but I would expect something… – one could optionally preallocate MyISAM tables in some chunks (say 1MB) so its gets less fragmentation. Though it would be interesting to… approach would generally help. Until we have this feature – reduced fragmentation is one more benefit we get with batching. For example…
Post: MySQL: what read_buffer_size value is optimal ?
September 17, 2007 By 25 Comments
… created table with simple structure: mysql> show create table dt2 \G *************************** 1. row *************************** Table: dt2 Create Table: CREATE TABLE `dt2` ( `grp…. Different table structures (ie longer rows) also may affect results, not to mention tables with fragmented rows …
