Обсуждение: Handling large volumes of data
I want to set up a system where logs of all kinds can be put in tables so that queries and reports can be generated from there. Our Firewall logs alone generate about 600,000,000 lines per month and that will increase as we get more bandwidth. I am testing postgresql's ability to handle large datasets. I have loaded about 4,900,000,000 in one of two tables with 7200684 in the second table in database 'firewall', built one index using one date-field (which took a few days) and used that index to copy about 3,800,000,000 of those records from the first to a third table, deleted those copied record from the first table and dropped the third table. This took about a week on a 2xCPU quadcore server with 8Gb RAM. During and after that exercise the server was lethargic and constantly ran at a load average of at least 5. So I decided maybe it is a good thing to run 'autovacuum analyse' to clean up things. After a few days the process is still running and the load average still constantly about 5. I then decided to just drop the tables. I did that about 18 hours ago and still there is no sign of Postgresql finishing that (pid 18614 below). The machine is sluggish. A 'ps fanx' shows: 12501 ? S 0:00 /usr/lib/postgresql/8.1/bin/postmaster -D /var/lib/postgresql/8.1/main -c config_file=/etc/postgresql/8.1/main/postgresql.conf 12504 ? D 0:54 \_ postgres: writer process 12505 ? S 1:42 \_ postgres: stats buffer process 12506 ? S 1:03 | \_ postgres: stats collector process 15918 ? D 1214:28 \_ postgres: autovacuum process firewall 18613 ? D 130:08 \_ postgres: exilog exilog 146.232.128.197(53907) SELECT 18614 ? S 12:42 \_ postgres: exilog exilog 146.232.128.197(53908) idle 31932 ? D 16:11 \_ postgres: exilog exilog 146.232.128.106(36547) INSERT 10380 ? S 0:00 \_ postgres: log firewall [local] DROP TABLE waiting 20753 ? D 5:11 \_ postgres: exilog exilog 146.232.128.197(43581) INSERT 16370 ? S 5:04 \_ postgres: exilog exilog 146.232.128.59(40620) idle 3483 ? S 0:50 \_ postgres: exilog exilog 146.232.128.49(33803) INSERT 3484 ? S 0:04 \_ postgres: exilog exilog 146.232.128.49(33804) idle 3485 ? S 0:02 \_ postgres: exilog exilog 146.232.128.49(33805) idle dstat-output shows a lot of IO. It is actually better now. Earlier today it was constantly about 50M per line: ----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system-- usr sys idl wai hiq siq|_read _writ|_recv _send|__in_ _out_|_int_ _csw_ 4 1 87 9 0 0| 24M 15M| 0 0 | 96B 10k|1108 1902 3 1 85 11 0 0| 38M 32M|7242B 8796B| 0 0 |1175 1501 2 0 88 10 0 0| 27M 33M|9949B 10k| 0 0 | 737 714 1 1 87 10 0 0| 24M 28M|2412B 2948B| 0 0 | 637 533 2 0 87 11 0 0| 28M 38M|2507B 2306B| 0 0 | 789 765 2 1 87 9 0 0| 40M 38M|2268B 2450B| 0 0 | 900 795 2 1 85 12 0 0| 33M 25M|4753B 3376B| 36k 0 | 950 1217 2 2 80 16 0 0| 24M 28M|2590B 2738B| 0 0 | 899 1487 2 1 84 12 0 0| 32M 40M|2603B 3025B| 0 0 |1042 1377 2 0 86 11 0 0| 28M 30M|8530B 9116B| 0 0 |1054 1302 1 0 77 22 0 0|8412k 12M| 12k 12k| 0 0 | 854 1286 Apparently the best approach is not to have very large tables. I am thinking of making (as far as the firewall is concerned) a different table for each day and then drop the older tables as necessary. Any advice on how to best handle this kind of setup will be appreciated. Regards Johann -- Johann Spies Telefoon: 021-808 4036 Informasietegnologie, Universiteit van Stellenbosch "Be careful for nothing; but in every thing by prayer and supplication with thanksgiving let your requests be made known unto God. And the peace of God, which passeth all understanding, shall keep your hearts hearts and minds through Christ Jesus." Philippians 4:6,7
Apparently the best approach is not to have very large tables. I am
thinking of making (as far as the firewall is concerned) a different
table for each day and then drop the older tables as necessary.
Any advice on how to best handle this kind of setup will be
appreciated.
On Tue, Apr 08, 2008 at 11:42:34AM +0200, Johann Spies wrote: > 12501 ? S 0:00 /usr/lib/postgresql/8.1/bin/postmaster -D /var/lib/postgresql/8.1/main -c config_file=/etc/postgresql/8.1/main/postgresql.conf > 12504 ? D 0:54 \_ postgres: writer process > 12505 ? S 1:42 \_ postgres: stats buffer process > 12506 ? S 1:03 | \_ postgres: stats collector process > 15918 ? D 1214:28 \_ postgres: autovacuum process firewall > 18613 ? D 130:08 \_ postgres: exilog exilog 146.232.128.197(53907) SELECT > 18614 ? S 12:42 \_ postgres: exilog exilog 146.232.128.197(53908) idle > 31932 ? D 16:11 \_ postgres: exilog exilog 146.232.128.106(36547) INSERT > 10380 ? S 0:00 \_ postgres: log firewall [local] DROP TABLE waiting The drop table will wait for the autovacuum to finish. You might want to kill the autovacuum process (this doesn't do any harm, just aborts the operation so the drop table may proceed). Bye, Tino. -- „Es gibt keinen Weg zum Frieden. Der Frieden ist der Weg.” (Mahatma Gandhi) www.craniosacralzentrum.de www.forteego.de
On Dienstag, 8. April 2008 Johann Spies wrote: > This took about a week on a 2xCPU quadcore server with 8Gb RAM. This is not the most interesting thing here. What disk I/O subsystem do you use? At least a hardware RAID controller with RAID 0 or 10 should be used, with 10krpm or 15krpm drives. SAS preferred, as on SATA the only quick disks are Western Digital Raptor (which aren't too bad, btw). mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0676/846 914 666 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: www.keyserver.net Key-ID: 1C1209B4
Вложения
On Tue, Apr 08, 2008 at 12:01:14PM +0200, Michael Monnerie wrote: > On Dienstag, 8. April 2008 Johann Spies wrote: > > This took about a week on a 2xCPU quadcore server with 8Gb RAM. > > This is not the most interesting thing here. What disk I/O subsystem do > you use? At least a hardware RAID controller with RAID 0 or 10 should > be used, with 10krpm or 15krpm drives. SAS preferred, as on SATA the > only quick disks are Western Digital Raptor (which aren't too bad, > btw). I have got 8x720G disks in a hardware raid 5 setup. It is a Dell 2950 server. I am using an XFS-filesystem. I am not certain about the speed of the hard disk, but we bought the fastest we could get. Regards Johann -- Johann Spies Telefoon: 021-808 4036 Informasietegnologie, Universiteit van Stellenbosch "Be careful for nothing; but in every thing by prayer and supplication with thanksgiving let your requests be made known unto God. And the peace of God, which passeth all understanding, shall keep your hearts hearts and minds through Christ Jesus." Philippians 4:6,7
On Tue, Apr 08, 2008 at 12:13:36PM +0200, Johann Spies wrote: > > > This took about a week on a 2xCPU quadcore server with 8Gb RAM. > > > > This is not the most interesting thing here. What disk I/O subsystem do > > you use? At least a hardware RAID controller with RAID 0 or 10 should > > be used, with 10krpm or 15krpm drives. SAS preferred, as on SATA the > > only quick disks are Western Digital Raptor (which aren't too bad, > > btw). > > I have got 8x720G disks in a hardware raid 5 setup. It is a Dell 2950 > server. I thought, the 2950 can only take 6 3.5" disks? Or are these 72GB 2.5" ones? Then there are 15k SAS drives available. > I am using an XFS-filesystem. I am not certain about the > speed of the hard disk, but we bought the fastest we could get. Bye, Tino. -- „Es gibt keinen Weg zum Frieden. Der Frieden ist der Weg.” (Mahatma Gandhi) www.craniosacralzentrum.de www.forteego.de
On Tue, Apr 08, 2008 at 11:55:00AM +0200, Tino Schwarze wrote: > The drop table will wait for the autovacuum to finish. You might want to > kill the autovacuum process (this doesn't do any harm, just aborts the > operation so the drop table may proceed). Thanks. That is good to know. Regards Johann -- Johann Spies Telefoon: 021-808 4036 Informasietegnologie, Universiteit van Stellenbosch "Be careful for nothing; but in every thing by prayer and supplication with thanksgiving let your requests be made known unto God. And the peace of God, which passeth all understanding, shall keep your hearts hearts and minds through Christ Jesus." Philippians 4:6,7
Hi,
> So I decided maybe it is a good thing to run 'autovacuum analyse' to
> clean up things. After a few days the process is still running and
> the load average still constantly about 5.
> I then decided to just drop the tables. I did that about 18 hours ago
> and still there is no sign of Postgresql finishing that (pid 18614 below).
A few months ago I had something kinda similar and asked advice on this
very list. Someone told me to look at a view called pg_stat_activity.
Like this: select * from pg_stat_activity;
It gave me insightful tips. One of the columns it reports is called
"waiting" and it shows which process has exclusive write on a table
and which process is being blocked from writing to the table. (I may
not be saying things perfectly correctly, but you get the gist of it.)
You might want to take a look at this view (there is nothing complicated
about running it) and you might find out a thing or two that is insightful.
Regards,
Tena Sakai
tsakai@gallo.ucsf.edu
-----Original Message-----
From: pgsql-admin-owner@postgresql.org on behalf of Johann Spies
Sent: Tue 4/8/2008 2:42 AM
To: pgsql-admin@postgresql.org
Subject: [ADMIN] Handling large volumes of data
I want to set up a system where logs of all kinds can be put in tables
so that queries and reports can be generated from there. Our Firewall
logs alone generate about 600,000,000 lines per month and that will
increase as we get more bandwidth.
I am testing postgresql's ability to handle large datasets. I have
loaded about 4,900,000,000 in one of two tables with 7200684 in the
second table in database 'firewall', built one index using one
date-field (which took a few days) and used that index to copy about
3,800,000,000 of those records from the first to a third table,
deleted those copied record from the first table and dropped the third
table.
This took about a week on a 2xCPU quadcore server with 8Gb RAM. During and
after that exercise the server was lethargic and constantly ran at a
load average of at least 5.
So I decided maybe it is a good thing to run 'autovacuum analyse' to
clean up things. After a few days the process is still running and
the load average still constantly about 5.
I then decided to just drop the tables. I did that about 18 hours ago
and still there is no sign of Postgresql finishing that (pid 18614 below).
The machine is sluggish. A 'ps fanx' shows:
12501 ? S 0:00 /usr/lib/postgresql/8.1/bin/postmaster -D /var/lib/postgresql/8.1/main -c config_file=/etc/postgresql/8.1/main/postgresql.conf
12504 ? D 0:54 \_ postgres: writer process
12505 ? S 1:42 \_ postgres: stats buffer process
12506 ? S 1:03 | \_ postgres: stats collector process
15918 ? D 1214:28 \_ postgres: autovacuum process firewall
18613 ? D 130:08 \_ postgres: exilog exilog 146.232.128.197(53907) SELECT
18614 ? S 12:42 \_ postgres: exilog exilog 146.232.128.197(53908) idle
31932 ? D 16:11 \_ postgres: exilog exilog 146.232.128.106(36547) INSERT
10380 ? S 0:00 \_ postgres: log firewall [local] DROP TABLE waiting
20753 ? D 5:11 \_ postgres: exilog exilog 146.232.128.197(43581) INSERT
16370 ? S 5:04 \_ postgres: exilog exilog 146.232.128.59(40620) idle
3483 ? S 0:50 \_ postgres: exilog exilog 146.232.128.49(33803) INSERT
3484 ? S 0:04 \_ postgres: exilog exilog 146.232.128.49(33804) idle
3485 ? S 0:02 \_ postgres: exilog exilog 146.232.128.49(33805) idle
dstat-output shows a lot of IO. It is actually better now. Earlier today
it was constantly about 50M per line:
----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--
usr sys idl wai hiq siq|_read _writ|_recv _send|__in_ _out_|_int_ _csw_
4 1 87 9 0 0| 24M 15M| 0 0 | 96B 10k|1108 1902
3 1 85 11 0 0| 38M 32M|7242B 8796B| 0 0 |1175 1501
2 0 88 10 0 0| 27M 33M|9949B 10k| 0 0 | 737 714
1 1 87 10 0 0| 24M 28M|2412B 2948B| 0 0 | 637 533
2 0 87 11 0 0| 28M 38M|2507B 2306B| 0 0 | 789 765
2 1 87 9 0 0| 40M 38M|2268B 2450B| 0 0 | 900 795
2 1 85 12 0 0| 33M 25M|4753B 3376B| 36k 0 | 950 1217
2 2 80 16 0 0| 24M 28M|2590B 2738B| 0 0 | 899 1487
2 1 84 12 0 0| 32M 40M|2603B 3025B| 0 0 |1042 1377
2 0 86 11 0 0| 28M 30M|8530B 9116B| 0 0 |1054 1302
1 0 77 22 0 0|8412k 12M| 12k 12k| 0 0 | 854 1286
Apparently the best approach is not to have very large tables. I am
thinking of making (as far as the firewall is concerned) a different
table for each day and then drop the older tables as necessary.
Any advice on how to best handle this kind of setup will be
appreciated.
Regards
Johann
--
Johann Spies Telefoon: 021-808 4036
Informasietegnologie, Universiteit van Stellenbosch
"Be careful for nothing; but in every thing by prayer
and supplication with thanksgiving let your requests
be made known unto God. And the peace of God, which
passeth all understanding, shall keep your hearts
hearts and minds through Christ Jesus."
Philippians 4:6,7
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
On Dienstag, 8. April 2008 Johann Spies wrote: > I have got 8x720G disks in a hardware raid 5 setup. It is a Dell 2950 > server. I am using an XFS-filesystem. I am not certain about the > speed of the hard disk, but we bought the fastest we could get. 720G looks like SATA disks, probably with "only" 7.200rpm. The Western Digital Raptor with 10.000rpm are about twice as fast on random access. Also, RAID5/6 are very,very slow when it comes to small disk *writes*. Which is exactly what a db makes. Retry with RAID-10, and you'll see the difference. We've had 6 WD Raptors in RAID-6 on an Areca RAID, and changed it to 8x Raptor RAID-10, that improved things. mfg zmi -- // Michael Monnerie, Ing.BSc ----- http://it-management.at // Tel: 0676/846 914 666 .network.your.ideas. // PGP Key: "curl -s http://zmi.at/zmi.asc | gpg --import" // Fingerprint: AC19 F9D5 36ED CD8A EF38 500E CE14 91F7 1C12 09B4 // Keyserver: www.keyserver.net Key-ID: 1C1209B4
Вложения
On Tue, Apr 08, 2008 at 12:13:36PM +0200, Johann Spies wrote: > I have got 8x720G disks in a hardware raid 5 setup. It is a Dell 2950 Thow away your RAID 5. It's a loser for this. Raid 1+0 is what you need. > server. I am using an XFS-filesystem. I am not certain about the On another note, I've had abysmal experiences with xfs on linux. Like, "Oops, everything's in lost+found now!" abysmal. (Unfortunately, actually, in my experience Linux has only bad and worse choices for database filesystems. Maybe they'll get it right with ext4.) A