Received: from malur.postgresql.org ([2a02:16a8:dc51::56]) by arkaria.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fErTk-0001bB-1M for pgsql-pkg-debian@arkaria.postgresql.org; Sat, 05 May 2018 07:19:36 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.89) (envelope-from ) id 1fErTg-0001mJ-DM for pgsql-pkg-debian@arkaria.postgresql.org; Sat, 05 May 2018 07:19:32 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA384:256) (Exim 4.89) (envelope-from ) id 1fErTg-0001m9-6A for pgsql-pkg-debian@lists.postgresql.org; Sat, 05 May 2018 07:19:32 +0000 Received: from mail-wr0-x22d.google.com ([2a00:1450:400c:c0c::22d]) by magus.postgresql.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_CBC_SHA1:256) (Exim 4.89) (envelope-from ) id 1fErTa-0001bu-7b for pgsql-pkg-debian@postgresql.org; Sat, 05 May 2018 07:19:29 +0000 Received: by mail-wr0-x22d.google.com with SMTP id o4-v6so23060237wrm.0 for ; Sat, 05 May 2018 00:19:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=subject:to:references:from:message-id:date:user-agent:mime-version :in-reply-to:content-transfer-encoding:content-language; bh=vj57nI5sIJCJ9t7feRhYT/Zrdnu8/L7j6RpiTBEYV6o=; b=DeFzs2DwzXrpdzXPFmsNIx+4EZD6g3Nah3a10nsU2TrKMOI39TE00MAqqrECykvVEX MgS0HXJoj6vpVvkN/k8uUjrbXgUrT/6ssxYhHoYk6PsMKoYqOzUC8R9PUVlVtGf8YlXk aJL6UJDyGIfBwdessq6QyDMCxdUZQK2cdvWUu+hEYjOBgZrl3kJ/rgl0xBEq/r0OX585 ReI9/QUAhCTwzTkH3Y6URGaAPB4PRQp8J2GrPRF0MlcozZA6BP6RIsENXaFeuu+/bMOb siGYTzjKJnZuBtqKJXdiY8xwb9PH/VLQWLSK9VmmzhsfLUG9BYowwvjhb3m860ABZHOQ pdJQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:subject:to:references:from:message-id:date :user-agent:mime-version:in-reply-to:content-transfer-encoding :content-language; bh=vj57nI5sIJCJ9t7feRhYT/Zrdnu8/L7j6RpiTBEYV6o=; b=arryZm8bxI2Yj0GlAKWoWvy3TKCwr913ihVY6Op9fop4fxJmP3CFhpVBgtNd6UKXTo 90BYjAr6/wBjBLTk3/UL4XCwuoCV/gnfsZJvPcYOWgERGHa3so4wd16lP1pocmiqbWqn ssSjk5YsGqjSHx4IDuaf8ilCFay1Ak4loj3nuNmAYSyBb8f/EfuPtGYTs5WpL2ht4AhW UsXIPoEScnPwd9QjdDnjSetjq4XmRj1OLTxkszRDI4hkdU/rs38wLLHOzsHxJErwlY6B 3HBuuM+j6oc1oP1hGot88njECG0y/ilkOXNN/BNkK+xyjnjlzohBJtYk0hEuikzOOjt+ MqJw== X-Gm-Message-State: ALQs6tBrZpMrdgUXGn0X4cXPZy03VsAYAElcL80Jtw6G5cMt8zPrbppE hT2LTZKBc83Sfm4YSaogqo/hq4Fe X-Google-Smtp-Source: AB8JxZpxerPRjmGwBi2ZvgQn3zOiLFP5k2c+3lPDhmoUtDKr9FjjkBcx/ZbW6XHzf2ZLRlygj3oSgA== X-Received: by 2002:adf:a075:: with SMTP id l50-v6mr25715153wrl.227.1525504761029; Sat, 05 May 2018 00:19:21 -0700 (PDT) Received: from [192.168.37.2] (gev44-1-78-228-108-65.fbx.proxad.net. [78.228.108.65]) by smtp.gmail.com with ESMTPSA id g78sm4094701wmc.27.2018.05.05.00.19.19 (version=TLS1_2 cipher=ECDHE-RSA-AES128-GCM-SHA256 bits=128/128); Sat, 05 May 2018 00:19:20 -0700 (PDT) Subject: Re: libuuid performance impact To: Christoph Berg , pgsql-pkg-debian@postgresql.org References: <4f125945-e979-1f9e-a39b-a4bf5a4eca6b@gmail.com> <20180501142455.GA3019@msg.df7cb.de> From: Marc Cousin Message-ID: <17587902-219a-90a2-4818-e3f0c2fdfddf@gmail.com> Date: Sat, 5 May 2018 09:19:19 +0200 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101 Thunderbird/52.7.0 MIME-Version: 1.0 In-Reply-To: <20180501142455.GA3019@msg.df7cb.de> Content-Type: text/plain; charset=utf-8; format=flowed Content-Transfer-Encoding: 8bit Content-Language: en-US List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk First, sorry for the delay, I've been out of office for a few days. On 01/05/2018 16:24, Christoph Berg wrote: > Re: Marc Cousin 2018-04-30 <4f125945-e979-1f9e-a39b-a4bf5a4eca6b@gmail.com> >> Hi >> >> I've been investigating a performance "problem" with uuid generation >> https://jasonaowen.net/blog/2017/Apr/13/benchmarking-uuids-v2/, which I >> couldn't reproduce on my computer while it occurred on my servers. After >> a few tests, it seems to come from uuid-ossp being replaced with >> libuuid… when linking postgresql with uuid-ossp I get the exact same >> performance between uuid_generate_v4 and gen_random_uuid. When linking >> with libuuid, I get the same results as those displayed in the linked >> webpage (I can provide numbers). >> >> Is this an expected difference ? > It also depends which uuid "version" you are generating" Yes, that's right, as the uuid doesn't use the same kind of data source depending on the version. gen_random_uuid produces a V4 uuid. > > =# explain analyze select uuid_generate_v1() from generate_series(1, 1000000); > Function Scan on generate_series (cost=0.00..12.50 rows=1000 width=16) (actual time=129.701..6847.020 rows=1000000 loops=1) > Planning time: 0.071 ms > Execution time: 6921.871 ms > > =# explain analyze select uuid_generate_v4() from generate_series(1, 1000000); > Function Scan on generate_series (cost=0.00..12.50 rows=1000 width=16) (actual time=123.784..5164.752 rows=1000000 loops=1) > Planning time: 0.073 ms > Execution time: 5203.226 ms > > =# explain analyze select gen_random_uuid() from generate_series(1, 1000000); QUERY PLAN > Function Scan on generate_series (cost=0.00..12.50 rows=1000 width=16) (actual time=121.428..1908.190 rows=1000000 loops=1) > Planning time: 0.070 ms > Execution time: 1947.951 ms > > I can't say if this is expected, or if the libuuid performance is just > worse, or if maybe the libuuid UUIDs are generated using stronger > crypto. > > Is that performance difference a problem for you in practise? It's not a big problem for us yet, as on most of our applications, the UUID generation doesn't amount for a big part of the costs. But we are building an audit application, and uuid will probably become non-negligible there. That's where the benchmark comes from :) So there is no emergency for us (we would have rebuilt the package ourselves or used the pgcrypto version if that was the case), but after diagnosing this I thought it was an issue that should be brought to your attention. Regards, Marc