$ dd bs=64k count=4k if=/dev/zero of=test oflag=dsync # 测试硬盘性能, 产品服务器上这个值至少要达到100M
$ df -T # 在安装前先查看硬盘格式, 数据库服务器等最好用ext4, 效率更高
主要参考: https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server http://www.revsys.com/writings/postgresql-performance.html
设置postgreSQL在CentOS reboot的时候自动启动
把/usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data start
加入到/etc/rc.d/rc.local
中.
把postgreSQL的bin目录加到PATH中 修改~/.bash_profile, 加入如下内容:
$ df -T # 查看硬盘格式, 数据库服务器等最好用ext4, 效率更高 一、操作系统安装
1.文件系统:ext4
2.磁盘分布
RAM Swap Space Between 1 GB and 2 GB 1.5 times the size of the RAM Between 2 GB and 16 GB Equal to the size of the RAM More than 16 GB 16 GB
一般16GB 3)/: 其它全部
3.磁盘布局
PG软件目录:本地磁盘/opt/pgsql9.5 或 /usr/local/pgsql/
二、系统参数配置
1.OS参数设置
kernel.shmmax = 4294967295 # 物理内存一半 kernel.shmall = 2097152 # 物理内存大小除以分页大小。# getconf PAGE_SIZE # 32GB/4096 ;select 32(10241024*1024)::bigint/4096 as SHMALL; kernel.shmmni = 4096 # SHMMNI参数:设置系统级最大共享内存段数量,default 4096。 kernel.msgmax = 65536 kernel.msgmni = 2005 kernel.msgmnb = 65536 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048576
postgres soft nproc 4096 postgres hard nproc 16384 postgres soft nofile 65535 postgres hard nofile 65535 postgres soft stack 10240 postgres hard stack 32768
session required /lib/security/pam_limits.so
2.postgresql调整 1)postgresql扩展安装 cd postgresql-source/contrib/pg_stat_statements
2)修改参数
PGDATA目录postgresql.conf
max_connections = 根据实际情况确定 superuser_reserved_connections = 根据实际情况确定 shared_buffers = 25%物理内存,上限40%
http://daigong.sinaapp.com/?p=67 log_min_duration_statement = 1000 # -1 不log sql 0 log 所有sql,如果大于1 ,以ms单位,记录超过该时间的sql,也就是我们说的查找sql中瓶颈
需要注意的是该属性与其他俩个属性有互斥,一定要确保
log_duration = off #这个的含义是记录所有duration时间
log_statement = ‘none’ #这个属性代表记录sql的类型
如果这俩个属性开启,你会发现你的log中有一次查询会有很多时间,其中很多是你不关心的。
$ pg_ctl reload -D data #当配置文件改变时,使用. 这样数据库不会重启,只会发出一个信号,让其重新读取log
$ sudo -u postgres psql
$ \connect project_production # 切换数据库
$ psql -d postgres # Login to postgres
$ psql -l # List all databases
$ psql --version # 查看pg版本
# \dt+ # List all tables.
# \d+ schema_migrations # Show DDL of a table
# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------+-----------
someuser | Superuser, Create role, Create DB | {}
postgres | Superuser, Create role, Create DB, Replication | {}
repluser | Replication | {}
详细见:
http://www.postgresql.org/docs/9.3/static/sql-createrole.html
http://www.postgresql.org/docs/9.3/static/sql-alterrole.html
http://www.postgresql.org/docs/9.2/static/app-createuser.html
# /usr/local/pgsql/bin/psql -d postgres -U postgres
# CREATE ROLE someuser SUPERUSER CREATEDB CREATEROLE LOGIN;
# ALTER ROLE someuser WITH PASSWORD 'hu8jmn3';
# ALTER ROLE someuser LOGIN;
# \q 退出psql
# CREATE USER postgres SUPERUSER;# if you got error: ActiveRecord::NoDatabaseError: FATAL: role "postgres" does not exist
http://www.moncefbelyamani.com/how-to-install-postgresql-on-a-mac-with-homebrew-and-lunchy/
http://stackoverflow.com/questions/10301794/difference-between-rake-dbmigrate-dbreset-and-dbschemaload
select * from pg_stat_activity where datname = 'some_production'; # 查看clients
这个重要,因为涉及到允许哪些ip地址的哪些用户以什么样的方式访问哪些数据库!
http://www.postgresql.org/docs/9.1/static/auth-pg-hba-conf.html
在大并发时就会有问题.
连接数据库的clients数量(用SELECT count() FROM pg_stat_activity;可以查看)在120内就够了. 不必过多, 够用就好.
因为clients都是进程,在postgresql的服务器上用ps -ef|grep postgres
可以看到,进程多了是实打实的消耗资源.
rails的database.yml中pool应填写puma的max_threads值, rails启动后会按puma的min_threads值分配连接数, 够用就不会多分配一个, 也不会少一个, 连接数会是workers * min_threads.
不够用时, 就会按最多max_threads去分配, 当系统卡时, 所有连接都不会被释放.
这时候 SELECT count() FROM pg_stat_activity; (本次故障时连接数>200, 当时光WEB就占用了workers * max_threads = 5 * 32 = 160个).
之后我把puma的max_threads改为16, rails的database.yml中pool也改为16, 并用$ kill -s SIGTERM cat /var/run/ucweb.pid
把WEB杀死,
并重新启动, bundle exec puma -t 8:16 -w 5 --preload -e production -d -b unix:///var/run/ucweb.sock --pidfile /var/run/ucweb.pid
再SELECT count(*) FROM pg_stat_activity where client_addr= ‘IP_of_WEB’;(返回40, 即workers * min_threads = 5 * 8 = 40个).
页面卡的问题一下子就解决了, 数据库CPU也正常了.
如果有大表,备份费力,可以通过如下方式剔除 $ pg_dump -U postgres -Fc –exclude-table=’big_table_name|not_important_big_table_name’ your_production > your_production_20150728
$ sudo -u lane pg_restore -d some_development < some_db $ pg_restore -l some_production_0410 > 0401.list # 用这个-l可以看到这个pg_dump出来的文件有什么东西. 比如有哪些表, index等等. 方便你最后pg_restore完了比对下. 报错role “xxx” does not exist解决办法: $ sudo -u postgres(or lane) createuser xxx
Restore From xxx.sql
(https://gist.github.com/syafiqfaiz/5273cd41df6f08fdedeb96e12af70e3b)
```shell script
createdb db_name
$ $ psql -U
```bash
pg_dump -h yourproject-qa.fd2411323.us-east-1.rds.amazonaws.com -U postgres -f yourproject_qa_20210119.sql yourproject_qa # Backup from AWS. Need to wait for several minutes which depends on the size of your db.
createdb yourproject_qa_20210119
psql -d yourproject_qa_20210119 -f yourproject_qa_20210119.sql # Restore from `.sql`
一个完整的数据库备份和还原的过程:
$ cd /srv/database_backup
$ nohup /usr/local/pgsql/bin/pg_dump -U postgres -Fc some_production > some_production_0410 &
$ sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ status
$ sudo -u postgres /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data/ restart -m f # 带这两个参数才能正常的重启, 否则有client连接在是无法顺利关闭的
$ createdb -U postgres django_spa_blog_dev # For Mac brew installed PG
$ sudo -u postgres /usr/local/pgsql/bin/dropdb some_production # For linux
$ sudo -u postgres /usr/local/pgsql/bin/createdb some_production
$ cd /srv/db_35
$ scp root@172.35.11.21:/srv/database_backup/some_production_20160412 ./
$ nohup sudo -u postgres /usr/local/pgsql/bin/pg_restore -d some_production < /srv/db_35/some_production_0410 -v >> ./db_restore.log &
Postgresql max integer 2100000000 查看当前的clients $ SELECT usesysid, usename FROM pg_stat_activity;
http://greenplum.org/ 查询性能成为问题的时候可以考虑用它
http://stackoverflow.com/questions/7975556/how-to-start-postgresql-server-on-mac-os-x
$ less /usr/local/var/postgres/postgresql.conf
$ ps aux|grep postgres
$ pg_ctl -D /usr/local/var/postgres status
$ pg_ctl -D /usr/local/var/postgres restart
TroubleShoot: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?
PG::ConnectionBad - could not connect to server: Connection refused
http://stackoverflow.com/questions/19828385/pgconnectionbad-could-not-connect-to-server-connection-refused
是因为关机时postgres没有正确的关闭!
$ cd /usr/local/var/postgres
$ rm postmaster.pid
$ pg_ctl -D /usr/local/var/postgres status
$ 把取得的进程PID杀死,略等几秒后,pg应该会自动重生!
$ mkdir /var/pgsql_socket/
$ touch /private/tmp/.s.PGSQL.5432
$ ln -s /private/tmp/.s.PGSQL.5432 /var/pgsql_socket/
Lanes-MacBook-Air-2:ucweb lane$ brew install postgresql ==> Downloading https://homebrew.bintray.com/bottles/postgresql-9.4.4.yosemite.bottle.tar.gz ######################################################################## 100.0% ==> Pouring postgresql-9.4.4.yosemite.bottle.tar.gz ==> /usr/local/Cellar/postgresql/9.4.4/bin/initdb /usr/local/var/postgres ==> Caveats If builds of PostgreSQL 9 are failing and you have version 8.x installed, you may need to remove the previous version first. See: https://github.com/Homebrew/homebrew/issues/2510
To migrate existing data from a previous major version (pre-9.4) of PostgreSQL, see: https://www.postgresql.org/docs/9.4/static/upgrading.html
To have launchd start postgresql at login: ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents Then to load postgresql now: launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist Or, if you don’t want/need launchctl, you can just run: postgres -D /usr/local/var/postgres ==> Summary 🍺 /usr/local/Cellar/postgresql/9.4.4: 3014 files, 40M
psql# EXPLAIN SELECT “customers”.* FROM “customers” WHERE “customers”.”company_id” = 64023 AND “customers”.”act” IS NULL ORDER BY “customers”.”id” DESC;
QUERY PLAN
———————————————————————————————————
Sort (cost=26130.34..26130.34 rows=2 width=1852)
Sort Key: id
-> Bitmap Heap Scan on customers (cost=1151.27..26130.33 rows=2 width=1852)
Recheck Cond: (company_id = 64023)
Filter: (act IS NULL)
-> Bitmap Index Scan on index_customers_on_company_id (cost=0.00..1151.27 rows=62245 width=0)
Index Cond: (company_id = 64023)
可以看到费时在act IS NULL 这里了。
可以看日志或者查系统视图 你日志里应该设置记录duration SELECT S.procpid, S.start, now() - start AS lap,S.current_query FROM ( SELECT backendid,pg_stat_get_backend_pid(S.backendid) AS procpid,pg_stat_get_backend_activity_start(S.backendid) AS start, pg_stat_get_backend_activity(S.backendid) AS current_query FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS S ) AS S ,pg_stat_activity a WHERE S.procpid =a.pid and a.state =’active’ ORDER BY lap DESC;
pg_dump and pg_restore Refer to https://gist.github.com/syafiqfaiz/5273cd41df6f08fdedeb96e12af70e3b
pg_dump -h rds-server -d your_db_qa -U postgres -f your_db_qa_20220429.sql # backup
psql -U postgres -d your_db_qa_20220429 -f your_db_qa_20220429.sql # Restore
or compressed backup
pg_dump -h -d project_name_qa -U postgres -Fc -f project_name_qa_20220506 # compressed backup
createdb project_name_qa_20220506
pg_restore -d project_name_qa_20220506 < project_name_qa_20220506 # Restore