June 18, 2013

Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark

March 11, 2013 By 7 Comments

… Default config for MySQL 5.5 and MySQL 5.6, no tuning at all Config 2: MySQL 5.6 … with 512MB write-back cache  Star Schema Benchmark – Scale Factor 1 – Mysql 5.5 vs 5….pages afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name…

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

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

February 26, 2013 By 1 Comment

… editions of MySQL 5.5.30 and MySQL 5.6.10 with no tuning whatsoever (…—-. 1 mysql mysql 98304 Feb 20 16:09 FTS_00000000000000ad_DELETED_CACHE.ibd -rw-rw—-. 1 mysql mysql …mysql> set global innodb_ft_aux_table=’test/dir_test_innodb’; mysql> set global innodb_optimize_fulltext_only=1; mysql> select…

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

Post: MySQL: Followup on UNION for query optimization, Query profiling

August 14, 2006 By 14 Comments

… sec) mysql> explain SELECT sql_no_cache name FROM people WHERE age in(18,19,20) AND zip IN (12345,12346, 12347); +—-+————-+——–+——-+—————+——+———+——+——+————-+ | id | select_type… – very helpful for MySQL Performance optimization: mysql> flush status; Query OK, 0 rows affected (0.00 sec) mysql> SELECT sql_no_cache name FROM people WHERE…

Filed Under: Insight for Developers Tagged With: ,

Post: MySQL-Memcached or NOSQL Tokyo Tyrant - part 1

October 15, 2009 By 16 Comments

…people force themselves into using a database like MySQL with no thought into whether if its the best…allocated to memcached to be successful. This application selects a random # between 1 and 2 Million … disk, expunge record 500,001, cache record 1 you keep overwriting the cache before you can use it…

Filed Under: Benchmarks, Cloud and NoSQL, MySQL Tagged With: , ,

Post: Using UNION to implement loose index scan in MySQL

August 10, 2006 By 15 Comments

…. Lets see how query times differ in these cases: mysql> SELECT sql_no_cache name FROM people WHERE age=19 AND zip IN (12345…); +———————————-+ | name | +———————————-+ | 888ba838661aff00bbbce114a2a22423 | +———————————-+ 1 row in set (0.06 sec) mysql> SELECT SQL_NO_CACHE name FROM people WHERE age BETWEEN 18 AND 22 AND…

Filed Under: Insight for Developers Tagged With: ,

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

February 12, 2009 By 17 Comments

…in one call. And what about the query cache and the table cache and the size needed for each thread…laptop: mysql> set @a := repeat(‘a’, 1024 * 1024 * 100); Query OK, 0 rows affected (2.34 sec) mysql> select get_… be because there are some things that have no defined limits. It’s not something that’s …

Filed Under: Insight for DBAs Tagged With:

Post: A micro-benchmark of stored routines in MySQL

August 16, 2009 By 7 Comments

… not in MySQL 5.1 and earlier. I loaded the World sample database and cooked up this query: select sql_no_cache sum(ci.Population) from City as ci where CountryCode in ( select distinct co.Code from Country….Code = c; > return res; > end// mysql> delimiter ; Now the query can be rewritten as this: mysql> select sql_no_cache sum(ci.Population) from City…

Filed Under: Benchmarks Tagged With: , , ,

Post: How expensive is a WHERE clause in MySQL?

October 31, 2008 By 17 Comments

… MySQL. The table ended up at 237MB. Now let’s see how long a table scan with no WHERE clause takes: mysql> select sql_no_cache… trivial WHERE clause: mysql> select count(*) from t where a = current_date; (Pop quiz: do I need SQL_NO_CACHE on this query?) The…% cost for the query. If I add another WHERE clause, mysql> select count(*) from t where a = current_date and left(a…

Filed Under: Benchmarks, Insight for DBAs Tagged With:

Post: Connecting orphaned .ibd files

May 13, 2011 By 19 Comments

…equal to 15 in all actor’s indexes: mysql> select * from INNODB_SYS_INDEXES WHERE TABLE_ID = 13G ***************************… the file FIL_PAGE_ARCH_LOG_NO_OR_SPACE_NO 4 /* starting from 4.1.x…sakila/customer.ibd’ InnoDB: to the tablespace memory cache, but tablespace InnoDB: 15 of name ‘./sakila/…

Filed Under: MySQL

Post: Why MySQL could be slow with large tables ?

June 9, 2006 By 141 Comments

…by index: mysql> select count(pad) from large; +————+ | count(pad) | +————+ | 31457280 | +————+ 1 row in set (4 min 58.63 sec) mysql> select count(pad…to memory before join so there is no random IO needed to populate the caches. With proper application architecture and table …

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