Tx to all.
I reach the same reflection on partitionning the data to those tables.
And postgresql is giving very good tools for that with the rules features.
I got the SAS server for immediate fix.
But I'm looking for buying a machine that will handle my needs for more
long time.
Regarding partitionning it seems that I could just use a daily tables
for daily treatment and keeping a another one for mass reading. I even
things to partition per years or half years.
One question is on table that have FK constraint, I don't know how to
maintain it ? Could I use rules for it too ? Tx for helps
Regards
David
Scott Marlowe a écrit :
On Mon, Aug 11, 2008 at 8:26 AM, dforums <dforums@xxxxxxxxxxx> wrote:
Houlala
I got headache !!!
So please help...........;;
"Assuming they all happen from 9 to 5 and during business days only,
that's about 86 transactions per second. Well within the realm of a
single mirror set to keep up if you don't make your db work real fat."
OK i like, But my reality is that to make an insert of a table that have 27
millions of entrance it took 200 ms.
so it took between 2 minutes and 10 minutes to treat 3000 records and
dispatch/agregate in other tables. And I have for now 20000 records every 3
minutes.
Can you partition your data on some logical barrier like a timestamp
or something? that would probably help a lot. also, are you doing
all 3000 records in one transaction or individual transactions? If
one at a time, can you batch them together for better performance or
are you stuck doing them one at a time?
At the moment I have a
I have a Linux 2.6.24.2-xxxx-std-ipv4-64 #3 SMP Tue Feb 12 12:27:47 CET 2008
x86_64 Intel(R) Xeon(R) CPU X5355 @ 2.66GHz GenuineIntel GNU/Linux
with 8Gb of memory. Using sata II disk in RAID 1 (I known that is bad, but
it would change has quickly I can).
Yeah, you're gonna be I/O bound as long as you've only got a single
mirror set. A machine with 8 or 12 SAS 15K drives should make it much
more likely you can handle the load.
I got 1-2 GO per week
Definitely let's look at partitioning then if we can do it.
I can change to 2 kinds of server, using 8.3.3 postgresql server, and even
taking more sever if need. But it is the biggest computer that I can rent
for now.
Intel 2x Xeon X5355
2x 4x 2.66 GHz
L2: 8Mo, FSB: 1333MHz
Double Quadruple Coeur
64 bits
12 Go FBDIMM DDR2
2x 147 Go
SAS 15 000 tr/min
RAID 1 HARD
All that memory and CPU power will be wasted on a db with just two
drives. Do you at least have a decent RAID controller in that setup?
I can add 500 Go under sataII
OR
Intel 2x Xeon X5355
2x 4x 2.66 GHz
L2: 8Mo, FSB: 1333MHz
Double Quadruple Coeur
64 bits
12 Go FBDIMM DDR2
5x 750 Go (2.8 To **)
SATA2 RAID HARD 5
I can add 500 Go under sataII
RAID5 is generally a poor choice for a write limited DB. I'd guess
that the dual SAS drives above would work better than the 5 SATA
drives in RAID 5 here.
After several tunings, reading, ect...
The low speed seems to be definetly linked to the SATA II in RAID 1.
Going to 15k SAS RAID 1 will just about double your write rate
(assuming it's a commits/second issue and it likely is). going to a 4
disk SAS RAID10 will double that, and so on.
So I need a solution to be able to 1st supporting more transaction, secondly
I need to secure the data, and being able to load balancing the charge.
Look at slony for read only slaves and the master db as write only.
If you can handle the slight delay in updates from master to slave.
Otherwise you'll need sync replication, and that is generally not as
fast.
Take a look at something like this server:
http://www.aberdeeninc.com/abcatg/Stirling-229.htm
With 8 15k SAS 146G drives it runs around $5k or so. Right now all
the servers your hosting provider is likely to provide you with are
gonna be big on CPU and memory and light on I/O, and that's the
opposite of what you need for databases.
--
<http://www.1st-affiliation.fr>
*David Bigand
*Président Directeur Générale*
*51 chemin des moulins
73000 CHAMBERY - FRANCE
Web : htttp://www.1st-affiliation.fr
Email : david@xxxxxxxxxxxxxxxxxxx
Tel. : +33 479 696 685
Mob. : +33 666 583 836
Skype : firstaffiliation_support
