2023年7月31日星期一

DB Performance Optimize

 PostgrelDB as example.

1. Install PostgrelSQL with default settings.
    with Selected Type Of cloud instance(Hardwares/OS/DB) as Standard. ex:
     

2. Check the performance insights counter metrics on AWS CloudWatch before.

3. Change the setting and check the performance  
    - memory: 

4. Change the setting and check the performance  
    - disk cache:  8MB->128MB

5. Change the Transaction log buffer and check the performance
    - wal_buffers: 16 -> 32

6. Change the Backgroud Write Process Count and check the performance
   - bgwrite_delay: 200
   - bgwrite_maxpages: 4 ->8

7. Change the checkpoint_segements and check the performance
   - checkpoint_segments: 4->16

8. Turn off recording disk access time and check the performance(Linux only)
   - mount -o noatime /etc/fstab

9. Change tablespace saved disk difference to pg_default.
    CREATE TABLESPACE pgdata1 LOCATION '/disk1/pgdata1';
    CREATE DATABASE ~ TABLESPACE pgdata1;
    CREATE TABLE ~ TABLESPACE pgdata1;
    ALTER TABLE ~ SET TABLESPACE pgdata1;

10. Clean the trash in disk monthly.

没有评论: