Обсуждение: Which hardware ?

Hi,

I need to install a 8.3 database and was wondering which hardware would be
sufficient to have good performances (less than 30s for� slowest select).

Database size: 25 Go /year, 5 years of history
One main table containing 40 million lines per year.
Batch inserts of 100000 lines. Very very few deletes, few updates.

30 other tables, 4 levels of hierarchy, containing from 10 lines up to 20000
lines.
5 of them have forein keys on the main table.

I will use table partitionning on the year column.

Statements will mainly do sums on the main table, grouped by whatever column
of the database (3-5 joined tables, or join on join), with some criterions
that may vary, lots of "joined varchar in ('a','b',...,'z')".
It's almost impossible to predict what users will do via the webapplication
that queries this database: almost all select, join, group by, where...
possibilities are available.

Up to 4 simultaneous users.

I'm planning to host it on a quad xeon 2.66Ghz with 8Go of DDR2, and a dual
(RAID1) SATA2 750Go HD.
Perharps with another HD for indexes.

Do you think it will be enough ?
Is another RAID for better performances a minimum requirement ?
Will a secondary HD for indexes help ?

Which OS would you use ? (knowing that there will be a JDK 1.6 installed
too)

With 5 millions of lines, the same application runs quite fast on windows
2000 on a single P4 2.8 GHz (very few statements last more than 10s, mostly
when concurrent statements are made). Each statement consumes 100% of the
CPU.


thanks for advices.

On Tue, Jun 17, 2008 at 7:38 AM, Lionel <lionel@art-informatique.com> wrote:
> Hi,
>
> I need to install a 8.3 database and was wondering which hardware would be
> sufficient to have good performances (less than 30s for² slowest select).
>
> Database size: 25 Go /year, 5 years of history
> One main table containing 40 million lines per year.
> Batch inserts of 100000 lines. Very very few deletes, few updates.
>
> 30 other tables, 4 levels of hierarchy, containing from 10 lines up to 20000
> lines.
> 5 of them have forein keys on the main table.
>
> I will use table partitionning on the year column.
>
> Statements will mainly do sums on the main table, grouped by whatever column
> of the database (3-5 joined tables, or join on join), with some criterions
> that may vary, lots of "joined varchar in ('a','b',...,'z')".
> It's almost impossible to predict what users will do via the webapplication
> that queries this database: almost all select, join, group by, where...
> possibilities are available.
>
> Up to 4 simultaneous users.
>
> I'm planning to host it on a quad xeon 2.66Ghz with 8Go of DDR2, and a dual
> (RAID1) SATA2 750Go HD.
> Perharps with another HD for indexes.
>
> Do you think it will be enough ?
> Is another RAID for better performances a minimum requirement ?
> Will a secondary HD for indexes help ?

More drives, all in the same RAID-10 setup.  For reporting like this
writing speed often isn't that critical, so you are often better off
with software RAID-10 than using a mediocre hardware RAID controller
(most adapatecs, low end LSI, etc...)

You'd be surprised what going from a 2 disk RAID1 to a 4 disk RAID10
can do in these circumstances.  Going up to 6, 8, 10 or more disks
really makes a difference.

> Which OS would you use ? (knowing that there will be a JDK 1.6 installed
> too)

I'd use RHEL5 because it's what I'm familiar with.  Any stable flavor
of linux or FreeBSD7 are good performance choices if you know how to
drive them.

> With 5 millions of lines, the same application runs quite fast on windows
> 2000 on a single P4 2.8 GHz (very few statements last more than 10s, mostly
> when concurrent statements are made). Each statement consumes 100% of the
> CPU.

That statement about concurrent statements REALLY sells me on the idea
of a many disk RAID10 here.  I'd take that over quad cores for what
you're doing any day.  Not that I'd turn down quad cores here either.
:)

On Tue, Jun 17, 2008 at 03:38:59PM +0200, Lionel wrote:
> Hi,
>
> I need to install a 8.3 database and was wondering which hardware would be
> sufficient to have good performances (less than 30s for� slowest select).

> Statements will mainly do sums on the main table, grouped by whatever column
> of the database (3-5 joined tables, or join on join), with some criterions
> that may vary, lots of "joined varchar in ('a','b',...,'z')".
> It's almost impossible to predict what users will do via the webapplication
> that queries this database: almost all select, join, group by, where...
> possibilities are available.

I'm not sure that I have any specific recommendation to make in the
face of such sweeping requirements.  But I'd say you need to make I/O
cheap, which means piles of memory and extremely fast disk
subsystems.

Also, there's another important question (which never gets asked in
these discussions), which is, "How much is the performance worth to
you?"  If the last 10% of users get something longer than 30s, but
less than 40s, and they will pay no more to get the extra 10s response
time, then it's worth nothing to you, and you shouldn't fix it.

> Up to 4 simultaneous users.

You won't need lots of processer, then.

> I'm planning to host it on a quad xeon 2.66Ghz with 8Go of DDR2, and a dual
> (RAID1) SATA2 750Go HD.
> Perharps with another HD for indexes.

How big's the database?  If you can have enough memory to hold the
whole thing, including all indexes, in memory, that's what you want.
Apart from that, "dual SATA2" is probably underpowered.  But. . .

> Which OS would you use ? (knowing that there will be a JDK 1.6 installed
> too)

. . .I think this is the real mistake.  Get a separate database box.
It's approximately impossible to tune a box correctly for both your
application and your database, in my experience.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Tue, Jun 17, 2008 at 8:25 AM, Andrew Sullivan <ajs@commandprompt.com> wrote:
> On Tue, Jun 17, 2008 at 03:38:59PM +0200, Lionel wrote:

>> Which OS would you use ? (knowing that there will be a JDK 1.6 installed
>> too)
>
> . . .I think this is the real mistake.  Get a separate database box.
> It's approximately impossible to tune a box correctly for both your
> application and your database, in my experience.

Having had to install jvm 1.6 for a few weird admin bits we were using
in the past, I didn't even think the same thing as you on this.  Good
catch.

On Tue, 17 Jun 2008, Lionel wrote:
> I need to install a 8.3 database and was wondering which hardware would be
> sufficient to have good performances (less than 30s for² slowest select).

> It's almost impossible to predict what users will do via the webapplication
> that queries this database: almost all select, join, group by, where...
> possibilities are available.

Well, Scott has given you some good pointers on how to make a fast system.
However, your original question ("is this fast enough") is impossible to
answer, especially if the users are allowed to run absolutely anything
they want. I bet you I could craft a query that takes more than 30 seconds
regardless of how fast you make your system.

Having said that, I'll add the suggestion that you should put as much RAM
in the box as possible. It can only help. As others have said, if you only
have four users, then CPU power isn't going to be such a problem, and
given that, I'd disagree with Andrew and say as long as you have plenty of
RAM, Java can play well with a database on the same box. Depends what it
is doing, of course.

Matthew

--
To be or not to be           -- Shakespeare
To do is to be               -- Nietzsche
To be is to do               -- Sartre
Do be do be do               -- Sinatra

Andrew Sullivan wrote:
> You won't need lots of processer, then.

can't find less than quad core for this price range...

> How big's the database?

with 20 millions of rows, the main table is 3.5 Go on win XP.
With 8 Go of indexes.

I estimate the whole database around 30 Go / year

>  If you can have enough memory to hold the
> whole thing, including all indexes, in memory, that's what you want.
> Apart from that, "dual SATA2" is probably underpowered.  But. . .

RAID is twice more expansive.
(600euros/month for a 5x750Go SATA2 with 12Gb of ram and unnecessary 2x quad
core)

didn't find any RAID 10 "not too expansive" dedicated server.

If this setup is twice as fast, I can afford it. But if it a 30sec VS
40sec...I'm not sure my customer will pay.

>> Which OS would you use ? (knowing that there will be a JDK 1.6
>> installed too)
>
> . . .I think this is the real mistake.  Get a separate database box.
> It's approximately impossible to tune a box correctly for both your
> application and your database, in my experience.

My tomcat webapp is well coded  and consumes nearly nothing.
On such powerful hardware, I prefer to run both on the same server.
I could eventually run it on a different server, much less powerfull, but
it's not on the same network, I guess this would be an issue.

On Tue, 17 Jun 2008, Andrew Sullivan wrote:

>> Which OS would you use ? (knowing that there will be a JDK 1.6 installed
>> too)
>
> . . .I think this is the real mistake.  Get a separate database box.
> It's approximately impossible to tune a box correctly for both your
> application and your database, in my experience.

I can't remember the last time I deployed a PG box that didn't have a Java
app or three on it, too.  You've got even odds that putting it a separate
system will even be a improvement.  Yes, if the Java app is a pig and the
machine doesn't have enough resources, separating it out to another system
will help.  But there are plenty of these buggers that will end up so much
slower from the additional network latency that it's a net loss (depends
on how the app groups its requests for rows).

If you know enough about Java to watch things like how much memory the
JVMs are taking up, I wouldn't hesitate to put them all on the same
machine.  Considering that Lionel's system seems pretty overpowered for
what he's doing--runs plenty fast on a much slower system, enough RAM to
hold a large portion of the primary tables and database, all batch updates
that don't really need a good RAID setup--I'd say "looks good" here and
recommend he just follow the plan he outlined.  Just watch the system with
top for a bit under load to make sure the Java processes are staying under
control.

As for OS, a RHEL5 or clone like CentOS should work fine here, which is
more appropriate depends on your support requirements.  I would recommend
against using FreeBSD as it's not the friendliest Java platform, and the
additional complexity of Solaris seems like overkill for your app.
Basically, evem though it's available for more of them, I only consider
deploying a Java app on one of the mainstream platforms listed at
http://www.java.com/en/download/manual.jsp right now because those are the
mature releases.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Tue, Jun 17, 2008 at 9:32 AM, Greg Smith <gsmith@gregsmith.com> wrote:

>  Considering that Lionel's system seems pretty overpowered for what he's
> doing--runs plenty fast on a much slower system, enough RAM to hold a large
> portion of the primary tables and database, all batch updates that don't
> really need a good RAID setup--I'd say "looks good" here and recommend he
> just follow the plan he outlined.  Just watch the system with top for a bit
> under load to make sure the Java processes are staying under control.

In the original post he mentioned that he had 5 years of data at about
25G / year.

With 125G of data, it's likely that if most queries are on recent data
it'll be in RAM, but anything that hits older data will NOT have that
luxury.  Which is why I recommended RAID-10.  It doesn't have to be on
a $1200 card with 44 disks or something, but even 4 disks in a sw
RAID-10 will be noticeably faster (about 2x) than a simple RAID-1 at
hitting that old data.

We had a reporting server with about 80G of data on a machine with 4G
ram last place I worked, and it could take it a few extra seconds to
hit the old data, but the SW RAID-10 on it made it much faster at
reporting than it would have been with a single disk.

On Tue, Jun 17, 2008 at 04:49:17PM +0200, Lionel wrote:
> My tomcat webapp is well coded  and consumes nearly nothing.

If I were ever inclined to say, "Nonsense," about code I've never
seen, this is probably the occasion on which I'd do it.  A running JVM
is necessarily going to use some memory, and that is memory use that
you won't be able to factor out properly when developing models of
your database system performance.

> I could eventually run it on a different server, much less powerfull, but
> it's not on the same network, I guess this would be an issue.

The power of the system is hard to know about in the context (with
only 8Go of memory, I don't consider this a powerful box at all,
note).  But why wouldn't it be on the same network?  You're using the
network stack anyway, note: JVMs can't go over domain sockets.

A

--
Andrew Sullivan
ajs@commandprompt.com
+1 503 667 4564 x104
http://www.commandprompt.com/

On Tue, Jun 17, 2008 at 9:42 AM, Andrew Sullivan <ajs@commandprompt.com> wrote:
> On Tue, Jun 17, 2008 at 04:49:17PM +0200, Lionel wrote:
>> My tomcat webapp is well coded  and consumes nearly nothing.
>
> If I were ever inclined to say, "Nonsense," about code I've never
> seen, this is probably the occasion on which I'd do it.  A running JVM
> is necessarily going to use some memory, and that is memory use that
> you won't be able to factor out properly when developing models of
> your database system performance.

But if that amount of memory is 256 Megs and it only ever acts as a
control panel or data access point, it's probably not a huge issue.
If it's 2 Gig it's another issue.  It's all about scale.  The real
performance hog for me on all in one boxes has been perl / fastcgi
setups.

> The power of the system is hard to know about in the context (with
> only 8Go of memory, I don't consider this a powerful box at all,
> note).

I always think of main memory in terms of how high a cache hit rate it
can get me.  If 8G gets you a 50% hit rate, and 16G gets you a 95% hit
rate, then 16G is the way to go.  But if 8G gets you to 75% and 32G
gets you to 79% because of your usage patterns (the world isn't always
bell curve shaped) then 8G is plenty and it's time to work on faster
disk subsystems if you need more performance.

On Tue, 17 Jun 2008, Andrew Sullivan wrote:

> A running JVM is necessarily going to use some memory, and that is
> memory use that you won't be able to factor out properly when developing
> models of your database system performance.

Now you've wandered into pure FUD.  Tuning maximum memory usage on a Java
app so you can model it is straightforward (albeit a little confusing at
first), and in most cases you can just sample it periodically to get a
good enough estimate for database tuning purposes.  JVMs let you adjust
maximum memory use with -Xmx , and if anything the bigger problem I run
into is that using too much memory hits that limit and crashes Java long
before it becomes a hazard to the database.

This is a system with 8GB of RAM here; having some Tomcat instances
co-existing with the database when there's that much room to work is not
that hard.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Tue, 17 Jun 2008, Scott Marlowe wrote:

> We had a reporting server with about 80G of data on a machine with 4G
> ram last place I worked, and it could take it a few extra seconds to
> hit the old data, but the SW RAID-10 on it made it much faster at
> reporting than it would have been with a single disk.

I agree with your statement above, that query time could likely be dropped
a few seconds with a better disk setup.  I just question whether that's
necessary given the performance target here.

Right now the app is running on an underpowered Windows box and is
returning results in around 10s, on a sample data set that sounds like 1/8
of a year worth of data (1/40 of the total).  It is seemingly CPU bound
with not enough processor to handle concurrent queries being the source of
the worst-case behavior.  The target is keeping that <30s on more powerful
hardware, with at least 6X as much processor power and a more efficient
OS, while using yearly partitions to keep the amount of data to juggle at
once under control.  That seems reasonable to me, and while better disks
would be nice I don't see any evidence they're really needed here.  This
application sounds a batch processing/reporting one where plus or minus a
few seconds doesn't have a lot of business value.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

On Tue, Jun 17, 2008 at 10:56 AM, Greg Smith <gsmith@gregsmith.com> wrote:
> On Tue, 17 Jun 2008, Scott Marlowe wrote:
>
>> We had a reporting server with about 80G of data on a machine with 4G
>> ram last place I worked, and it could take it a few extra seconds to
>> hit the old data, but the SW RAID-10 on it made it much faster at
>> reporting than it would have been with a single disk.
>
> I agree with your statement above, that query time could likely be dropped a
> few seconds with a better disk setup.  I just question whether that's
> necessary given the performance target here.
>
> Right now the app is running on an underpowered Windows box and is returning
> results in around 10s, on a sample data set that sounds like 1/8 of a year
> worth of data (1/40 of the total).  It is seemingly CPU bound with not
> enough processor to handle concurrent queries being the source of the
> worst-case behavior.  The target is keeping that <30s on more powerful
> hardware, with at least 6X as much processor power and a more efficient OS,
> while using yearly partitions to keep the amount of data to juggle at once
> under control.  That seems reasonable to me, and while better disks would be
> nice I don't see any evidence they're really needed here.  This application
> sounds a batch processing/reporting one where plus or minus a few seconds
> doesn't have a lot of business value.

I think you're making a big assumption that this is CPU bound.  And it
may be that when all the queries are operating on current data that it
is.  But as soon as a few ugly queries fire that need to read tens of
gigs of data off the drives, then you'll start to switch to I/O bound
and the system will slow a lot.

We had a single drive box doing work on an 80G set that was just fine
with the most recent bits.  Until I ran a report that ran across the
last year instead of the last two days, and took 2 hours to run.

All the queries that had run really quickly on all the recent data
suddenly were going from 1 or 2 seconds to 2 or 3 minutes.  And I'd
have to kill my reporting query.

Moved it to the same exact hardware but with a 4 disc RAID-10 and the
little queries stayed 1-2 seconds while th reporting queries were cut
down by factors of about 4 to 10.  RAID-1 will be somewhere between
them I'd imagine.  RAID-10 has an amazing ability to handle parallel
accesses without falling over performance-wise.

You're absolutely right though, we really need to know the value of
fast performance here.

If you're monitoring industrial systems you need fast enough response
to spot problems before they escalate to disasters.

If you're running aggregations of numbers used for filling out
quarterly reports, not so much.

"Scott Marlowe" wrote:
> You're absolutely right though, we really need to know the value of
> fast performance here.

the main problem is that my customers are used to have their reporting after
few seconds.
They want do have 10 times more data but still have the same speed, which
is, I think, quite impossible.

> If you're running aggregations of numbers used for filling out
> quarterly reports, not so much.

The application is used to analyse products sales behaviour, display charts,
perform comparisons, study progression...
10-40 seconds seems to be a quite good performance.
More than 1 minute will be too slow (meaning they won't pay for that).

I did some test with a 20 millions lines database on a single disk dual core
2GB win XP system (default postgresql config), most of the time is spent in
I/O: 50-100 secs for statements that scan 6 millions of lines, which will
happen. Almost no CPU activity.

So here is the next question: 4 disks RAID10 (did not find a french web host
yet) or 5 disk RAID5 (found at 600euros/month) ?
I don't want to have any RAID issue...
I did not have any problem with my basic RAID1 since many years, and don't
want that to change.

On Tue, Jun 17, 2008 at 11:59 AM, Lionel <lionel@art-informatique.com> wrote:
> "Scott Marlowe" wrote:
>> You're absolutely right though, we really need to know the value of
>> fast performance here.
>
> the main problem is that my customers are used to have their reporting after
> few seconds.
> They want do have 10 times more data but still have the same speed, which
> is, I think, quite impossible.
>
>> If you're running aggregations of numbers used for filling out
>> quarterly reports, not so much.
>
> The application is used to analyse products sales behaviour, display charts,
> perform comparisons, study progression...
> 10-40 seconds seems to be a quite good performance.
> More than 1 minute will be too slow (meaning they won't pay for that).
>
> I did some test with a 20 millions lines database on a single disk dual core
> 2GB win XP system (default postgresql config), most of the time is spent in
> I/O: 50-100 secs for statements that scan 6 millions of lines, which will
> happen. Almost no CPU activity.
>
> So here is the next question: 4 disks RAID10 (did not find a french web host
> yet) or 5 disk RAID5 (found at 600euros/month) ?
> I don't want to have any RAID issue...
> I did not have any problem with my basic RAID1 since many years, and don't
> want that to change.

Do you have root access on your servers?  then just ask for 5 disks
with one holding the OS / Apps and you'll do the rest.  Software RAID
is probably a good fit for cheap right now.

If you can set it up yourself, you might be best off with >2 disk
RAID-1.  5 750G disks in a RAID-1 yields 750G of storage (duh) but
allows for five different readers to operate without the heads having
to seek.  large amounts of data can be read at a medium speed from a
RAID-1 like this.  But most RAID implementations don't aggregate
bandwidth for RAID-1.

They do for RAID-0.  So, having a huge RAID-0 zero array allows for
reading a large chunk of data really fast from all disks at once.

RAID1+0 gives you the ability to tune this in either direction.  But
the standard config of a 4 disk setup (striping two mirrors, each made
from two disks, is a good compromise to start with.  Average read
speed of array is doubled, and the ability to have two reads not
conflict helps too.

RAID5 is a comproise to provide the most storage while having mediocre
performance or, when degraded, horrifficaly poor performance.

Hard drives are cheap, hosting not as much.

Also, always look at optimizing their queries.  A lot of analysis is
done by brute force queries that rewritten intelligently suddenly run
in minutes not hours.  or seconds not minutes.

On Tue, 17 Jun 2008, Lionel wrote:

> I did some test with a 20 millions lines database on a single disk dual core
> 2GB win XP system (default postgresql config), most of the time is spent in
> I/O: 50-100 secs for statements that scan 6 millions of lines, which will
> happen. Almost no CPU activity.

I hope you're aware that the default config is awful, and there are all
sorts of possible causes for heavy I/O churn that might improve if you
setup the postgresql.conf file to use the server's resources more
aggressively (the default is setup for machines with a very small amount
of RAM).  There are lots of links to articles that cover the various areas
you might improve at
http://wiki.postgresql.org/wiki/Performance_Optimization

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD

----------------------------------------
> From: lionel@art-informatique.com
> Subject: [PERFORM] Which hardware ?
> Date: Tue, 17 Jun 2008 15:38:59 +0200
> To: pgsql-performance@postgresql.org
>
> Hi,
>
> I need to install a 8.3 database and was wondering which hardware would be
> sufficient to have good performances (less than 30s for² slowest select).
>
> Database size: 25 Go /year, 5 years of history
> One main table containing 40 million lines per year.
> Batch inserts of 100000 lines. Very very few deletes, few updates.
>
> 30 other tables, 4 levels of hierarchy, containing from 10 lines up to 20000
> lines.
> 5 of them have forein keys on the main table.
>
> I will use table partitionning on the year column.
>
> Statements will mainly do sums on the main table, grouped by whatever column
> of the database (3-5 joined tables, or join on join), with some criterions
> that may vary, lots of "joined varchar in ('a','b',...,'z')".
> It's almost impossible to predict what users will do via the webapplication
> that queries this database: almost all select, join, group by, where...
> possibilities are available.
>
> Up to 4 simultaneous users.
>
> I'm planning to host it on a quad xeon 2.66Ghz with 8Go of DDR2, and a dual
> (RAID1) SATA2 750Go HD.
> Perharps with another HD for indexes.
>
> Do you think it will be enough ?
> Is another RAID for better performances a minimum requirement ?
> Will a secondary HD for indexes help ?
>
> Which OS would you use ? (knowing that there will be a JDK 1.6 installed
> too)
>
> With 5 millions of lines, the same application runs quite fast on windows
> 2000 on a single P4 2.8 GHz (very few statements last more than 10s, mostly
> when concurrent statements are made). Each statement consumes 100% of the
> CPU.
>
>
> thanks for advices.
>
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance


I think hardware isnt going to solve your problem,  especially the cpu.  You only have four users..  and postgres can
onlyuse 1 core per query.   If you have sequential scans that span this table and say it has 60-80 million rows,  It
cancould take longer then 30 seconds.  Even if you have alot of ram.  Just imagine what postgres is doing... if its
targetsearch is going to end in searching 40 million rows and it has to aggregate on two,  or three columns its going
tobe slow.  No amount of hardware is going to fix this.   Sure you can gain some speed by having entire tables in ram.
No magic bullet here.   Disk is definitely not a magic bullet.    Even if you have a bunch of fast disks its still much
slowerthen RAM in performing reads.    So if you read heavy then adding more disk isnt going to just solve all your
problems.  RAM is nice.   The more pages you can keep in ram the less reading from the disk.   

Even with that all said and done...   aggregating lots of rows takes time.    I suggest you come up with a system from
preaggregatingyour data if possible.  Identify all of your target dimensions.   If your lucky,  you only have a few key
dimensionswhich can reduce size of table by lots and reduce queries to 1-2 seconds.   There are a number of ways to
tacklethis,  but postgres is a nice db to do this with, since writers do not block readers.    

I think you should focus on getting this system to work well with minimal hardware first.  Then you can upgrade.   Over
thenext few years the db is only going to get larger.   You have 4 users now.. but who's to say what it will evolve
into.    
_________________________________________________________________
Earn cashback on your purchases with Live Search - the search that pays you back!
http://search.live.com/cashback/?&pkw=form=MIJAAF/publ=HMTGL/crea=earncashback

"Scott Marlowe" wrote:
> We had a reporting server with about 80G of data on a machine with 4G
> ram last place I worked, and it could take it a few extra seconds to
> hit the old data, but the SW RAID-10 on it made it much faster at
> reporting than it would have been with a single disk.

Would this be a nice choice ?

HP Proliant DL320 G5p  Xeon DC 3 GHz - 8 Go RAM DDR2 ECC
- 4 x 146 Go SAS 15k rpm   - RAID-10 HP Smart Array (128 Mo cache)

I finally choose to have 2 data tables:
- one with pre aggregated (dividing size by 10), unpartitionned (=the
database they currently use)
- one with original data, yearly partitionned

I will choose before each statement which table will be used depending on
which select/joins/where/groupby the user choosed.
The aggregated datas will allow me to maintain actual performances (and even
improve it using the new hardware twice more powerfull).

I think lines aggregation will be handled by the java application (excel/csv
file loaded in memory),
which will be much faster than using a trigger on insertion in the full
table.

Thanks.