… most painful troubleshooting tasks with MySQL is troubleshooting memory usage. The problem usually starts like this – you have configured MySQL to use reasonable global… running MySQL in wild. I also wanted to share some troubleshooting techniques I use. Plot Memory Usage First I would like to see MySQL memory…_LENGTH: 16384 INDEX_LENGTH: 0 CREATE_TIME: NULL UPDATE_TIME: NULL 3 rows in set (0.00 sec) Innodb Memory Usage Finally it…
Post: Dynamic row format for MEMORY tables
September 6, 2011 By 3 Comments
… eBay implemented true-VARCHAR support for MEMORY tables for MySQL 5.0. Although this patch did …either wasting space (VARCHAR) or outright impossible (BLOB). Indexes on BLOB columns are not supported. While …Here we can see that the performance and memory usage is about the same for both row formats….
Post: MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
…Relational Database Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance … dedicated to RDS, not shared with the general usage of AWS as you get on normal EC2 … rows. This creates a table+index data size approximately twice as big as memory, so the workload should …
Post: SHOW INNODB STATUS walk through
July 17, 2006 By 40 Comments
… 3771312 starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 320 MySQL thread id… starting index read, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 3 lock struct(s), heap size 320 MySQL… and memory usage. You can see total memory allocated by Innodb (sometimes it is higher than you anticipated), amount of memory allocated in additional memory…
Post: Choosing innodb_buffer_pool_size
November 3, 2007 By 38 Comments
… not only contain data pages – it also contain adaptive hash indexes, insert buffer, locks which also take some time. Though it… way – OS may kill MySQL Process running out of memory (or thinking it is running out of memory) which may happen ie in… try locking MySQL in memory by using –memlock – just be careful as in case you have memory usage spike you may have MySQL Server being…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
…join means any of the joins didn’t use indexes. If a temporary table is required for query… must be created on disk instead of in memory, in such case Disk_tmp_table will state … through temporary files. INNODB USAGE The final part are the InnoDB usage statistics. MySQL currently allows you to see …
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… MySQL uses single index – there are more complex rules of how indexes will be used if you look at multiple indexes usage with “index merge” Using Index… the reason covering indexes help to speed up queries even if data is in memory. If MySQL is only reading index and not accessing rows you will see “using index” in EXPLAIN output. These…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… Due to memory requirements, the InnoDB versions are only available on 64 bit instances. MySQL will fail … There will be another blog post about the usage and performance of the splitter. It is … 241 Data_length: 1616904192 Max_data_length: 0 Index_length: 539279360 Data_free: 4194304 Auto_increment: …
Post: Percona Server 5.5.15-21.0
August 31, 2011 By 9 Comments
…MEMORY Storage Engine As of MySQL 5.5.15, a Fixed Row Format (FRF) is still being used in the MEMORY… of it. All values for columns used in indexes are stored in fixed format at the first … DRF. More information about the usage and implementation of the Improved MEMORY Storage Engine can be found in …
Post: How much memory Innodb locks really take ?
July 13, 2006 By 11 Comments
…in terms of low resource usage as they are advertised. …` (`j`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 mysql> show table status like “sample” \G; *************************** 1…: 100253696 Max_data_length: 0 Index_length: 128974848 Data_free: 0…half GB of memory, which is small fraction of memory used on …
