Linux ready for high-volume databases?

[Ron Johnson]

On Mon, 2003-08-25 at 16:28, Gregory S. Williamson wrote:

One of our sysads sent this link ... wondering if there is any comment on it from the world of actual users of linux and a database.

<http://story.news.yaho o.com/news?tmpl=story &cid=1738&ncid= 738&e=9&u=/zd/20030825/tc_zd/55311>

"Weak points include lack of available tools, ease of use and ease
of installation"

Sounds like he needs point-and-drool tools...

On the other hand, could even a beefy Linux 2.4 *today* system handle
a 24x7 500GB db that must process 6-8M OLTP-style transactions per
day, while also getting hit by report queries?

Don't think of this as a troll, because I really don't know, even
though I do know that MVS, OpenVMS & Solaris can. (I won't even
ask about toys like Windows and FreeBSD.)

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@c ox.net
Jefferson, LA USA

"Knowledge should be free for all."
Harcourt Fenton Mudd, Star Trek:TOS, "I, Mudd"
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Greg Stark]

Dennis Gearon <ge*****@firese rve.net> writes:

With the low cost of disks, it might be a good idea to just copy to disks, that
one can put back in.

Uh, sure, using hardware raid 1 and breaking one set of drives out of the
mirror to perform the backup is an old trick. And for small databases backups
are easy that way. Just store a few dozen copies of the pg_dump output on your
live disks for local backups and burn CD-Rs for offsite backups.

But when you have hundreds of gigabytes of data and you want to be able to
keep multiple snapshots of your database both on-site and off-site... No, you
can't just buy another hard drive and call it a business continuity plan.

As it turns out my current project will be quite small. I may well be adopting
the first approach. I'm thinking taking a pg_dump regularly (nightly if I can
get away with doing it that infrequently) keeping the past n dumps, and
burning a CD with those dumps.

This doesn't provide what online backups do, of recovery to the minute of the
crash. And I get nervous having only logical pg_dump output, no backups of the
actual blocks on disk. But is that what everybody does?

--
greg
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Shridhar Daithankar]

On 26 Aug 2003 at 9:15, Al Hulaton wrote:

After seeing this article yesterday, I did a bit of research. One _big_ reason
why Sourceforge/VA/OSDN is moving over to IBM/Webshere/DB2 from PostgreSQL is
the resulting product will be jointly marketed by Sourceforge and IBM's
zillions of sales people. So not only will they get a shiny, new db, but
backend revenue.

"The companies will jointly market and sell the software as part of the
commercial agreement. "-- 4th paragraph, last sentence.
http://www.eweek.com/print_article/0...a=30025,00.asp

<From vague memory somewhere from some article>

One of the technical reasons sourceforge went to DB2 was that DB2 had
clustering. Postgresql could not scale beyond single machine and on single
machine it had limitations on scaling as well.

Note that this was done quite a while back. Today postgresql might be as
scalable as required by sourceforge but they needed it then and had to move.

</From vague memory somewhere from some article>

<rant>
However if DB clustering was the problem, personally I would have split the
data on two machines on two different databases and had an app consolidate that
data. The efforts in rewriting app. could have been well compensated for
performance hit sourceforge took immediately after the move.

But that's me..
<rant>

Bye
Shridhar

--
There are always alternatives. -- Spock, "The Galileo Seven", stardate 2822.3
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Ron Johnson]

On Tue, 2003-08-26 at 23:35, Greg Stark wrote:

Dennis Gearon <ge*****@firese rve.net> writes: [snip] This doesn't provide what online backups do, of recovery to the minute of the
crash. And I get nervous having only logical pg_dump output, no backups of the
actual blocks on disk. But is that what everybody does?

Gak!! It can never be guaranteed that the "actual blocks on disk"
are transactionally consistent. Thus, the pg_dump output is suff-
icient.

However, there is still the large problem of PITR. Unless you
double your h/w and run Postgresql-R, you can not guarantee recov-
ery to an exact point in time if there is a hardware failure that
destroys the database.

Therefore, you can only restore databases to the time that the
last pg_dump was taken.

--
-----------------------------------------------------------------
Ron Johnson, Jr. ro***********@c ox.net
Jefferson, LA USA

The difference between Rock&Roll and Country Music?
Old Rockers still on tour are pathetic, but old Country singers
are still great.
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddres sHere" to ma*******@postg resql.org)

[Vivek Khera]

>>>>> "GS" == Greg Stark <gs*****@mit.ed u> writes:

GS> Vivek Khera <kh***@kcilink. com> writes:

I run a 24x7x365 db on FreeBSD which has *never* crashed in the 3
years it has been in production. Only downtime was the upgrade from
PG 7.1 to 7.2 and once for a switchover from RAID5 to RAID10.

GS> I would be interested to know what backup strategy you use for
GS> this. Without online backups this means that if you had crashed
GS> you would have lost data up to the last pg_dump you took? Had you
GS> done tests to see how long it would have taken to restore from the
GS> pg_dump?

Currently it is pg_dump. Once the new server is online this week,
we'll be using eRServer to keep a 'hot spare' slave ready for quick
switchover.

Both systems use RAID10 hardware arrays for the database.

Restore from dump takes about an hour for the data, and then the rest
of eternity (something like 18 hours last time I did it) for index
generation.

The pg_dump process takes about 52 minutes across the network.

GS> Oh, it's a really small database. That helps a lot with the backup
GS> problems of 24x7 operation. Still I would be interested.

Well, perhaps, but it is big enough and pounded on enough
(read/insert/update very often) that it saturates the disk. I have
memory to spare according to the system stats.

I personally *really* wonder how people run DBs that are much larger
and have high rate of read/insert/update across large tables with RI
checks and all that normal good stuff. The tuning recommendations I
have been through are insufficent to really help for my load. Perhaps
my current server hardware just isn't up to it.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.c om Rockville, MD [phone]
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Vivek Khera]

>>>>> "GS" == Greg Stark <gs*****@mit.ed u> writes:

GS> the first approach. I'm thinking taking a pg_dump regularly
GS> (nightly if I can get away with doing it that infrequently)
GS> keeping the past n dumps, and burning a CD with those dumps.

Basically what I do. I burn a set of CDs from one of my dumps once a
week, and keep the rest online for a few days. I'm really getting
close to splurging for a DVD writer since my dumps are way too big for
a single CD.

GS> This doesn't provide what online backups do, of recovery to the
GS> minute of the crash. And I get nervous having only logical pg_dump
GS> output, no backups of the actual blocks on disk. But is that what
GS> everybody does?

Well, if you want backups of the blocks on disk, then you need to shut
down the postmaster so that it is a consistent copy. You can't copy
the table files "live" this way.

So, yes, having the pg_dump is pretty much your safest bet to have a
consistent dump. And using a replicated slave with, eg, eRServer, is
also another way, but that requires more hardware.

--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.c om Rockville, MD [phone]
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Vivek Khera]

>>>>> "AH" == Alvaro Herrera <al******@dcc.u chile.cl> writes:

AH> On Wed, Aug 27, 2003 at 12:21:53PM -0400, Vivek Khera wrote:

>>>>> "GS" == Greg Stark <gs*****@mit.ed u> writes:

GS> Vivek Khera <kh***@kcilink. com> writes:

GS> Oh, it's a really small database. That helps a lot with the backup
GS> problems of 24x7 operation. Still I would be interested.
Well, perhaps, but it is big enough and pounded on enough
(read/insert/update very often) that it saturates the disk. I have
memory to spare according to the system stats.

AH> Well, was it really a 27 MB database, or it was a typo and you meant 27
AH> GB? The latter doesn't fit in my "really small database" category...

Yes, typo. It is 27GB *not* just 27MB. Heck, I could do 27MB on
my solid state drive at an incredible speed ;-)

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Vivek Khera]

>>>>> "GS" == Greg Stark <gs*****@mit.ed u> writes:

The DB is currently about 27Mb on disk (including indexes) and
processes several million inserts and updates daily, and a few million
deletes once every two weeks.

GS> Oh, it's a really small database. That helps a lot with the backup
GS> problems of 24x7 operation. Still I would be interested.

Ok... so I re-read my post. I mean 27Gb on disk. Duh. Sorry for the
confusion!
--
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
Vivek Khera, Ph.D. Khera Communications, Inc.
Internet: kh***@kciLink.c om Rockville, MD [phone]
AIM: vivekkhera Y!: vivek_khera http://www.khera.org/~vivek/

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Steve Lane]

Hello all:

I'm building a web-based app that is purely a query tool: no data can be
added or edited. Postgres is the back end.

What steps do I need to take to make the setup as fast as possible for
read-only access? Are there any default settings I can disable because I
don't need them, and gain some speed that way? AFAIK there's no way to turn
transactions off, but what about something like f_sync? Will I get a
performance boost by turning that off?

I'm aware of the "standard" pgsql optimizations and I'll do my best to put
those in place. I'm wondering whether there's anything extra I can do, that
might not normally be "safe", but might become so in a read-only
environment.

All the data will be scrubbed out every night and refreshed from the
original source. Should I be running a VACUUM ANALYZE after each refresh?
Any other optimizations or hints I can pass along to the query processor
that reflect the fact that the data will NEVER change between VACUUM passes?

Thanks for any thoughts or advice.

-- sgl
=============== =============== =============== ==========
Steve Lane

Vice President
The Moyer Group
14 North Peoria St Suite 2H
Chicago, IL 60607

Voice: [phone] Email: sl***@moyergrou p.com
Fax: [phone] Web: http://www.moyergroup.com
=============== =============== =============== ==========
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Greg Stark]

Steve Lane <sl***@moyergro up.com> writes:

Ron Johnson <ro***********@ cox.net> writes:

Dennis Gearon <ge*****@firese rve.net> writes:

This doesn't provide what online backups do, of recovery to the minute of the
crash. And I get nervous having only logical pg_dump output, no backups of the
actual blocks on disk. But is that what everybody does?

Gak!! It can never be guaranteed that the "actual blocks on disk"
are transactionally consistent. Thus, the pg_dump output is suff-
icient.

Hello all:

I'm building a web-based app that is purely a query tool: no data can be
added or edited. Postgres is the back end.

What does this have to do with online backups vs pg_dump ?
Please don't follow up to threads with unrelated questions.

In any case you're far more likely to see answers if you post a message
properly as your message won't show up buried inside old threads in people's
mail user agents.

--
greg
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly