"Mysterious" issues with newly installed 8.3
От | Carlos Moreno |
---|---|
Тема | "Mysterious" issues with newly installed 8.3 |
Дата | |
Msg-id | 48EE98FE.60100@mochima.com обсуждение исходный текст |
Ответы |
Re: "Mysterious" issues with newly installed 8.3
("Scott Carey" <scott@richrelevance.com>)
Re: "Mysterious" issues with newly installed 8.3 ("Scott Marlowe" <scott.marlowe@gmail.com>) |
Список | pgsql-performance |
Ok, I know that such an open and vague question like this one is... well, open and vague... But still. The short story: Just finished an 8.3.4 installation on a new machine, to replace an existing one; the new machine is superior (i.e., higher performance) in virtually every way --- twice as much memory, faster processor, faster drives, etc. I made an exact copy of the existing database on the new machine, and the exact same queries run on both reveal that the old machine beats the new one by a factor of close to 2 !!!! (i.e., the same queries run close to twice as fast on the old machine!!!) To make things worse: the old machine is in operation, under normal workload (and right now the system may be around peak time), and the new machine is there sitting doing nothing; just one user logged in using psql to run the queries --- *no-one and nothing* is connecting to the new server. So... What's going on??? The details: CPU: New: Opteron DC 1218HE (1MB cache per core) @2.6GHz Old: Athlon64 X2 (512K cache per core) @2.2GHz RAM: New: 4GB Old: 2GB HD: Doesn't matter the capacity, but I have every reason to believe the new one is faster --- hdparm reports 105MB/sec transfer rate; the measurement for the old server is meaningless, since it is in operation (i.e., there is actual database activity), so it measures between 50MB/sec and 70MB/sec. Given its age, I would estimate 70 to 80 MB/sec OS: New: CentOS 5.2 (gcc 4.1.2) Old: FC6 (gcc 4.1.2) PG: New: 8.3.4 installed from source Old: 8.2.4 installed from source Presumably relevant configuration parameters --- shared_buffers was set to 250MB on the old one; I set it to 500MB on the new one (kinda makes sense, no? 1/8 of the physical memory in both cases). I set max_fsm_pages a little bit higher on the new one (409600 instead of 307200 on the old one). The rest is pretty much identical (except for the autovacuum --- I left the defaults in the new one) The old machine is vacuum-analyzed once a day (around 4AM); on the new one, I ran a vacuumdb -z -f after populating it. Some interesting outputs: explain analyze select count(*) from users; New: Aggregate (cost=8507.11..8507.12 rows=1 width=0) (actual time=867.582..867.584 rows=1 loops=1) -> Seq Scan on users (cost=0.00..7964.49 rows=217049 width=0) (actual time=0.016..450.560 rows=217049 loops=1) Total runtime: 867.744 ms Old: Aggregate (cost=17171.22..17171.22 rows=1 width=0) (actual time=559.475..559.476 rows=1 loops=1) -> Seq Scan on users (cost=0.00..16628.57 rows=217057 width=0) (actual time=0.009..303.026 rows=217107 loops=1) Total runtime: 559.536 ms Running the same command again several times practically does not change anything. explain analyze select count(*) from users where username like 'A%'; New: Aggregate (cost=6361.28..6361.29 rows=1 width=0) (actual time=87.528..87.530 rows=1 loops=1) -> Bitmap Heap Scan on users (cost=351.63..6325.33 rows=14376 width=0) (actual time=6.444..53.426 rows=17739 loops=1) Filter: ((username)::text ~~ 'a%'::text) -> Bitmap Index Scan on c_username_unique (cost=0.00..348.04 rows=14376 width=0) (actual time=5.383..5.383 rows=17739 loops=1) Index Cond: (((username)::text >= 'a'::text) AND ((username)::text < 'b'::text)) Total runtime: 87.638 ms Old: Aggregate (cost=13188.91..13188.92 rows=1 width=0) (actual time=61.743..61.745 rows=1 loops=1) -> Bitmap Heap Scan on users (cost=392.07..13157.75 rows=12466 width=0) (actual time=7.433..40.847 rows=17747 loops=1) Filter: ((username)::text ~~ 'a%'::text) -> Bitmap Index Scan on c_username_unique (cost=0.00..388.96 rows=12466 width=0) (actual time=5.652..5.652 rows=17861 loops=1) Index Cond: (((username)::text >= 'a'::character varying) AND ((username)::text < 'b'::character varying)) Total runtime: 61.824 ms Any ideas?
В списке pgsql-performance по дате отправления:
Предыдущее
От: Dimitri FontaineДата:
Сообщение: Re: low performance on functions returning setof record