zabetak commented on code in PR #6825: URL: https://github.com/apache/hive/pull/6825#discussion_r4133980188
########## standalone-metastore/metastore-server/docker/hive-postgres-tpcds-metastore/postgresql.conf: ########## @@ -0,0 +1,47 @@ +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed with +# this work for additional information regarding copyright ownership. +# The ASF licenses this file to You under the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. + +# general + +listen_addresses = '*' +dynamic_shared_memory_type = posix + +# performance + +work_mem = 256MB +shared_buffers = 128MB + +# reduce WAL and archiving + +checkpoint_timeout=1d +max_wal_size = 64MB +min_wal_size = 32MB +wal_level = minimal +max_wal_senders = 0 +wal_buffers = -1 +archive_mode = off +archive_command = '/bin/true' + +# locale settings + +log_timezone = 'Etc/UTC' +datestyle = 'iso, mdy' +timezone = 'Etc/UTC' +lc_messages = 'en_US.utf8' +lc_monetary = 'en_US.utf8' +lc_numeric = 'en_US.utf8' +lc_time = 'en_US.utf8' +default_text_search_config = 'pg_catalog.english' Review Comment: Why are these locale settings necessary? ########## standalone-metastore/metastore-server/docker/hive-postgres-tpcds-metastore/postgresql.conf: ########## @@ -0,0 +1,47 @@ +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed with +# this work for additional information regarding copyright ownership. +# The ASF licenses this file to You under the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. + +# general + +listen_addresses = '*' +dynamic_shared_memory_type = posix + +# performance + +work_mem = 256MB +shared_buffers = 128MB + +# reduce WAL and archiving + +checkpoint_timeout=1d +max_wal_size = 64MB +min_wal_size = 32MB +wal_level = minimal +max_wal_senders = 0 +wal_buffers = -1 Review Comment: When I start the container, I see lots of entries with the following content in the logs: ``` 2026-09-29 11:46:14.087 UTC [37] LOG: checkpoints are occurring too frequently (2 seconds apart) 2026-09-29 11:46:14.087 UTC [37] HINT: Consider increasing the configuration parameter "max_wal_size". 2026-09-29 11:46:14.087 UTC [37] LOG: checkpoint starting: wal 2026-09-29 11:46:15.225 UTC [37] LOG: checkpoint complete: wrote 3 buffers (0.0%), wrote 0 SLRU buffers; 0 WAL file(s) added, 0 removed, 2 recycled; write=1.104 s, sync=0.020 s, total=1.138 s; sync files=3, longest=0.018 s, average=0.007 s; distance=33145 kB, estimate=33301 kB; lsn=0/2DF631D0, redo lsn=0/2C10DAE8 ``` Is this expected outcome from the configuration? ########## standalone-metastore/metastore-server/docker/hive-postgres-tpcds-metastore/postgresql.conf: ########## @@ -0,0 +1,47 @@ +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed with +# this work for additional information regarding copyright ownership. +# The ASF licenses this file to You under the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. + +# general + +listen_addresses = '*' +dynamic_shared_memory_type = posix + +# performance + +work_mem = 256MB Review Comment: The default is 4MB and we are increasing to 256MB that is a big jump. Maybe we should verify that everything runs fine in our CI before bumping the value. ########## standalone-metastore/metastore-server/docker/hive-postgres-tpcds-metastore/postgresql.conf: ########## @@ -0,0 +1,47 @@ +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed with +# this work for additional information regarding copyright ownership. +# The ASF licenses this file to You under the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. + +# general + +listen_addresses = '*' +dynamic_shared_memory_type = posix Review Comment: default? ########## standalone-metastore/metastore-server/docker/hive-postgres-tpcds-metastore/postgresql.conf: ########## @@ -0,0 +1,47 @@ +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed with +# this work for additional information regarding copyright ownership. +# The ASF licenses this file to You under the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. + +# general + +listen_addresses = '*' +dynamic_shared_memory_type = posix + +# performance + +work_mem = 256MB +shared_buffers = 128MB Review Comment: `shared_buffers` seems to be using the default value so can we remote it? ########## standalone-metastore/metastore-server/docker/hive-postgres-tpcds-metastore/entrypoint.sh: ########## @@ -13,10 +15,9 @@ # See the License for the specific language governing permissions and # limitations under the License. -#!/bin/bash +if [ -f /tmp/metastore_db.zstd ]; then + zstdcat /tmp/metastore_db.zstd | tar -C /var/lib/postgresql/ -x + rm /tmp/metastore_db.zstd +fi Review Comment: When the container starts I see the following in the logs. Wondering if its due to these commands? ``` tar: removing leading '/' from member names tar: can't make dir : No such file or directory ``` Do we need to do the restoration with a custom entrypoint? Can't we put the data directly in the appropriate `data` directory in the Dockerfile itself? ########## standalone-metastore/metastore-server/docker/hive-postgres-tpcds-metastore/Dockerfile: ########## @@ -14,9 +14,16 @@ # See the License for the specific language governing permissions and # limitations under the License. # -FROM postgres:12.3 +FROM postgres:18-alpine + +ADD https://github.com/thomasrebele/hive-postgres-metastore/releases/download/tpcds-30tb-histogram-1.0/metastore_tpcds30tb_with_histograms.raw_db.zstd /tmp/metastore_db.zstd Review Comment: Let's move the data into https://github.com/apache/hive-test-datasets. I can upload the dataset to release assets once we add the appropriate description. ########## standalone-metastore/metastore-server/docker/hive-postgres-tpcds-metastore/postgresql.conf: ########## @@ -0,0 +1,47 @@ +# Licensed to the Apache Software Foundation (ASF) under one or more +# contributor license agreements. See the NOTICE file distributed with +# this work for additional information regarding copyright ownership. +# The ASF licenses this file to You under the Apache License, Version 2.0 +# (the "License"); you may not use this file except in compliance with +# the License. You may obtain a copy of the License at +# +# http://www.apache.org/licenses/LICENSE-2.0 +# +# Unless required by applicable law or agreed to in writing, software +# distributed under the License is distributed on an "AS IS" BASIS, +# WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied. +# See the License for the specific language governing permissions and +# limitations under the License. + +# general + +listen_addresses = '*' +dynamic_shared_memory_type = posix + +# performance + +work_mem = 256MB +shared_buffers = 128MB + +# reduce WAL and archiving + +checkpoint_timeout=1d +max_wal_size = 64MB +min_wal_size = 32MB +wal_level = minimal +max_wal_senders = 0 +wal_buffers = -1 +archive_mode = off +archive_command = '/bin/true' Review Comment: `archive_mode` seems to be `off` by default so why do need to specify it here? More generally, if we are using the default configs there is no reason to explicitly add them in the conf file since it creates confusion on what is really necessary. ########## standalone-metastore/metastore-server/docker/hive-postgres-tpcds-metastore/README.md: ########## @@ -19,7 +19,7 @@ limitations under the License. # Postgres TPC-DS metastore A dockerized Postgres database with a Hive metastore dump from a -[TPC-DS 30TB dataset](https://github.com/zabetak/hive-test-datasets/releases/download/1.0/metastore_tpcds30tb_3_1_3000.dump.gz). +[TPC-DS 30TB dataset](https://github.com/thomasrebele/hive-postgres-metastore/releases/download/tpcds-30tb-histogram-1.0/metastore_tpcds30tb_with_histograms.raw_db.zstd), including histograms. The dump has been created with a fresh Postgres database, importing a dump with `pg_restore`, stopping Postgres, and then compressing the Postgres database files. More details can be found [here](https://github.com/thomasrebele/hive-postgres-metastore/tree/tpcds-30tb-histogram-1.0). Review Comment: Since now we have an official ASF repo for hosting datasets (https://github.com/apache/hive-test-datasets) please raise a PR there for adding the extra details and replace the link to point there. -- 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: [email protected] For queries about this service, please contact Infrastructure at: [email protected] --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
