Friday, May 20, 2011

Classic AskTom Post

Everyone should read this one at least once. Yes even you. The question and his initial answer, at least - AskTom's "perfect example" post

Friday, May 06, 2011

Logging DDL in Oracle

I could have sworn I had drilled into my head (from studying for the Oracle 10g OCA exam) that DDL is audited in the alert log. I had a need for this today but it wasn't showing up.

Turns out, DDL in the alert log is a new feature in 11g, and to enable it you'll want to run:


For older versions you'll have to audit another way. One way that worked for my purposes today is a simple DDL trigger:

Tuesday, May 03, 2011

Pgpool-II Load Balancing

I've been trying, so far unsuccessfully, to get load balancing working in a pgpool-II replication environment with two nodes. This is supposed to spread SELECT queries across multiple nodes, according to the nodes' values of backend_weight. Presumably it does so in a round-robin manner, and if the two nodes had the same weight, query 1 would go to one node, query 2 would go to the other node, query 3 would go back to the first node, and so on...However, my queries are all being sent to one node.

This table, taken from the pgpool-II docs, shows whether a SELECT query will replicate, be sent to master only, or be load balanced based on three conditions:
SELECT is inside a transaction block Y Y Y N N N Y N
replicate_select is true Y Y N N Y Y N N
load_balance_mode is true Y N N N Y N Y Y
results(R:replication, M: send only to master, L: load balance) R R M M R R M L

It's absolutely clear that I have replicate_select set to false and load_balance_mode set to true. I seem to be getting a result of "M", which must mean my SELECT is in a transaction block. It doesn't appear to be though, acc. to my pg logs - at least, I don't see any BEGIN before the SELECT. Hmm!

If I fiddle with my two values of backend_weight and restart pgpool, it will switch to the node with the greater weight. Doesn't help me much, but it shows it's doing...something.

Stay tuned...