Re: [PERFORM] slow database, queries accumulating
I have read that 600 connections are a LOT (somebody correct me please if I'm wrong), since each connections requires a process and your server must serve this. Besides the overhead involved, you will end up with 1200 megabytes of sort_mem allocated (probably idle most of time)... pgpool allows you to reuse process (similar to oracle shared servers). Fact: I didn't have the need to use it. AFAICS, it's easy to use. (I'll try to make it work and I'll share tests, but dunno know when) long life, little spam and prosperity -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] nombre de Anjan Dave Enviado el: viernes, 23 de septiembre de 2005 13:02 Para: pgsql-performance@postgresql.org Asunto: [PERFORM] slow database, queries accumulating Hi We are experiencing consistent slowness on the database for one application. This is more a reporting type of application, heavy on the bytea data type usage (gets rendered into PDFs in the app server). A lot of queries, mostly selects and a few random updates, get accumulated on the server - with increasing volume of users on the application. Below is a snapshot of top, with about 80 selects and 3 or 4 updates. Things get better eventually if I cancel (SIGINT) some of the oldest queries. I also see a few instances of shared locks not being granted during this time.I don't even see high iowait or memory starvation during these times, as indicated by top. -bash-2.05b$ psql -c select * from pg_locks; dbname | grep f | |77922136 | 16761 | ShareLock| f We (development) are looking into the query optimization (explain analyze, indexes, etc), and my understanding is that the queries when run for explain analyze execute fast, but during busy times, they become quite slow, taking from a few seconds to a few minutes to execute. I do see in the log that almost all queries do have either ORDER BY, or GROUP BY, or DISTINCT. Does it hurt to up the sort_mem to 3MB or 4MB? Should I up the effective_cache_size to 5 or 6GB? The app is does not need a lot of connections on the database, I can reduce it down from 600. Based on the description above and the configuration below does any thing appear bad in config? Is there anything I can try in the configuration to improve performance? The database size is about 4GB. This is PG 7.4.7, RHAS3.0 (u5), Local 4 spindle RAID10 (15KRPM), and logs on a separate set of drives, RAID10. 6650 server, 4 x XEON, 12GB RAM. Vacuum is done every night, full vacuum done once a week. I had increased the shared_buffers and sort_memory recently, which didn't help. Thanks, Anjan 10:44:51 up 14 days, 13:38, 2 users, load average: 0.98, 1.14, 1.12 264 processes: 257 sleeping, 7 running, 0 zombie, 0 stopped CPU states: cpuusernice systemirq softirq iowaitidle total 14.4%0.0%7.4% 0.0% 0.0%0.0% 77.9% cpu00 15.7%0.0%5.7% 0.0% 0.1%0.0% 78.2% cpu01 15.1%0.0%7.5% 0.0% 0.0%0.1% 77.0% cpu02 10.5%0.0%5.9% 0.0% 0.0%0.0% 83.4% cpu039.9%0.0%5.9% 0.0% 0.0%0.0% 84.0% cpu047.9%0.0%3.7% 0.0% 0.0%0.0% 88.2% cpu05 19.3%0.0% 12.3% 0.0% 0.0%0.0% 68.3% cpu06 20.5%0.0%9.5% 0.0% 0.0%0.1% 69.7% cpu07 16.1%0.0%8.5% 0.0% 0.1%0.3% 74.7% Mem: 12081736k av, 7881972k used, 4199764k free, 0k shrd, 82372k buff 4823496k actv, 2066260k in_d,2036k in_c Swap: 4096532k av, 0k used, 4096532k free 6888900k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 16773 postgres 15 0 245M 245M 240M S 0.0 2.0 1:16 7 postmaster 16880 postgres 15 0 245M 245M 240M S 0.1 2.0 0:49 6 postmaster 16765 postgres 15 0 245M 245M 240M S 0.0 2.0 1:16 0 postmaster 16825 postgres 15 0 245M 245M 240M S 0.0 2.0 1:02 5 postmaster 16774 postgres 15 0 245M 245M 240M S 0.1 2.0 1:16 0 postmaster 16748 postgres 15 0 245M 245M 240M S 0.0 2.0 1:19 5 postmaster 16881 postgres 15 0 245M 245M 240M S 0.1 2.0 0:50 7 postmaster 16762 postgres 15 0 245M 245M 240M S 0.0 2.0 1:14 4 postmaster . . max_connections = 600 shared_buffers = 3 #=234MB, up from 21760=170MB min 16, at least max_connections*2, 8KB each sort_mem = 2048 # min 64, size in KB vacuum_mem = 32768 # up from 16384 min 1024, size in KB # - Free Space Map - #max_fsm_pages = 2 # min max_fsm_relations*16, 6 bytes each #max_fsm_relations = 1000 # min 100, ~50 bytes each #fsync = true # turns forced synchronization on or off #wal_sync_method = fsync# the default varies across platforms: # fsync,
Re: [PERFORM] slow database
On Feb 11, 2004, at 7:23 AM, [EMAIL PROTECTED] wrote: # # Optimizer Parameters # enable_seqscan = false enable_indexscan = false enable_tidscan = false enable_sort = false enable_nestloop = false enable_mergejoin = false enable_hashjoin = false Why did you disable *every* type of query method? Try commenting all of these out or changing them to true instead of false. -- PC Drew Manager, Dominet IBSN 1600 Broadway, Suite 400 Denver, CO 80202 Phone: 303-984-4727 x107 Cell: 720-841-4543 Fax: 303-984-4730 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] slow database
On Wed, 2004-02-11 at 09:23, [EMAIL PROTECTED] wrote: my data base is very slow. The machine is a processor Xeon 2GB with 256 MB of RAM DDR. My archive of configuration is this: I'm not surprised. New values below old. sort_mem = 131072 # min 64, size in KB sort_mem = 8192. fsync = false Are you aware of the potential for data corruption during a hardware, power or software failure? enable_seqscan = false enable_indexscan = false enable_tidscan = false enable_sort = false enable_nestloop = false enable_mergejoin = false enable_hashjoin = false You want all of these set to true, not false. effective_cache_size = 17 # typically 8KB each effective_cache_size = 16384. random_page_cost = 10 # units are one sequential page fetch cost random_page_cost = 3 cpu_tuple_cost = 0.3# (same) cpu_tuple_cost = 0.01 cpu_index_tuple_cost = 0.6 # (same) cpu_index_tuple_cost = 0.001 cpu_operator_cost = 0.7 # (same) cpu_operator_cost = 0.0025 default_statistics_target = 1 # range 1-1000 default_statistics_target = 10 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] slow database
On Feb 11, 2004, at 7:23 AM, [EMAIL PROTECTED] wrote: my data base is very slow. The machine is a processor Xeon 2GB with 256 MB of RAM DDR. My archive of configuration is this: After looking through the configuration some more, I would definitely recommend getting rid of your current postgresql.conf file and replacing it with the default. You have some very very odd settings, namely: This is dangerous, but maybe you need it: fsync = false You've essentially disabled the optimizer: enable_seqscan = false enable_indexscan = false enable_tidscan = false enable_sort = false enable_nestloop = false enable_mergejoin = false enable_hashjoin = false WOAH, this is huge: random_page_cost = 10 Take a look at this page which goes through each option in the configuration file: http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html -- PC Drew Manager, Dominet IBSN 1600 Broadway, Suite 400 Denver, CO 80202 Phone: 303-984-4727 x107 Cell: 720-841-4543 Fax: 303-984-4730 Email: [EMAIL PROTECTED] ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] slow database
[EMAIL PROTECTED] wrote: my data base is very slow. The machine is a processor Xeon 2GB with 256 MB of RAM DDR. My archive of configuration is this: This is a joke, right? chris ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] slow database
[EMAIL PROTECTED] writes: my data base is very slow. The machine is a processor Xeon 2GB with 256 MB of RAM DDR. My archive of configuration is this: sort_mem = 131072 # min 64, size in KB #vacuum_mem = 8192 # min 1024, size in KB Change it back to 8192, or perhaps even less. This large value is probably causing swapping, because it leads to every sort trying to use 1073741824 bytes of memory, which is considerably more than you have. fsync = false wal_sync_method = fdatasync # the default varies across platforms: I presume that you are aware that you have chosen the value that leaves your data vulnerable to corruption? I wouldn't set this to false... enable_seqscan = false enable_indexscan = false enable_tidscan = false enable_sort = false enable_nestloop = false enable_mergejoin = false enable_hashjoin = false Was there some reason why you wanted to disable every query optimization strategy that can be disabled? If you're looking to get slow queries, this would accomplish that nicely. effective_cache_size = 17 # typically 8KB each random_page_cost = 10 # units are one sequential page fetch cost cpu_tuple_cost = 0.3# (same) cpu_index_tuple_cost = 0.6 # (same) cpu_operator_cost = 0.7 # (same) Where did you get those numbers? The random_page_cost alone will probably force every query to do seq scans, ignoring indexes, and is _really_ nonsensical. The other values seem way off. default_statistics_target = 1 # range 1-1000 ... Apparently it didn't suffice to try to disable query optimization, and modify the cost parameters into nonsense; it was also needful to tell the statistics analyzer to virtually eliminate statistics collection. If you want a value other than 10, then pick a value slightly LARGER than 10. somebody please knows to give tips to me to increase the performance Delete the postgresql.conf file, create a new database using initdb, and take the file produced by _that_, and replace with that one. The default values, while not necessarily perfect, are likely to be 100x better than what you have got. Was this the result of someone trying to tune the database for some sort of anti-benchmark? -- let name=cbbrowne and tld=cbbrowne.com in name ^ @ ^ tld;; http://www.ntlug.org/~cbbrowne/rdbms.html Rules of the Evil Overlord #179. I will not outsource core functions. http://www.eviloverlord.com/ ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] slow database
the normal queries do not present problems, but all the ones that join has are very slow. OBS: I am using way ODBC. He will be that they exist some configuration specifies inside of the same bank or in the ODBC? Quoting Rod Taylor [EMAIL PROTECTED]: On Wed, 2004-02-11 at 09:23, [EMAIL PROTECTED] wrote: my data base is very slow. The machine is a processor Xeon 2GB with 256 MB of RAM DDR. My archive of configuration is this: I'm not surprised. New values below old. sort_mem = 131072 # min 64, size in KB sort_mem = 8192. fsync = false Are you aware of the potential for data corruption during a hardware, power or software failure? enable_seqscan = false enable_indexscan = false enable_tidscan = false enable_sort = false enable_nestloop = false enable_mergejoin = false enable_hashjoin = false You want all of these set to true, not false. effective_cache_size = 17 # typically 8KB each effective_cache_size = 16384. random_page_cost = 10 # units are one sequential page fetch cost random_page_cost = 3 cpu_tuple_cost = 0.3# (same) cpu_tuple_cost = 0.01 cpu_index_tuple_cost = 0.6 # (same) cpu_index_tuple_cost = 0.001 cpu_operator_cost = 0.7 # (same) cpu_operator_cost = 0.0025 default_statistics_target = 1 # range 1-1000 default_statistics_target = 10 ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster ---(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
Re: [PERFORM] slow database
If my boss came to me and asked me to make my database server run as slowly as possible, I might come up with the exact same postgresql.conf file as what you posted. Just installing the default postgresql.conf that came with postgresql should make this machine run faster. Read this: http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html ---(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
Re: [PERFORM] slow database
On Wed, 2004-02-11 at 12:15, [EMAIL PROTECTED] wrote: I already came back the old conditions and I continue slow in the same way! Dumb question, but did you restart the database after changing the config file? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
Re: [PERFORM] slow database
If things are still slow after you have checked your keys as indicated, then pick one query and post the output from EXPLAIN ANALYZE for the list to examine. oh - and ensure you are *not* still using your original postgresql.conf :-) best wishes Mark scott.marlowe wrote: First thing I would check is to make sure all those foreign keys are the same type. Second, make sure you've got indexes to go with them. I.e. on a multi-key fk, have a multi-key index. ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
