June 19, 2013

Post: Are you designing IO bound or CPU bound application ?

December 20, 2006 By 10 Comments

… hits the most is there a lot of rows are analyzed – count queries, group by, order by without indexes, search queries etc. Basically as soon as… as use total number of messages in the mailbox at least to draw “pager”. Easy solution is to do select count(*) from messages where…

Filed Under: Insight for Developers Tagged With:

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

March 21, 2012 By 10 Comments

…of ‘pk_column’ which is a part of the secondary key tuple, a point primary key lookup is made against base table, the number of…group…in the query times, which is lower by a couple of seconds when buffer size of…

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

Post: How well does your table fits in innodb buffer pool ?

December 9, 2010 By 12 Comments

… queries. MySQL Server does not provide any information of this type, Percona Server however adds number of…COUNT(*) cnt, SUM(dirty = 1) dirty, SUM(hashed = 1) hashed FROM innodb_buffer_pool_pages_index GROUP…planning/performance management. In many cases you would learn you need a certain fit to …

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

Post: Sphinx search performance optimization: multi-threaded search

January 16, 2013 By 10 Comments

…nodes (even if local ones) and in parallel, merging of results is done by the …COUNT with GROUP BY and if data nodes return large amounts of data to post-process, aggregator may well become a…your Sphinx query log and look at the number of results matched per query – this should give you a clue….

Filed Under: MySQL Tagged With: , , ,

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

March 11, 2013 By 7 Comments

…results in 587MB of raw data, mostly in one  table (lineorder). Each of the 13 queries were executed serially in a single connection I modified the queries …count(*) – sum(is_old=’YES’) hot, sum(number_records) records from information_schema.innodb_buffer_page_lru where table_name like ‘%ssb%’ group…

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

Post: Analyzing air traffic performance with InfoBright and MonetDB

October 2, 2009 By 50 Comments

…but I was not successful in this. I tried to analyze a bigger dataset and I took…of rows in each chunk is 483762.46 (the query Q0 is: select avg(c1) from (select year,month,count(*) as c1 from ontime group…of RAM and Dual Intel(R) Xeon(TM) CPU 3.40GHz. InfoBright (ICE) version: 5.1.14-log build number…

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

Comment: Implementing efficient counters with MySQL

July 2, 2007 By 16 Comments

…could group these into one query like this: UPDATE table SET count = count + 3 WHERE id IN(…); This greatly reduces the number of update queries. During rush hours you may want to introduce a threshold…

Filed Under: Insight for Developers Tagged With: ,

Post: Advanced index analysis with mk-index-usage

November 11, 2010 By 2 Comments

…query these tables any way you want. The documentation includes a number of other canned queries you can run. I found a…

Filed Under: Insight for DBAs, MySQL

Post: Researching your MySQL table sizes

March 17, 2008 By 28 Comments

…query to look into few more aspects of data sizes so here it goes: Find total number of tables, rows, total data in… FROM information_schema.TABLES GROUP BY table_schema …query a bit and get most popular storage engines by number of tables or number of rows instead of data stored. SELECT engine, count…

Filed Under: Insight for DBAs Tagged With: , ,

Post: Fun with the MySQL pager command

January 21, 2013 By 6 Comments

…COUNT(*) | +———-+ | 320 | +———-+ and counting the number of connection for each status can be done with: mysql> SELECT COMMAND,COUNT(*) TOTAL FROM INFORMATION_SCHEMA.PROCESSLIST GROUP…

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