…: 1343751174852 MIN_TIMER_INSERT: 31913539 AVG_TIMER_INSERT: 313082268 MAX_TIMER_INSERT: 57121140547 COUNT_UPDATE: 8584 SUM_TIMER_UPDATE: … There is table with statistic per index: mysql> select * from table_io_waits_summary_by_index_usage where OBJECT_NAME=’sbtest55′G *************************** …
Post: MySQL 5.5 and MySQL 5.6 default variable values differences
February 21, 2013 By 8 Comments
…| | PERFORMANCE_SCHEMA_MAX_RWLOCK_INSTANCES | 1000000 | 1724 | | PERFORMANCE_SCHEMA_MAX_TABLE_HANDLES | …select * from var55 where variable_name=’OPTIMIZER_SWITCH’ \G *************************** 1. row *************************** VARIABLE_NAME: OPTIMIZER_SWITCH VARIABLE_VALUE: index_merge=on,index…
Comment: PBXT benchmarks
November 29, 2007 By 14 Comments
… Query_type num_queries max_time min_time q_per_s select_index 60000 12 0 7253.63 update_index 60000 23 0 7253… Query_type num_queries max_time min_time q_per_s select_index 60000 15 0 7464.76 update_index 60000 10 0 7464… connect: max=57ms min=0ms avg= 11ms from 120 clients Query_type num_queries max_time min_time q_per_s select_index 120000…
Post: Tuning InnoDB Concurrency Tickets
May 24, 2010 By 5 Comments
…> CREATE TABLE child (id INT, parent_id INT, -> INDEX par_ind (parent_id), -> FOREIGN KEY (parent_id…queries. Imagine those 900-row SELECT statements were actually 10,000 row selects, this would become a more…8 innodb_io_capacity=500 innodb_max_dirty_pages_pct=90 max_connections=3000 query_cache_size=…
Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1
January 8, 2007 By 117 Comments
…=1500M –innodb-log-file-size=100M –innodb-thread-concurrency=8 –max-connections=1500 –table-cache=512 –net_read_timeout=30 –net… 4 threads but drops down very fast. READ_PK_POINT_INDEX Query: SELECT id FROM $tableName WHERE id = %d Simple but very… Falcon by 1.64 – 3.85 times. READ_PK_RANGE_INDEX Query: SELECT count(id) FROM $tableName WHERE id between %d and…
Post: Efficient Boolean value storage for Innodb Tables
April 23, 2008 By 6 Comments
…: 2097152 Avg_row_length: 11 Data_length: 23068672 Max_data_length: 3096224743817215 Index_length: 1024 Data_free: 0 Auto_increment: …) or you can use Null-Aware comparison operator: mysql> select count(*) from cbool where c1NULL; +———-+ | count(*) | +———-+ | 1048576 | +———-+ 1 row …
Comment: MySQL Server Memory Usage
September 1, 2006 By 21 Comments
… ‘Com_create_function’, ’0′ ‘Com_create_index‘, ’0′ ‘Com_create_table’, ’31′ ‘Com…Key_write_requests’, ’114303′ ‘Key_writes’, ’1346′ ‘Max_used_connections’, ’651′ ‘Not_flushed_…NULL’ ‘Select_full_join’, ’0′ ‘Select_full_range_join’, ’0′ ‘Select_range’, ’5683551′ ‘Select_range_check’, ’0′ ‘Select…
Comment: MySQL Partitioning - can save you or kill you
June 28, 2012 By 45 Comments
…`), INDEX `UTCTime` (`UTCTime`), INDEX `MSISDN` (`ObjInstance`), INDEX `GroupId` (`ObjectClass`) ) ENGINE=MyISAM; 3 main SELECTS are ( replaced actual fields with “*” ): ##————————————————– SELECT o1.* FROM ObjectInstances_T o1 ,(SELECT ObjInstance, MAX(UTCTime…
Comment: MySQL Partitioning - can save you or kill you
June 28, 2012 By 45 Comments
… _week_ at a time. (reformatting for readability): SELECT o1.* FROM ObjectInstances_T o1 , ( SELECT ObjInstance, MAX(UTCTime) AS UTCTime FROM ObjectInstances_T where… IN in compound indexes. What version are you running? What does the EXPLAIN say? If those are the _only_ SELECTs you need…
Post: Gathering queries from a server with Maatkit and tcpdump
July 1, 2009 By 23 Comments
… 0.15 QPS, 0.00x concurrency ________________ # total min max avg 95% stddev median # Exec time 114ms…\G # SHOW CREATE TABLE `dual`\G # EXPLAIN select 1 from ( select sleep(.1) from dual ) as x\G Indeed…bit, we can see # 0% No_good_index_used # 100% No_index_used # Query_time distribution … snip ……
