performance modality in 7.1 for large text attributes?
От | Paul A Vixie |
---|---|
Тема | performance modality in 7.1 for large text attributes? |
Дата | |
Msg-id | 200012190455.UAA30442@redpaul.mfnx.net обсуждение исходный текст |
Ответы |
Re: performance modality in 7.1 for large text attributes?
(Alex Pilosov <alex@pilosoft.com>)
Re: performance modality in 7.1 for large text attributes? (Bruce Momjian <pgman@candle.pha.pa.us>) Re: performance modality in 7.1 for large text attributes? (Tom Lane <tgl@sss.pgh.pa.us>) |
Список | pgsql-hackers |
(plz cc me on your replies, i'm not on pgsql-hackers for some reason.) http://www.vix.com/~vixie/results-psql.png shows a gnuplot of the wall time of 70K executions of "pgcat" (shown below) using a CIDR key and TEXT value. (this is for storing the MAPS RSS, which we presently have in flat files.) i've benchmarked this against a flat directory with IP addresses as filenames, and against a deep directory with squid/netnews style hashing (127/0/0/1.txt) and while it's way more predictable than either of those, there's nothing in my test framework which explains the 1.5s mode shown in the above *.png file. anybody know what i could be doing wrong? (i'm also wondering why SELECT takes ~250ms whereas INSERT takes ~70ms... seems counterintuitive, unless TOAST is doing a LOT better than i think.) furthermore, are there any plans to offer a better libpq interface to INSERT? the things i'm doing now to quote the text, and the extra copy i'm maintaining, are painful. arbitrary-sized "text" attributes are a huge boon -- we would never have considered using postgres for MAPS RSS (or RBL) with "large objects". (kudos to all who were involved, with both WAL and TOAST!) here's the test jig -- please don't redistribute it yet since there's no man page and i want to try binary cursors and other things to try to speed it up or clean it up or both. but if someone can look at my code (which i'm running against the 7.1 bits at the head of the pgsql cvs tree) and at the *.png file and help me enumerate the sources of my stupidity, i will be forever grateful. # This is a shell archive. Save it in a file, remove anything before # this line, and then unpack it by entering "sh file". Note, it may # create directories; files and directories will be owned by you and # have default permissions. # # This archive contains: # # Makefile # pgcat.c # echo x - Makefile sed 's/^X//' >Makefile << 'END-of-Makefile' X## Copyright (c) 2000 by Mail Abuse Prevention System LLC X## X## Permission to use, copy, modify, and distribute this software for any X## purpose with or without fee is hereby granted, provided that the above X## copyright notice and this permission notice appear in all copies. X## X## THE SOFTWARE IS PROVIDED "AS IS" AND INTERNET SOFTWARE CONSORTIUM DISCLAIMS X## ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES X## OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL INTERNET SOFTWARE X## CONSORTIUM BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL X## DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR X## PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS X## ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS X## SOFTWARE. X X# $Id: Makefile,v 1.1.1.1 2000/12/19 04:49:51 vixie Exp $ X XCC= gcc -Wall XALL= pgcat X XLDFLAGS= -L/usr/local/pgsql/lib -L/usr/local/krb5/lib XCFLAGS= -I/usr/local/pgsql/include XLIBS= -lpq -lcom_err X Xall: $(ALL) X Xkit:; shar Makefile pgcat.c >kit X Xclean:; rm -f $(ALL) kit; rm -f *.o X Xpgcat: pgcat.o Makefile X $(CC) $(LDFLAGS) -o pgcat pgcat.o $(LIBS) X Xpgcat.o: pgcat.c Makefile END-of-Makefile echo x - pgcat.c sed 's/^X//' >pgcat.c << 'END-of-pgcat.c' X/* X * Copyright (c) 2000 by Mail Abuse Prevention System LLC X * X * Permission to use, copy, modify, and distribute this software for any X * purpose with or without fee is hereby granted, provided that the above X * copyright notice and this permission notice appear in all copies. X * X * THE SOFTWARE IS PROVIDED "AS IS" AND INTERNET SOFTWARE CONSORTIUM DISCLAIMS X * ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES X * OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL INTERNET SOFTWARE X * CONSORTIUM BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL X * DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR X * PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS X * ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS X * SOFTWARE. X */ X X#ifndef LINT Xstatic const char rcsid[] = "$Id: pgcat.c,v 1.1.1.1 2000/12/19 04:49:50 vixie Exp $"; X#endif X X#include <sys/param.h> X#include <sys/types.h> X#include <sys/stat.h> X X#include <stdio.h> X#include <stdlib.h> X#include <string.h> X#include <unistd.h> X X#include <libpq-fe.h> X Xstatic const char tmp_template[] = "/tmp/pgcat.XXXXXX"; Xstatic const char *progname = "amnesia"; X Xstatic int get(PGconn *, const char *, const char *, const char *, X const char *, const char *); Xstatic int put(PGconn *, const char *, const char *, const char *, X const char *, const char *); X Xstatic void Xusage(const char *msg) { X fprintf(stderr, "%s: usage error (%s)\n", progname, msg); X fprintf(stderr, X "usage: %s get|put <dbname> <table> <key> <value> <text> [<file>]\n", X progname); X exit(1); X} X Xint Xmain(int argc, char *argv[]) { X const char *pghost = NULL, *pgport = NULL, *pgoptions = NULL, X *pgtty = NULL; X const char *op, *dbname, *table, *key, *value, *text, *file; X PGconn *conn; X int status; X X if ((progname = strrchr(argv[0], '/')) != NULL) X progname++; X else X progname = argv[0]; X if (argc < 7) X usage("too few arguments"); X op = argv[1]; X dbname = argv[2]; X table = argv[3]; X key = argv[4]; X value = argv[5]; X text = argv[6]; X if (argc > 8) X usage("too many arguments"); X else if (argc == 8) X file = argv[7]; X else X file = NULL; X if (strcmp(op, "get") != 0 && strcmp(op, "put") != 0) X usage("operation must be 'get' or 'put'"); X conn = PQsetdb(pghost, pgport, pgoptions, pgtty, dbname); X if (PQstatus(conn) == CONNECTION_BAD) { X fprintf(stderr, "%s: \"%s\": %s", progname, dbname, X PQerrorMessage(conn)); X status = 1; X } else if (strcmp(op, "get") == 0) { X status = get(conn, table, key, value, text, file); X } else { X status = put(conn, table, key, value, text, file); X } X PQfinish(conn); X return (status); X} X Xstatic int Xget(PGconn *conn, const char *table, const char *key, const char *value, X const char *text, const char *file) X{ X char cmd[999], ch, pch; X const char *p; X PGresult *res = NULL; X int status = 0; X FILE *fp = stdout; X X /* Open the output file if there is one. */ X if (file != NULL) { X fp = fopen(file, "w"); X if (fp == NULL) { X perror(file); X status = 1; X goto done; X } X } X X /* Quote the lookup value if nec'y. */ X if (strchr(value, '\'') != NULL || strchr(value, ':') != NULL) X p = ""; X else X p = "'"; X X /* Send the query. */ X if (snprintf(cmd, sizeof cmd, "SELECT %s FROM %s WHERE %s = %s%s%s", X text, table, key, p, value, p) >= sizeof cmd) { X fprintf(stderr, "%s: snprintf overflow\n", progname); X status = 1; X goto done; X } X res = PQexec(conn, cmd); X if (PQresultStatus(res) != PGRES_TUPLES_OK) { X fprintf(stderr, "%s: \"%s\": %s", progname, cmd, X PQresultErrorMessage(res)); X status = 1; X goto done; X } X if (PQnfields(res) != 1) { X fprintf(stderr, "%s: \"%s\": %d fields?\n", X progname, cmd, PQnfields(res)); X status = 1; X goto done; X } X if (PQntuples(res) != 1) { X fprintf(stderr, "%s: \"%s\": %d tuples?\n", X progname, cmd, PQntuples(res)); X status = 1; X goto done; X } X X /* Output the result. */ X pch = '\0'; X for (p = PQgetvalue(res, 0, 0), ch = '\0'; (ch = *p) != '\0'; p++) { X putc(ch, fp); X pch = ch; X } X if (pch != '\n') X putc('\n', fp); X done: X if (fp != NULL && fp != stdout) X fclose(fp); X if (res != NULL) X PQclear(res); X return (status); X} X Xstatic int Xput(PGconn *conn, const char *table, const char *key, const char *value, X const char *text, const char *file) X{ X char *t, *tp, cmd[999]; X const char *p; X PGresult *res = NULL; X int status = 0, ch, n; X FILE *fp = stdin, *copy = NULL; X struct stat sb; X size_t size; X X /* Open the file if there is one. */ X if (file != NULL) { X fp = fopen(file, "r"); X if (fp == NULL) { X perror(file); X status = 1; X goto done; X } X } X X /* X * Read the file to find out how large it will be when quoted. X * If it's not a regular file, make a copy while reading, then switch. X */ X if (fstat(fileno(fp), &sb) < 0) { X perror("stat"); X status = 1; X goto done; X } X if ((sb.st_mode & S_IFMT) != S_IFREG) { X char tmpname[MAXPATHLEN]; X int fd; X X strcpy(tmpname, tmp_template); X fd = mkstemp(tmpname); X if (fd < 0) { X perror("mkstemp"); X status = 1; X goto done; X } X copy = fdopen(fd, "r+"); X unlink(tmpname); X } X size = 0; X while ((ch = getc(fp)) != EOF) { X if (ch == '\\' || ch == '\'') X size++; X size++; X if (copy) X putc(ch, copy); X } X if (ferror(fp)) { X perror("fread"); X status = 1; X goto done; X } X if (copy) { X if (fp != stdin) X fclose(fp); X fp = copy; X copy = NULL; X } X rewind(fp); X X /* Quote the lookup value if nec'y. */ X if (strchr(value, '\'') != NULL || strchr(value, ':') != NULL) X p = ""; X else X p = "'"; X X /* Construct the INSERT command. */ X n = snprintf(cmd, sizeof cmd, X "INSERT INTO %s ( %s, %s ) VALUES ( %s%s%s, '", X table, key, text, p, value, p); X if (n >= sizeof cmd) { X fprintf(stderr, "%s: snprintf overflow\n", progname); X status = 1; X goto done; X } X t = malloc(n + size + sizeof "');"); X if (t == NULL) { X perror("malloc"); X status = 1; X goto done; X } X strcpy(t, cmd); X tp = t + n; X while ((ch = getc(fp)) != EOF) { X if (ch == '\\' || ch == '\'') X *tp++ = '\\'; X *tp++ = ch; X } X *tp++ = '\''; X *tp++ = ')'; X *tp++ = ';'; X *tp++ = '\0'; X X /* Send the command. */ X res = PQexec(conn, t); X if (PQresultStatus(res) != PGRES_COMMAND_OK) { X fprintf(stderr, "%s: \"%s\": %s", progname, t, X PQresultErrorMessage(res)); X status = 1; X goto done; X } X if (strcmp(PQcmdTuples(res), "1") != 0) { X fprintf(stderr, "%s: \"%s...\": '%s' tuples? (%s)\n", X progname, cmd, PQcmdTuples(res), PQcmdStatus(res)); X status = 1; X goto done; X } X X done: X if (fp != NULL && fp != stdin) X fclose(fp); X if (res != NULL) X PQclear(res); X return (status); X} END-of-pgcat.c exit
В списке pgsql-hackers по дате отправления: