0

I am running a more or less out the box Postgresql installation which is running really slowly.

I am parsing a whole bunch of logs files using Perl and using Perl's DBI interface (connecting to the database via the IP address 127.0.0.1) to add the data to the database. My database has around 4 tables. My script basically checks to see whether the normalized data already exists. If the data does not exist it adds it to the database. Otherwise it extracts the keys to use to update other tables.

I am running on more or less desktop hardware with 2GB RAM but I was not expecting it to well over 5 days to add round 12 million rows.

PS. I did increase the size of my shared_buffers to 25% of my RAM but this did not make much of a difference.

Any tips will be appreciated.

Edit: I am running Ubuntu Linux

2
  • 1
    You may need to optimize your queries. Did you consider using index(es) in your table(s) to make the search faster?
    – Khaled
    Nov 15, 2010 at 10:31
  • I do use primary keys and foreign keys when linking tables but I have not explicitly created indexes.
    – doron
    Nov 15, 2010 at 10:41

1 Answer 1

2

I've written scripts to do exactly what you're doing (parsing log files from Squid cache, being the main reason). The queries you're running are probably fairly basic and, assuming you don't have surrogate primary keys, the implict index on the primary key should be used. You can always "EXPLAIN ANALYZE" your queries just to be sure that it looks like they're doing what you'd expect, though.

Basic performance analyis is your first task. You don't mention the OS you're running on so I can't give you nuts-and-bolts instructions, but you should use the underlying OS performance monitoring features to determine where you're bottle-necking (CPU, I/O, memory paging, etc). Even simple tools like "top" and "Task Manager" can quickly give you some idea.

Profiling your script is the next stop. Figure out where the script is spending most of its time and optimize those parts.

Assuming that the values your script retrieves from the database aren't going to be changing while the script is running you might want to consider caching data retrieved during execution. With surrogate primary keys, for example, you can cache the natural key to surrogate key mapping in an associative array during script execution and save repeated database queries for the same values. I find that this is typically a Big WinTM.

1
  • I have edited my OP to say I am working on Ubuntu. I guess there will be interesting information in the /proc directory.
    – doron
    Nov 15, 2010 at 23:33

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .