Slideshare uses cookies to improve functionality and performance, and to provide you with relevant advertising. If you continue browsing the site, you agree to the use of cookies on this website. See our User Agreement and Privacy Policy.

Slideshare uses cookies to improve functionality and performance, and to provide you with relevant advertising. If you continue browsing the site, you agree to the use of cookies on this website. See our Privacy Policy and User Agreement for details.

3.
What is PgQ?
 A queue implementation on PostgreSQL with a stored procedure
interface.
 Events are queued by producers to be subsequently processed by
consumers.
producer event queue events consumer
Database

5.
PgQ: Features
 Transactional. Events are created transactionally, can be coupled
with surrounding business logic.
 Efficient. Events are processed in batches which gives low per event
overhead.
 Flexible. No limits on the number of producers or consumers. Custom
event formats.
 Reliable. Events are stored in PostgreSQL database – this adds the
benefit of write ahead logging and crash recovery.
 Easy to use. Simple SQL interface, API-s for several languages.
 Open Source. No licensing fees, but occasionally you'll have to get
your hands dirty.

8.
PgQ: brief history
 Started on 2006
 Inspired by ideas from Slony
 First application was Londiste replication
 Open source since 2007 as part of Skytools framework
 Version 3.0 in progress, alpha version out.

9.
PgQ: glossary
 Event - atomic piece of data created by Producers. In PgQ event is one record in
one of tables that services that queue. PgQ guarantees that each event is seen at
least once but it is up to consumer to make sure that event is processed no more
than once if that is needed.
 Batch - PgQ is designed for efficiency and high throughput so events are grouped
into batches for bulk processing.
 Queue - Event are stored in queue tables i.e queues. Several producers can write
into same queue and several consumers can read from the queue. Events are kept
in queue until all the consumers have seen them.
 Producer - applications that pushes event into queue. Producer can be written in
any language that is able to run stored procedures in PostgreSQL.
 Consumer - application that reads events from queue. Consumers can be written in
any language that can interact with PostgreSQL.

13.
PgQ: Batch
 Events are grouped into batches for efficient processing.
 Consumers obtain events in batches.
 Batch size can be tuned to suit the application or network topology. For
example, we might want to use a larger batch size for processing over
wide area networks.
 Small batches have higher processing overhead, however too big
batches have their own disadvantages.
 Batches are prepared by separate process called the ticker.

14.
PgQ: Ticker
 Is a daemon that periodically creates ticks on the queues. The tick is
essentially a position in the event stream.
 A batch is formed of events that are enqueued between two ticks.
 Without ticker there are no batches, without batches events cannot be
processed.
 Pausing the ticker for extended period will produce a huge batch,
consumers might not be able to cope with it.
 Ticker is also involved in miscellaneous housekeeping task, such as
vacuuming pgq tables, scheduling retry events and rotating queue
tables.
 Keep the ticker running!

15.
PgQ: Consumer
 Subscribes to queue.
 Obtains events from queue by asking for a batch.
 Sees only events that have been produced after the subscription.
 Events are seen at least once – events don't get lost.
 Must have some sort of event tracking to process only once –
skytools has several implementations.
 If the event cannot be immediately processed it can be postponed for
retry processing (eg. some resource temporarily unavailable).

17.
PgQ: Consumer status
 Can be obtained by calling pgq.get_consumer_info()
 Reports queue, consumer name, lag and last seen for all of the
consumers. Parameterized versions also available.
 lag is the age of the last finished batch.
 last seen is the elapsed time since consumer processed a batch.
userdb=# select * from pgq.get_consumer_info();
-[ RECORD 1 ]-+----------------
queue_name | notifications
consumer_name | welcome_emailer
lag | 02:32:34.440654
last_seen | 00:00:00.019398
last_tick | 4785
current_batch | 4754
next_tick | 4786

18.
PgQ: Event tracking
 PgQ guarantees that the consumer sees the event at least once. But
this could mean that the consumer could see the event more than
once.
 This happens if the consumer crashes in the middle of processing a
batch.
 Not a problem if the events are processed in the same database that
they are produced – just keep the processing in the same transaction.
 We need to address the case where events are processed outside a
database or in a remote database.
 Consumer needs to be able to keep track of which events are already
processed.

19.
PgQ: Event tracking
 Event and batch tracking support is included in pgq_ext schema – this
is part of Skytools, but must be installed separately to target database.
 Use batch tracking when the whole batch is processed transactionally
pgq_ext.is_batch_done(consumer, batch_id)
pgq_ext.set_batch_done(consumer, batch_id)
 Per-event tracking is used to keep track of single events:
pgq_ext.is_event_done(consumer, batch_id, ev_id)
pgq_ext.set_event_done(consumer, batch_id, ev_id)
 Event tracking can be used to implement distributed transactions.

20.
PgQ: distributed transactions
 Event tracking can be used to implement asynchronous distributed
transactions.
 We'll use batch tracking as an example.
 Skip the batch if it is already processed on target.
 Otherwise process the batch and mark as processed.
 Commit on target.
 Finish batch and commit on source.
 Skytools framework handles this automatically!

26.
Skytools 3: new features
 Cooperative consumer – distributing the load of a single consumer
between cooperating sub consumers.
 Cascading support – identical queue is maintained across nodes,
consumers can easily move between nodes.
 Per-database tickers replaced with a single pgqd daemon.
 qadmin utility for managing queues and consumers.

27.
PgQ: Cooperative consumer
 Sometimes single consumer cannot keep up with the volume of
incoming events.
 Parallel consumers would help, but we need to somehow divide the
workload – avoid processing the same event twice.
 We need the consumers to work in cooperation!
 Skytools 3 introduces sub consumers for the purpose. These share the
workload of the master consumer in a cooperative manner.
 There are some differences in registration and batch handling, but they
look a lot like regular consumers:
pgq_coop.register_subconsumer(qname,cname,scname)
pgq_coop.unregister_subconsumer(qname,cname,scname,mode)
pgq_coop.next_batch(qname,cname,scname)
pgq_coop.finish_batch(batch_id)

28.
PgQ: Cascading
 The cascade is a set of database
nodes and a queue that is distributed root branch 1
between the nodes. Event and batch q1 q1
numbers are kept identical!
 The cascade can be depicted as a
tree, where events created in the root
are propagated down the cascade to
other nodes. q1
leaf
 There can be only one root node, but branch 2
any number of branches or leaves.
 Leaf nodes are specific to replication.
They don't have a copy of the queue
and don't participate in event
propagation.

29.
PgQ: Cascading
 Typical replication cascade would consist of a primary database -- the
root, replicated to standby database – a branch.
 We can easily switch the replication roles of root and branch nodes.
Consumers will continue as if nothing happened.
 On branch node failure we move its consumers to some surviving
node. Business as usual.
 On root node failure we promote some surviving branch to root and
reattach the consumers. In this scenario we have to deal with data
loss.

32.
Skytools: introduction
 Set of applications for replication, batch processing and queue
management.
 Includes a Python scripting framework that greatly simplifies the
implementation of custom consumers.
 Written mostly in Python with some bits in C.
 PgQ is distributed as part of Skytools.
 Get it from http://pgfoundry.org/projects/skytools/
 We'll start by configuring the ticker.

33.
The Ticker
 The ticker prepares batches for the consumers.
 Also performs queue maintenance: table rotation, vacuuming, re-
queuing retry events.
 We'll need to configure it for each database that has queues.
 Skytools 3 includes a super-ticker that handles all the databases in a
PostgreSQL cluster.

34.
pgqadm.py: configuration file
 db – the name of the database [pgqadm]
job_name = pgqadm_userdb
where queues are located. db = dbname=userdb
 maint_delay_min – interval, # how often to run maintenance [minutes]
at which maintenance maint_delay_min = 5
commands are run. # how often to check for activity [secs]
loop_delay = 0.5
 loop_delay – interval, at which
the ticker wakes up to check for logfile = log/%(job_name)s.log
pidfile = pid/%(job_name)s.pid
work.

43.
Custom consumer
 We'll write a small queue application called welcome_consumer.
 The application reads events from a queue and prints out event
payload if type matches “welcome”.
 The application base class extends Consumer which in turn extends
DBScript.
 Consumer implements the pgq consumer event loop. We only need to
add the bits that do the event handling.
 All of the regular DBScript configuration and command options apply.

46.
Custom consumer: event processing
 The process_event() function is called for each event in a batch.
If there are no batches the script sleeps loop_delay seconds and
retries.
 A DB API connection to the queue database is passed in src_db,
the transaction will be committed after successfully returning from
process_event().
 On failure, the transaction will be rolled back, active batch will be
reprocessed on next iteration.
 We need to call tag_done() for the processed events – otherwise
they'll be scheduled for retry processing.
...
def process_event(self, src_db, ev):
if ev.ev_type == 'welcome':
self.log.info('Welcome %s!' % ev.ev_data)
ev.tag_done()
...

66.
Skytools: migrations and upgrades
 Upgrading a database with PgQ – pretty much straightforward, but has
some additional steps.
 Migrating consumers from one database to another – to take some
load off the primary server or to prepare for database migrations.
 Migrating whole databases.

68.
Skytools2: migrating consumers
1. Set up a queue mover to replicate the queue to new database, we will
move the consumer subscriptions to the queue replica.
2. Stop the ticker on primary database - no more new batches will be
prepared. After processing the pending batches, the consumers will
stop at identical positions on the queue.
3. We can now subscribe the consumers to the replicated queue. Note
that we need to reset the event tracking for the migrated consumers.
Replication tools have --reset option for the purpose.
4. Start the ticker. Queue mover will continue queue replication,
consumers on the new queue will continue where they left off.
5. If all is good, unregister the consumers from the old master.

69.
Skytools3: migrating consumers
 Cascaded queues are kept identical across nodes - no need to set up
explicit queue movers.
 Consumers that extend CascadedConsumer can be switched by
simply running change-provider command of the set admin tool.
 No need to mess around with tickers and configuration files.
 The core features are complete, some development needed.

70.
Skytools2: migrating databases
1. Create the database structure on the new host. Omit pgq, pgq_ext and
londiste schemas – better to reinstall those later.
2. Replicate the database to the new host using londiste.
3. Create the queues and changelog triggers on the new database.
4. Pay special attention to applications that use stored procedures to
enqueue events - maybe a queue mover is needed?
5. Migrate the consumers to the new database.
6. Make the primary database read-only and wait for londiste replication
to catch up.
7. Redirect the applications to the new database.

71.
Skytools3: migrating databases
 Cascading can be used to greatly simplify the migration process.
 The target database should be a branch node of the cascade.
 Migration is then performed by stopping the applications, running a
londiste switchover command and redirecting the applications to the
new database.
 Switchover will switch the roles of the root and branch, consumers
needn't be aware that something changed.

72.
Skytools: monitoring consumers
 We need to ensure that all our consumers are running happily.
 The best indicator for this is the consumer lag – if a consumer is
lagging, it is not processing events adequately.
 pgqadm.py status command or pgq.get_consumer_info() SQL
function can be used to determine the lag.
 In the example welcome_consumer hasn't processed anything in 6
days – probably not running at all.
select queue_name, consumer_name, lag, last_seen from pgq.get_consumer_info();
queue_name | consumer_name | lag | last_seen
---------------+--------------------+-----------+----------
notifications | user_counter | 00:00:43 | 00:00:00
notifications | welcome_consumer | 6 days | 6 days

73.
Skytools: logging
 Skytools applications use Python logging module which can be used to
forward the log and statistics messages to a central location.
 Just set use_skylog = 1 in the configuration and configure the log
handlers in skylog.ini
 Use syslog or write your own log handler. Examples are provided for
sending the log over UDP or to a PostgreSQL database via stored
procedure calls (see skylog.py).
 At Skype, we use the logging facilities to populate a configuration
management database and feed error messages to Nagios.