From mboxrd@z Thu Jan 1 00:00:00 1970 Return-Path: Received: from mp0 ([2001:41d0:8:6d80::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by ms0.migadu.com with LMTPS id aODMJvmyjmAv/gAAgWs5BA (envelope-from ) for ; Sun, 02 May 2021 16:11:05 +0200 Received: from aspmx1.migadu.com ([2001:41d0:8:6d80::]) (using TLSv1.3 with cipher TLS_AES_256_GCM_SHA384 (256/256 bits)) by mp0 with LMTPS id OEaCIvmyjmDKMAAA1q6Kng (envelope-from ) for ; Sun, 02 May 2021 14:11:05 +0000 Received: from lists.gnu.org (lists.gnu.org [209.51.188.17]) (using TLSv1.2 with cipher ECDHE-RSA-AES256-GCM-SHA384 (256/256 bits)) (No client certificate requested) by aspmx1.migadu.com (Postfix) with ESMTPS id 407902FE15 for ; Sun, 2 May 2021 16:11:05 +0200 (CEST) Received: from localhost ([::1]:59892 helo=lists1p.gnu.org) by lists.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1ldCoK-0001qL-FR for larch@yhetil.org; Sun, 02 May 2021 10:11:04 -0400 Received: from eggs.gnu.org ([2001:470:142:3::10]:46992) by lists.gnu.org with esmtps (TLS1.2:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.90_1) (envelope-from ) id 1ldCoB-0001qD-7F for guix-devel@gnu.org; Sun, 02 May 2021 10:10:55 -0400 Received: from mira.cbaines.net ([212.71.252.8]:38372) by eggs.gnu.org with esmtp (Exim 4.90_1) (envelope-from ) id 1ldCo9-0001Bz-5G for guix-devel@gnu.org; Sun, 02 May 2021 10:10:54 -0400 Received: from localhost (unknown [IPv6:2a02:8010:68c1:0:8ac0:b4c7:f5c8:7caa]) by mira.cbaines.net (Postfix) with ESMTPSA id C8D0327BC7E; Sun, 2 May 2021 15:10:51 +0100 (BST) Received: from capella (localhost [127.0.0.1]) by localhost (OpenSMTPD) with ESMTP id cc1be947; Sun, 2 May 2021 14:10:51 +0000 (UTC) References: <87a6pjfus6.fsf@cbaines.net> User-agent: mu4e 1.4.15; emacs 27.1 From: Christopher Baines To: Canan Talayhan Subject: Re: [Outreachy] [Guix Data Service]: Identify the slow parts of process In-reply-to: Date: Sun, 02 May 2021 15:10:49 +0100 Message-ID: <87tunlcjkm.fsf@cbaines.net> MIME-Version: 1.0 Content-Type: multipart/signed; boundary="=-=-="; micalg=pgp-sha512; protocol="application/pgp-signature" Received-SPF: pass client-ip=212.71.252.8; envelope-from=mail@cbaines.net; helo=mira.cbaines.net X-Spam_score_int: -18 X-Spam_score: -1.9 X-Spam_bar: - X-Spam_report: (-1.9 / 5.0 requ) BAYES_00=-1.9, SPF_HELO_PASS=-0.001, SPF_PASS=-0.001 autolearn=ham autolearn_force=no X-Spam_action: no action X-BeenThere: guix-devel@gnu.org X-Mailman-Version: 2.1.23 Precedence: list List-Id: "Development of GNU Guix and the GNU System distribution." List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , Cc: guix-devel@gnu.org Errors-To: guix-devel-bounces+larch=yhetil.org@gnu.org Sender: "Guix-devel" X-Migadu-Flow: FLOW_IN ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=yhetil.org; s=key1; t=1619964665; h=from:from:sender:sender:reply-to:subject:subject:date:date: message-id:message-id:to:to:cc:cc:mime-version:mime-version: content-type:content-type:in-reply-to:in-reply-to: references:references:list-id:list-help:list-unsubscribe: list-subscribe:list-post; bh=T/711Vy2LQeYj3OoVRT00hnd5GHeQqTNigZraqbXOvE=; b=tWqsmU6gjNB+d2eFBsuNdno6RIB2SYmPauxam57ZZpZTrXjWUOZMDh8AsUMazD2RH0Gm0s Ie687n+r62KP++kUn5gp2T+VEtru+KLxo+Lnm6muhreT0j/yZAOcgkFGom/YY/tWzPXPvw I2yjEuha8SRvHP3o0IRVBScmAvI8miJf+SQzqUGfxYT6T5dOpAzi8eT8WST9zhF1lWBg+6 EF3XINASUNLQdQuBmLixgnlecYdpKzYkqdYPZANfYDOKKzXpXxW3KQ9Etbjd+LkoQTlV9K xmfqWRXmJWV4ZLZ4TBOGFrG+GwYpLSYsRPWrVcH1EqBbYZxIoEOIMfFC99/nYQ== ARC-Seal: i=1; s=key1; d=yhetil.org; t=1619964665; a=rsa-sha256; cv=none; b=f8Bmj70lHMYILT2kRHz+M+NkxUrvINy1gj+4xvFbHqcqweiR/BUGHxDA5qjeaeTt8epTPi 5krzfKT69IMIafohMY5fc8zjBf2zaa770xN6P5PdsvfmvJu0XQRlxjCyhanIAiDjgx8FNr LDF3eR8ATK5OmXmbNTmUtPyBLf/SRz2ogj81AY27NsfWQtBM0u4IUx9RA+S55W23pOYh6e 3DpxS8MIX4PuGuHvnFvmmPECADpdlSWBKQcacsIzmdSClNFcmsoGHMezNxNEUX1j6ROYBo ESILvWWbDPK1dAtxaSh6vEXAMWgkfAysZp6VZ94rfSC6tQyWkW+WWSAp5Yvk4Q== ARC-Authentication-Results: i=1; aspmx1.migadu.com; dkim=none; spf=pass (aspmx1.migadu.com: domain of guix-devel-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=guix-devel-bounces@gnu.org X-Migadu-Spam-Score: -3.06 Authentication-Results: aspmx1.migadu.com; dkim=none; dmarc=none; spf=pass (aspmx1.migadu.com: domain of guix-devel-bounces@gnu.org designates 209.51.188.17 as permitted sender) smtp.mailfrom=guix-devel-bounces@gnu.org X-Migadu-Queue-Id: 407902FE15 X-Spam-Score: -3.06 X-Migadu-Scanner: scn0.migadu.com X-TUID: kXaHUgF1pX9S --=-=-= Content-Type: text/plain Canan Talayhan writes: >>>From this I'm guessing the temp_package_metadata table has only one >>row. My understanding is that this table would normally have as many >>rows as packages in the revision of Guix being processed. It might not >>be possible to reproduce the slowness of the query without more rows. > > I've inserted one row just as an example. As you've already said, > the temp_package_metadata table should have as many rows > as package_metadata. "as many rows as packages in the revision of Guix being processed" is only going to be similar to the number of rows in the package_metadata table if there's only been one or a number of similar revisions processed, since the package_metadata table has entires covering all processed revisions. > After populated the temp_package_metadata with 500 rows of > package_metadata, the query takes a long time as we expected. Great, being able to reproduce the problem in a way that makes trying things out easy is a good step forward. I'd pull on this thread further, now you've got a slow query, how can you make it faster? > I'm using Flame Graph to visualize the slow paths on the revision part. > At first, I choose the slow one that I already know. > However, I can't successfully trigger the slow query following the below step: > > * Run the **guix-data-service-process-job** under guix-data-service/scripts > folder as standalone providing an existing revision on my local db. > > Am I on the right path for adding new jobs log to my local db? > > In addition, I've successfully generated simple Flame Graph using Linux perf. > It visualizes only the data that was captured while I'm browsing on the > Guix Data Service Page. Please find the svg file attached. If this relates to the query involving the temp_package_metadata table, I'd focus on analyzing the slow query you're able to execute manually, rather than processing an entire revision. If you do however want to add more unprocessed jobs to your local database, then you can use the guix-data-service-process-branch-updated-mbox script to do this. It takes one argument, an mbox file (file containing a bunch of emails). You can download files by month from here [1], and you'll probably want the month or next month on from the latest revision your local database knows about. 1: https://lists.gnu.org/archive/mbox/guix-commits/ --=-=-= Content-Type: application/pgp-signature; name="signature.asc" -----BEGIN PGP SIGNATURE----- iQKlBAEBCgCPFiEEPonu50WOcg2XVOCyXiijOwuE9XcFAmCOsulfFIAAAAAALgAo aXNzdWVyLWZwckBub3RhdGlvbnMub3BlbnBncC5maWZ0aGhvcnNlbWFuLm5ldDNF ODlFRUU3NDU4RTcyMEQ5NzU0RTBCMjVFMjhBMzNCMEI4NEY1NzcRHG1haWxAY2Jh aW5lcy5uZXQACgkQXiijOwuE9XegmQ//f9bQgbzADmDEmIVI5OgrTsXZjr69r8Dm by8oyIDxhF9gaydTzlHttnYO2J2ShqZYSTb+4aVceYLp71dQ6b59T5fWyFe3fXJQ EPdyU29VQJJdqUoFL1J5sD9QQL65sn2GpkfQvGF2VfCo0pSH/Lv9K+qqDjMPzGJR K15PepEpE6NFRKcq9mPLH4Fk+S9FGNACjWEEJupqiI5EIPxtLsTpqHGkuLhlkLH+ iPG8+tEWK0Uw7aqYM2znYOus/xdz55nxtvqSlj8jyDH+RriCBkQbVtrsA6NRMktk m+EiKI17LTKxEy0AERmacnL2BU2PBRjiIRgfS6e7YwqfzgSSEX20kf85HZvPpDXM 9WvMtKNPSJZwuAsZ+RKHg5FbMNwNpF31DVR3/H/yEEPAYh7TgpIXEdSun9f8YbCX caMq9VQ0mwMGLchjjeqiSxIumo5usoFJ7gpqDvTuDQt+bZWmua5pDFQ+ye6rZcFl C8LxulUEbnR9wXfhlHn7pOZzCTEU+aFt2fv19MufCXudt46GMKrZpiGA/oNQxhrI +ypjt4LA1kyXGETJAUG2KIkimRQJWkZIO9p2E8dgduq6ertA4DS1tPiukZ5M7fJF 0b79kdq1JCTEzWp3pDXb3SUaE8qRfOjTXWj9WI7p5ZRdaSE9nLprLMoH8Nb90UQE 9B28+jjP0fM= =vNL/ -----END PGP SIGNATURE----- --=-=-=--