June 19, 2013

Post: MySQL Server Variables - SQL layer or Storage Engine specific.

June 8, 2006 By 14 Comments

…. bulk_insert_buffer_size – Used by MyISAM tables only to optimize bulk inserts (multiple value… tables. key_cache_age_threshold, key_cache_block_size, key_cache_division_limit Key Cache/Key Buffer replacement algorithm… and Innodb. Some SQL level items as Query Cache also could use this feature. low_…

Filed Under: Insight for DBAs Tagged With: ,

Post: Tuning InnoDB Concurrency Tickets

May 24, 2010 By 5 Comments

…) – 1 As with any performance optimization effort, you will want to optimize for the common case. If you…the number of threads that can enter InnoDB is limited by innodb_thread_concurrency (which is why these…_max_dirty_pages_pct=90 max_connections=3000 query_cache_size=0 skip-name-resolve table_cache…

Filed Under: Benchmarks, Insight for DBAs, MySQL Tagged With: , , , ,

Post: SHOW INNODB STATUS walk through

July 17, 2006 By 40 Comments

…id 8079, query id 728899 localhost root Sending data select sql_calc_found_rows * from b limit 5 Trx …your innodb_log_buffer_size is optimal – if you see more than 30% of log buffer size being unflushed you …_commit=2 log writes are done to OS cache, and being sequential writes these logs writes are …

Filed Under: Insight for DBAs Tagged With:

Comment: MySQL Server Memory Usage

September 1, 2006 By 21 Comments

… ’0′ ‘Com_lock_tables’, ’102′ ‘Com_optimize‘, ’0′ ‘Com_preload_keys’, ’0′ ‘Com_prepare…queries=/var/log/mysql/slow-queries.log max_connections=650 open_files_limit…size=32M # Only for MyISAM which isnt used. sort_buffer_size=2M query_cache_size=10M # Might be increased thread_cache_size=40 table_cache…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Economics of Performance Optimization

March 13, 2008 By 9 Comments

…about optimizing application vs getting better hardware. With application optimization typically…dramatically after certain hardware size. For example right … single user CPU bound query takes 30 seconds you…limited by MySQL server so the alternative would be either spend $5000 on implementing caching…

Filed Under: Insight for Developers Tagged With:

Post: Heikki Tuuri Innodb answers - Part I

October 26, 2007 By 19 Comments

…size drops below this limit, merging it to a neighbor is tried */ #define BTR_CUR_PAGE_COMPRESS_LIMIT (UNIV_PAGE_SIZE…PZ: Another possible area of optimization. I frequently see batch…they were not matched by query where clause when it …a battery-backed disk controller cache, then the commit returns in…

Filed Under: Insight for Developers Tagged With: ,

Post: MySQL 5.5.8 and Percona Server: being adaptive

December 20, 2010 By 25 Comments

… neighborhoods feature was created as an optimization for hard drives, since InnoDB …limit). In additional to increasing log_size we added option innodb_log_block_size which allows to change IO block size…500 max_connections=3000 query_cache_size=0 skip-name-resolve table_cache=10000 [mysql] socket=/…

Filed Under: Benchmarks, MySQL Tagged With: , ,

Post: MySQL 5.5 and MySQL 5.6 default variable values differences

February 21, 2013 By 8 Comments

… 1 optimizing for higher concurrency workloads. Makes sense as servers have a lot more cores available these days. query_cache_type and query_cache_size. The… increased to 2000 from 300. Good change considering open_files_limit was also raised. Unlikely to cause significant gains unless opening…

Filed Under: Insight for DBAs, MySQL Tagged With: , , ,

Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1

January 8, 2007 By 117 Comments

…cache is not scaled. READ_KEY_POINT_LIMIT Query: SELECT name FROM $tableName WHERE country_id = %d LIMIT 5 The same query…optimization we use in our practice. READ_KEY_POINT_NO_DATA_LIMIT Query: SELECT state_id FROM $tableName WHERE country_id = %d LIMIT…

Filed Under: Benchmarks Tagged With: ,

Post: InnoDB Full-text Search in MySQL 5.6 (part 1)

February 26, 2013 By 1 Comment

…5.6 instance is 128MB – smaller than the size of my DIR data. So, with all …Feb 20 16:09 FTS_00000000000000ad_BEING_DELETED_CACHE.ibd -rw-rw—-. 1 mysql mysql 98304 …delete from dir_test_innodb LIMIT 200000; Query OK, 200000 rows affected (8.65 sec) mysql> optimize table dir_test_innodb; …

Filed Under: Insight for DBAs, Insight for Developers, MySQL Tagged With: , , , , ,
« Older Posts
Newer Posts »