Tuesday, May 10. 2011
A reminder that tomorrow, Wednesday (the 11th) is the BWPUG meeting for
May. This month Greg Smith will be stopping by to give a preview of
his upcoming PGCon Tutorial, "Postgres Performance PItfalls".
PostgreSQL is a database system that can deliver excellent performance
for a wide variety of applications. But it's easy to run into an issue
that keeps you from seeing its full potential. There are a few basic
PostgreSQL configuration and use misunderstandings that cause most of
the early performance issues administrators and developers encounter.
When: May 11th, ~6:30PM.
Where: 7070 Samuel Morse Dr, Columbia, MD, 21042.
Host: OmniTI
As always we will have time for networking and we can do some more
open Q & A, and we'll likely hit one of the local restaurants after
the meet.
BWPUG Meetup Page
BWPUG Mailing List
Wednesday, April 13. 2011
A reminder that today, Wednesday (the 13th) is the BWPUG meeting for
April. This month we're going to forgo a formal speaker and in favor of an "Open Mic Night". Got questions about Postgres? Need help on a problem? Recently done something awesome? Did you learn anything from PGEast? Come swap stories with fellow Meetup members and/or get help if you need it.
When: April 13th, ~6:30PM.
Where: 7070 Samuel Morse Dr, Columbia, MD, 21042.
Host: OmniTI
As always we will have time for networking and we can do some more
open Q & A, and we'll likely hit one of the local restaurants after
the meet.
Don't forget to check out our Meetup page, please feel free to sign up and/or RSVP.
Sunday, April 3. 2011
I think I'm just about dug out from last weeks NYC trip for PGEast. I have to say I had a good time at the conference, although I was kept busy enough not to be able to get to see nearly as many presentations as I would have liked. One thing I did walk away with was a renewed appreciation for the projects we get to work on at OmniTI. I angled most of my talks toward PostgreSQL 9.0, going in thinking that a majority of people would have probably made the move already. Apparently that isn't the case, which in retrospect seems obvious; a majority of our customers still run some 8.x version of Postgres, as we work to vet applications and find the right windows to make their upgrades happen. Still, we are fortunate to have some clients who are very aggressive when it comes to server versioning, and we're lucky enough to get to run a healthy amount of Postgres 9, in some cases with fairly complex distributed server arrangements.
In any case, as per the title of this post, slides for my talks are now up on slide share, feel free to peruse, and if you have any questions please let me know.
Wednesday, March 16. 2011
Earlier today my colleague Depesz posted a nice write up showing one of the use cases for the new 9.1 feature, "Writable CTE's". It certainly shows one handy thing that this feature will enable, but it's not the only one. Here's a quick little bit of SQL I have been playing with for some time that re-implements the infamous "UPSERT" command (a long time sticking point for people trying to make MySQL apps more cross-database compatible) into Postgres.
pagila=# select * from actor where first_name = 'AMBER' and last_name = 'LEE';
actor_id | first_name | last_name | last_update
----------+------------+-----------+-------------
(0 rows)
pagila=# with upsert as
pagila-# (update actor set last_update = now() where first_name = 'AMBER' and last_name = 'LEE' returning actor_id)
pagila-# insert into actor (first_name, last_name, last_update)
pagila-# select 'AMBER', 'LEE', now() WHERE NOT EXISTS (SELECT 1 FROM upsert) ;
INSERT 0 1
pagila=# select * from actor where first_name = 'AMBER' and last_name = 'LEE';
actor_id | first_name | last_name | last_update
----------+------------+-----------+----------------------------
201 | AMBER | LEE | 2011-03-16 11:29:15.611445
(1 row)
pagila=# with upsert as
pagila-#(update actor set last_update = now() where first_name = 'AMBER' and last_name = 'LEE' returning actor_id)
pagila-# insert into actor (first_name, last_name, last_update)
pagila-# select 'AMBER', 'LEE', now() WHERE NOT EXISTS (SELECT 1 FROM upsert) ;
INSERT 0 0
pagila=# select * from actor where first_name = 'AMBER' and last_name = 'LEE';
actor_id | first_name | last_name | last_update
----------+------------+-----------+----------------------------
201 | AMBER | LEE | 2011-03-16 11:30:21.244226
(1 row)
Now, to be fair, this bit of SQL does have a race condition (think two people trying to insert the same actor at the same time), so it doesn't really solve all of your problems, but if you are looking for a quick hack, it might just do the trick. Also don't be afraid to play with it; this was like 2 minutes of thought and making sure the syntax worked; you could certainly try turning it around or coming up with other variants. That's actually one of the coolest things about this feature; waiting to see what use cases people come up with for it.
Tuesday, March 1. 2011
If you spend anytime either giving or receiving database tuning advice, perhaps the most common phrase you will hear is "it depends". Of course that is made in reference to a whole lot of things, but one of the things it depends on is read vs write load. While that's easy to point out, it's not always that obvious as to how best to measure read/write load for a system.
Continue reading "Monitoring Read vs. Write Activity In Postgres "
Monday, February 7. 2011
Once again someone has brought up the idea of having a hints system in Postgres, which means once again we're all subjected to watching people trod out the same tired, faulty, and even self-contradictory reasons to try to justify the idea that Postgres doesn't have, need, or even want a hinting system. As frail as the arguments might be, people are so entrenched in their positions now that even having a discussion on the topic is difficult. And in fact, there are really two discussion going on here; one is whether Postgres should have any type of hinting system, and the other a more specific discussion on if we should have a query hinting system.
Continue reading "Why the F&#% Doesn't Postgres Have Hints?!?! "
Tuesday, January 18. 2011
People ask me why I haven't blogged much lately and I generally tell them I've been busy fixing broken stuff. Then they tell me to blog about the broken stuff I'm fixing. So, ok. Here's a random encounter from before the holidays....
There's nothing like trying to log into a database server to do some simple schema work, only to have you're login hang trying to load your session. After some brief poking around and attempts to login with different accounts, I was able to see that we had a disk problem on the database. (Nail in the coffin, zpool status, the zfs command which shows the condition of your drives, was hanging trying to show the last pool). So, poke the SA's and see what they can dig up... and unfortunately things were bad enough, we were going to need a reboot. So, we reboot.
Upon restart, well, things don't restart; where "things" means postgres. SMF shows that we tried to start, but something has gone awry. Looking in the database logs, I see this
2010-12-23 17:29:16 EST:resmon@127.0.0.1(32789):782: FATAL: the database system is starting up
2010-12-23 17:29:17 EST:sauron@10.0.0.231(58978):783: FATAL: the database system is starting up
2010-12-23 17:29:18 EST:@:494: FATAL: could not create relation 16385/58326/58757497: File exists
2010-12-23 17:29:18 EST:@:494: CONTEXT: xlog redo file create: 16385/58326/58757497
2010-12-23 17:29:18 EST:@:492: LOG: startup process (PID 494) exited with exit code 1
2010-12-23 17:29:18 EST:@:492: LOG: aborting startup due to startup process failure
Continue reading "A present from my filesystem"
|
You were saying?
Tue, 07.02.2012 05:16
Hi Robert, Tanks for your t houghtful interest in my lates t post. You are absolutely right about the underlyi [...]
Tue, 20.12.2011 10:49
thanks for the slides and the post.
Sun, 27.11.2011 15:42
And the slides are up at http: //www.2ndquadrant.com/en/talks /