June 19, 2013

Post: MySQL and the SSB - Part 2 - MyISAM vs InnoDB low concurrency

May 22, 2013 By Leave a Comment

… sort via ALTER TABLE ENABLE KEYS. my.cnf [mysqld] datadir=/mnt/mysql56/data basedir=/usr/local/mysql socket=/var/lib/mysql/mysql.sock user=justin innodb_buffer_pool_size=64G innodb_log_file_size=4G innodb_file_per_table… innodb_undo_logs=32 innodb_old_blocks_time=0 table_open_cache=2048 table_definition_cache=16384 innodb_flush_method=O_DIRECT key_buffer…

Filed Under: Benchmarks, MySQL Tagged With: , , , , , , ,

Post: Why you should ignore MySQL's key cache hit ratio

February 28, 2010 By 28 Comments

… that somewhere around of 99% of advice about tuning MySQL‘s key cache hit ratio is wrong, even when you hear it… sound like on a machine that’s dedicated to MyISAM tables. Consider your mixture of storage engines (some InnoDB, some MyISAM… — the “key cache hit ratio.” This is a shame. When you are new to MySQL, trying to configure my.cnf, and you have…

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

Post: Multiple purge threads in Percona Server 5.1.56 and MySQL 5.6.2

May 3, 2011 By 13 Comments

… are on Benchmark Wiki and the relevant my.cnf settings were innodb_buffer_pool_size=16G innodb_…80 innodb_file_format=barracuda innodb_file_per_table max_connections=2000 table_cache=2000 Percona Server 5.1 Results …not to penalize further TPS. MySQL 5.6.2 Results Now let’s test MySQL 5.6.2. It …

Filed Under: MySQL

Post: Shard-Query EC2 images available

May 11, 2011 By 2 Comments

…,14.573 InnoDB my.cnf [client] port=3306 socket=/tmp/mysql-inno.sock [mysqld] socket=/tmp/mysql-inno.sock default-storage-…TABLES symbolic-links table-definition-cache=16384 table-open-cache=128 thread-cache-size=32 thread-stack=256K tmp-table-size=64M transaction-isolation=READ-COMMITTED user=mysql…

Filed Under: MySQL

Post: Aligning IO on a hard disk RAID – the Benchmarks

June 9, 2011 By 13 Comments

… with the following my.cnf configuration: [mysqld] datadir=/data/mysql socket=/var/run/mysqld/mysqld.sock innodb_file_per_table = true innodb_data… table_cache = 2048 max_allowed_packet = 16M binlog_cache_size = 16M max_heap_table_size = 64M thread_cache_size = 32 query_cache_size = 0 tmp_table_size…

Filed Under: MySQL

Post: What to tune in MySQL Server after installation

September 29, 2006 By 118 Comments

… case MySQL Server crashes. Value 2 only cause data loss with full OS crash. table_cache – Opening tables can be expensive. For example MyISAM tables mark MYI header to mark table as currently in use… I can analyze workload. P.S Note MySQL distribution contains bunch of sample my.cnf files which may be great templates to…

Filed Under: Insight for DBAs Tagged With:

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

February 26, 2013 By 1 Comment

….ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_DELETED_CACHE.ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 16….ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 15:54 FTS_0000000000000114_DELETED_CACHE.ibd -rw-rw—-. 1 mysql mysql 98304 Feb 20 15… of the few people that likes lower_case_table_names = 1 in /etc/my.cnf), but I think it’s even worse…

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

Post: Review of MySQL 5.6 Defaults Changes

October 8, 2012 By 10 Comments

…great summary of defaults changes in MySQL 5.6 compared to MySQL 5.5 In general there are…tables. There is also little “savings” in keeping this value low – 64K or even more would be good default. query_cache… also promises updated sample my.cnf and getting rid of old my-huge.cnf which are great. …

Filed Under: Insight for DBAs, MySQL

Post: New SpecJAppServer results at MySQL and Sun.

November 12, 2008 By Leave a Comment

… disable them. The MySQL Settings are probably what is the most interesting: MySQL 5.0 Tuning in /etc/my.cnf (included in FDA… table_cache = 6000 read_rnd_buffer_size = 2M sort_buffer_size = 32k thread_cache = 16 query_cache_size = 0M thread_concurrency = 8 max_heap_table_size=200M log-output = FILE log-slow-queries=/tmp/mysql-slow…

Filed Under: Benchmarks

Post: How much memory can MySQL use in the worst case?

February 12, 2009 By 17 Comments

…cache and the table cache and the size needed for each thread (thread stack, etc)? Those take memory too. The query cache…OK, 0 rows affected (2.34 sec) mysql> select get_lock(@a, 1); +—————–+ | get_lock(@a…cnf for a server, I usually calculate the Big Ones like the buffer pool, query cache…

Filed Under: Insight for DBAs Tagged With:
« Older Posts