… cases where optimizing a query automatically would be more costly than just running the query in a suboptimal form. Q: Doesn’t the primary key solution for random selection…count the movies per production year for each kind_id. It was more efficient in this case to force MySQL…
Post: Benchmarking Percona Server TokuDB vs InnoDB
May 7, 2013 By 32 Comments
… strong side of TokuDB: it has more than 4x data compression on this dataset….where `id` is sequential, and `hid`,`mid` is low selectivity is not good for fast inserts, but it is suitable for range selects…-table-size=10000 –mysql-user=root –oltp-tables-count=32 –mysql_table_engine=tokudb –oltp_…
Post: The case for getting rid of duplicate “sets”
May 17, 2011 By 2 Comments
… in sets, which can be much faster than processing it serially. When the relational database… 10 times, and then then ten times more? Of course not. You did simple arithmetic…mysql> delete from data where val=16; Query OK, 1 row affected (3.14 sec) mysql> select val, count(distinct id) from data where…
Post: How to find MySQL queries worth optimizing ?
September 11, 2012 By 6 Comments
… only. What is about queries which query more than one table ? # Time: 120911 17:25:22 # …select * from sbtest a,sbtest b where a.id=5 and b.id=a.k; mysql> explain select * from sbtest a,sbtest b where…0 # InnoDB_trx_id: 12F24 SET timestamp=1347399108; select count(*) from sbtest group by k; This only…
Post: Modeling MySQL Capacity by Measuring Resource Consumptions
March 8, 2011 By 1 Comment
…procfs for MySQL process: root@ubuntu:/var/log/mysql# cat …select queries which is about 270us per select query…avg 95% stddev median # ============ === ======= ======= ======= ======= ======= ======= ======= # Count 98 1753697 # … range are ones where no IO … (less than 10 ms) or needed more than that….
Post: Troubleshooting MySQL Memory Usage
March 21, 2012 By 5 Comments
…MySQL takes much more memory than you would expect, causing swapping or other problems. This simple problem on the surface becomes challenge with MySQL… look at Prepared_stmt_count to see how many …tables: mysql> select sum(data_length+index_length) from information_schema.tables where engine=’memory…
Post: Percona Replication Manager, a solution for MySQL high availability with replication using Pacemaker
November 29, 2011 By 102 Comments
…to a level where we started looking at other ways of achieving higher availability using MySQL replication. …lag in seconds, if a slave lags by more than that value, it will lose its reader VIP… MySQL. Default is to run select count(*) on mysql.user table, so the user given should at least have select…
Post: MySQL 5.6 vs MySQL 5.5 and the Star Schema Benchmark
March 11, 2013 By 7 Comments
… set of data which is more rich than the simple sysbench table. The…mysql> select sq.*, pages / (@@innodb_buffer_pool_size / 16384) * 100 pct_buffer_pool from ( select table_name, index_name, count…Using where 2 rows in set (0.00 sec) mysql> select * from information_schema.global_status where…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
…: mysql> select count(*), count(distinct UniqueCarrier) from dim_flightG *************************** 1. row *************************** count(*): 58625 count(distinct… by spreading them over more than one physical server. This is …id) WHERE DepDelay>10 GROUP BY Year) t JOIN (select Year, count(*) …
Post: Flexviews - part 3 - improving query performance using materialized views
April 4, 2011 By 20 Comments
…as stored in the data dictionary: mysql> select * from flexviews.mview_table where mview_id= flexviews.get_id…more quickly than COUNT(*). mysql> select count(*) cnt from order_lines\G *************************** 1. row *************************** cnt: 155187034 1 row in set (32.03 sec) mysql> select…
