Well day two here at PgEast has drawn to a close and it was another
very informative day.

Today I concentrated on the more common tasks of a Pg DBA so I attended three
talks (four if you count mine) that where rather heavy on the technical side of being a Pg DBA

Keven Kempter drew me back again with his excellent talk on Backup and recovery methods
this time giving some very good advice on how to use and abuse of pg_Dump_all and
PG_restore. He also touched on three different recipes PITR on ProstgreSQL and gave some handy
advice on when and why to use it.

I also caught another Mongo talk this time by Steve Francia it was on the application of Mongo
in a real world web retail store. He presented a very convincing argument for the NoSQL side of things in
the retail realm namely that RDBMS works great when you have but a few similar products
such as books, CDs and …

I guess I brought the snow with me to New York as I awoke to a nice 10cm dump. Anyway today would best be described as a day of ‘Disruptive Tech’

I first attended Kevin Kempters intro into PorstgreSQL High Availability. A very well balanced presentation that gave a very good overview of what is available out of the box for both Warm Standbys and Hot Standbys how they can be very easily implemented. He also gave a quick overview of other tools that can be used including Slony for detailed fail-overs and PgPool for load balancing and relication. Not very disruptive but it does show that Pg is on par with most of the heavy hitters such as MySQL and Oracle.

The keynote this year was by Ed Boyajian the CEO or EnerpriseDB and he gave an big picture of the DB in terms of market which is a whopping 26$ Billion a year in the US alone of which the the two five players have 90% of the market one having more than half.

He made …

“I believe that one should benchmark before making any technology decisions.” An interview with Pieter van Zyl creator of the OO7J benchmark. In August last year, I published an interesting resource in ODBMS.ORG, the dissertation of Pieter van Zyl, from the University of Pretoria:“Performance investigation into selected object persistence stores”. The dissertation presented the OO7J [...]

Hi Everyone,

I would like to personally invite you to a GreenSQL Express Webinar,
I’ll be demonstrating GreenSQL Express, the free and simple way to keep your information private and safe.

On Wednesday, March 16th (just 2 weeks from now),
It’s called “How to Protect Sensitive Information in Minutes: Setting up GreenSQL Express with Basic Security Rules”

If you’re serious about protecting your data, you need to hear and see how it’s done. I’ll talk about:

1. Why you need a Database firewall / security solution
2. Where and How to install GreenSQL Express in your infrastructure
3. How to use GreenSQL Express to protect you database
4. How to create the security polices you need in minutes
5. How to protect your database from SQL injection attacks
6. How to implement a separation of duties in your database access
7. How to maintain business continuity …

From the Security threat report 2011 by Sophos, Page 46:

“Cybercrime is encroaching more and more into the business space. Industrial espionage, spearphishing of important employees to breach network boundaries and mass theft of customer information are more diffcult to detect and have very serious consequences. At the same time, network boundaries are becoming ever more indistinct and porous as new technologies enable greater access from remote workers and mobile devices. In addition, legal requirements place greater emphasis on traceability and compliance with predefned standards of data hygiene.

Increasing amounts of sensitive data is stored, accessed and manipulated in databases connected to company websites as businesses increasingly interact with their customers through the Internet. As a result, it’s become as easy to …

PostgreSQL 9.0 High Performance

PostgreSQL 9.0 High Performance. By Gregory Smith, Packt 2010. About 420 pages. (Here’s a link to the publisher’s page for this book.)

I enjoyed this book a lot and recommend it to everyone who uses PostgreSQL or MySQL. MySQL users should benefit from understanding PostgreSQL. Beyond that, I learned a lot from this book that I can apply directly to MySQL. In particular, the book begins with a few chapters on hardware performance, benchmarking, and configuration. This material is database-agnostic and very well done. There is about 70 pages of it — it goes into a lot of details. It is more detailed than the similar material in my own book …

Giuseppe has a great post about the Evolution of MySQL metadata, and I thought I’d have a look at what we have in Drizzle. It’s pretty easy to work out how many tables are in each schema, we just query the standard INFORMATION_SCHEMA.TABLES view:

drizzle> select table_schema,count(table_name)
    ->  from information_schema.tables
    -> group by table_schema;
+--------------------+-------------------+
| table_schema       | count(table_name) |
+--------------------+-------------------+
| DATA_DICTIONARY    |                53 |
| INFORMATION_SCHEMA |                20 |
+--------------------+-------------------+
2 rows in set (0 sec)

In Drizzle it’s important to note that there is a differentiation between SQL …

I’ve just committed some changes to diskstats, an I/O analysis tool in Aspersa that’s actually been in the Subversion repository for a long time, but in a barely usable fashion and with no documentation. Now it’s usable and documented.

It is basically a reimplementation of iostat in awk. Why on earth would I reinvent that wheel? Because I spend a lot of time gathering and analyzing raw data from /proc/diskstats, which is vital to really understanding what the storage subsystem is doing. The iostat tool hides important details. Seeing that detail has immediately solved many a disk performance problem and proven SAN vendors wrong, for instance. (I used to do this the old-fashioned way.) Disk performance, of course, is one of the most important …

If you execute the following, what does your RDBMS do?

CREATE TABLE t1 (a int);
START TRANSACTION;
INSERT INTO t1 (a) VALUES (1);
START TRANSACTION;
INSERT INTO t1 (a) VALUES (2);
ROLLBACK;
SELECT * FROM t1;

The answer may surprise you.

A couple of extremely informative recent blog posts have gone to either Planet MySQL or Planet PostgreSQL, but not both, and I think everyone on both aggregators who cares about database internals should be interested in them. Here they are:

• Robert Haas: MySQL vs. PostgreSQL, Part 2: VACUUM vs. Purge
• Ewen Fortune: How InnoDB handles REDO logging

Happy cross-pollination.

Related posts:

1. Awesome Postgres/MySQL cross-pollination
2. …