On 8/17/05, Ron <[EMAIL PROTECTED]> wrote: > At 05:15 AM 8/17/2005, Ulrich Wisser wrote: > >Hello, > > > >thanks for all your suggestions. > > > >I can see that the Linux system is 90% waiting for disc io. ... > 1= your primary usage is OLTP-like, but you are also expecting to do > reports against the same schema that is supporting your OLTP-like > usage. Bad Idea. Schemas that are optimized for reporting and other > data mining like operation are pessimal for OLTP-like applications > and vice versa. You need two schemas: one optimized for lots of > inserts and deletes (OLTP-like), and one optimized for reporting > (data-mining like).
Ulrich, If you meant that your disc/scsi system is already the fastest available *with your current budget* then following Ron's advise I quoted above will be a good step. I have some systems very similar to yours. What I do is import in batches and then immediately pre-process the batch data into tables optimized for quick queries. For example, if your reports frequenly need to find the total number of views per hour for each customer, create a table whose data contains just the totals for each customer for each hour of the day. This will make it a tiny fraction of the size, allowing it to fit largely in RAM for the query and making the indexes more efficient. This is a tricky job, but if you do it right, your company will be a big success and buy you more hardware to work with. Of course, they'll also ask you to create dozens of new reports, but that's par for the course. Even if you have the budget for more hardware, I feel that creating an effective db structure is a much more elegant solution than to throw more hardware. (I admit, sometimes its cheaper to throw more hardware) If you have particular queries that are too slow, posting the explain analyze for each on the list should garner some help. -- Matthew Nuzum www.bearfruit.org ---------------------------(end of broadcast)--------------------------- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq