kennygorman.com

The love of Data, Database Engineering, Architecture, Entrepreneurship, and other assorted bits

Primary Keys for Slony-I

23 March 2008

One of the prerequisites for <a href=http://slony.info>Slony</a> replication is to be sure that primary keys (or at least suitable columns) are defined for each table to be under replication. This is a simple example script for getting that data in postgresql. This assumes that the intention is to replicate all of the public schema.

Read More

Memory sizing for PostgreSQL

11 March 2008

I needed to make an assessments of a linux server’s memory footprint consumed by postgresql, however, calculating the exact memory footprint of a postgresql database is not as straightforward as one might think. Since unix utilities like top, and ps tend to show some amount of shared memory usage in their tallies, they are next to useless for this purpose. The other problem is Linux caches filesystem buffers to fill up most free space on the box, so deciphering what is actually used by postgresql and what is really filesystem buffers is confusing as well. The calculation needed to be just a reasonable estimate of the memory usage, so this method is non-perfect, but should be good for most planning purposes.

Read More

Python and cx_oracle

09 March 2008

I decided to start futzing with Python and Oracle. I generally use perl for most everything, and sometimes php for web based things, but I wanted to try Python at the urging of colleagues. I downloaded the source from here and added in cx_Oracle from here. The installation on linux was about as smooth as possible. I installed the source @ ~oracle/python so I could keep a user space version of Python. This is similar to how I generally install perl. So I created my first application using Python, select * from dual;. No big deal, but wow, Python is a pretty straightforward language. Here are some resources for Oracle based code:

Read More

now on WordPress

09 March 2008

I just updated the blog to WordPress in order to get a few (ok, a lot) more features, and use the general editing environment.  I imported some of the popular posts from my old blog as well.

Read More

Backing up Oracle optimizer statistics

09 March 2008

Oracle 10g has some neat features for keeping track of old statistics. Pre-10g It used to be important to backup your statistics before you analyzed a table just in case your plans went crazy. Now Oracle does this for you automatically. There is a great post on Doug’s Oracle Blog that talks about this with some examples. So now one can analyze as needed without fear of not being able to roll back the statistics to the previous value. In a crisis, it might be something to check to see if analyze recently ran and now plans are bad by selecting from tab_stats_history. Then simply back out the stats with dbms_stats.restore_schema_stats to revert back to a known good statistics state. Be sure to check dbms_stats.get_stats_history_retention and make sure you are keeping a long enough record of stats, you can adjust with dbms_stats.alter_stats_history_retention as needed to keep yourself sleeping at night.

Read More

Performance Tuning: Be Proactive

09 June 2006

I see many articles out there talking about performance tuning using the Oracle waits interface. While I am not contending that technique; I believe it to be sound, and use this technique every day. What I am saying, is performance tuning in this manner, or the ‘traditional’ manner, is inherently reactive. Reactive performance tuning has it’s place, but many time some proactive performance tuning can help reduce the amount of reactive performance tuning a DBA needs to do. By it’s very definition, if one is reacting to some performance problem, it’s most likely it’s impacting someone, and perhaps your company is losing money.

Read More