Local, 53% improvement vs.> We have AWS RDS and we are trying to connect to DB remotely from EC2 instance.as client connection using psql. Original RDS: Min 10s, Max 20s, Avg 15sĤ2% improvement vs.Original RDS: Min 12s, Max 25s, Avg 18sģ3% improvement vs.We solved the slow large queries issue after migrating PostgreSQL from a web server to Amazon RDS by eliminating disk swap usage and increasing parallel workers. This method reduced our maximum query times by ~50%. Substitute the value from your local database you’re migrating from for (integer) above. nf # max_parallel_workers = engine-default The final step is to override max_parallel_workers in your nf from “engine-default” to match the local database. Step 3: Optimize PostgreSQL to Increase Parallel Workers This method reduced our minimum query times by ~20%. psql "EXPLAIN ANALYZE the rest of your query goes here" Next, run your EXPLAIN ANALYZE queries again to confirm they’re using system memory for sorts. Once you’ve updated the parameter, restart PostgreSQL. To prevent disk swapping for sorts (slower), and use memory instead (faster), increase the work_mem parameter in nf - we increased ours from 20MB to 40MB and saw immediate speed improvements. Review the output of this query, and if you see “Sort Method: external merge Disk: kB”, then your query is indeed using swap disk for sorting. Next, run the EXPLAIN ANALYZE command on your test queries to show you if a SORT function is using the disk. …where the size is in bytes and the number at the end of the temp file name is the index when multiple temp files are required for a single query. Now you can see which queries are swapping to disk. If you do a lot of complex sorts, and have a lot of memory, then increasing the work_mem parameter allows PostgreSQL to do larger in-memory sorts which, unsurprisingly, will be faster than disk-based equivalents.įirst, Locate your PostgreSQL configuration file (nf) and enable the log_temp_files parameter - setting this value to 0 (zero) will ensure that a log entry is made for each temporary file created (including sorts, which we are particularly interested in here). Memory speeds are faster than disk speeds. Step 2: Optimize PostgreSQL to Reduce Disk Swapping The output of the command above will provide a baseline of execution times to improve upon with optimization. xargs -d "\n" -n 1 -P 20 psql -U -a -c < ~/mytestfile.txt
echo "\i ~/query1.sql" > ~/mytestfile.txtĮxecute the test file and indicate how many queries you want to execute in parallel - this example will run 20 parallel queries via the “-P 20” argument. Note: the same sql file can be specified multiple times if you want to run it multiple times. Use the \i command in the test file to execute each file (if you’re running more than one file, separate the files by a newline). echo "your 1st large sql query goes here" > ~/query1.sqlĬreate the test file, where each sql file you created above will be run. chmod 600 ~/.pgpassĬreate a file for each of your test queries containing a single query (e.g.
pgpass file to disallow any access to world or group - using less strict permissions will cause the. echo "your_host:your_port:your_db_name:your_user:your_password" > ~/.pgpass
#Aws postgresql rds connectivity password#
pgpass file in your home directory and add an entry for each database in the format HOST:PORT:DB_NAME:USER:PASSWORD - this password file provides Postgres your database host & authentication details. psqlrc file in your home directory and add “\timing on” to the file - this will cause all queries to display query times. RDS: db.r6g.8xlarge 256GiB RAM, 32 vCPUsĬreate a.Localhost: EC2 Ubuntu m4.10xlarge 160GiB RAM 40 CPUs.To run our optimization tests, we used an environment with:
0 kommentar(er)
