Hi Vincent & William,
Below is the explain plan, both are PARALLEL excuted in plan:
explain upsert into table1 select * from table2;
UPSERT
SELECT
|
CLIENT 27-CHUNK 915799 ROWS 2831155510 BYTES PARALLEL 18-WAY ROUND ROBIN
FULL SCAN OVER table2
explain upsert into table1 select * from table2 limit 2000000;
UPSERT
SELECT
|
| CLIENT 27-CHUNK 36000000 ROWS 481140000000 BYTES PARALLEL 18-WAY ROUND
ROBIN FULL SCAN OVER table2 |
| SERVER 2000000 ROW
LIMIT
|
| CLIENT 2000000 ROW LIMIT
Thanks,
Shawn
On Tue, Dec 18, 2018, 13:30 Vincent Poon <vincentp...@apache.org wrote:
> Shawn,
>
> Can you do an "explain" to show what your two statements are doing? That
> might give some clues. Perhaps one is able to be run on the server for
> some reason and the other is not.
> Otherwise, I don't see why one would be substantially slower than the
> other.
>
> Vincent
>
> On Mon, Dec 17, 2018 at 9:14 PM Shawn Li <shawnli...@gmail.com> wrote:
>
>> Hi Jonathan,
>>
>> The single threaded on one side sounds logical to me. Hopefully Vincent
>> can confirm it.
>>
>> Thanks,
>> Shawn
>>
>> On Mon, Dec 17, 2018 at 9:25 PM Jonathan Leech <jonat...@gmail.com>
>> wrote:
>>
>>> My guess is that in order to enforce the limit that it’s effectively
>>> single threaded in either the select or the upsert.
>>>
>>> On Dec 17, 2018, at 6:43 PM, Shawn Li <shawnli...@gmail.com> wrote:
>>>
>>> Hi Vincent,
>>>
>>> Thanks for explaining. That makes much more sense now and it explains
>>> the high memory usage when without "limit" clause. Because it upserts much
>>> quickly when using "upsert select" without "limit", the memory usage in
>>> client machine is much higher than "upsert select" with "limit" .
>>>
>>> So back to the other question. Can you explain what is underlying
>>> Phoenix implementation for "upsert select limit"? Why it is slower than
>>> without "limit" when insert a huge number (2m rows) like ""upsert into
>>> table2 select * from table1 limit 2,000,000;". This is much slower than
>>> inserting the whole table (upsert into table2 select * from table1;).
>>>
>>> Thanks,
>>> Xiang
>>>
>>>
>>> On Mon, Dec 17, 2018 at 1:56 PM Vincent Poon <vincentp...@apache.org>
>>> wrote:
>>>
>>>> Shawn,
>>>> Your query *upsert into table2 select * from table1; *would not be
>>>> run on the server - the source and target table are different. It would
>>>> have to be something like:
>>>> *upsert into table1 select * from table1;*
>>>>
>>>> If you want to run server-side upsert select on a target table that is
>>>> different from the source table, you need to set
>>>> "phoenix.client.enable.server.upsert.select" to true on your client.
>>>> The are some other restrictions: the table can't have any global
>>>> indexes, and the statement can't have a join or where subquery. We need to
>>>> update the documentation with this information.
>>>>
>>>> The reason there are all these hurdles is because it's generally not
>>>> recommended to do server-side upsert select across different tables,
>>>> because that means you're doing cross-regionserver RPCs (e.g. read data
>>>> from a region of sourcetable, and write to a region of targettable on a
>>>> different regionserver), potentially tying up handlers in an unpredictable
>>>> way.
>>>>
>>>> On Sun, Dec 16, 2018 at 7:12 PM Shawn Li <shawnli...@gmail.com> wrote:
>>>>
>>>>> Hi Jaanai,
>>>>>
>>>>> According to Phoenix website, " If auto commit is on, and both a) the
>>>>> target table matches the source table, and b) the select performs no
>>>>> aggregation, then the population of the target table will be done
>>>>> completely on the server-side (with constraint violations logged, but
>>>>> otherwise ignored). Otherwise, data is buffered on the client and, if auto
>>>>> commit is on, committed in row batches as specified by the UpsertBatchSize
>>>>> connection property (or the phoenix.mutate.upsertBatchSize HBase config
>>>>> property which defaults to 10000 rows)"
>>>>>
>>>>> And our sql statement is just: *upsert into table2 select * from
>>>>> table1; *which should match the first case, all operations should be
>>>>> in server site. But the memory usage on the client machine is higher than
>>>>> "upsert select limit" clause. And the memory usage is check by run 'top'
>>>>> command under Linux. So we are sure it is caused by "select upsert" in
>>>>> Phoenix and not others, and can't explain why there is so high memory
>>>>> usage
>>>>> on client/gateway machine when all operations are supposed to happen on
>>>>> the
>>>>> serve side.
>>>>>
>>>>> Thanks,
>>>>> Shawn
>>>>>
>>>>> On Thu, Dec 13, 2018 at 3:15 AM Jaanai Zhang <cloud.pos...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Shawn,
>>>>>>
>>>>>> The UPSERT SELECT will run in a coprocessor on if it hasn't limit
>>>>>> clause, only query 1 table, the query is doing aggregation, no sequences
>>>>>> and auto commit is on. Please check your SQL ... and you can also check
>>>>>> whether some resources have not been released.
>>>>>>
>>>>>> ----------------------------------------
>>>>>> Jaanai Zhang
>>>>>> Best regards!
>>>>>>
>>>>>>
>>>>>>
>>>>>> Shawn Li <shawnli...@gmail.com> 于2018年12月13日周四 下午12:10写道：
>>>>>>
>>>>>>> Hi Jaanai,
>>>>>>>
>>>>>>> Thanks for putting your thought. The behavior you describe is
>>>>>>> correct on the Hbase region sever side. The memory usage for blockcache
>>>>>>> and
>>>>>>> memstore will be high under such high throughput. But our phoenix
>>>>>>> client is
>>>>>>> on a gateway machine (no hbase region server sitting on it or any Hbase
>>>>>>> service on it), so not sure how to explain such high memory usage for
>>>>>>> upsert select without "limit" clause. The high memory usage behavior
>>>>>>> like
>>>>>>> all select results send to client machine, cached in client machine's
>>>>>>> memory, and then insert back to target table, which is not like the
>>>>>>> behavior that should happen, all of this should be done on the server
>>>>>>> side
>>>>>>> as the table schema is exactly the same. By the way, this happens on
>>>>>>> both
>>>>>>> Phoenix 4.7 and Phoenix 4.14.
>>>>>>>
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Shawn
>>>>>>>
>>>>>>> On Wed, Dec 12, 2018 at 10:26 PM Jaanai Zhang <
>>>>>>> cloud.pos...@gmail.com> wrote:
>>>>>>>
>>>>>>>> Shawn,
>>>>>>>>
>>>>>>>>
>>>>>>>> For the upsert without limit, which will read the source table and
>>>>>>>> write the target tables on the server side. I think the higher memory
>>>>>>>> usage is caused by using scan cache and memstore under the higher
>>>>>>>> throughput.
>>>>>>>>
>>>>>>>> ----------------------------------------
>>>>>>>> Jaanai Zhang
>>>>>>>> Best regards!
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>>> Shawn Li <shawnli...@gmail.com> 于2018年12月13日周四 上午10:13写道：
>>>>>>>>
>>>>>>>>> Hi Vincent,
>>>>>>>>>
>>>>>>>>> So you describe limit will sent result to client side and then
>>>>>>>>> write to server, this might explain why upsert with limit is slower
>>>>>>>>> than
>>>>>>>>> without limit. But looks like it can't explain the memory usage? The
>>>>>>>>> memory
>>>>>>>>> usage on client machine is 8gb (without "limit") vs 2gb (with limit),
>>>>>>>>> sometime upsert without "limit" can even reach 20gb for big table.
>>>>>>>>>
>>>>>>>>> Thanks,
>>>>>>>>> Shawn
>>>>>>>>>
>>>>>>>>> On Wed, Dec 12, 2018 at 6:34 PM Vincent Poon <
>>>>>>>>> vincentp...@apache.org> wrote:
>>>>>>>>>
>>>>>>>>>> I think it's done client-side if you have LIMIT. If you have
>>>>>>>>>> e.g. LIMIT 1000 , it would be incorrect for each regionserver to
>>>>>>>>>> upsert
>>>>>>>>>> 100, if you have more than one regionserver. So instead results are
>>>>>>>>>> sent
>>>>>>>>>> back to the client, where the LIMIT is applied and then written back
>>>>>>>>>> to the
>>>>>>>>>> server in the UPSERT.
>>>>>>>>>>
>>>>>>>>>> On Wed, Dec 12, 2018 at 1:18 PM Shawn Li <shawnli...@gmail.com>
>>>>>>>>>> wrote:
>>>>>>>>>>
>>>>>>>>>>> Hi Vincent,
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> The table creation statement is similar to below. We have about
>>>>>>>>>>> 200 fields. Table is mutable and don’t have any index on the table.
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> CREATE TABLE IF NOT EXISTS us_population (
>>>>>>>>>>>
>>>>>>>>>>> state CHAR(2) NOT NULL,
>>>>>>>>>>>
>>>>>>>>>>> city VARCHAR,
>>>>>>>>>>>
>>>>>>>>>>> population BIGINT,
>>>>>>>>>>>
>>>>>>>>>>> …
>>>>>>>>>>>
>>>>>>>>>>> CONSTRAINT my_pk PRIMARY KEY (state));
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> Thanks,
>>>>>>>>>>>
>>>>>>>>>>> Shawn
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>>
>>>>>>>>>>> On Wed, Dec 12, 2018, 13:42 Vincent Poon <vincentp...@apache.org
>>>>>>>>>>> wrote:
>>>>>>>>>>>
>>>>>>>>>>>> For #2, can you provide the table definition and the statement
>>>>>>>>>>>> used? e.g. Is the table immutable, or does it have indexes?
>>>>>>>>>>>>
>>>>>>>>>>>> On Tue, Dec 11, 2018 at 6:08 PM Shawn/Xiang Li <
>>>>>>>>>>>> shawnli...@gmail.com> wrote:
>>>>>>>>>>>>
>>>>>>>>>>>>> Hi,
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> 1. Want to check what is underlying running for limit
>>>>>>>>>>>>> clause used in the following Upsert statement (is it involving any
>>>>>>>>>>>>> coprocessor working behind?):
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> * upsert into table2 select *
>>>>>>>>>>>>> from table1 limit 3000000; * (table 1 and table 2 have same
>>>>>>>>>>>>> schema)
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> The above statement is running a lot slower than
>>>>>>>>>>>>> without “limit” clause as shown in following, even the above
>>>>>>>>>>>>> statement
>>>>>>>>>>>>> upsert less data:
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> * upsert into table2 select *
>>>>>>>>>>>>> from table1;*
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> 2. We also observe memory usable is pretty high without
>>>>>>>>>>>>> the limit clause (8gb vs 2gb), sometimes for big table it can
>>>>>>>>>>>>> reach 20gb
>>>>>>>>>>>>> without using limit clause. According to phoenix website
>>>>>>>>>>>>> description for
>>>>>>>>>>>>> upsert select “If auto commit is on, and both a) the target table
>>>>>>>>>>>>> matches
>>>>>>>>>>>>> the source table, and b) the select performs no aggregation, then
>>>>>>>>>>>>> the
>>>>>>>>>>>>> population of the target table will be done completely on the
>>>>>>>>>>>>> server-side
>>>>>>>>>>>>> (with constraint violations logged, but otherwise ignored).”
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> My question is If everything is done on
>>>>>>>>>>>>> server-side, how come we have such high memory usage on the
>>>>>>>>>>>>> client machine?
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>>
>>>>>>>>>>>>> Thanks,
>>>>>>>>>>>>>
>>>>>>>>>>>>> Shawn
>>>>>>>>>>>>>
>>>>>>>>>>>>
