June 18, 2013

Post: Computing 95 percentile in MySQL

November 28, 2008 By 5 Comments

… response time in terms of 95/99 percentile and get your logs in the table, so how to get the data if MySQL only provides you the avg: mysql> select count(*),avg(wtime) from performance_log_081128 where page_type=’search’; +———-+—————–+ | count(*) | avg…

Filed Under: Insight for Developers Tagged With:

Post: How to convert MySQL's SHOW PROFILES into a real profile

February 20, 2012 By 12 Comments

…the Sakila sample database to demonstrate: mysql> SET profiling=1; mysql> pager cat > /dev/null mysql> SELECT * FROM nicer_but_slower_film_list; 997 rows in…SELECT SUM(DURATION) FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID = @query_id ), 2) AS Pct_R, COUNT(*) AS Calls, SUM(DURATION) / COUNT…

Filed Under: Insight for Developers, MySQL

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

March 11, 2013 By 7 Comments

…how long a page must be on the cold portion of the LRU before it is eligible to be moved to the hot portion.  In MySQL…the buffer pool in pages afterwards: mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count…

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

Post: The case for getting rid of duplicate “sets”

May 17, 2011 By 2 Comments

…in sets. This seems hard to fathom with the way we are used to dealing with data in a computer, but intuitive when you “think about how… 1 row in set (0.00 sec) mysql> select count(*) from ex1; +———-+ | count(*) | +———-+ | 73027220 | +———-+ 1 row in set (0.00 sec) mysql> select sum(…

Filed Under: MySQL

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

February 26, 2013 By 1 Comment

…in MySQL 5.6 (part 1) in MySQL 5.6 is syntactically identical to MyISAM full-text search, in the sense that the SQL required to…to find out how to make InnoDB FTS blazingly-fast, but simply to get a sense of how it works compared to…in set (4.60 sec) mysql> select…

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

Post: Distributed Set Processing with Shard-Query

May 14, 2011 By 27 Comments

…the database what you want but not how to get it. Most distributed engines work with rows. They break the queries up into the…IN (2009) GROUP BY 1 ORDER BY NULL ) — AGGREGATION SQL: SELECT `origin_airport_id`, SUM(`count(*)`) AS `count…

Filed Under: MySQL

Post: Shard-Query adds parallelism to queries

November 15, 2010 By 18 Comments

… one) to demonstrate how much improvement could be made to MySQL so…data in one query, it runs many smaller queries each requesting significantly less data. On the other hand, MySQL…The purpose of this test is to demonstrate that Shard-Query works with GROUP BY and other constructs. Q1 – SELECT count…

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

Post: UDF -vs- MySQL Stored Function

September 6, 2010 By 8 Comments

…the whole table of 7 million records: mysql> select count(*) from paintings where title != ltrim_junk(title); +———-+ | count(*) | +———-+ | 101533 | +———-+ 1 row in set (6.82 sec) mysql> select count…

Filed Under: MySQL Tagged With: , ,

Post: How To Test Your Upgrades - pt-upgrade

December 29, 2011 By 7 Comments

…SELECT queries against multiple MySQL servers and reports on how…to test.  Another is populating spare servers from backup and testing with them. With the former, you can easily verify that data…

Filed Under: Insight for DBAs

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

October 15, 2009 By 16 Comments

…the total data in memory. let’s look at the 1GB Setting: What, a performance regression? But we threw more memory at it!! How…to be helpful. I am still getting a really solid # of hits in memcached, but the # of writes in MySQL coupled with the…

Filed Under: Benchmarks, Cloud and NoSQL, MySQL Tagged With: , ,
« Older Posts
Newer Posts »