On 8/17/05, Ron <rjpeace@earthlink.net> 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