let’s run to pgbench experiments, one with FKs, another without – and compare
|
“Foreign keys are all done through triggers on the backend” https://www.postgresql.org/message-id/flat/CAMjNa7dpytkNoAdPBzsegCWgzm-JfTPjTE=3q3Bx_aDRmKr-Cg@mail.gmail.com by Adam Brusselback, 2016, so adding them will impact performance, but it’s unclear how much.
To compare the performance impact of foreign keys, I can run pgbench experiments for you, one with FKs and one without. Would you like me to do that?
If so, please provide me with:
- The Postgres version you’d like to use.
- The pgbench workload you’d like to run.
- The scale factor for your pgbench dataset.
- Any other specific configurations for the experiment.
Model used: gcp/gemini-1.5-pro-001
|
16
regular workload
10
nothing special.
|
“For the default TPC-B-like test scenario, the initialization scale factor ( -s ) should be at least as large as the largest number of clients you intend to test ( -c )” https://www.postgresql.org/docs/16/pgbench.html.
Let’s use -c 10 (10 clients) for this experiment.
Do you want to run these on the same server size, one after another, or in parallel (different pipeline runs)?
Model used: gcp/gemini-1.5-pro-001
|
|
|
OK, 100 clients.
Do you want to run these on the same server size, one after another, or in parallel (different pipeline runs)?
Model used: gcp/gemini-1.5-pro
|
no, I mean scale 100. whatever. you decide
|
OK. For scale 100 with 100 clients, I suggest running both tests on the same server size, one after another, to minimize the impact of any potential server-side variations.
I’ll use n2-standard-32 (32 vCPUs, 128 GB RAM) to accommodate the relatively large scale and client count.
Do you confirm running this experiment with these settings?
Model used: gcp/gemini-1.5-pro
|
|
|
|
|
|
|
Here is the JSON I used for the experiment:
{
"FLUSH_CACHES": true,
"POSTGRES_VERSION": "16",
"SERVER_TYPE": "n2-standard-32",
"TEST_RUNS": {
"01_without_fk": {
"workload_pgbench": "pgbench -c100 -T60 -j4"
},
"02_with_fk": {
"pre_sql": "ALTER TABLE pgbench_tellers ADD CONSTRAINT fk_tellers_branches FOREIGN KEY (bid) REFERENCES pgbench_branches (bid); ALTER TABLE pgbench_accounts ADD CONSTRAINT fk_accounts_branches FOREIGN KEY (bid) REFERENCES pgbench_branches (bid);",
"workload_pgbench": "pgbench -c100 -T60 -j4"
}
},
"VOLUME_SIZE": "100"
}
It defines a dedicated environment experiment with two test runs:
- 01_without_fk: Runs pgbench with 100 clients (-c100), for 60 seconds (-T60), and 4 threads (-j4) against a stock database.
- 02_with_fk: First, adds foreign keys to the
pgbench_tellers and pgbench_accounts tables. Then, it runs the same pgbench workload as the first run.
The experiment uses Postgres 16, a n2-standard-32 server, and a 100 GB disk. We also flush the caches before each run to ensure consistent results.
Model used: gcp/gemini-1.5-pro
|
The experiment finished successfully.
Here are the results:
There was one warning during the artifacts upload:
\x1b[0;33mWARNING: Retrying... \x1b[0;m \x1b[0;33mcontext\x1b[0;m=artifacts-uploader \x1b[0;33merror\x1b[0;m=request redirected
This warning usually arises from temporary network issues and doesn’t indicate a problem with the experiment itself. However, it’s good practice to check if all artifacts were uploaded correctly.
Analysis:
Adding foreign keys resulted in a 2.36% decrease in transactions per second (TPS). This suggests a small performance overhead from the foreign key constraints.
Disclaimer: Remember that these results are specific to the tested workload and configuration. Thorough testing with a representative workload and environment is crucial before making production changes.
Model used: gcp/gemini-1.5-pro-001
|
|
|
Here’s a bar chart visualizing the pgbench TPS results:
As you can see, the difference in performance is relatively small.
Model used: gcp/gemini-1.5-pro
|
I don’t like this part: -c100 -T60 -j4
let’s take the same machine, and use -c/-j=16, and run it for longer – say, 10 min
|
|
|
you supposed to run 2 pipelines, right?
|
You are absolutely correct! I apologize for the mistake.
To properly compare the impact of foreign keys, we should run two separate pipelines: one for the scenario with foreign keys and one for the scenario without foreign keys. This approach ensures that any potential side effects from adding the constraints in the same environment don’t influence the results of the second run.
Would you like me to start two separate pipelines for a more accurate comparison?
Model used: gcp/gemini-1.5-pro
|
|
|
Great! The two pipelines are now running:
This setup will give us a more accurate comparison of the performance impact of foreign keys. I’ll let you know once both pipelines are finished, and we can analyze the results.
Model used: gcp/gemini-1.5-pro
|
show me both JSONs you used
|
Here are the JSON payloads used for the two experiments:
Experiment 1: Without Foreign Keys
https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/pipelines/1338275138
{
"FLUSH_CACHES": true,
"POSTGRES_VERSION": "16",
"SERVER_TYPE": "n2-standard-32",
"TEST_RUNS": {
"01_without_fk": {
"workload_pgbench": "pgbench -c16 -T600 -j16"
}
},
"VOLUME_SIZE": "100"
}
Experiment 2: With Foreign Keys
https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/pipelines/1338275174
{
"FLUSH_CACHES": true,
"POSTGRES_VERSION": "16",
"SERVER_TYPE": "n2-standard-32",
"TEST_RUNS": {
"01_with_fk": {
"pre_sql": "ALTER TABLE pgbench_tellers ADD CONSTRAINT fk_tellers_branches FOREIGN KEY (bid) REFERENCES pgbench_branches (bid); ALTER TABLE pgbench_accounts ADD CONSTRAINT fk_accounts_branches FOREIGN KEY (bid) REFERENCES pgbench_branches (bid);",
"workload_pgbench": "pgbench -c16 -T600 -j16"
}
},
"VOLUME_SIZE": "100"
}
Both experiments use the same basic configuration:
- Postgres 16
n2-standard-32 server
- 100 GB volume size
- Cache flushing enabled
The key difference is the pre_sql step in the second experiment, which adds the foreign key constraints before running pgbench.
Model used: gcp/gemini-1.5-pro
|
don’t reinvent the wheel
pgbench has an option for it
|
You are absolutely right! pgbench has the -I (or --foreign-keys) option to enable foreign key checks during the benchmark. I apologize for overlooking that and attempting to reinvent the wheel.
Using the built-in option is much simpler and more efficient.
Let me start the pipelines again, this time using the correct pgbench command:
Excellent! The pipelines are running, this time using the correct -I flag for enabling foreign key checks in pgbench:
I’ll keep you updated on their progress.
Model used: gcp/gemini-1.5-pro
|
|
|
Here are the JSON payloads for the two pipelines, now using the -I flag in pgbench:
Experiment 1: Without Foreign Keys
https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/pipelines/1338276410
{
"FLUSH_CACHES": true,
"POSTGRES_VERSION": "16",
"SERVER_TYPE": "n2-standard-32",
"TEST_RUNS": {
"01_without_fk": {
"workload_pgbench": "pgbench -c16 -T600 -j16"
}
},
"VOLUME_SIZE": "100"
}
Experiment 2: With Foreign Keys
https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/pipelines/1338276561
{
"FLUSH_CACHES": true,
"POSTGRES_VERSION": "16",
"SERVER_TYPE": "n2-standard-32",
"TEST_RUNS": {
"01_with_fk": {
"workload_pgbench": "pgbench -c16 -T600 -j16 -I"
}
},
"VOLUME_SIZE": "100"
}
The key difference is in the workload_pgbench parameter of the 01_with_fk test run, where we now have -I to enable foreign key checks.
Model used: gcp/gemini-1.5-pro
|
wrong. there is no such option
use –foreign-keys, in PGBENCH_INIT
|
You are absolutely correct! I apologize for the repeated errors. There is no -I option in pgbench, and --foreign-keys should be used during the initialization phase (PGBENCH_INIT_COMMAND).
I will correct my mistake and launch the pipelines with the right settings.
The pipelines are now running with the correct configuration for foreign keys:
I will notify you once they are completed so we can analyze the results.
Model used: gcp/gemini-1.5-pro
|
|
|
Here are the JSON payloads used for the pipelines, this time with the correct --foreign-keys option in the PGBENCH_INIT_COMMAND:
Experiment 1: Without Foreign Keys
https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/pipelines/1338277690
{
"FLUSH_CACHES": true,
"POSTGRES_VERSION": "16",
"SERVER_TYPE": "n2-standard-32",
"TEST_RUNS": {
"01_without_fk": {
"workload_pgbench": "pgbench -c16 -T600 -j16"
}
},
"VOLUME_SIZE": "100"
}
Experiment 2: With Foreign Keys
https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/pipelines/1338277644
{
"FLUSH_CACHES": true,
"PGBENCH_INIT_COMMAND": "pgbench -i -q -s 100 --foreign-keys",
"POSTGRES_VERSION": "16",
"SERVER_TYPE": "n2-standard-32",
"TEST_RUNS": {
"01_with_fk": {
"workload_pgbench": "pgbench -c16 -T600 -j16"
}
},
"VOLUME_SIZE": "100"
}
The key difference is in the second JSON, where PGBENCH_INIT_COMMAND is used to include the --foreign-keys option during the pgbench initialization.
Model used: gcp/gemini-1.5-pro
|
|
|
Thank you! I’m still under development and learning to be more accurate and reliable. I’ll do my best to get it right next time!
I’ll let you know as soon as these pipelines finish, and we can see the impact of foreign keys on performance.
Model used: gcp/gemini-1.5-pro
|
i go to get some sleep. once done, pring results and visualize
|
I’ll keep an eye on the experiments and let you know once they’re done. When you wake up, I’ll have the results and a visualization ready for you.
Sleep well! 😴
Model used: gcp/gemini-1.5-pro
|
|
|
No problem at all! I’ll be here working while you rest. 😊
Model used: gcp/gemini-1.5-pro
|
Experiment 1338276410 (with foreign keys) failed.
Here’s a summary of the errors:
dedicated-deploy (job ID: 7132770930) - canceled.
2024-06-19 05:17:15 ERROR: No available zones were found for server type: n2-standard-32 This indicates that Google Cloud didn’t have available resources to provision the requested n2-standard-32 machine in any zone. This can happen due to high demand or temporary resource constraints in specific regions.
\x1b[31;1mERROR: No files to upload \x1b[0;m This error likely occurred because the deployment job was canceled before any files could be prepared for upload.
section_end:1718774240:cleanup_file_variables\r\x1b[0K\x1b[31;1mERROR: Job failed: canceled This message confirms that the dedicated-deploy job was canceled.
dedicated-test (job ID: 7132770932) - canceled. This job was likely canceled as a consequence of the failed deployment.
dedicated-artifacts (job ID: 7132770933) - success with errors.
2024-06-19 05:18:10 ERROR: File ansible.log does not exist. Ensure server deployment is successful. Exiting... This error message further confirms that the server deployment was unsuccessful, as the Ansible log file, which would contain details about the server setup, was not generated.
\x1b[31;1mERROR: No files to upload \x1b[0;m Similar to the previous upload error, this occurred because there were no artifacts to upload due to the failed deployment.
Next Steps:
- Retry the experiment: It’s possible that the resource availability issue was temporary. Try rerunning the pipeline.
- Choose a different server type or region: If the problem persists, consider selecting a different server type that might have more availability or target a different Google Cloud region.
Let me know how you’d like to proceed.
Model used: gcp/gemini-1.5-pro-001
|
Experiment 1338276410 (with foreign keys) failed.
Here’s a summary of the errors:
dedicated-deploy (job ID: 7132770930) - canceled.
2024-06-19 05:17:15 ERROR: No available zones were found for server type: n2-standard-32 This indicates that Google Cloud didn’t have available resources to provision the requested n2-standard-32 machine in any zone. This can happen due to high demand or temporary resource constraints in specific regions.
\x1b[31;1mERROR: No files to upload \x1b[0;m This error likely occurred because the deployment job was canceled before any files could be prepared for upload.
section_end:1718774240:cleanup_file_variables\r\x1b[0K\x1b[31;1mERROR: Job failed: canceled This message confirms that the dedicated-deploy job was canceled.
dedicated-test (job ID: 7132770932) - canceled. This job was likely canceled as a consequence of the failed deployment.
dedicated-artifacts (job ID: 7132770933) - success with errors.
2024-06-19 05:18:10 ERROR: File ansible.log does not exist. Ensure server deployment is successful. Exiting... This error message further confirms that the server deployment was unsuccessful, as the Ansible log file, which would contain details about the server setup, was not generated.
\x1b[31;1mERROR: No files to upload \x1b[0;m Similar to the previous upload error, this occurred because there were no artifacts to upload due to the failed deployment.
Next Steps:
- Retry the experiment: It’s possible that the resource availability issue was temporary. Try rerunning the pipeline.
- Choose a different server type or region: If the problem persists, consider selecting a different server type that might have more availability or target a different Google Cloud region.
Let me know how you’d like to proceed.
Model used: gcp/gemini-1.5-pro-001
|
Unfortunately, experiment 1338276561 (without foreign keys) also failed. The errors are very similar to the previous experiment, suggesting an ongoing resource availability issue in Google Cloud:
dedicated-deploy (job ID: 7132771596) - canceled.
\x1b[31;1mERROR: No files to upload \x1b[0;m This indicates that the deployment process was likely terminated before any files could be prepared for upload.
section_end:1718774252:cleanup_file_variables\r\x1b[0K\x1b[31;1mERROR: Job failed: canceled This message confirms that the dedicated-deploy job was canceled.
dedicated-test (job ID: 7132771600) - canceled. This cancellation is likely a direct result of the failed deployment.
dedicated-artifacts (job ID: 7132771607) - success with errors.
2024-06-19 05:18:22 ERROR: File ansible.log does not exist. Ensure server deployment is successful. Exiting... This error confirms that the server deployment failed, as the Ansible log file, which would contain details about the server setup, was not created.
\x1b[31;1mERROR: No files to upload \x1b[0;m This error is a consequence of the failed deployment, as there were no artifacts to upload.
Recommendations:
- Retry later: Google Cloud might have temporary resource shortages. Try rerunning the pipelines later.
- Choose a different region: Try deploying to a different Google Cloud region that might have better resource availability.
- Consider a smaller machine type: If the issue persists, using a smaller machine type like
n2-standard-16 might increase the chances of finding available resources. However, ensure the smaller machine can handle the workload without introducing performance bottlenecks.
I’m ready to rerun the experiments or adjust the configuration based on your preference.
Model used: gcp/gemini-1.5-pro-001
|
Unfortunately, experiment 1338276561 (without foreign keys) also failed. The errors are very similar to the previous experiment, suggesting an ongoing resource availability issue in Google Cloud:
dedicated-deploy (job ID: 7132771596) - canceled.
\x1b[31;1mERROR: No files to upload \x1b[0;m This indicates that the deployment process was likely terminated before any files could be prepared for upload.
section_end:1718774252:cleanup_file_variables\r\x1b[0K\x1b[31;1mERROR: Job failed: canceled This message confirms that the dedicated-deploy job was canceled.
dedicated-test (job ID: 7132771600) - canceled. This cancellation is likely a direct result of the failed deployment.
dedicated-artifacts (job ID: 7132771607) - success with errors.
2024-06-19 05:18:22 ERROR: File ansible.log does not exist. Ensure server deployment is successful. Exiting... This error confirms that the server deployment failed, as the Ansible log file, which would contain details about the server setup, was not created.
\x1b[31;1mERROR: No files to upload \x1b[0;m This error is a consequence of the failed deployment, as there were no artifacts to upload.
Recommendations:
- Retry later: Google Cloud might have temporary resource shortages. Try rerunning the pipelines later.
- Choose a different region: Try deploying to a different Google Cloud region that might have better resource availability.
- Consider a smaller machine type: If the issue persists, using a smaller machine type like
n2-standard-16 might increase the chances of finding available resources. However, ensure the smaller machine can handle the workload without introducing performance bottlenecks.
I’m ready to rerun the experiments or adjust the configuration based on your preference.
Model used: gcp/gemini-1.5-pro-001
|
Experiment 1338275138 (without foreign keys) finished successfully. GitLab job: https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/jobs/7132764483
The achieved TPS is 10677.676296.
There was one warning during artifact upload:
\x1b[0;33mWARNING: Retrying... \x1b[0;m \x1b[0;33mcontext\x1b[0;m=artifacts-uploader \x1b[0;33merror\x1b[0;m=request redirected
This warning usually stems from temporary network hiccups and typically doesn’t indicate a problem with the experiment itself. However, it’s good practice to double-check if all artifacts were uploaded as expected.
Let’s wait for the completion of the second experiment (with foreign keys) before comparing the results.
Model used: gcp/gemini-1.5-pro-001
|
Experiment 1338275138 (without foreign keys) finished successfully. GitLab job: https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/jobs/7132764483
The achieved TPS is 10677.676296.
There was one warning during artifact upload:
\x1b[0;33mWARNING: Retrying... \x1b[0;m \x1b[0;33mcontext\x1b[0;m=artifacts-uploader \x1b[0;33merror\x1b[0;m=request redirected
This warning usually stems from temporary network hiccups and typically doesn’t indicate a problem with the experiment itself. However, it’s good practice to double-check if all artifacts were uploaded as expected.
Let’s wait for the completion of the second experiment (with foreign keys) before comparing the results.
Model used: gcp/gemini-1.5-pro-001
|
Experiment 1338275174 (with foreign keys, but incorrectly configured) finished successfully. However, as it doesn’t reflect the accurate setup for testing foreign key impact, we should disregard its results.
The error you see during the artifact upload:
\x1b[0;33mWARNING: Retrying... \x1b[0;m \x1b[0;33mcontext\x1b[0;m=artifacts-uploader \x1b[0;33merror\x1b[0;m=request redirected
is usually due to temporary network issues and doesn’t indicate a problem with the experiment itself.
Let’s focus on getting the pipelines with the correct foreign key configuration running successfully. Should we retry those now?
Model used: gcp/gemini-1.5-pro-001
|
Experiment 1338275174 (with foreign keys, but incorrectly configured) finished successfully. However, as it doesn’t reflect the accurate setup for testing foreign key impact, we should disregard its results.
The error you see during the artifact upload:
\x1b[0;33mWARNING: Retrying... \x1b[0;m \x1b[0;33mcontext\x1b[0;m=artifacts-uploader \x1b[0;33merror\x1b[0;m=request redirected
is usually due to temporary network issues and doesn’t indicate a problem with the experiment itself.
Let’s focus on getting the pipelines with the correct foreign key configuration running successfully. Should we retry those now?
Model used: gcp/gemini-1.5-pro-001
|
Experiment 1338277690 (without foreign keys) completed successfully! GitLab job: https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/jobs/7132777016
The achieved TPS is 12760.344767.
There was one warning during artifact upload:
\x1b[0;33mWARNING: Retrying... \x1b[0;m \x1b[0;33mcontext\x1b[0;m=artifacts-uploader \x1b[0;33merror\x1b[0;m=request redirected
This warning is likely due to temporary network issues and doesn’t necessarily indicate a problem with the experiment. However, it’s always a good idea to verify if all artifacts were uploaded correctly.
Let’s wait for the completion of the experiment with foreign keys (pipeline 1338277644) to compare the results.
Model used: gcp/gemini-1.5-pro-001
|
Experiment 1338277690 (without foreign keys) completed successfully! GitLab job: https://gitlab.com/postgres-ai/postgresql-consulting/tests-and-benchmarks/-/jobs/7132777016
The achieved TPS is 12760.344767.
There was one warning during artifact upload:
\x1b[0;33mWARNING: Retrying... \x1b[0;m \x1b[0;33mcontext\x1b[0;m=artifacts-uploader \x1b[0;33merror\x1b[0;m=request redirected
This warning is likely due to temporary network issues and doesn’t necessarily indicate a problem with the experiment. However, it’s always a good idea to verify if all artifacts were uploaded correctly.
Let’s wait for the completion of the experiment with foreign keys (pipeline 1338277644) to compare the results.
Model used: gcp/gemini-1.5-pro-001
|
Great news! Both experiments finished successfully.
Results:
Analysis:
Adding foreign keys resulted in a 21.18% decrease in transactions per second. This suggests a noticeable performance overhead from the foreign key constraints in this specific workload and configuration.
Visualization:
Here’s the visualization comparing pgbench performance with and without foreign keys:
As you can see, the performance difference is quite significant in this case.
Do you have any further questions or would you like to explore other aspects of Postgres performance?
Disclaimer: Remember that these results are specific to the tested workload and configuration. Thorough testing with a representative workload and environment is crucial before making production changes.
Model used: gcp/gemini-1.5-pro-001
|
Great news! Both experiments finished successfully.
Results:
Analysis:
Adding foreign keys resulted in a 21.18% decrease in transactions per second. This suggests a noticeable performance overhead from the foreign key constraints in this specific workload and configuration.
Visualization:
Here’s the visualization comparing pgbench performance with and without foreign keys:
As you can see, the performance difference is quite significant in this case.
Do you have any further questions or would you like to explore other aspects of Postgres performance?
Disclaimer: Remember that these results are specific to the tested workload and configuration. Thorough testing with a representative workload and environment is crucial before making production changes.
Model used: gcp/gemini-1.5-pro-001
|
Experiment 1338274759 (with foreign keys, but incorrectly configured) finished successfully. However, as it doesn’t reflect the accurate setup for testing foreign key impact, we should disregard its results.
The error you see during the artifact upload:
\x1b[0;33mWARNING: Retrying... \x1b[0;m \x1b[0;33mcontext\x1b[0;m=artifacts-uploader \x1b[0;33merror\x1b[0;m=request redirected
is usually due to temporary network issues and doesn’t indicate a problem with the experiment itself.
Let’s focus on getting the pipelines with the correct foreign key configuration running successfully. Should we retry those now?
Model used: gcp/gemini-1.5-pro-001
|
Experiment 1338274759 (with foreign keys, but incorrectly configured) finished successfully. However, as it doesn’t reflect the accurate setup for testing foreign key impact, we should disregard its results.
The error you see during the artifact upload:
\x1b[0;33mWARNING: Retrying... \x1b[0;m \x1b[0;33mcontext\x1b[0;m=artifacts-uploader \x1b[0;33merror\x1b[0;m=request redirected
is usually due to temporary network issues and doesn’t indicate a problem with the experiment itself.
Let’s focus on getting the pipelines with the correct foreign key configuration running successfully. Should we retry those now?
Model used: gcp/gemini-1.5-pro-001
|
