… but the second one …select avg(id) from sbtest$i force key (primary) select count(*) from sbtest$i WHERE k like ‘%0%’ SysBench-0.5/lua: POINT_SELECT…max-requests=0 –percentile=99 –mysql-user=root –mysql-db=sbtest8t1M –mysql-table-engine=INNODB –mysql-socket=/tmp/mysql.sock –oltp-point-selects…
Post: How much overhead is caused by on disk temporary tables
August 16, 2007 By 26 Comments
…select count(*) cnt,c from gt group by c order by null limit 10; +—-+————-+——-+——+—————+——+———+——+———+—————–+ | id | select_type | table…which takes fraction of second to do, but …) mysql> set global max_heap_table_size=1000000000; Query OK,…
Post: Gathering queries from a server with Maatkit and tcpdump
July 1, 2009 By 23 Comments
…0.00x concurrency ________________ # total min max avg 95% stddev median…Tables # SHOW TABLE STATUS LIKE ‘dual’\G # SHOW CREATE TABLE `dual`\G # EXPLAIN select 1 from ( select sleep(.1) from dual ) as x\G Indeed, it’s no surprise the query took a tenth of a second…
Comment: Why MySQL could be slow with large tables ?
June 20, 2007 By 141 Comments
… key_buffer=750M join_buffer=10M max_heap_table_size=50M tmp_table_size=64M max_allowed_packet=16M table_cache=1800 record_buffer=10M… GMT the SELECTs from this table would take about 7-8 seconds each on a very simple query such as this: SELECT column2, column3 FROM table1 WHERE column1 = id; The index is on column1. So when I would “REPAIR TABLE table1 QUICK…
Post: Identifying the load with the help of pt-query-digest and Percona Server
December 29, 2011 By 3 Comments
…say 0.1 seconds, it will … pct total min max avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= …Tables # SHOW TABLE STATUS FROM `wp_blog_one ` LIKE ‘wp_options’\G # SHOW CREATE TABLE `wp_blog_one `.`wp_options`\G # EXPLAIN /*!50100 PARTITIONS*/ SELECT…
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…from SQL statements. It reads one or more “CREATE TABLE db.schema … AS SELECT” and/or “INSERT INTO db.schema … AS SELECT” statements from…seconds to calculate the total sales for all customers from the dashboard_customer_sales table…..04 sec) *If you use MIN/MAX/COUNT_DISTINCT, a secondary view …
Post: Recovering Innodb table Corruption
July 4, 2008 By 22 Comments
…second…select max(id) from test2; +———+ | max(id) | +———+ | 220 | +———+ 1 row in set (0.00 sec) mysql> insert ignore into test2 select * from…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…comparison to InnoDB and MyISAM. The second goal of benchmark was a popular…–falcon_max_record_memory=2GB –falcon_page_cache_size=1500M –max-connections=1500 –table-cache=…SELECT city FROM $tableName WHERE country_id = %d and state_id between %d and %d LIMIT 50 READ_FTS Query: SELECT min(dob) FROM…
Post: How Percona does a MySQL Performance Audit
November 24, 2008 By 17 Comments
… The second person will be from the same…tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table… client IN (SELECT id FROM clients)” which …
Post: How innodb_open_files affects performance
November 18, 2009 By 4 Comments
…tables/sec) – first time Innodb actually populates data dictionary. The second time we do same operation it takes about 25 seconds (4.000 tables…all tables once) Same as with select …from the .ibd files… InnoDB: Restoring possible half-written data pages from… value of innodb_max_files_open in InnoDB…
