Re: Extremely slow server? - Mailing list pgsql-performance
| From | Vitalii Tymchyshyn |
|---|---|
| Subject | Re: Extremely slow server? |
| Date | |
| Msg-id | CABWW-d2TSJXfHDOk7eQM0FTasA0X+L6uaZG=it0u-oYh5issTw@mail.gmail.com Whole thread Raw |
| In response to | Re: Extremely slow server? (Craig James <cjames@emolecules.com>) |
| List | pgsql-performance |
Can it be hardware problem with io? Try finding out which file the stuck table is and do a simple fs copy. Or simply do a copy of the whole pg data directory.
15 вер. 2013 04:54, "Craig James" <cjames@emolecules.com> напис.
On Sat, Sep 14, 2013 at 11:36 AM, bricklen <bricklen@gmail.com> wrote:On Sat, Sep 14, 2013 at 11:28 AM, Craig James <cjames@emolecules.com> wrote:I'm trying to do a pg_dump of a database, and it more-or-less just sits there doing nothing.What is running in the db? Perhaps there is something blocking the pg_dump? What does the output of the following query look like?select * from pg_stat_activity where pid <> pg_backend_pid()
=# select * from pg_stat_activity where pid <> pg_backend_pid();
datid | datname | pid | usesysid | usename | application_name | client_addr | client_hostname | client_port | backend_start
| xact_start | query_start | state_change | waiting | state |
query
--------+------------+-------+----------+----------+------------------+-------------+-----------------+-------------+------------------------------
-+-------------------------------+------------------------------+-------------------------------+---------+--------+-------------------------------
-------------------------------------------------------------------------
231308 | emolecules | 13312 | 10 | postgres | pg_dump | | | -1 | 2013-09-14 18:37:08.752938-07
| 2013-09-14 18:37:08.783782-07 | 2013-09-14 18:39:43.74618-07 | 2013-09-14 18:39:43.746181-07 | f | active | COPY orders.chmoogle_thesaurus
(thesaurus_id, version_id, normalized, identifier, typecode) TO stdout;
(1 row)And a bit later:
231308 | emolecules | 13312 | 10 | postgres | pg_dump | | | -1 | 2013-09-14 18:37:08.752938-07
| 2013-09-14 18:37:08.783782-07 | 2013-09-14 18:47:38.287109-07 | 2013-09-14 18:47:38.287111-07 | f | active | COPY orders.customer_order_items (customer_order_item_id, customer_order_id, orig_smiles, orig_sdf, orig_datatype, orig_catalog_num, orig_rownum, cansmiles, version_id, version_smiles, parent_id, match_type, catalogue_id, supplier_id, sample_id, catalog_num, price_code, reason, discount, format_ordered, amount_ordered, units_ordered, format_quoted, amount_quoted, units_quoted, price_quoted, wholesale, nitems_shipped, nitems_analytical, comment, salt_name, salt_ratio, original_order_id, price_quoted_usd, wholesale_usd, invoice_price, hazardous) TO stdout;
(1 row)The Apache web server is shut off, and I upgraded to Postgres 9.2.4 since my first email.top(1) reports nothing interesting that I can see:top - 18:50:18 up 340 days, 3:28, 4 users, load average: 1.46, 1.40, 1.17
Tasks: 282 total, 1 running, 281 sleeping, 0 stopped, 0 zombie
Cpu(s): 0.2%us, 0.1%sy, 0.0%ni, 86.9%id, 12.7%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 12322340k total, 11465028k used, 857312k free, 53028k buffers
Swap: 19796984k total, 50224k used, 19746760k free, 10724856k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
13311 emi 20 0 28476 8324 1344 S 1 0.1 3:48.90 pg_dump
13359 emi 20 0 19368 1576 1076 R 0 0.0 0:00.09 top
1 root 20 0 23840 1372 688 S 0 0.0 0:03.13 init
2 root 20 0 0 0 0 S 0 0.0 0:00.04 kthreadd
3 root RT 0 0 0 0 S 0 0.0 0:00.27 migration/0
4 root 20 0 0 0 0 S 0 0.0 0:05.85 ksoftirqd/0... etc.Interestingly, it starts off going fairly fast according to "vmstat 2". This was started almost immediately after pg_dump started. Notice that it goes well for a couple minutes, slows for a bit, speeds up, then drops to almost nothing. It stays that way forever, just not doing anything. See below.Thanks,
Craig
procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu----
r b swpd free buff cache si so bi bo in cs us sy id wa
1 0 50492 4129584 53672 7455892 0 0 46 37 0 0 0 0 99 0
1 0 50488 4091536 53672 7494072 0 0 15744 0 389 717 12 0 87 0
3 0 50480 4053868 53672 7531836 0 0 15680 36 408 759 17 0 83 0
1 0 50480 4016252 53680 7569636 0 0 15580 6 379 700 12 0 87 0
2 0 50480 3979140 53680 7606800 0 0 15360 0 372 682 17 0 82 0
1 1 50480 3943536 53696 7642740 0 0 14720 68846 2076 1343 11 1 81 7
1 0 50448 3906040 53700 7680044 0 0 15488 2 456 760 18 0 80 1
1 0 50424 3866036 53704 7720436 0 0 16818 0 389 713 13 1 86 0
1 0 50424 3813564 53712 7772656 0 0 21952 16 424 772 18 1 81 0
1 0 50424 3762460 53712 7823840 0 0 21376 0 414 761 13 0 87 0
1 0 50424 3710416 53720 7875968 0 0 22080 6 420 777 18 0 82 0
1 0 50424 3660616 53720 7925828 0 0 20672 0 412 777 11 0 88 0
1 0 50424 3611208 53720 7975484 0 0 20608 0 411 753 17 0 83 0
1 0 50424 3566916 53728 8019580 0 0 18176 14 406 743 14 0 86 0
1 0 50424 3514708 53728 8071780 0 0 21760 0 427 791 17 1 83 0
0 1 50424 3478924 53736 8107700 0 0 15360 6 323 592 9 0 87 3
0 1 50424 3478636 53736 8108040 0 0 960 0 95 164 2 0 81 17
2 0 50424 3456340 52604 8130768 0 0 10818 36 365 532 6 0 89 5
1 0 50424 3413248 52580 8173744 0 0 21504 6 612 814 19 1 81 0
2 0 50424 3376956 52580 8210000 0 0 18368 0 554 759 15 0 85 0
2 1 50424 3338208 52600 8249304 0 0 19456 21360 848 800 18 0 81 0
2 0 50424 3304936 52600 8283196 0 0 16896 31532 1238 759 13 0 84 3
2 0 50424 3265300 52600 8323020 0 0 19776 0 581 780 18 0 82 0
3 0 50424 3218968 52608 8369276 0 0 23104 18 633 843 16 1 83 0
1 0 50424 3177500 52608 8410292 0 0 20544 0 583 761 15 0 85 0
2 0 50424 3133868 52608 8453808 0 0 21760 22 609 807 16 1 83 0
1 0 50424 3094220 52608 8493780 0 0 19840 0 576 765 18 1 81 0
2 0 50424 3054492 52608 8533436 0 0 19712 0 564 748 14 0 85 0
1 0 50424 3015664 52624 8572184 0 0 19264 12 562 755 18 0 82 0
0 1 50380 2946524 52628 8641404 0 0 34734 0 856 1035 15 1 83 1
2 0 50380 2882988 52636 8704484 0 0 28784 10 757 853 15 0 84 1
1 0 50376 2816264 52636 8771276 0 0 29632 4 751 842 15 0 84 1
1 0 50376 2743160 52636 8843216 0 0 32832 36 817 910 16 0 83 0
1 0 50372 2655072 52644 8931240 0 0 40260 6 912 892 17 1 82 1
2 0 50356 2531112 52644 9055776 0 0 58560 0 1211 940 15 1 84 0
2 0 50356 2406352 52660 9180476 0 0 58600 30 1214 945 18 1 80 1
2 0 50356 2303256 52664 9284364 0 0 48512 55414 2022 883 15 1 80 4
1 0 50356 2174184 52664 9413440 0 0 60608 0 1240 941 17 1 82 0
1 0 50356 2091428 52672 9495784 0 0 37652 20 858 758 14 0 86 0
1 0 50356 2022020 52672 9565420 0 0 31296 0 748 694 16 1 83 0
1 0 50356 1953052 52680 9634684 0 0 31168 48 759 709 14 0 86 0
1 1 50356 1903932 52700 9682092 10 0 21024 0 632 813 11 0 85 3
1 0 50356 1862844 52700 9723072 0 0 20138 0 702 981 15 1 78 6
1 0 50356 1809516 52716 9776556 0 0 22400 34 619 783 15 0 85 0
1 0 50348 1755744 52716 9830160 0 0 22208 0 605 774 14 0 86 0
2 0 50336 1702324 52724 9884184 0 0 22400 6 600 773 16 0 84 0
1 0 50336 1648008 52732 9938304 0 0 22592 6 623 796 14 1 85 0
2 0 50336 1592896 52732 9993144 0 0 23424 36 658 861 15 0 84 0
1 0 50336 1547552 52744 10038416 0 0 19264 66 1045 980 12 0 85 3
2 0 50336 1490988 52744 10095964 0 0 26116 0 676 732 15 1 80 3
1 0 50336 1434456 52760 10151276 0 0 26600 32 697 758 13 1 86 0
1 2 50336 1408144 52768 10177992 0 0 11270 52636 1296 819 13 0 82 5
1 0 50336 1369920 52768 10216120 0 0 15872 3710 702 681 15 0 84 1
1 0 50336 1331664 52776 10254604 0 0 16000 18 501 687 16 0 84 0
1 0 50328 1293004 52776 10293184 0 0 15936 0 504 670 13 0 87 0
1 0 50268 1254560 52784 10331812 0 0 15936 18 512 680 16 0 83 0
1 0 50268 1216276 52784 10370156 0 0 15872 0 514 688 14 0 86 0
1 0 50252 1177600 52784 10408532 0 0 16000 0 502 672 15 0 85 0
1 0 50244 1138936 52796 10447412 0 0 16072 8 512 680 14 0 86 0
0 1 50220 1109028 52796 10476884 0 0 12608 18 423 566 11 0 85 3
0 1 50220 1109912 52804 10476520 0 0 704 10 100 196 1 0 84 14
0 1 50220 1107952 52800 10478324 0 0 1728 0 127 260 2 0 88 10
1 0 50220 1088676 52800 10497236 0 0 8832 36 336 513 7 0 86 6
1 0 50220 1038608 52808 10547152 0 0 20856 146 610 763 15 1 84 0
1 0 50220 985768 52808 10600160 0 0 22336 0 613 774 13 0 86 0
1 0 50220 933764 52824 10652336 0 0 21888 28 601 767 16 0 84 0
1 0 50220 883664 52824 10702216 0 0 20800 0 589 752 14 1 86 0
0 1 50220 879304 52808 10706896 0 0 3584 44682 928 335 4 0 84 12
0 2 50220 877780 52816 10707928 0 0 1408 16 110 188 2 0 84 14
0 1 50220 877156 52816 10709232 0 0 1472 0 112 196 2 0 87 11
0 1 50220 876540 52804 10710044 0 0 1408 20 119 213 1 0 85 14
0 1 50220 876104 52812 10710008 0 0 1024 6 117 234 1 0 89 10
0 1 50220 875736 52812 10710072 0 0 896 0 102 197 1 0 84 15
0 1 50220 875248 52820 10710856 0 0 1280 8 128 229 2 0 85 13
2 1 50220 874628 52820 10711556 0 0 1216 0 110 198 2 0 88 10
1 1 50220 873992 52820 10712288 0 0 1280 0 103 189 3 0 83 14
0 1 50220 875564 52824 10710784 0 0 384 6 113 207 1 0 86 13
0 1 50220 875460 52824 10710448 0 0 1216 36 142 263 2 0 86 12
1 1 50220 873852 52820 10712376 0 0 1856 0 143 255 3 0 85 12
0 1 50220 873752 52828 10712144 0 0 832 6 112 210 2 0 86 12
1 1 50220 873336 52832 10712884 0 0 1280 20 112 207 2 0 86 12
0 1 50220 872588 52840 10713684 0 0 1280 6 119 214 2 0 87 11
0 1 50220 872276 52840 10713944 0 0 1152 3884 181 184 2 0 86 13
0 1 50220 871672 52836 10714584 0 0 1088 4 91 169 1 0 85 13
0 1 50220 871504 52844 10714636 0 0 1088 14 116 200 2 0 86 13
1 1 50220 871040 52852 10715800 0 0 1280 34 118 217 2 0 87 12
0 1 50220 870308 52852 10716292 0 0 1088 0 115 204 1 0 87 12
0 1 50220 869972 52860 10716428 0 0 1216 6 103 190 2 0 85 14
0 1 50220 869492 52864 10716792 0 0 1024 2 98 179 2 0 85 13
0 1 50220 869372 52872 10716676 0 0 960 6 108 204 1 0 88 11
0 1 50220 869076 52872 10717008 0 0 1088 0 107 187 2 0 84 14
0 1 50220 868380 52864 10717668 0 0 1152 0 116 203 1 0 88 11
0 1 50220 867384 52872 10718000 0 0 1026 42 142 277 1 0 87 12
0 1 50220 866332 52872 10719644 0 0 1600 0 118 205 2 0 83 14
1 0 50220 866424 52880 10719784 0 0 1216 6 100 184 1 0 86 12
0 1 50220 865836 52880 10720324 0 0 1216 20 101 197 1 0 87 12
0 1 50220 865524 52880 10720592 0 0 960 0 113 207 1 0 85 14
pgsql-performance by date: