… BigData with a talk titled, “Conquering BigData with MySQL and Shard Query” Daniel Nichter, lead of the Percona Toolkit development team… – as well as go into more detail about using pt-table-checksum tool to verify replication integrity And finally, Andrew Ferlitsch…
Post: Shard-Query EC2 images available
May 11, 2011 By 2 Comments
… one shard. The schema is duplicated 20 times (but not the ontime_fact data): DROP TABLE IF EXISTS `dim_airport`; CREATE TABLE `dim… cluster of 1 to 20 nodes. cd shard-query #generate a config for 20 shards (adjust to your number of nodes) php… output from the ./run_query command, called pivot_results cd shard-query/ $ ./run_query < queries.sql | tee raw |./pivot_results & [1…
Post: Shard-Query turbo charges Infobright community edition (ICE)
May 6, 2011 By 23 Comments
… is a completely denormalized schema (single table). In order to demonstrate the power of Shard-Query it is important to test complex… subset of the data, performance can be improved significantly: $ cat shards.ini [default] user=mysql db=ontime password= port=5029 column… described in another post. Complex queries Shard-Query was tested with the simple single table version of this data set in a…
Post: Distributed Set Processing with Shard-Query
May 14, 2011 By 27 Comments
… give a quick preview, then explain exactly how Shard-Query works.Yes, Shard-Query scales very well at 20 nodes Distributed set… incremental materialized view optimizations can be applied. Shard-Query works only on sets Shard-Query takes relational algebra to its logical maximum… IN (2008,2009) group by 1; –PARALLEL OPTIMIZATIONS: * Base level table name: `aggregation_tmp#74082863` * IN list optimization enabled * Detected an…
Comment: Sharing an auto_increment value across multiple MySQL tables
October 5, 2010 By 9 Comments
… each shard (if you need to identify insert order, for example to return only N most recent items): create table some_sharded_table( shard_key int, some_child_key bigint auto_increment not null, key(some_child_key), primary key (shard_key, some… shards. You can then partition `some_sharded_table` nicely: alter table some_sharded_table partition by hash(shard_key) partitions 16; All the values related to one shard…
Post: Why you don't want to shard.
August 6, 2009 By 34 Comments
… have established that you need to shard there are quite a few options available to you: Sharding Partitioning by Application Function – This… unless you have a single table which by itself can’t fit into the above constraints. Sharding by hash or key – This method works by picking a column on a table and try and divide…
Post: Shard-Query adds parallelism to queries
November 15, 2010 By 18 Comments
…data used in this earlier post into a partitioned InnoDB table. Partitioning example: /*!50500 PARTITION BY RANGE COLUMNS(FlightDate) (PARTITION p2…solid #CCC;border-bottom:1px solid #CCC;} .Query NumberNo shard-query16 workersNo shard-query16 workers.14.6610.864.6605691909790.8596088886261.284.36219…
Post: Sharding and Time Base Partitioning
March 14, 2008 By 12 Comments
For large number of online applications once you implemented proper sharding you can consider your scaling problems solved – by getting more… most optimal way by itself to do things. The “classical” sharding involves partitioning by user_id,site_id or somethat similar… version creation. It may make sense to separate data on table (or partition) level to get better “clustering” – because data is…
Post: Getting History of Table Sizes in MySQL
December 8, 2010 By 13 Comments
…` bigint(21) unsigned DEFAULT NULL, PRIMARY KEY(DAY,TABLE_SCHEMA,TABLE_NAME), KEY(TABLE_SCHEMA,TABLE_NAME) ) ENGINE=INNODB DEFAULT CHARSET=utf8; And use… for individual table you will have it handy. If you’re having large number of MySQL servers, especially in Sharded environment it… nice queries to see how evenly data is distributed among shards etc. In replicated environment you should put this cron on…
Post: Researching your MySQL table sizes
March 17, 2008 By 28 Comments
… by particular table “type” in sharded environment when multiple tables with same structure and similar name exists: SELECT count(*) tables, concat(round(sum(table_rows… (0.03 sec) Find biggest databases SELECT count(*) tables, table_schema,concat(round(sum(table_rows)/1000000,2),’M') rows, concat(round(sum…
