… I use. Plot Memory Usage First I would like to see MySQL memory consumption plotted. I use “VSZ” columns…used by current MEMORY tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory’; +——————————-+ | sum(data_length+index…
Post: Advanced index analysis with mk-index-usage
November 11, 2010 By 2 Comments
…index_usage \ –create-save-results-database After that finishes, the ‘index_usage‘ database contains several tables: mysql> show tables; +———————–+ | Tables_in_index_usage | +———————–+ | index_alternatives | | index_usage | | indexes…
Post: 3 ways MySQL uses indexes
September 12, 2009 By 27 Comments
… ways MySQL can use indexing, getting wrong ideas on what query performance they should expect. There are 3 main ways how MySQL can use the indexes… describes how 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 to…
Post: MySQL extensions for hosting
April 24, 2008 By 11 Comments
… uploads a really badly designed application, which runs queries not using indexes or which examine a lot of rows. This can elevate… useful. With simple MySQL SHOW command it gives you the basic statistics on the database activity broken down by users, tables or indexes… a dedicated server because their usage is very high temporarily block users having huge spike in MySQL usage Personally I would see a…
Post: The new cool MySQL patch has landed! Check your queries performance!
October 31, 2007 By 16 Comments
… can be executed in various ways. For example it may use indexes or do a full table scan, or a temporary table… table. Full_join means any of the joins didn’t use indexes. If a temporary table is required for query execution Tmp… was used, while Disk_filesort means that sorting was done through temporary files. INNODB USAGE The final part are the InnoDB usage statistics. MySQL…
Post: Indexes in MySQL
June 2, 2006 By 27 Comments
MySQL does not always make a right decision about indexes usage. Condsider a simple table: … | Using where | +—-+————-+——-+——+—————+——+———+——-+——-+————-+ That is MySQL will use index for this query. Let’s compare the execution time with index…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… will be another blog post about the usage and performance of the splitter. It is… avaialble data from 1988 to 2010′; mysql> use ontime1; Database changed mysql> show table status like ‘ontime_fact… Data_length: 1616904192 Max_data_length: 0 Index_length: 539279360 Data_free: 4194304 Auto_increment:…
Post: Performance Schema tables stats
May 2, 2011 By 2 Comments
… MAX_TIMER_DELETE: 62494969560 Or using this data we can TOP 5 accessed tables via mysql> select OBJECT_NAME,COUNT_STAR from…) 2. There is table with statistic per index: mysql> select * from table_io_waits_summary_by_index_usage where OBJECT_NAME=’sbtest55′G *************************** 1…
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 …Capacity.” I used sysbench’s oltp benchmark, with 400,000,000 rows. This creates a table+index data size …
Comment: COUNT(*) vs COUNT(col)
April 25, 2007 By 41 Comments
… index | NULL | PRIMARY | 8 | NULL | 424672 | Using index | +—-+————-+————-+——-+—————+———+———+——+——–+————-+ 1 row in set (0.11 sec) mysql…index data, not the row data for the table when you use a secondary index. Check the buffer pool usage…
