I will check the salting option. About the bug you have mentioned we are using the master version 3.0.0 From 29.01, is it relevant?
From: James Taylor [mailto:[email protected]] Sent: Thursday, February 27, 2014 08:18 To: [email protected] Subject: Re: performance issues Hi Dana, Comments inline below. Thanks, James On Wed, Feb 26, 2014 at 11:00 AM, Livni, Dana <[email protected]<mailto:[email protected]>> wrote: Hi, I have a table with 50M records (primary key is compose of 4 columns) and there is only one other column. The definition of the table is: CREATE TABLE MY_TABLE ("key1" VARCHAR, "key2" VARCHAR, "key3" VARCHAR, "key4" VARCHAR,"data"."valueColumn" VARCHAR CONSTRAINT pk PRIMARY KEY ("key1","key2","key3","key4")); I have several issues: 1. when preforming a query on this table using the 3 first keys: select count(*) from MY_TABLE where (("key1","key2","key3") in (('k1','k2','k3')); this query counts 1.5M records and it run time is about 3 seconds, does it sounds reasonable? Is there a way to improve it? I'd play around with two things: salting (http://phoenix.incubator.apache.org/salted.html) and your block size. Just tack on a SALT_BUCKETS=16 to your DDL and rebuild your table. Salting helps by evenly distributing your read and write count. You could alternatively pre-split your table by adding a SPLIT ON clause to the end of your DDL. Setting the block size lower is a trade off. It helps random read times, but makes compaction more expensive as your table grows. Is there any hbase phoenix configuration I should play with? Any other ideas? 2. if I change the query to use other aggregative function select min("valueColumn") from MY_TABLE where (("key1","key2","key3") in (('k1','k2','k3')); the run time increase significantly to around 8 seconds I can see in the execution plan that the different comes from not using the first row key filter, is there a way to improve the performance? At least make it the same as the count query. There was a bug here, causing a range scan to occur instead of a skip scan for row value constructors. Can you try with the latest RC at http://people.apache.org/~jamestaylor/phoenix-2.2.3-incubating-RC3/ and let us know if you see improvements? The explain plan should say SKIP SCAN instead of RANGE SCAN. 3. When I use multiple in phrases: select count(*) from MY_TABLE where (("key1","key2","key3") in (('k1','k2','k3'), ('k4','k5','k6')); This query return twice the record size (3M records) but in 4th the time around 14 seconds. I can see that the execution plan changes from RANGE SCAN OVER DEVICE_MEASUREMENT ['k1','k2','k3'] - [*] SERVER FILTER BY FIRST KEY ONLY To RANGE SCAN OVER DEVICE_MEASUREMENT ['k1\x00k2\x00k3'] - ['k4\x00k5\x00k6'] SERVER FILTER BY FIRST KEY ONLY AND (key1, key2, key3) IN ([114,101,112,111,114,116,0,72,117,109,105,100,105,116,121,0,100,97,110,97,49,48,126,115,101,108,102],[114,101, Is there a way to make it use the scan differently? am I doing something wrong? Again is there a way to improve the execution time? Same as above - try with the latest. 4. joins: When trying to perform a join between this big table (50M records) to a much smaller table (10 records) The structure of the two tables is: CREATE TABLE MY_TABLE ("key1" VARCHAR, "key2" VARCHAR, "key3" VARCHAR, "key4" VARCHAR,"data"."valueColumn" VARCHAR CONSTRAINT pk PRIMARY KEY ("key1","key2","key3","key4")); CREATE TABLE SMALL_TABLE ("key1" VARCHAR, "key3" VARCHAR,"data"."property" VARCHAR CONSTRAINT pk PRIMARY KEY ("key1","key3")); Each record in the small table have approximately 4.5M records connected to it. I'm using the master version from the 29-01. When trying to run the following join: Select count(*) from SMALL_TABLE a join MY_TABLE b on a."key1"=b."key1" and a."key3"=b."key3" Where b."property" = 'val' I can see in the execution plan that a full san is being preform on the big table (MY_TABLE) hence the run time is very long around 1min. Put the smaller table last in the join. Since we don't have stats yet to drive this, you should list tables from biggest to smallest, as they're processed in reverse order. I have tried to add an index on the small table on the property column but it didn't change the fact that a full scan was preform on the big table and the run time stayed long. Is there a way to improve this? Are there any big changes expected in this area in future versions? Any estimations when? Thanks in advance Dana. HTH. Thanks, James --------------------------------------------------------------------- Intel Electronics Ltd. This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies. --------------------------------------------------------------------- Intel Electronics Ltd. This e-mail and any attachments may contain confidential material for the sole use of the intended recipient(s). Any review or distribution by others is strictly prohibited. If you are not the intended recipient, please contact the sender and delete all copies.
