Steve Shockley wrote:
I've currently got a server running syslog-ng (1.6.9) with PostgreSQL (8.1.1) on a 3.9 snapshot from March 1. The setup has been working well for a while, but I've recently been told to have it accept syslog for a couple of anti-spam appliance devices, and they generate between 1-2 million syslog entries per day, and that seems to kill PostgreSQL's performance. Any query which needs to iterate the whole database will take 10-15 minutes after a day or so, and that's no good for a web front-end.
<snip>
When I'm accepting syslog from the mail devices, a postgres process holds steady using 5-10% CPU, but the load average hovers between 1 and 2.
<snip>
Is there anything else I can do to improve performance on this hardware? It seems that I'm not using very much memory (around 512mb of 2gb), what's the best way to tell PostgreSQL to use more memory, in a way that will actually make a difference?
A lot of things come to mind that you might do. The hardware is more than capable of taking the info in, storing it, and letting you search it. It's how you're currently doing it that's not working so well, and that probably has little to do with tuning kernels and daemons.
Knowing neither the database structure nor the queries, I can only guess at what would really help...
Tons of steady inserts on one end of an index (such as time) will lead to a lot of tree balancing. Can you partition the data into unindexed hourly tables, indexes to be added *after* you're done inserting?
Can you make some generalizations about the queries typically performed and structure the database to make it faster to access?
There are about two dozen questions like this, but you get the idea. -- Darrin Chandler | Phoenix BSD Users Group [EMAIL PROTECTED] | http://bsd.phoenix.az.us/ http://www.stilyagin.com/ |
