Postgresql in Education

AICT, University of Alberta

Academic Information & Communication Technologies http://www.aict.ualberta.ca

Application Hosting Currently 6 people taking care of •  Shared Oracle environment with about 120 databases and

applications •  Shared SQL Server environment with about 150+ databases and

applications •  Individual databases and applications ~200 (oracle, sql server,

mysql, postgresql, tomcat, alfresco, AIM, moodle, etc.) •  SLA’s from on-incident support, business hours to 24x7 support. •  Dealing with Financial data and private (FOIP protected) data •  Go to team for application / database errors for people we do not

directly support. •  Transitioning and consolidating applications and databases from

around 75 departments.

Initiation

Implement

Project Management

Group

Service Life cycle

Why am I here?

In 2010 University of Alberta was looking for a new Learning Content Management System. Our existing system was Blackboard Vista running on Oracle.

Objectives:

-  Reduce licensing costs

-  Create a service people want to use

-  Improve service reliability

-  Collaborate with other institutions across the province and beyond

We selected Moodle (Open Source) on Postgresql (9.0). Migration took around 2 years.

Central Learning Management System

Upcoming and existing projects using Postgresql

OTRS Ticketing System

Primary Secondary (used for

searching)

Reporting DB

Jaspersoft Business Intelligence

Enterprise Document and Records Management System •  Business critical records like land titles, contracts, billing, student records •  Expected size in 1 year – 1.5 TB (DB + FS) with 3 departments on board. •  73 more departments to go

New Shared Postgresql Environment •  24x7 managed shared environment •  Automated replication to TEST / UAT •  Service reporting for clients?

Core considerations •  We are not particularly big or busy •  We do care about our data (and jobs!) •  People are expensive •  Incidents are expensive and disruptive •  Reliability is important, up to a point. •  KISS. Over-engineering usually comes with high

maintenance costs.

Lessons Learned •  Only change things you absolutely need. •  vm.dirty_bytes and vm.dirty_background_bytes •  Autovacuum is usually just fine (but give it more

maintenance memory) •  Iptables and pg_hba.conf. Only allow specific ip’s

access to your database servers. •  To pool or not to pool? •  Omnipitr is your friend

Basic backup procedure

Monitoring •  CPU •  RAM •  Disk •  Presence of postgresql process •  Backups (including temporary space) •  Replication status •  Long running queries •  ?

Load Replication ideas / procedure TEST / UAT environments never see the same level of traffic as production. 1. Query pg_stat_activity on a schedule 2. Get a sampling of all the queries being run (caveats) 3. Generate statistics on the data (order by times a given

query was run) 4. Create a pgbench script to be used against your test /

uat. 5.  include write queries as well as read for better testing.

0

20

40

60

80

100

120

140

160

1 3 5 7 9 11 13 15 17 19 21 23 25 27 29 31 33 35 37 39 41 43 45 47 49 51 53 55 57 59 61

Average tps, 500 users, Read/write workload on SSD, 50 GB DB.

Average tps

Example performance run

Upgrading Option 1: pg_upgrade: •  ~ 24 hours to perform upgrade – not acceptable Option 2: Dump / restore •  time pg_dump -p 5434 -Fc moodle > moodle.dump (9m

30 sec) •  time pg_restore -C -e -j 4 -v -d postgres moodle.dump

(131 m) Option 3: Upgrade standby and setup replication? •  Needs testing

Challenges moving into hosting a multi-tenant environment. •  Identifying problem queries and clients •  Isolating problem clients (limiting cpu / ram) •  Generating client usage reports •  Better upgrade procedure •  Minimizing maintenance tasks – setting up schedules

for test / uat.

Challenges moving into hosting a multi-tenant environment.

•  Identifying problem queries and clients •  Isolating problem clients (limiting cpu / ram, disk) •  Generating client usage reports •  Migrating from other systems? Oracle? SQL Server?

MySQL?

Closing notes •  Slides, documentation and scripts available at

http://www.ualberta.ca/~dostatni/pgcon2013 •  Please contact me if you have any questions. I am

happy to help – dostatni@ualberta.ca •  Questions?