You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/10/11 07:56:50 UTC
[GitHub] [shardingsphere] TeslaCN opened a new issue #12985: OOM occurred when testing PostgreSQL Proxy with BenchmarkSQL
TeslaCN opened a new issue #12985:
URL: https://github.com/apache/shardingsphere/issues/12985
## Bug Report
### Which version of ShardingSphere did you use?
master 1beb26125aff7ea315ec9e23f8aee5d9f2878052
### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
Proxy
### Expected behavior
### Actual behavior
```
-Xms256m -Xmx256m -Xmn128m -XX:+HeapDumpOnOutOfMemoryError
```
```
[INFO ] 2021-10-11 15:40:45.674 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Logic SQL: SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = ? AND d_id = ? ) ) AS L
[INFO ] 2021-10-11 15:40:45.674 [Connection-2-ThreadExecutor] ShardingSphere-SQL - SQLStatement: PostgreSQLSelectStatement(limit=Optional.empty, lock=Optional.empty, window=Optional.empty)
[INFO ] 2021-10-11 15:40:45.674 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_0 ::: SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = ? AND d_id = ? ) ) AS L ::: [20, 15, 20, 7]
[INFO ] 2021-10-11 15:40:45.674 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_1 ::: SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = ? AND d_id = ? ) ) AS L ::: [20, 15, 20, 7]
[INFO ] 2021-10-11 15:40:45.674 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_2 ::: SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = ? AND d_id = ? ) ) AS L ::: [20, 15, 20, 7]
[INFO ] 2021-10-11 15:40:45.674 [Connection-2-ThreadExecutor] ShardingSphere-SQL - Actual SQL: ds_3 ::: SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock WHERE s_w_id = ? AND s_quantity < ? AND s_i_id IN ( SELECT ol_i_id FROM bmsql_district JOIN bmsql_order_line ON ol_w_id = d_w_id AND ol_d_id = d_id AND ol_o_id >= d_next_o_id - 20 AND ol_o_id < d_next_o_id WHERE d_w_id = ? AND d_id = ? ) ) AS L ::: [20, 15, 20, 7]
java.lang.OutOfMemoryError: Java heap space
Dumping heap to java_pid58369.hprof ...
Heap dump file created [484944992 bytes in 1.617 secs]
Exception in thread "Connection-2-ThreadExecutor" java.lang.OutOfMemoryError: Java heap space
at java.base/java.lang.Integer.valueOf(Integer.java:1081)
at org.postgresql.jdbc.PgResultSet.internalGetObject(PgResultSet.java:183)
at org.postgresql.jdbc.PgResultSet.getObject(PgResultSet.java:2572)
at com.zaxxer.hikari.pool.HikariProxyResultSet.getObject(HikariProxyResultSet.java)
at org.apache.shardingsphere.infra.executor.sql.execute.result.query.impl.driver.jdbc.type.stream.JDBCStreamQueryResult.getValue(JDBCStreamQueryResult.java:87)
at org.apache.shardingsphere.infra.executor.sql.federate.original.row.FilterableRowEnumerator.setCurrentRow(FilterableRowEnumerator.java:79)
at org.apache.shardingsphere.infra.executor.sql.federate.original.row.FilterableRowEnumerator.moveNext0(FilterableRowEnumerator.java:64)
at org.apache.shardingsphere.infra.executor.sql.federate.original.row.FilterableRowEnumerator.moveNext(FilterableRowEnumerator.java:56)
at org.apache.calcite.linq4j.EnumerableDefaults$17$1.moveNext(EnumerableDefaults.java:2821)
at org.apache.calcite.linq4j.TransformedEnumerator.moveNext(TransformedEnumerator.java:35)
at org.apache.calcite.linq4j.EnumerableDefaults.toLookup_(EnumerableDefaults.java:3602)
at org.apache.calcite.linq4j.EnumerableDefaults.toLookup(EnumerableDefaults.java:3594)
at org.apache.calcite.linq4j.EnumerableDefaults.toLookup(EnumerableDefaults.java:3570)
at org.apache.calcite.linq4j.DefaultEnumerable.toLookup(DefaultEnumerable.java:748)
at org.apache.calcite.linq4j.EnumerableDefaults$7.enumerator(EnumerableDefaults.java:1422)
at org.apache.calcite.linq4j.EnumerableDefaults.groupBy_(EnumerableDefaults.java:978)
at org.apache.calcite.linq4j.EnumerableDefaults.groupBy(EnumerableDefaults.java:781)
at org.apache.calcite.linq4j.DefaultEnumerable.groupBy(DefaultEnumerable.java:311)
at Baz.bind(Unknown Source)
at org.apache.calcite.jdbc.CalcitePrepare$CalciteSignature.enumerable(CalcitePrepare.java:363)
at org.apache.calcite.jdbc.CalciteConnectionImpl.enumerable(CalciteConnectionImpl.java:333)
at org.apache.calcite.jdbc.CalciteMetaImpl._createIterable(CalciteMetaImpl.java:578)
at org.apache.calcite.jdbc.CalciteMetaImpl.createIterable(CalciteMetaImpl.java:569)
at org.apache.calcite.avatica.AvaticaResultSet.execute(AvaticaResultSet.java:182)
at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:64)
at org.apache.calcite.jdbc.CalciteResultSet.execute(CalciteResultSet.java:43)
at org.apache.calcite.avatica.AvaticaConnection.executeQueryInternal(AvaticaConnection.java:573)
at org.apache.calcite.avatica.AvaticaPreparedStatement.executeQuery(AvaticaPreparedStatement.java:137)
at org.apache.shardingsphere.infra.executor.sql.federate.original.OriginalFilterableExecutor.execute(OriginalFilterableExecutor.java:87)
at org.apache.shardingsphere.infra.executor.sql.federate.original.OriginalFilterableExecutor.executeQuery(OriginalFilterableExecutor.java:77)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.federateExecute(ProxySQLExecutor.java:162)
at org.apache.shardingsphere.proxy.backend.communication.ProxySQLExecutor.execute(ProxySQLExecutor.java:134)
```
### Reason analyze (If you can)
This SQL was not executed by calcite before.
Each `bmsql_stock` contains 600,000 rows and each `bmsql_order_line` contains about 2,300,000 rows.
### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
Testing PostgreSQL Proxy with BenchmarkSQL.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero closed issue #12985: OOM occurred when testing PostgreSQL Proxy with BenchmarkSQL
Posted by GitBox <gi...@apache.org>.
tristaZero closed issue #12985:
URL: https://github.com/apache/shardingsphere/issues/12985
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #12985: OOM occurred when testing PostgreSQL Proxy with BenchmarkSQL
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #12985:
URL: https://github.com/apache/shardingsphere/issues/12985#issuecomment-940624536
I found this issue occurs since this commit 2f2497397497c8c8f090630fba5309d8551133e9.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #12985: OOM occurred when testing PostgreSQL Proxy with BenchmarkSQL
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12985:
URL: https://github.com/apache/shardingsphere/issues/12985#issuecomment-939984539
@TeslaCN I noticed that these tables all have the same sharding algorithm, can we configure them as binding tables? The query optimization function is currently not well for filter and optimize, which will take up a lot of memory.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #12985: OOM occurred when testing PostgreSQL Proxy with BenchmarkSQL
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #12985:
URL: https://github.com/apache/shardingsphere/issues/12985#issuecomment-939989877
Yes, I made a mistake and I will check it again.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #12985: OOM occurred when testing PostgreSQL Proxy with BenchmarkSQL
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #12985:
URL: https://github.com/apache/shardingsphere/issues/12985#issuecomment-940063203
`tableCreates.sql`
```sql
create table bmsql_config (
cfg_name varchar(30) primary key,
cfg_value varchar(50)
);
create table bmsql_warehouse (
w_id integer not null,
w_ytd decimal(12,2),
w_tax decimal(4,4),
w_name varchar(10),
w_street_1 varchar(20),
w_street_2 varchar(20),
w_city varchar(20),
w_state char(2),
w_zip char(9)
);
create table bmsql_district (
d_w_id integer not null,
d_id integer not null,
d_ytd decimal(12,2),
d_tax decimal(4,4),
d_next_o_id integer,
d_name varchar(10),
d_street_1 varchar(20),
d_street_2 varchar(20),
d_city varchar(20),
d_state char(2),
d_zip char(9)
);
create table bmsql_customer (
c_w_id integer not null,
c_d_id integer not null,
c_id integer not null,
c_discount decimal(4,4),
c_credit char(2),
c_last varchar(16),
c_first varchar(16),
c_credit_lim decimal(12,2),
c_balance decimal(12,2),
c_ytd_payment decimal(12,2),
c_payment_cnt integer,
c_delivery_cnt integer,
c_street_1 varchar(20),
c_street_2 varchar(20),
c_city varchar(20),
c_state char(2),
c_zip char(9),
c_phone char(16),
c_since timestamp,
c_middle char(2),
c_data varchar(500)
);
create sequence bmsql_hist_id_seq;
create table bmsql_history (
hist_id integer,
h_c_id integer,
h_c_d_id integer,
h_c_w_id integer,
h_d_id integer,
h_w_id integer,
h_date timestamp,
h_amount decimal(6,2),
h_data varchar(24)
);
create table bmsql_new_order (
no_w_id integer not null,
no_d_id integer not null,
no_o_id integer not null
);
create table bmsql_oorder (
o_w_id integer not null,
o_d_id integer not null,
o_id integer not null,
o_c_id integer,
o_carrier_id integer,
o_ol_cnt integer,
o_all_local integer,
o_entry_d timestamp
);
create table bmsql_order_line (
ol_w_id integer not null,
ol_d_id integer not null,
ol_o_id integer not null,
ol_number integer not null,
ol_i_id integer not null,
ol_delivery_d timestamp,
ol_amount decimal(6,2),
ol_supply_w_id integer,
ol_quantity integer,
ol_dist_info char(24)
);
create table bmsql_item (
i_id integer not null,
i_name varchar(24),
i_price decimal(5,2),
i_data varchar(50),
i_im_id integer
);
create table bmsql_stock (
s_w_id integer not null,
s_i_id integer not null,
s_quantity integer,
s_ytd integer,
s_order_cnt integer,
s_remote_cnt integer,
s_data varchar(50),
s_dist_01 char(24),
s_dist_02 char(24),
s_dist_03 char(24),
s_dist_04 char(24),
s_dist_05 char(24),
s_dist_06 char(24),
s_dist_07 char(24),
s_dist_08 char(24),
s_dist_09 char(24),
s_dist_10 char(24)
);
```
`indexCreates.sql`
```sql
alter table bmsql_warehouse add constraint bmsql_warehouse_pkey
primary key (w_id);
alter table bmsql_district add constraint bmsql_district_pkey
primary key (d_w_id, d_id);
alter table bmsql_customer add constraint bmsql_customer_pkey
primary key (c_w_id, c_d_id, c_id);
create index bmsql_customer_idx1
on bmsql_customer (c_w_id, c_d_id, c_last, c_first);
alter table bmsql_oorder add constraint bmsql_oorder_pkey
primary key (o_w_id, o_d_id, o_id);
create unique index bmsql_oorder_idx1
on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);
alter table bmsql_new_order add constraint bmsql_new_order_pkey
primary key (no_w_id, no_d_id, no_o_id);
alter table bmsql_order_line add constraint bmsql_order_line_pkey
primary key (ol_w_id, ol_d_id, ol_o_id, ol_number);
alter table bmsql_stock add constraint bmsql_stock_pkey
primary key (s_w_id, s_i_id);
alter table bmsql_item add constraint bmsql_item_pkey
primary key (i_id);
```
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] TeslaCN commented on issue #12985: OOM occurred when testing PostgreSQL Proxy with BenchmarkSQL
Posted by GitBox <gi...@apache.org>.
TeslaCN commented on issue #12985:
URL: https://github.com/apache/shardingsphere/issues/12985#issuecomment-939988749
> @TeslaCN I noticed that these tables all have the same sharding algorithm, can we configure them as binding tables? The query optimization function is currently not well for filter and optimize, which will take up a lot of memory.
Hi @strongduanmu
The tables in the SQL are already binding tables.
--
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org