June 18, 2013

Post: Distro Packages, Pre-built Binaries or Compile Your Own MySQL

September 4, 2012 By 7 Comments

… greatest, simply, select bug fixes especially security bug fixes are incrementally backported with each update leaving some if not all incompatible… MySQL‘s default behavior i.e. disabling and totally disallowing use of query cache or increasing maximum total number of indexes per table…

Filed Under: MySQL

Post: Flexviews - part 3 - improving query performance using materialized views

April 4, 2011 By 20 Comments

…cache “expires”, the contents must be fully recalculated.. An incrementally refreshable view (that is, one which uses the incremental…The incremental method is somewhat conceptually similar to using mysql binary…table_id: 28 mview_id: 21 mview_table_name: customers mview_table_schema: demo mview_table…

Filed Under: Insight for DBAs, MySQL

Post: Enum Fields VS Varchar VS Int + Joined table: What is Faster?

January 24, 2008 By 30 Comments

… benchmark. We have 4 tables: 1) Table with ENUM: CREATE TABLE cities_enum ( id int(10) unsigned NOT NULL auto_increment, state enum(‘Alabama… (state) ) ENGINE=MyISAM; 2) Table with VARCHAR: CREATE TABLE cities_varchar ( id int(10) unsigned NOT NULL auto_increment, state varchar(50) NOT…, while join performance degraded dramatically. Here is why: mysql> explain select SQL_NO_CACHE c.city from cities_join c JOIN states…

Filed Under: Benchmarks 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…’ll get an error: mysql> CREATE TABLE dir_test_innodb4 (fts_doc_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY); ERROR 1166…

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

Comment: MySQL Server Memory Usage

December 20, 2012 By 21 Comments

… binlog-ignore-db=mysql,test #replicate_do_db=crmdb #replicate_do_db=newcrmdb #auto-increment-increment = 3 #auto-increment-offset = 1 #log… 8M table_cache = 3600 table_definition_cache = 4096 thread_cache_size = 16K wait_timeout = 120 connect_timeout = 60 tmp_table_size = 192M max_heap_table_size = …

Filed Under: Insight for DBAs Tagged With: , ,

Post: Improved InnoDB fast index creation

November 6, 2011 By 4 Comments

…’s start with a table containing 4 million rows and one secondary key: mysql> CREATE TABLE t(id INT AUTO_INCREMENT PRIMARY KEY, c FLOAT) ENGINE=InnoDB; Query OK, 0 rows affected (0.00 sec) mysql> INSERT….000050 | | copy to tmp table | 61.816063 | | rename result table | 0.161528 | | end | 0.000007 | | Waiting for query cache lock | 0.000002 | | end…

Filed Under: Benchmarks, Insight for DBAs, MySQL

Post: MySQL Users Conference - Innodb

May 9, 2007 By 6 Comments

… table. This was born same as bunch of others due to statement level MySQL Replication which among other requirements, needs all auto-increment… In MySQL 5.1+ it is also possible to fix this problem for cases when number of rows in auto-increment batch…, why large stripes are better and why battery backed up cache is so important. I also usually speak about these in…

Filed Under: Events and Announcements Tagged With:

Post: InnoDB vs MyISAM vs Falcon benchmarks - part 1

January 8, 2007 By 117 Comments

…table “normal” table structure which corresponds to typical structure you would see in OLTP or Web applications – medium size rows, auto increment…cache_size=1500M –max-connections=1500 –table-cache…

Filed Under: Benchmarks Tagged With: ,

Post: Multi Range Read (MRR) in MySQL 5.6 and MariaDB 5.5

March 21, 2012 By 10 Comments

… Suppose you have the following query executed on the InnoDB table: SELECT non_key_column FROM tbl WHERE key_column=… of MySQL 5.5 and ~5 hours in case of MySQL 5.6 and MariaDB 5.5. Note that query cache …its only incremented in MariaDB 5.5 and not in MySQL 5.6, is that because of a bug in MySQL 5….

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

Post: How Percona does a MySQL Performance Audit

November 24, 2008 By 17 Comments

…incremental…connects | 29 | | Binlog_cache_disk_use | 0 | | Binlog_cache_use | …tables, I’ll paste in mysql> show global variables like ‘%table_size%’; +———————+———–+ | Variable_name | Value | +———————+———–+ | max_heap_table_size | 268435456 | | tmp_table…

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