Install the repository package (configures yum for PGDG repo):
wget http://yum.postgresql.org/9.1/redhat/rhel-6-x86_64/pgdg-centos91-9.1-4.noarch.rpm
rpm -Uvh pgdg-centos91-9.1-4.noarch.rpm
Install server, clients and contrib modules (this last one provides pgbench etc):
yum install postgresql91-server.x86_64
yum install postgresql91.x86_64
yum install postgresql91-contrib-9.1.2-1PGDG.rhel6.x86_64
Make it come up on reboot:
chkconfig postgresql-9.1 on
Create one server on each of the two mount points - /ebs1 is the mount point of the single plain EBS volume and /ebs2 is the mount point of the RAID10 volume of the 4 plain EBS volumes:
mkdir -p /ebs1/pgsql/data
mkdir -p /ebs2/pgsql/data
chown postgres /ebs1/pgsql/data
chown postgres /ebs2/pgsql/data
su postgres
/usr/pgsql-9.1/bin/initdb -D /ebs1/pgsql/data
/usr/pgsql-9.1/bin/initdb -D /ebs2/pgsql/data
Start the server on the plain EBS volume:
/usr/pgsql-9.1/bin/pg_ctl -D /ebs1/pgsql/data/ start
Initialize pgbench (~15Gb of data):
/usr/pgsql-9.1/bin/pgbench -i -s 1000 postgres
Stress it for 5 minutes:
/usr/pgsql-9.1/bin/pgbench -j 4 -c 100 -M prepared -T 300
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 4
duration: 300 s
number of transactions actually processed: 69748
tps = 232.166776 (including connections establishing)
tps = 232.441306 (excluding connections establishing)
Stop the server on plain EBS volume and start the one on the RAID10 volume:
/usr/pgsql-9.1/bin/pg_ctl -D /ebs1/pgsql/data/ stop
/usr/pgsql-9.1/bin/pg_ctl -D /ebs2/pgsql/data/ start
Initialize:
/usr/pgsql-9.1/bin/pgbench -i -s 1000 postgres
And stress for 5 min:
/usr/pgsql-9.1/bin/pgbench -j 4 -c 100 -M prepared -T 300
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1000
query mode: prepared
number of clients: 100
number of threads: 4
duration: 300 s
number of transactions actually processed: 94884
tps = 315.728824 (including connections establishing)
tps = 316.180605 (excluding connections establishing)
/usr/pgsql-9.1/bin/pg_ctl -D /ebs2/pgsql/data/ stop
And now with a 1000 concurrent users:
# change /ebs1/pgsql/data/postgresql.conf
# max concurrent connections from default of 100 to 1000
nano /ebs1/pgsql/data/postgresql.conf
/usr/pgsql-9.1/bin/pg_ctl -D /ebs2/pgsql/data/ stop
/usr/pgsql-9.1/bin/pg_ctl -D /ebs1/pgsql/data/ start
/usr/pgsql-9.1/bin/pgbench -j 4 -c 1000 -M prepared -T 300
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1000
query mode: prepared
number of clients: 1000
number of threads: 4
duration: 300 s
number of transactions actually processed: 32450
tps = 106.620361 (including connections establishing)
tps = 108.312732 (excluding connections establishing)
/usr/pgsql-9.1/bin/pg_ctl -D /ebs1/pgsql/data/ stop
# change max_connections 1000
nano /ebs2/pgsql/data/postgresql.conf
/usr/pgsql-9.1/bin/pg_ctl -D /ebs2/pgsql/data/ start
/usr/pgsql-9.1/bin/pgbench -j 4 -c 1000 -M prepared -T 300
starting vacuum...end.
transaction type: TPC-B (sort of)
scaling factor: 1000
query mode: prepared
number of clients: 1000
number of threads: 4
duration: 300 s
number of transactions actually processed: 55574
tps = 182.883653 (including connections establishing)
tps = 185.534468 (excluding connections establishing)
At almost double the TPS the RAID10 configuration is definitely worth the effort.
