Monday, December 13, 2010
One of the new features in dbShards version 2.2.2 is the ability to provide “shard hints” as a comment before any SQL statement. There are a number of hints that can be supplied to tell the dbShards driver where to execute the query.
For example, an application could use shard hints to tell the driver to run certain queries against a secondary shard to reduce load on the primary shard. Other uses for shard hints include forcing an UPDATE or DELETE to be executed as a “global write” that gets replicated across all primary shards.
So what does this look like? Here is a Java code sample:
String sql = "/*DBS_HINT: dbs_shard_action=global_write */ "
+ "DELETE FROM `user` WHERE last_login < ?";
PreparedStatement pstmt = conn.prepareStatement(sql);
pstmt.setDate(1, oldDate);
pstmt.executeUpdate();
Without the shard hint, this DELETE statement would usually fail because the `user` table is sharded on a userID column and the query does not contain a shard key, and by default, dbShards would not allow an UPDATE or DELETE that spans multiple shards as this would not be an ACID-compliant transaction.
This table shows all shard hints that are currently supported.
| Hint Name | Description |
|---|---|
| dbs_shard_action | Possible values are auto, shard_read, shard_write, go_fish, global_write. |
|
|
| dbs_vshard | Virtual shard number. Used in conjunction with shard_read, shard_write, global_write hints. |
| dbs_pshard | Physical shard number. Used in conjunction with shard_read, shard_write, global_write hints. |
| dbs_use_secondary | Enables or disables query execution against a secondary shard. Valid values are ‘true’ and ‘false’. Enabling this option forces the driver to execute the query against the secondary shard. An application may choose to execute some read-only queries against the secondary shard for performance reasons (to take some load off the primary shard). This feature should be used with caution as there is no guarantee that the secondary database is online and available at all times. |
| dbs_auto_incr_support | Enables or disables auto increment handling in the driver. Valid values are ‘true’ and ‘false’. |
| dbs_cache_stmt | This hint is specific to the dbShards JDBC driver. By default, parsed queries are cached to improve performance. This hint can be provided to disable caching of a particular statement. This is recommended for dynamic SQL where the SQL is different each time due to embedded literal values and would not benefit from this type of caching. |
Examples:
Example 1 – Force the driver to execute a SELECT query against a specific virtual shard:
/*DBS_HINT: dbs_shard_action=shard_read, dbs_vshard=1234 */ SELECT * FROM customer WHERE id = 1234
Example 2 – Force the driver to execute a SELECT query against a specific physical shard:
/*DBS_HINT: dbs_shard_action=shard_read, dbs_pshard=2 */ SELECT * FROM customer WHERE id = 1234
Example 3 – Force the driver to execute an UPDATE query against a specific virtual shard:
/*DBS_HINT: dbs_shard_action=shard_read, dbs_vshard=1234 */ UPDATE customer SET active = 0 WHERE id = 1234
Example 4 – Force the driver to execute an UPDATE query against a specific physical shard:
/*DBS_HINT: dbs_shard_action=shard_read, dbs_pshard=2 */ UPDATE customer SET active = 0 WHERE id = 1234
Posted in Tutorial by Andy Grove - No Comments »