http://bora.bilg.in/blog/02/postgresql-database-server-farm-installation-and-configuration-with-pgcluster-on-freebsd
I recently installed a PostgreSQL Database Server Farm on FreeBSD at work. This installation had 1 loadbalancer, 3 cluster servers and 2 replication servers. PostgreSQL works really fast under FreeBSD (especially if you apply the correct configuration). I had some hard time finding a documentation, so I’m publishing the one I wrote here. This guide includes FreeBSD installation (step by step) and configuration, PgCluster installation and configuration (with a little bit performance tuning, thanks to EnderUnix Team and Alper YALCINER) and also performance testing via Bristlecone.
Overview
This document outlines installing a PostgreSQL Database Server farm on FreeBSD System.The installation in this document was tested on a Server Farm which includes 6 PC’s. Hardware specifications of these PC’s are: Pentium 4 CPU’s, 512 MB of RAM, 64 MB Graphics Card and 80 GB Disks. We used PostgreSQL 8.3 Latest on FreeBSD 7.0 and PgCluster 1.9 Latest.
Download FreeBSD from http://www.freebsd.org/. The following instructions are based on FreeBSD Version 7.0
1. Boot with FreeBSD 7.0 installation disk.
2. When menu loads hit 1 to enter the default option.
3. Afterwards choose your country.
4. Then choose to begin a standard installation, it’s the recommended option at this dialog.
5. Then enter OK at the next two messages.
6. When partitioning your drive select ‘A’ for “Use Entire Disk,” then ‘Q’ to finish.
7. Next we need to install a boot manager, so select “BootMgr”.
8. Then enter OK at the next message.
9. Hit ‘A’ for the auto defaults, then ‘Q’ for finish.
10. When choosing your distribution pick option 9 “X-User,” then click OK to the next message, then click OK.
11. Then to install choose the option to do so via FreeBSD CD/DVD, and then click Yes.
12. The installation will then begin.
13. After the installation hit “OK”
14. Hit “No” when configuring any Ethernet or slip/ppp network devices (this can be done later)
15. Hit “No” when prompted if the machine will function as a network gateway.
16. Hit “No” for the next 6 dialogs.
17. Then hit “Yes” to set your machines time zone.
18. Then hit “No” when asked if your CMOS clock is set to UTC.
19. Select your region, then your country, then timezone.
20. When asked to enable Linux binary compatibility, hit no.
21. Hit “Yes” to having a mouse, and then hit no when asked to browse the content for the FreeBSD package.
22. Hit Yes to adding user accounts.
23. Select User to add a new user.
24. Name your user, (i.e normaluser) and give a password (i.e user12345!), then hit okay, then exit user adding menu.
25. When asked to set “root’s password, set it. (i.e rootAxR5!).
26. Then hit no to last dialog, you can now exit installation.
27. You can now reboot and log on.
PgCluster INSTALLATION GUIDE
Download PgCluster from http://pgfoundry.org/frs/download.php/1705/pgcluster-1.9.0rc5.tar.gz
1. Login as root, and create a directory called “pgcluster” under /root (mkdir /root/pgcluster)
2. Move the source package to pgcluster directory (mv /source/path/of/the/package /root/pgcluster)
3. Untar the package with “tar -zxvf pgcluster-1.9.0rc5.tar.gz”
4. Enter the directory with “cd pgcluster-1.9.0rc5″
5. Do a configure with “./configure”
6. Open Makefile.global with a text editor (use nano or pice, pico /root/pgcluster/pgcluster-1.9.0rc5/src/Makefile.global)
7. Find the line starting with “LIBS” and change it to : “LIBS = -lz -lreadline -lcrypt -lm -lcompat -lpthread”
8. Go back to the root directory of the program (cd /root/pgcluster/pgcluster-1.9.0rc5)
9. Type “gmake”
10. If all goes well without any errors, type “gmake install”
11. As root, enter the following commands:
adduser postgres mkdir /usr/local/pgsql/data chown postgres /usr/local/pgsql/data
CONFIGURING FREEBSD
In order to prevent unprepared access during starting / stop, a load balancer should start at the end. Stop from a load balancer conversely at the time of a stop.
/usr/local/etc/rc.d/pgcluster startor
/usr/local/etc/rc.d/pgcluster stopon all computers. To start/stop them manually:
Replication Server
1. Start replication server
$ /usr/local/pgsql/bin/pgreplicate -D /usr/local/pgsql/etc2. Stop replication server
$ /usr/local/pgsql/bin/pgreplicate -D /usr/local/pgsql/etc stop3. Restart replication server
$ /usr/local/pgsql/bin/pgreplicate -D /usr/local/pgsql/etc restart4. Usage of a replication server’s operation command.
pgreplicate [-D path of a configuration file] [-W path of a log file][-v] [-n] [-l][-h] [stop | restart]Clusters
1. Starting of Cluster DB
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data -o "-i" start2. Stop of Cluster DB
$ /usr/local/pgsql/bin/pg_ctl -D /usr/local/pgsql/data stopLoad Balancer
1. Start load balance server
$ /usr/local/pgsql/bin/pglb -D /usr/local/pgsql/etc2. Stop load balance server
$ /usr/local/pgsql/bin/pglb -D /usr/local/pgsql/etc stop3. Restart load balance server
$ /usr/local/pgsql/bin/pglb -D /usr/local/pgsql/etc restart4. Usage of a load balance server’s operation command.
pglb [-D path of a configuration file] [-W path of log file][-v] [-n] [-l][-h] [stop | restart]
PERFORMANCE TESTING
We used Bristlecone from Continuent Systems for performance testing.Bristlecone provides tools for testing database performance. There are two main tools included in the package.
Evaluator generates mixed loads of inserts, updates, deletes, and selects. Output can be built-in graphics, HTML, XML, or CSV.
Benchmark runs performance test cases with systematically varying parameters. Output can be HTML or CSV.
The Bristlecone tools were designed for comparative evaluations of database clusters. They include built-in support for threading, ability to generate new tests quickly with simple configuration file changes, and the ability to do systematic tests across different database implementations.
Bristlecone is written in Java. It has mostly run on Linux but is easily adaptable to other platforms. Bristlecone has been tested against the following database types: PostgreSQL, MySQL, and HSQLDB (Hypersonic)
1. Download Bristlecone from https://forge.continuent.org/frs/download.php/1069/bristlecone-0.6-bin.zip
2. Unzip it in a directory
3. Go to bin directory
4. Create the following file: postgresqlConnection.properties
url=jdbc:postgresql://loadbalancer user=benchmarkuser password=benchmark type=PostgreSQL 8.3 #reusedata=true #replicaUrl=jdbc:postgresql://pelican/test2 # Command to reformat/analyze tables. analyzeCmd=vacuum full analyze5. Create the following file: benchmark.properties
scenario=com.continuent.bristlecone.benchmark.scenarios.WriteSimpleScenario # Specify separate connection files for mcluster/mysql. include=pclusterConnection.properties|postgresqlConnection.properties # Specify thread combinations. threads=1|2|4 # Specify table combinations. tables=1|2|4 # Remaining values are fixed or irrelevant. iterations=1000 datarows=1000 datatype=varchar|blob|text datawidth=100 fetchrows=106. Create the following file: pclusterConnection.properties
url=jdbc:postgresql://loadbalancer/benchmarkdb user=benchmarkuser password=benchmark type=uni/cluster for PG # Command to reformat/analyze tables. analyzeCmd=vacuum full analyze7. Enter config/evaluator directory and create the following file: postgres.xml
< !DOCTYPE EvaluatorConfiguration SYSTEM > <evaluatorconfiguration name="postgres" testDuration="120" autoCommit="true" statusInterval="2" xmlFile="postgresResults.xml"> <database driver="org.postgresql.Driver" url="jdbc:postgresql://loadbalancer/benchmarkdb" user="benchmarkuser" password="benchmark"/> <tablegroup name="tbl" size="200"></tablegroup> <threadgroup name="A" threadCount="40" thinkTime="500" updates="7" deletes="1" inserts="2" readSize="10" rampUpInterval="10" rampUpIncrement="10"/>
8. Before starting a benchmark/evaluation, create a test database from loadbalancer:
# su postgres $/usr/local/pgsql/bin/createuser -S -d -R -P benchmarkuser[And enter the password "benchmark" when asked]
$ /usr/local/pgsql/bin/createdb -owner=benchmarkuserThis will create the database on all clusters. If not, check your cluster configuration settings and make sure your clusters/loadbalancers are up and connected to each other and running.
9. To benchmark, go to bin folder in bristlecone directory and type:
$./benchmark.shThe benchmark should start
10. To evaluate, go to bin folder in bristlecone directory and type:
$./evaluator.sh ../config/evaluator/postgres.xmlEnjoy. Keep the light.
Bora Bilgin
No comments:
Post a Comment