Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still wanted … in a way it presents information: SELECT CONCAT(table_schema, ‘.’, table_name), CONCAT(ROUND(table_rows / 1000000, 2), ‘M’) rows, CONCAT(ROUND(data… whatever database you have active. It does not work with MySQL before 5.0 still of course 
Post: Finding out largest tables on MySQL Server
February 4, 2008 By 24 Comments
Post: Researching your MySQL table sizes
March 17, 2008 By 26 Comments
I posted a simple INFORMATION_SCHEMA query to find largest tables last month and it got a good response. Today I … total number of tables, rows, total data in index size for given MySQL Instance SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2…; +——–+——————–+——-+——-+——-+————+———+ | tables | table_schema | rows | data | idx | total_size | idxfrac | +——–+——————–+——-+——-+——-+————+———+ | 48 | cacti | 0.01M | 0.00G | 0.00G | 0.00G | 0.72 | | 17 | mysql…
Post: MySQL on Amazon RDS part 1: insert performance
March 15, 2011 By 37 Comments
… Database Service (RDS) is a cloud-hosted MySQL solution. I’ve had some clients hitting performance… normal EC2 servers with EBS. I benchmarked the largest available RDS instance, which is listed as “High… with 400,000,000 rows. This creates a table+index data size approximately twice as big as …
Post: Upgrading MySQL
January 5, 2010 By 23 Comments
…. The largest amount of potential issues happens when you upgrade to different major version, especially if you skip over one. MySQL 5…, such as DECIMAL field got whole another meaning in MySQL 5.0 MySQL generally supports legacy storage format for a while, even… and made it to caught up you can use mk-table-checksum to ensure data is the same. It is possible…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
… enable logging atomically, not just for new connections as in MySQL. This is very helpful for measurement as otherwise we might… output ranks the queries and shows the top queries with largest impact – longest sum of run time which typically (not always… gather more data about the underlying tables involved and the query execution plan used by MySQL. The end result might be that…
Post: MySQL Server Memory Usage
May 17, 2006 By 18 Comments
… have a lot of tables. It also means user having CREATE TABLE privilege should be able to run MySQL server out of memory MyISAM buffers. MyISAM may allocate buffer which is large enough to contain largest record in the given table which is held until table is closed. Federated Storage Engine. This…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 22 Comments
…_id in the fact table, respectively. There are nearly 400 major airports included in the data set. mysql> select count(*) from dim… largest air carriers must register and report ontime information with the FAA, so there are only 29 air carriers in the table: mysql….02 sec) Each year has tens of millions of flights: mysql> select count(*) from ontime_one.ontime_fact; +———–+ | count(*) | +———–+ | 135125787 | +———–+ 1…
Comment: Finding out largest tables on MySQL Server
October 14, 2008 By 24 Comments
…Referenced from: mysqlperformanceblog.com Finding largest tables on MySQL instance is no brainier in MySQL 5.0+ thanks to Information Schema but I still… in a way it presents information: SQL: mysql> SELECT concat(table_schema,’.',table_name),concat(round(table_rows/1000000,2),’M') rows,concat(round…
Post: Getting MySQL to use full key length
January 31, 2007 By 5 Comments
… 0,10 \G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: msg132 type: ref possible_keys: group_id,thread_id,groupid… FORCE INDEX actually forces index to be used to largest extent possible: mysql> explain SELECT thread_id FROM nn2_msg132.msg132 force…
Comment: Finding out largest tables on MySQL Server
August 8, 2008 By 24 Comments
[...] Finding out largest tables on MySQL Server [...]
