E posibil sa fie de vina comanda vacuum a postgressql-ului: poate ai ceva in cron care o porneste periodic.
e.p. On Thu, 11 Nov 2004 14:36:00 +0200 (EET) George Razvan NICA <[EMAIL PROTECTED]> wrote: > Se da urmatoarea masina: > > CPU: 2 x Athlon 2.1GHz > RAM: 2GB RAM > DISK: LSI Logic MegaRAID - RAID1 - 152GB > KERNEL: 2.4.26 vanilla > > Peste masina asta lucreaza cuminti un PostgreSQL, un Apache cu SSL, > PHP cu turck-mmcache si un qmail care este insa folosit doar pentru > trimitere de mail-uri din PHP. Cireasa de pe tort este un CRM. > > Problema: Desi ruleaza cu 0.1, 0.2 load aproape tot timpul, din cand > in cand(a se citi cam de doua ori pe zi) masina are varfuri de > incarcare intre 2 si 3, timp de 1-2 minute, dupa care revine la > normal. Din ce am vazut, load-ul e cauzat de PostgreSQL. Puteti vedea > si voi asta din "rapoartele" pe care le adaug mai jos. > > Ce vreau sa fac: enable query logging in PostgreSQL, dupa care sa fac > statistici cu query-urile cel mai des apelate pe care sa i le dau > omului care face development sa se spele cu ele pe cap. Ceva similar > cu ce gasiti la > http://www.databasejournal.com/features/postgresql/article.php/3323561 > . De asemenea, sa vad ce query-uri sunt apelate atunci cand creste > load-ul, confruntand ora din log cu ora la care primesc raportul prin > mail. > > Totodata, as vrea sa stiu ce credeti voi, bazat si pe informatiile din > copy-paste-urile de mai jos: CPU bottleneck, Disk bottleneck, needs > more RAM, bad queries? Pot furniza si alte informatii daca e nevoie. > > Orice sugestii si further-reading URLs sunt most welcome. > > TIA, > -- > GRN > > Temet Nosce > > PS: "Rapoartele" (trimise automat in momentul in care creste load-ul): > > *** primul > > /bin/ps -eo user,pid,pcpu,pmem,vsz,rss,tty,stat,start,wchan:15,comm > --sort -vsize,-rss=== cut here === > USER PID %CPU %MEM VSZ RSS TT STAT STARTED WCHAN > COMMAND > postgres 29670 12.0 3.3 85556 68944 ? D 10:07:00 > wait_on_page postmaster postgres 29664 17.0 3.3 77264 70044 ? > D 10:06:36 lock_page postmaster > postgres 28647 0.0 0.2 76532 4620 ? S 09:56:40 > unix_stream_dat postmaster postgres 7335 0.0 0.0 75816 428 ? > S Oct 26 select postmaster > nobody 3093 0.0 1.0 56100 20696 ? S Oct 28 semtimedop > httpd > nobody 3071 0.0 0.9 56072 19692 ? S Oct 28 semtimedop > httpd > nobody 3074 0.0 0.7 50908 16408 ? S Oct 28 poll > httpd > nobody 3091 0.0 0.9 50824 19116 ? S Oct 28 semtimedop > httpd > nobody 3102 0.0 0.7 49324 15348 ? S Oct 28 > tcp_data_wait httpd nobody 3075 0.0 0.7 48400 16520 ? S > Oct 28 select httpd > nobody 3072 0.0 0.7 48368 15668 ? S Oct 28 > tcp_data_wait httpd nobody 3610 0.0 0.7 48084 16536 ? S > Oct 28 semtimedop httpd > nobody 3078 0.0 0.7 48052 16540 ? S Oct 28 semtimedop > httpd > nobody 3073 0.0 0.7 48016 14848 ? S Oct 28 semtimedop > httpd > root 9335 0.0 0.0 44596 840 ? Ss Oct 12 select > httpd > root 14105 0.0 0.0 12120 1436 ? Ss Oct 25 nanosleep > MegaServ > postgres 7353 0.0 0.1 11004 2124 ? S Oct 26 select > postmaster > postgres 7352 0.0 0.0 7872 1384 ? S Oct 26 select > postmaster > [snip] > === and here === > > /usr/bin/vmstat -S M 1 5 > === cut here === > procs -----------memory---------- ---swap-- -----io---- --system-- > ----cpu---- > r b swpd free buff cache si so bi bo in cs us > sy id wa 0 3 49 49 10 1757 0 0 0 0 1 > 2 2 2 96 0 > 1 1 49 48 10 1758 0 0 25128 0 1045 1183 0 > 11 89 0 0 2 49 48 10 1758 0 0 27028 148 1130 > 1343 0 11 89 0 0 2 49 48 10 1758 0 0 27360 > 0 1140 1346 2 13 85 0 0 3 49 48 10 1758 0 0 > 24976 1200 1151 1248 0 10 90 0 > === and here === > > *** al doilea, la 30 de secunde de primul > > /bin/ps -eo user,pid,pcpu,pmem,vsz,rss,tty,stat,start,wchan:15,comm > --sort -vsize,-rss=== cut here === > USER PID %CPU %MEM VSZ RSS TT STAT STARTED WCHAN > COMMAND > postgres 29853 19.0 0.6 78712 13620 ? S 10:12:17 > wait_for_tcp_me postmaster postgres 29855 1.0 0.2 76672 4456 ? > D 10:12:18 wait_on_page postmaster > postgres 7335 0.0 0.0 75816 428 ? S Oct 26 select > postmaster > nobody 3093 0.0 1.0 56100 20696 ? S Oct 28 semtimedop > httpd > nobody 3071 0.0 0.9 56072 18732 ? S Oct 28 semtimedop > httpd > nobody 3074 0.0 0.7 50908 16516 ? S Oct 28 semtimedop > httpd > nobody 3091 0.0 0.9 50824 19116 ? S Oct 28 semtimedop > httpd > nobody 3102 0.0 0.7 49324 15348 ? S Oct 28 semtimedop > httpd > nobody 3075 0.0 0.7 48400 16520 ? S Oct 28 select > httpd > nobody 3072 0.0 0.7 48368 15668 ? S Oct 28 semtimedop > httpd > nobody 3610 0.0 0.7 48084 15920 ? S Oct 28 semtimedop > httpd > nobody 3078 0.0 0.7 48052 16540 ? S Oct 28 semtimedop > httpd > nobody 3073 0.0 0.7 48016 14848 ? S Oct 28 semtimedop > httpd > root 9335 0.0 0.0 44596 840 ? Ss Oct 12 select > httpd > root 14105 0.0 0.0 12124 908 ? Ss Oct 25 nanosleep > MegaServ > postgres 7353 0.0 0.0 11004 2060 ? S Oct 26 select > postmaster > postgres 7352 0.0 0.0 7872 1384 ? S Oct 26 select > postmaster > root 27674 0.0 0.0 5624 240 ? S Oct 23 > unix_stream_dat sshd postgres 29231 0.0 0.0 4636 1624 ? S > 10:02:01 poll psql[snip] > === and here === > > /usr/bin/vmstat -S M 1 5 > === cut here === > procs -----------memory---------- ---swap-- -----io---- --system-- > ----cpu---- > r b swpd free buff cache si so bi bo in cs us > sy id wa 0 2 49 49 40 1750 0 0 0 0 1 > 2 2 2 96 0 > 0 3 49 48 40 1751 0 0 6774 1640 948 1347 13 > 3 83 0 0 2 49 49 40 1751 0 0 5256 1104 595 > 1262 5 1 93 0 0 2 49 49 41 1750 0 0 8616 > 2080 871 2597 13 8 79 0 1 1 49 49 41 1750 0 > 0 6152 364 502 645 2 3 94 0 > === and here === > > *** al treilea, la inca 30 de secunde > > /bin/ps -eo user,pid,pcpu,pmem,vsz,rss,tty,stat,start,wchan:15,comm > --sort -vsize,-rss=== cut here === > USER PID %CPU %MEM VSZ RSS TT STAT STARTED WCHAN > COMMAND > postgres 29890 3.0 0.2 84868 5660 ? D 10:12:52 lock_page > postmaster > postgres 29887 17.3 0.5 78568 11944 ? S 10:12:50 > tcp_data_wait postmaster postgres 7335 0.0 0.0 75816 428 ? > S Oct 26 select postmaster > nobody 3093 0.0 1.0 56100 20696 ? S Oct 28 semtimedop > httpd > nobody 3071 0.0 0.9 56072 18732 ? S Oct 28 semtimedop > httpd > nobody 3074 0.0 0.7 50908 16516 ? S Oct 28 semtimedop > httpd > nobody 3091 0.0 0.9 50824 19116 ? S Oct 28 semtimedop > httpd > nobody 3102 0.0 0.7 49324 15348 ? S Oct 28 semtimedop > httpd > nobody 3075 0.0 0.7 48400 16520 ? S Oct 28 semtimedop > httpd > nobody 3072 0.0 0.7 48368 15668 ? S Oct 28 select > httpd > nobody 3610 0.0 0.7 48084 15920 ? S Oct 28 semtimedop > httpd > nobody 3078 0.0 0.7 48052 16540 ? S Oct 28 semtimedop > httpd > nobody 3073 0.0 0.7 48016 14848 ? S Oct 28 semtimedop > httpd > root 9335 0.0 0.0 44596 840 ? Ss Oct 12 select > httpd > root 14105 0.0 0.0 12124 908 ? Ss Oct 25 nanosleep > MegaServ > postgres 7353 0.0 0.0 11004 2060 ? S Oct 26 select > postmaster > postgres 7352 0.0 0.0 7872 1384 ? S Oct 26 select > postmaster > root 27674 0.0 0.0 5624 240 ? S Oct 23 > unix_stream_dat sshd postgres 29231 0.0 0.0 4636 1624 ? S > 10:02:01 poll psql root 2320 0.0 0.0 3496 1068 ? > S Nov 07 pipe_wait loadmon.pl > root 30888 0.0 0.0 3384 204 ? Ss Jul 24 select > sshd > root 29893 0.0 0.0 3356 1664 ? R 10:12:53 - > ps > postgres 29061 0.0 0.0 2288 1088 ? S 10:02:01 wait4 > do.maintenance > postgres 29030 0.0 0.0 2264 1016 ? Ss 10:02:01 wait4 > sh > [snip] > === and here === > > /usr/bin/vmstat -S M 1 5 > === cut here === > procs -----------memory---------- ---swap-- -----io---- --system-- > ----cpu---- > r b swpd free buff cache si so bi bo in cs us > sy id wa 0 1 49 50 46 1743 0 0 0 0 1 > 2 2 2 96 0 > 0 3 49 49 46 1745 0 0 6420 2180 1274 1563 11 > 3 85 0 3 0 49 48 46 1745 0 0 6684 3268 825 > 1557 8 4 87 0 0 2 49 48 47 1745 0 0 9992 > 5144 1165 2071 15 8 76 0 0 3 49 48 47 1745 0 > 0 11896 7492 1153 1752 9 10 80 0 > === and here === > > *** al patrulea, la inca 30 de secunde > > /bin/ps -eo user,pid,pcpu,pmem,vsz,rss,tty,stat,start,wchan:15,comm > --sort -vsize,-rss=== cut here === > USER PID %CPU %MEM VSZ RSS TT STAT STARTED WCHAN > COMMAND > postgres 29896 13.2 3.3 85400 69760 ? R 10:12:54 - > postmaster > postgres 29909 4.0 0.3 84880 7836 ? D 10:13:26 > wait_on_page postmaster postgres 7335 0.0 0.0 75816 428 ? > S Oct 26 select postmaster > nobody 3093 0.0 1.0 56100 20696 ? S Oct 28 semtimedop > httpd > nobody 3071 0.0 0.9 56072 18732 ? S Oct 28 semtimedop > httpd > nobody 3074 0.0 0.7 50908 16516 ? S Oct 28 semtimedop > httpd > nobody 3091 0.0 0.9 50824 19116 ? S Oct 28 > tcp_data_wait httpd nobody 3102 0.0 0.7 49324 15016 ? S > Oct 28 semtimedop httpd > nobody 3075 0.0 0.7 48400 16520 ? S Oct 28 semtimedop > httpd > nobody 3072 0.0 0.7 48368 15668 ? S Oct 28 semtimedop > httpd > nobody 3610 0.0 0.7 48084 15528 ? S Oct 28 > tcp_data_wait httpd nobody 3078 0.0 0.7 48052 16540 ? S > Oct 28 semtimedop httpd > nobody 3073 0.0 0.7 48016 14848 ? S Oct 28 select > httpd > root 9335 0.0 0.0 44596 840 ? Ss Oct 12 select > httpd > root 14105 0.0 0.0 12124 908 ? Ss Oct 25 nanosleep > MegaServ > postgres 7353 0.0 0.0 11004 2060 ? S Oct 26 select > postmaster > postgres 7352 0.0 0.0 7872 1384 ? S Oct 26 select > postmaster > root 27674 0.0 0.0 5624 240 ? S Oct 23 > unix_stream_dat sshd postgres 29231 0.0 0.0 4636 1624 ? S > 10:02:01 poll psql root 2320 0.0 0.0 3496 1068 ? > S Nov 07 pipe_wait loadmon.pl > root 30888 0.0 0.0 3384 204 ? Ss Jul 24 select > sshd > root 29912 0.0 0.0 3356 1664 ? R 10:13:27 - > ps > postgres 29061 0.0 0.0 2288 1088 ? S 10:02:01 wait4 > do.maintenance > postgres 29030 0.0 0.0 2264 1016 ? Ss 10:02:01 wait4 > sh > qmaild 387 0.0 0.0 2244 16 ? S Jun 28 > wait_for_connec tcpserver root 284 0.0 0.0 2240 508 ? > Ss Jun 28 select syslogd[ snip ] > === and here === > > /usr/bin/vmstat -S M 1 5 > === cut here === > procs -----------memory---------- ---swap-- -----io---- --system-- > ----cpu---- > r b swpd free buff cache si so bi bo in cs us > sy id wa 0 2 50 49 46 1747 0 0 0 0 1 > 2 2 2 96 0 > 0 2 50 49 45 1747 0 0 32556 1488 1317 1884 8 > 12 80 0 1 1 50 48 44 1749 0 0 22976 796 1298 > 1688 5 8 87 0 0 2 50 48 44 1750 0 0 9616 > 612 1367 1033 0 6 93 0 2 1 50 49 44 1747 0 0 > 7100 3164 831 1631 5 21 74 0 > === and here === > > > *** al cincilea, la inca 30 de secunde > > /bin/ps -eo user,pid,pcpu,pmem,vsz,rss,tty,stat,start,wchan:15,comm > --sort -vsize,-rss=== cut here === > USER PID %CPU %MEM VSZ RSS TT STAT STARTED WCHAN > COMMAND > postgres 29970 5.1 1.2 85412 25940 ? D 10:14:24 > wait_on_page postmaster postgres 29969 12.8 3.3 85392 69076 ? > D 10:14:22 lock_page postmaster > postgres 7335 0.0 0.0 75816 428 ? S Oct 26 select > postmaster > nobody 3093 0.0 1.0 56100 20696 ? S Oct 28 semtimedop > httpd > nobody 3071 0.0 0.9 56072 18732 ? S Oct 28 semtimedop > httpd > nobody 3074 0.0 0.7 50908 16516 ? S Oct 28 > tcp_data_wait httpd nobody 3091 0.0 0.9 50824 19116 ? S > Oct 28 tcp_data_wait httpd > nobody 3102 0.0 0.7 49324 15016 ? S Oct 28 semtimedop > httpd > nobody 3075 0.0 0.7 48400 16520 ? S Oct 28 select > httpd > nobody 3072 0.0 0.7 48368 15668 ? S Oct 28 semtimedop > httpd > nobody 3610 0.0 0.7 48084 15528 ? S Oct 28 > tcp_data_wait httpd nobody 3078 0.0 0.7 48052 16540 ? S > Oct 28 semtimedop httpd > nobody 3073 0.0 0.7 48016 14848 ? S Oct 28 semtimedop > httpd > root 9335 0.0 0.0 44596 840 ? Ss Oct 12 select > httpd > root 14105 0.0 0.0 12124 908 ? Ss Oct 25 nanosleep > MegaServ > postgres 7353 0.0 0.0 11004 2060 ? S Oct 26 select > postmaster > postgres 7352 0.0 0.0 7872 1384 ? S Oct 26 select > postmaster > [ snip ] > === and here === > > /usr/bin/vmstat -S M 1 5 > === cut here === > procs -----------memory---------- ---swap-- -----io---- --system-- > ----cpu---- > r b swpd free buff cache si so bi bo in cs us > sy id wa 0 2 50 49 46 1746 0 0 0 0 1 > 2 2 2 96 0 > 0 2 50 48 46 1747 0 0 7224 0 1054 869 0 > 4 96 0 0 2 50 48 46 1746 0 0 6536 2268 1000 > 793 1 3 95 0 0 2 50 48 46 1746 0 0 8116 > 628 865 686 0 4 96 0 0 3 50 49 46 1745 0 0 > 3952 5128 732 510 2 15 83 0 > === and here === > > *** al saselea, la inca 30 de secunde > > /bin/ps -eo user,pid,pcpu,pmem,vsz,rss,tty,stat,start,wchan:15,comm > --sort -vsize,-rss=== cut here === > USER PID %CPU %MEM VSZ RSS TT STAT STARTED WCHAN > COMMAND > postgres 30085 10.6 0.7 77172 14576 ? S 10:15:34 > unix_stream_dat postmaster postgres 30092 0.0 0.1 76268 2152 ? > D 10:15:37 wait_on_page postmaster > postgres 30091 0.0 0.1 76248 2996 ? D 10:15:37 > wait_on_page postmaster postgres 7335 0.0 0.0 75816 428 ? > S Oct 26 select postmaster > nobody 3093 0.0 1.0 56100 20696 ? S Oct 28 semtimedop > httpd > nobody 3071 0.0 0.9 56072 18732 ? S Oct 28 > tcp_data_wait httpd nobody 3074 0.0 0.7 50908 16516 ? S > Oct 28 select httpd > nobody 3091 0.0 0.9 50824 19116 ? S Oct 28 semtimedop > httpd > nobody 3102 0.0 0.7 49324 15016 ? S Oct 28 semtimedop > httpd > nobody 3075 0.0 0.7 48400 16520 ? S Oct 28 semtimedop > httpd > nobody 3072 0.0 0.7 48368 15668 ? S Oct 28 semtimedop > httpd > nobody 3610 0.0 0.7 48084 15528 ? S Oct 28 semtimedop > httpd > nobody 3078 0.0 0.7 48052 16540 ? S Oct 28 > tcp_data_wait httpd nobody 3073 0.0 0.7 48016 14848 ? S > Oct 28 semtimedop httpd > root 9335 0.0 0.0 44596 840 ? Ss Oct 12 select > httpd > root 14105 0.0 0.0 12124 908 ? Ss Oct 25 nanosleep > MegaServ > postgres 7353 0.0 0.0 11004 2060 ? S Oct 26 select > postmaster > nobody 30012 0.3 0.1 9496 3976 ? S 10:15:01 poll > reminder_send > postgres 7352 0.0 0.0 7872 1384 ? S Oct 26 select > postmaster > [ snip ] > === and here === > > /usr/bin/vmstat -S M 1 5 > === cut here === > procs -----------memory---------- ---swap-- -----io---- --system-- > ----cpu---- > r b swpd free buff cache si so bi bo in cs us > sy id wa 0 2 50 49 54 1735 0 0 0 0 1 > 2 2 2 96 0 > 0 2 50 49 54 1737 0 0 8356 3584 757 1351 7 > 7 86 0 1 1 50 48 54 1737 0 0 9352 2280 884 > 1915 9 8 83 0 1 1 50 49 54 1736 0 0 8308 > 3916 839 1382 5 8 87 0 2 0 50 48 54 1737 0 > 0 7908 2148 730 1603 6 7 87 0 > === and here === > > > --- > Detalii despre listele noastre de mail: http://www.lug.ro/ > --- Detalii despre listele noastre de mail: http://www.lug.ro/
