Re: PG 9.1 much slower than 8.2 ?
От | Marc Richter |
---|---|
Тема | Re: PG 9.1 much slower than 8.2 ? |
Дата | |
Msg-id | 540827B5.70807@marc-richter.info обсуждение исходный текст |
Ответ на | Re: PG 9.1 much slower than 8.2 ? (Merlin Moncure <mmoncure@gmail.com>) |
Список | pgsql-novice |
Hi everyone, as Merlin suggested, I've spend the last days with bringing PostgreSQL versions 8.2.5 and 9.3.5 on the same hardware and re-ran my tests with this. The Hardware-Specs are already told in the initial mail, quoted below (under "PG Version 9.1.13"). Both are using the SSD-Raid for storing their data. Version 8.2.5 was compiled by hand (using "./configure --prefix=/usr/local --with-openssl --enable-thread-safety"), version 9.3.5 was taken from PGDG Repo (https://wiki.postgresql.org/wiki/Apt). PostgreSQL 8.2.5 binds to Port 5433, 9.3.5 binds to the default of 5432. I've used a dump of one of our production sites, which results in ~12 GB of storage used in PostgreSQL Data Directory. Here's where the first sign of low speed of 9.3.5 shows up: These are the lines I used to insert my Dump into both Postgres DBs, using the "matching" version of psql of the two versions. I ran these one after another to not interfere each other in performance. This is dedicated, yet unproductive labor-hardware, so there is nothing else that may interfere: sync ; sleep 10 ; time zcat /usr/src/db.gz | \ /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 [...] real 37m57.023s user 7m9.683s sys 1m15.613s sync ; sleep 10 ; time zcat /usr/src/db.gz | \ /usr/bin/psql -U postgres -h 127.0.0.1 -p 5432 [...] real 80m57.667s user 89m45.597s sys 1m57.479s So, inserting the Dump takes more than twice the time in 9.3 than it takes to do the same in 8.2 . After this, I issued "SELECT *" on a table, containing 360881 rows in both versions and took the time for this. I did this with the following "for", three times each: for x in 1 2 3 ; do sync sleep 10 time /usr/local/bin/psql -U postgres -h 127.0.0.1 -p 5433 -c \ "SELECT * FROM billing_events;" db | wc -l done This is the "for" for 8.2; in 9.3 I used Port 5432 instead of 5433 and /usr/bin/psql instead of /usr/local/bin/psql. These are the results for 8.2: 360881 real 0m6.044s user 0m4.428s sys 0m0.692s 360881 real 0m5.916s user 0m4.476s sys 0m0.596s 360881 real 0m6.023s user 0m4.520s sys 0m0.656s These are the results for 9.3: 360881 real 0m12.885s user 0m9.741s sys 0m0.652s 360881 real 0m12.679s user 0m9.613s sys 0m0.724s 360881 real 0m12.717s user 0m9.749s sys 0m0.616s As you can see: PostgreSQL 9.3 takes round about the double amount of time to return these results than 8.2 needs to; quite the same dimensions like the initial Insert does. The config is like the following: PG 8.2.5: listen_addresses = '*' port = 5433 max_connections = 512 shared_buffers = 400MB temp_buffers = 8MB work_mem = 256MB maintenance_work_mem = 256MB max_fsm_pages = 204800 checkpoint_segments = 16 effective_cache_size = 6GB log_destination = 'stderr' redirect_stderr = on log_directory = 'pg_log' log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' log_rotation_age = 1d log_rotation_size = 100MB syslog_ident = 'postgres' client_min_messages = warning log_min_messages = notice log_min_error_statement = info log_line_prefix = '%m %r %u ' log_statement = 'mod' stats_start_collector = on stats_row_level = on autovacuum = on autovacuum_naptime = 1min autovacuum_vacuum_threshold = 500 autovacuum_analyze_threshold = 250 autovacuum_vacuum_scale_factor = 0.2 autovacuum_analyze_scale_factor = 0.1 autovacuum_freeze_max_age = 200000000 autovacuum_vacuum_cost_delay = -1 autovacuum_vacuum_cost_limit = -1 datestyle = 'iso, dmy' lc_messages = 'de_DE.UTF-8' lc_monetary = 'de_DE.UTF-8' lc_numeric = 'de_DE.UTF-8' lc_time = 'de_DE.UTF-8' regex_flavor = advanced PG 9.3.5 data_directory = '/var/lib/postgresql/9.3/main' hba_file = '/etc/postgresql/9.3/main/pg_hba.conf' ident_file = '/etc/postgresql/9.3/main/pg_ident.conf' external_pid_file = '/var/run/postgresql/9.3-main.pid' listen_addresses = '*' port = 5432 max_connections = 512 unix_socket_directories = '/var/run/postgresql' ssl = true ssl_cert_file = '/etc/ssl/certs/ssl-cert-snakeoil.pem' ssl_key_file = '/etc/ssl/private/ssl-cert-snakeoil.key' shared_buffers = 4GB temp_buffers = 8MB work_mem = 256MB maintenance_work_mem = 1GB checkpoint_segments = 16 effective_cache_size = 24GB log_destination = 'syslog' syslog_facility = 'LOCAL0' syslog_ident = 'postgres' client_min_messages = warning log_min_messages = notice log_min_error_statement = info log_line_prefix = '%m %r %u ' log_statement = 'mod' log_timezone = 'localtime' datestyle = 'iso, dmy' timezone = 'localtime' lc_messages = 'de_DE.UTF-8' lc_monetary = 'de_DE.UTF-8' lc_numeric = 'de_DE.UTF-8' lc_time = 'de_DE.UTF-8' default_text_search_config = 'pg_catalog.german' I hope I measured valid things and delivered all information properly for everyone to comprehend. Can anybody explain what else to try or why recent Postgres is slower than quite exactly 7 years old 8.2.5 Release (Release Date: 2007-09-17) this noticeable? Best regards, Marc Am 26.08.2014 21:57, schrieb Merlin Moncure: > On Tue, Aug 26, 2014 at 10:10 AM, Marc Richter <mail@marc-richter.info> wrote: >> Hi everyone, >> >> I'm in the process of migrating a really old PostgreSQL DB from 8.2.5 to a >> (more) recent PostgreSQL 9.1. I know that 9.1 is somewhat old already, too, >> but since we are stuck to Debian stable and don't want to start using >> self-compiled software and this is the version which is included in Debian >> stable currently, this is the version of choice. >> >> I've managed to create a dump of the database from 8.2.5 and inserting it >> into 9.1.13 successfully, thanks to the help of this list ("Upgrading from >> PG 8.2.5 to 9.1.13" - Thread). So I gave the result to another department to >> make their compatibility- and overall-tests on it. >> They did not come up with incompatibilities, but with a performance-related >> issue: >> >> When we do a "SELECT *" on a table with 355332 rows in it without using an >> index or limit or such, this takes round about 10.5 seconds on the >> PostgreSQL 8.2.5 host and 12.2 seconds on the PostgreSQL 9.1.13 host. Both >> servers are using the same database. >> >> I know, this seems like near to nothing, but the hardware of the 9.1.13 host >> is way more recent than the one of the 8.2.5 PostgreSQL, too: >> >> PG Version 8.2.5: >> * CPU: Intel Xeon CPU E5506 (4-Core 2,13 GHz) >> * RAM: 4 GB (2x2GB) DDR3 1066 >> * Storage: >> System, SWAP und PostgreSQL Data: RAID1 - ST3500320NS >> >> PG Version 9.1.13: >> * CPU: AMD Opteron 4334 (6 Core 3,1 GHz) >> * RAM: 32 GB (4x8GB) DDR3 1600 >> * Storage: >> System + SWAP: RAID1 - ST1000DM003-1CH1 >> PostgreSQL Data: RAID1 - SD6SB1M2 (SSD) >> >> I know that PostgreSQL has little chance to optimize a query like this, when >> no logic and no index is used to lookup a result, but taking this into >> account, we would have expected that issuing the same, bad query on old >> hardware and newer hardware once, should deliver results on the better/newer >> hardware a lot faster than on the older one. Instead, we experience the >> opposite. > > very possibly you are measuring hardware differences or something else > not related to the database itself. do isolate that, fire up 8.2.5 on > the same server and run queries side by side. Also on both sides be > sure to run the test several times (say, 10) and take the median > speed. Better yet, use pgbench; feel free to supplement the stock > tpc-b with custom test of your choosing (even if select * from table). > > merlin > >
В списке pgsql-novice по дате отправления:
Предыдущее
От: "Luiz Matsumura"Дата:
Сообщение: Re: Join three tables and specify criteria... I know this should be easy!