dblink performance regression
От | Joe Conway |
---|---|
Тема | dblink performance regression |
Дата | |
Msg-id | 52A1367D.7080505@joeconway.com обсуждение исходный текст |
Ответы |
Re: dblink performance regression
(Tom Lane <tgl@sss.pgh.pa.us>)
|
Список | pgsql-hackers |
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I was recently approached by someone with a dblink performance regression, going back to somewhere between 8.3 (good) and 8.4 (bad). They were measuring ~8% degradation in dblink connection speed. I was able to replicate on my own hardware with the following script: 8<------------------------------ create or replace function test_dblink(loops int) returns text as $$ declare i int; ret int; begin for i in 1..loops loop IF i % 100 = 0 THEN RAISE NOTICE 'connect attempt %', i; END IF; PERFORM dblink_connect('me','dbname=postgres host=w.x.y.z'); SELECT x into ret FROM dblink('me','SELECT 1', true) as x(x int); PERFORM dblink_disconnect('me'); end loop; return 'done'; end; $$ language plpgsql; \timing select test_dblink(1000); 8<------------------------------ In my testing I saw a very consistent 4-5% degradation. I then did a git bisect and traced the performance degradation to this commit: - ------------------ http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=e5de601267d98c5d60df6de8d436685c7105d149 committer Joe Conway <mail@joeconway.com> Tue, 9 Jun 2009 16:35:36 +0000 (16:35 +0000) commit e5de601267d98c5d60df6de8d436685c7105d149 "Default client encoding to server encoding for dblink connections. Addresses issue raised by Ruzsinszky Attila and confirmed by others." - ------------------ with this diff: - ------------------ http://git.postgresql.org/gitweb/?p=postgresql.git;a=blobdiff;f=contrib/dblink/dblink.c;h=e709ae9cc3b9f7177eaae08d11540a8590e7dc63;hp=283b4d0b25e9924dfc2d16075e7cfc1277ce5956;hb=e5de601267d98c5d60df6de8d436685c7105d149;hpb=adaf60131f81394939b5b55f74130343d520cd2d - ------------------ Apparently setting client encoding is an expensive operation. My proposed fix (diff attached) is to simply skip setting client encoding in the case where it already matches server side encoding. With the patch applied, and client encoding matching server (both UTF8), the performance regression is completely gone. Possibly additional work could go into determining if PQsetClientEncoding() can be made more efficient, but I believe it still makes sense in the case of dblink to do nothing when nothing is required. If there are no objections I'll commit this to all branches this weekend. Thanks, Joe - -- Joe Conway credativ LLC: http://www.credativ.us Linux, PostgreSQL, and general Open Source Training, Service, Consulting, & 24x7 Support -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.12 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/ iQIcBAEBAgAGBQJSoTZ9AAoJEDfy90M199hlVVsP/iG6XbcXYR2G2TC9I6FIaYzQ c9503QcewPW+tY/0TAhzOYbXfdkkmRDf+4d9HYiCnYANhJW86dxmqtFKMyASbT74 BYjrZUHhafhSobDZBtM0t/6J1CMhLB/Gy66dziqkw0/hx0NeSuQqkluDazcIh/83 34hCRB2LNE7CXx88HvIc0hd6ACk7Ecrw7W6xyoznhmajHnq2G4Mp1AKXSkOTsYcJ GkKy1G+u42M6pcBaNp6hnPuRj3HGECz6NdeuzWvb2IKLL6cY7C0fvCccqvkhcUpl SPfIzlzxxtavzkkkxjOQUWrUFVWulZjeTFsQz3AWPQZoLtY+YDJ513R16Jh8s4RI XoUWXRQVcMS2kWAIck6Nt/2zpIN9Rr4MlUgqh4mXlAZ59ErigVAqbhg9SAE0N4/h Fa31OlTousTT4Wph34n/2nZK3uF46SiOHAoFipjRtGavbFW4lXKFryz6AEJ6e1Xy fNpfNrXbKFkmRYdcafjZ7k6+NW70iCcH98A78wgyRLy59/b/M3u/K9TH5YN5tKLH O+fK+S+s6eA9+omeu2hLl+6CkwDvEfBvzKkLu+9+sdV0s0b7VDt2vnMx/hg6E7EG wCKB5X451lUz2MhXqX8vKiSGLk2ShmV8o8u0ovh4kFRV4YmjPK7LyDenIdmsoYuQ NHiORCMc3V9USObJJ8so =E/Hx -----END PGP SIGNATURE-----
Вложения
В списке pgsql-hackers по дате отправления: