[ADMIN] performance problem - 10.000 databases
Marek Florianczyk
> Heck, you're already pushing the performance envelope with 3,000 users,
> might as well go for the faster of the two and you'll have one less
> scheduled upgrade ahead of you.
>
> When do you need to go live? If it's >1 month, then I'd definitely
> recommend 7.4.
heh... ;)
PostgreSQL 7.4 is so fu*#$%^ fast!!!
Unbelievable...
I've made test as usual ( some post earlier ) 3.000 schemas and 300
simultaneously connected. I've tuned postgresql.conf with my friend who
is our sql guru ( but he runs oracle usualy )
7.4 is so fast, that sometimes clients ( laptop with full X11
workstation and celeron 700 ) could not keep up forking perl script to
test new "database" ;)
my conf:
-----------------------
max_connections = 512
shared_buffers = 32768
sort_mem = 2048
vacuum_mem = 20480
max_fsm_pages = 589824
max_fsm_relations = 32768
fsync = false
wal_sync_method = fsync
wal_buffers = 1024
checkpoint_segments = 4
checkpoint_timeout = 1800
checkpoint_warning = 30
commit_delay = 10000
commit_siblings = 2
effective_cache_size = 131072
random_page_cost = 4
log_connections = true
log_duration = true
log_pid = true
log_statement = true
log_timestamp = true
search_path = '$user'
max_locks_per_transaction = 512
--------------------------------
from this test 4 tables(int,text,int) 1000 rows in each, no indexes
------------------------------------------------------------------
[test] times in sec.
(dbname) (conn. time) (q = queries)
(1row)(250rows)(tripleJoin)(update250rows)(update1000rows)
test2291: connect:1 q_fast:1 q_med:0 q_slow:4 q_upd:0 q_upd_all:9
test2260: connect:0 q_fast:1 q_med:0 q_slow:4 q_upd:0 q_upd_all:10
test2274: connect:0 q_fast:1 q_med:0 q_slow:4 q_upd:0 q_upd_all:8
test2296: connect:0 q_fast:1 q_med:0 q_slow:6 q_upd:0 q_upd_all:6
test2283: connect:0 q_fast:1 q_med:0 q_slow:4 q_upd:0 q_upd_all:8
test2302: connect:0 q_fast:1 q_med:0 q_slow:4 q_upd:0 q_upd_all:8
test2290: connect:0 q_fast:1 q_med:0 q_slow:3 q_upd:0 q_upd_all:8
test2287: connect:0 q_fast:1 q_med:0 q_slow:6 q_upd:0 q_upd_all:6
test2267: connect:0 q_fast:1 q_med:0 q_slow:1 q_upd:0 q_upd_all:11
-----------------------------------------------------------------
the "\d" queries works under this load just fine!
Now, I just have to modify phpPgAdmin (it's for users to modify their
own "database" ), I don't know why when I select to database it's try to
fetch all tablenames from all schemas. From log:
---------------------------------------------------------------------
2003-11-06 22:53:06 [8880] LOG: statement: SET SEARCH_PATH TO "test998"
2003-11-06 22:53:06 [8880] LOG: duration: 1.207 ms
2003-11-06 22:53:06 [8880] LOG: statement: SELECT tablename, tableowner
FROM pg_catalog.pg_tables
WHERE schemaname='test998' ORDER BY
tablename
2003-11-06 22:53:06 [8880] LOG: duration: 31.005 ms
2003-11-06 22:53:06 [8880] LOG: statement: SET SEARCH_PATH TO "test999"
2003-11-06 22:53:06 [8880] LOG: duration: 1.202 ms
2003-11-06 22:53:06 [8880] LOG: statement: SELECT tablename, tableowner
FROM pg_catalog.pg_tables
WHERE schemaname='test999' ORDER BY
tablename
2003-11-06 22:53:06 [8880] LOG: duration: 30.604 ms
----------------------------------------------------------------
I should go alive with this hosting at the end of the month, but at the
beginning we shouldn't have many customer, so we decide to try v7.4 in
beta now, and wait for official release.
... And my management says, that there is no good support for Open
Source, heh... ;)))
thanks all
Marek
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majo...@postgresql.org so that your
message can get through to the mailing list cleanly
Jeff
Marek Florianczyk <fra...@tpi.pl> wrote:
> fsync = false
HOLD THE BOAT THERE BATMAN!
I would *STRONGLY* advise not running with fsync=false in production as
PG _CANNOT_ guaruntee data consistancy in the event of a hardware
failure. It would sure suck to have a power failure screw up your nice
db for the users!
> wal_buffers = 1024
This also seems high. come to think about it- shared_buffers is also
high.
> commit_delay = 10000
I could also read to data loss, but you'll get a speed increase on
inserts.
One of the best things you can do to increase insert speed is a nice,
battery backed raid card with a pile of disks hanging off of it.
--
Jeff Trout <je...@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Marek Florianczyk
> Marek Florianczyk <fra...@tpi.pl> wrote:
>
>
> > fsync = false
>
> HOLD THE BOAT THERE BATMAN!
>
> I would *STRONGLY* advise not running with fsync=false in production as
> PG _CANNOT_ guaruntee data consistancy in the event of a hardware
> failure. It would sure suck to have a power failure screw up your nice
> db for the users!
Sure I know, but with WAL it will make fsync every some? seconds, right?
Maybe users data, aren't so critical ;) it's not for bank, only for www
sites.
I will try with fsync=true also.
>
>
> > wal_buffers = 1024
>
> This also seems high. come to think about it- shared_buffers is also
> high.
>
> > commit_delay = 10000
>
> I could also read to data loss, but you'll get a speed increase on
> inserts.
>
> One of the best things you can do to increase insert speed is a nice,
> battery backed raid card with a pile of disks hanging off of it.
we will put 4 disks for /data directory ( raid1+0 ) so it will have
performance and fault tolerance, so it should be OK.
greetings
Marek
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
Tom Lane
>> PG _CANNOT_ guaruntee data consistancy in the event of a hardware
>> failure. It would sure suck to have a power failure screw up your nice
>> db for the users!
> Sure I know, but with WAL it will make fsync every some? seconds, right?
No. fsync = false turns off fsync of WAL. It's okay for development
but not when you actually care about integrity of your data.
regards, tom lane
scott.marlowe
> On 06 Nov 2003 15:21:03 +0100
> Marek Florianczyk <fra...@tpi.pl> wrote:
>
>
> > fsync = false
>
> HOLD THE BOAT THERE BATMAN!
>
> I would *STRONGLY* advise not running with fsync=false in production as
> PG _CANNOT_ guaruntee data consistancy in the event of a hardware
> failure. It would sure suck to have a power failure screw up your nice
> db for the users!
Note that if you're on an IDE drive and you haven't disabled the write
cache, you may as well turn off fsync as well, as it's just getting in the
way and doing nothing, i.e. the IDE drives are already lying about fsync
so why bother.
Step the first, get on SCSI / or a good IDE RAID controller, then step the
second, turn fsync back on. Without reliable storage, fsync is a dunsel.
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings
David Green
Marek Florianczyk wrote, on Thursday, 11/06/03:
>
>... And my management says, that there is no good support for Open
>Source, heh... ;)))
In my experience, there is better support for Open Source than
Closed Source when it comes to development (and usually all around).
David Green
Sage Automation, Inc.
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
scott.marlowe
>
> ... And my management says, that there is no good support for Open
> Source, heh... ;)))
That's because your "support" needs are different. A developer wants
answers and solutions, a manager often wants someone to blame. :-)
CoL
Christopher Browne wrote, On 11/6/2003 4:40 PM:
> thre...@torgo.978.org (Jeff) writes:
>> On 06 Nov 2003 15:21:03 +0100
>> Marek Florianczyk <fra...@tpi.pl> wrote:
>>
>>> fsync = false
>>
>> HOLD THE BOAT THERE BATMAN!
>>
>> I would *STRONGLY* advise not running with fsync=false in production as
>> PG _CANNOT_ guaruntee data consistancy in the event of a hardware
>> failure. It would sure suck to have a power failure screw up your nice
>> db for the users!
>
> On one of our test servers, I set "fsync=false", and a test load's
> load time dropped from about 90 minutes to 3 minutes. (It was REALLY
> update heavy, with huge numbers of tiny transactions.)
>
> Which is, yes, quite spectacularly faster. But also quite
> spectacularly unsafe.
>
> I'm willing to live with the risk on a test box whose purpose is
> _testing_; it's certainly not a good thing to do in production.
There is something like: set fsync to off; or set fsync to on;
But it says: 'fsync' cannot be changed now. However could be very useful
to set this option from sql, not just from config.
Tom Lane probably knows why :)
C.
Christopher Browne
> On 06 Nov 2003 15:21:03 +0100
> Marek Florianczyk <fra...@tpi.pl> wrote:
>
>> fsync = false
>
> HOLD THE BOAT THERE BATMAN!
>
> I would *STRONGLY* advise not running with fsync=false in production as
> PG _CANNOT_ guaruntee data consistancy in the event of a hardware
> failure. It would sure suck to have a power failure screw up your nice
> db for the users!
On one of our test servers, I set "fsync=false", and a test load's
load time dropped from about 90 minutes to 3 minutes. (It was REALLY
update heavy, with huge numbers of tiny transactions.)
Which is, yes, quite spectacularly faster. But also quite
spectacularly unsafe.
I'm willing to live with the risk on a test box whose purpose is
_testing_; it's certainly not a good thing to do in production.
--
let name="cbbrowne" and tld="libertyrms.info" in String.concat "@" [name;tld];;
<http://dev6.int.libertyrms.com/>
Christopher Browne
(416) 646 3304 x124 (land)
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majo...@postgresql.org
Stephen Frost
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
* Christopher Browne (cbbr...@libertyrms.info) wrote:
> On one of our test servers, I set "fsync=3Dfalse", and a test load's
> load time dropped from about 90 minutes to 3 minutes. (It was REALLY
> update heavy, with huge numbers of tiny transactions.)
>=20
> Which is, yes, quite spectacularly faster. But also quite
> spectacularly unsafe.
>=20
> I'm willing to live with the risk on a test box whose purpose is
> _testing_; it's certainly not a good thing to do in production.
Would it be possible to have the effectively done for a specific
transaction? If this was done as a single large transaction could there
be an option to say "don't fsync this until it's all done and then do it
all" or something? Just looking for a way to get the 'best of both
worlds'...
Stephen
--n7Lj0ukKFj+YqP4g
Content-Type: application/pgp-signature; name="signature.asc"
Content-Description: Digital signature
Content-Disposition: inline
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.3 (GNU/Linux)
iD8DBQE/rVQ/rzgMPqB3kigRAnbBAJ9acKJRzy+laMcdOCUx6DnS7WDBlgCfY8YC
sHDqFT25Op9uNTbdUmNu2ac=
=EEyt
-----END PGP SIGNATURE-----
--n7Lj0ukKFj+YqP4g--
Christopher Browne
>> load time dropped from about 90 minutes to 3 minutes. (It was
>> REALLY update heavy, with huge numbers of tiny transactions.)
>>
>> spectacularly unsafe.
>>
>> _testing_; it's certainly not a good thing to do in production.
>
> Would it be possible to have the effectively done for a specific
> transaction? If this was done as a single large transaction could
> there be an option to say "don't fsync this until it's all done and
> then do it all" or something? Just looking for a way to get the
> 'best of both worlds'...
Oh, for sure, the whole thing could be invoked as one giant
transaction, which would reduce the cost dramatically.
But it diminishes the value of the benchmark for my purposes. It's
useful to measure how costly those individual transactions are.
--
wm(X,Y):-write(X),write('@'),write(Y). wm('cbbrowne','cbbrowne.com').
http://www.ntlug.org/~cbbrowne/oses.html
"As far as Saddam Hussein being a great military strategist, he is
neither a strategist, nor is he schooled in the operational arts, nor
is he a tactician, nor is he a general, nor is he as a soldier. Other
than that, he's a great military man, I want you to know that."
-- General Norman Schwarzkopf, 2/27/91
scott.marlowe
> scott.marlowe wrote:
> > Note that if you're on an IDE drive and you haven't disabled the write
> > cache, you may as well turn off fsync as well, as it's just getting in the
> > way and doing nothing, i.e. the IDE drives are already lying about fsync
> > so why bother.
>
> What about Serial ATA?
I haven't gotten my hands on one yet to test. We might be getting some in
in the next few months where I work and I'll test them and report back
here then.