From pgsql-hackers-owner+M5481@hub.org Mon Jul 31 02:52:11 2000 Return-Path: Received: from hub.org (root@hub.org [216.126.84.1]) by fw.wintelcom.net (8.10.0/8.10.0) with ESMTP id e6V9qBx09344 for ; Mon, 31 Jul 2000 02:52:11 -0700 (PDT) Received: from hub.org (majordom@localhost [127.0.0.1]) by hub.org (8.10.1/8.10.1) with SMTP id e6V9pWB45192; Mon, 31 Jul 2000 05:51:37 -0400 (EDT) Received: from ara.zf.jcu.cz (zakkr@ara.zf.jcu.cz [160.217.161.4]) by hub.org (8.10.1/8.10.1) with ESMTP id e6V9nuB44621 for ; Mon, 31 Jul 2000 05:49:56 -0400 (EDT ) Received: from localhost (zakkr@localhost) by ara.zf.jcu.cz (8.9.3/8.9.3/Debian/GNU) with SMTP id LAA12598 for ; Mon, 31 Jul 2000 11:49:49 +0200 Date: Mon, 31 Jul 2000 11:49:49 +0200 (CEST) From: Karel Zak To: pgsql-hackers Subject: quiet? Re: [HACKERS] The query cache - first snapshot (long) In-Reply-To: Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII X-Mailing-List: pgsql-hackers@postgresql.org Precedence: bulk Sender: pgsql-hackers-owner@hub.org Status: RO X-Status: A Content-Length: 8699 Lines: 245 Still *quiet* for this theme? I output it two weeks ago and I haven't still some reaction. I can stop work on this if it is not wanted and not interesting... Karel On Wed, 19 Jul 2000, Karel Zak wrote: > The Query Cache and new SPI description > ======================================= > > Note: cache is based on new memory design. > > Implementation > ~~~~~~~~~~~~~~ > The qCache allows to save queryTree and queryPlan. Available are two space > for data caching. > > LOCAL - data are cached in backend non-shared memory and data aren't > available in other backends. > > SHARE - data are cached in backend shared memory and data are > visible in all backends. > > Because size of share memory pool is limited and it's set during > postmaster start, the qCache must remove all old planns if pool is > full. You can mark each entry as "REMOVEABLE" or "NOTREMOVEABLE". > > The removeable entry is removed if pool is full and entry is last > in list that keep track usage of entry. > > A not-removeable entry must be removed via qCache_Remove() or > the other routines. The qCache not remove this entry itself. > > All records in the qCache are cached in the hash table under some key. The > qCache knows two alternate of key --- "KEY_STRING" and "KEY_BINARY". A > key must be always less or equal "QCACHE_HASH_KEYSIZE" (128b) > > The qCache API not allows to access to shared memory, all cached planns > that API returns are copy to CurrentMemoryContext or to defined context. > All (qCache_ ) routines lock shmem itself (exception is > qCache_RemoveOldest_ShareRemoveAble()). > > - for locking is used spin lock. > > Memory management > ~~~~~~~~~~~~~~~~~ > The qCache use for qCache's shared pool organized via memory contexts > independent on standard aset/mcxt, but use compatible API --- it allows > to use standard palloc() (it is very needful for basic plan-tree operations , > an example for copyObject()). The qCache memory management is very simular > to current aset.c code. It is chunked blocks too, but the block is smaller > - 1024b. > > The number of blocks is available set in postmaster 'argv' via option > '-Z'. > > For planns storing is used separate MemoryContext for each plan, it > is good idea (Hiroshi's ?), bucause create new context is simple and > inexpensive and allows easy destroy (free) cached plan. This method is > used in my SPI overhaul instead TopMemoryContext feeding. > > Postmaster > ~~~~~~~~~~ > The query cache memory is init during potmaster startup. The size of > query cache pool is set via '-Z ' switch --- default > is 100 blocks where 1 block = 1024b, it is sufficient for 20-30 cached > planns. One query needs somewhere 3-10 blocks, for example query like > > PREPARE sel AS SELECT * FROM pg_class; > > needs 10Kb, because table pg_class has very much columns. > -- > > Note: for development I add SQL function: "SELECT qcache_state();", > this routine show usage of qCache. > > SPI > ~~~ > I a little overwrite SPI save plan method and remove TopMemoryContext > "feeding" (already discussed). > > Standard SPI: > > SPI_saveplan() - save each plan to separate standard memory context. > > SPI_freeplan() - free plan. > > By key SPI: > > It is SPI interface for query cache and allows save planns to SHARED > or LOCAL cache 'by' arbitrary key (string or binary). Routines: > > SPI_saveplan_bykey() - save plan to query cache > > SPI_freeplan_bykey() - remove plan from query cache > > SPI_fetchplan_bykey() - fetch plan saved in query cache > > SPI_execp_bykey() - execute (via SPI) plan saved in query > cache > > - now, users can write functions that save planns to shared memory > and planns are visible in all backend and are persistent arcoss > connection. > > Example: > ~~~~~~~ > /* ---------- > * Save/exec query from shared cache via string key > * ---------- > */ > int keySize = 0; > flag = SPI_BYKEY_SHARE | SPI_BYKEY_STRING; > char *key = "my unique key"; > > res = SPI_execp_bykey(values, nulls, tcount, key, flag, keySize); > > if (res == SPI_ERROR_PLANNOTFOUND) > { > /* --- not plan in cache - must create it --- */ > > void *plan; > > plan = SPI_prepare(querystr, valnum, valtypes); > SPI_saveplan_bykey(plan, key, keySize, flag); > > res = SPI_execute(plan, values, Nulls, tcount); > } > > elog(NOTICE, "Processed: %d", SPI_processed); > > > PREPARE/EXECUTE > ~~~~~~~~~~~~~~~ > * Syntax: > > PREPARE AS > [ USING type, ... typeN ] > [ NOSHARE | SHARE | GLOBAL ] > > EXECUTE > [ INTO [ TEMPORARY | TEMP ] [ TABLE ] new_table ] > [ USING val, ... valN ] > [ NOSHARE | SHARE | GLOBAL ] > > DEALLOCATE PREPARE > [ [ NOSHARE | SHARE | GLOBAL ]] > [ ALL | ALL INTERNAL ] > > > I know that it is a little out of SQL92... (use CREATE/DROP PLAN instead > this?) --- what mean SQL standard guru? > > * Where: > > NOSHARE --- cached in local backend query cache - not accessable > from the others backends and not is persisten a across > conection. > > SHARE --- cached in shared query cache and accessable from > all backends which work over same database. > > GLOBAL --- cached in shared query cache and accessable from > all backends and all databases. > > - default is 'SHARE' > > Deallocate: > > ALL --- deallocate all users's plans > > ALL INTERNAL --- deallocate all internal plans, like planns > cached via SPI. It is needful if user > alter/drop table ...etc. > > * Parameters: > > "USING" part in the prepare statement is for datetype setting for > paremeters in the query. For example: > > PREPARE sel AS SELECT * FROM pg_class WHERE relname ~~ $1 USING text; > > EXECUTE sel USING 'pg%'; > > > * Limitation: > > - prepare/execute allow use full statement of SELECT/INSERT/DELETE/ > UPDATE. > - possible is use union, subselects, limit, ofset, select-into > > > Performance: > ~~~~~~~~~~~ > * the SPI > > - I for my tests a little change RI triggers to use SPI by_key API > and save planns to shared qCache instead to internal RI hash table. > > The RI use very simple (for parsing) queries and qCache interest is > not visible. It's better if backend very often startup and RI check > always same tables. In this situation speed go up --- 10-12%. > (This snapshot not include this RI change.) > > But all depend on how much complicate for parser is query in > trigger. > > * PREPARE/EXECUTE > > - For tests I use query that not use some table (the executor is > in boredom state), but is difficult for the parser. An example: > > SELECT 'a text ' || (10*10+(100^2))::text || ' next text ' || cast > (date_part('year', timestamp 'now') AS text ); > > - (10000 * this query): > > standard select: 54 sec > via prepare/execute: 4 sec (93% better) > > IMHO it is nod bad. > > - For standard query like: > > SELECT u.usename, r.relname FROM pg_class r, pg_user u WHERE > r.relowner = u.usesysid; > > it is with PREPARE/EXECUTE 10-20% faster. > > > I will *very glad* if someone try and test patch; some discussion is wanted > too. > > Thanks. > > Karel > > PS. Excuse me, my English is poor and this text is long --- it is not good > combination... > >