Re: Re: MySQL -- was mysql backup quesiton

["markw-4" <user=5@nabble.blu.org>]

 > dear mark, 
> 
> yes, it did not respond to my PHP application when the record hit 
> millions entry. well, it will need sometime to dig my archives since 
> i'm not into it right now (it was 2-3 years ago). anyway, i'm glad to know 
> there's somebody here could help me with pg, i really want to give it a 
> chance. 
> 

Ahh, it didn't crash, it "stopped responding."  That could be very telling. 

Well, one of the downsides of "good" or *intelligent* query planners is 
that they need information to work correctly. Databases like Oracle and 
PostgreSQL have an "analyze" command which does a statistical sampling of 
the data and creates hints for the query analyzer to use. 

What you describe sounds like a classic case. I'm not saying this is what 
happened, but I've seen this behavior on a few different database systems, 
alas PostgreSQL as well. 

The query analyzer thinks the table is pretty small, so it plans the query 
assuming it can load all the data it needs to use in RAM. It then starts 
to churn through the data, low and behold, it runs out of its alloted 
memory. Then it switches storage from RAM to disk, which slows it down 
HUGELY.  This sort of behavior makes the system look broken because its 
working fine, and then all the sudden, it seems to take for ever or hang. 

The fix is to run analyze. This is something you should do periodically as 
it helps the query planner decide the best approach. For instance, 
PostgreSQL often decides NOT to use an index if it thinks the table is 
pretty small. This will save memory and disk I/O not having to scan the 
index. If PostgreSQL doesn't know that the table is large, it doesn't use 
the index. 

-- 
This message has been scanned for viruses and 
dangerous content by MailScanner, and is 
believed to be clean. 

_______________________________________________ 
Discuss mailing list 
[hidden email] 
http://lists.blu.org/mailman/listinfo/discuss