Re: [PERFORM] maintenance_work_mem + create index
Em 24-03-2011 11:40, Uwe Bartels escreveu: Or is there a dynamic way to put $PGDATA/base/pgsql_tmp into RAM without blocking it completely like a ram disk? Create a tablespace in a ram disk and set temp_tablespaces. -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] pg_xlog size
Em 15-03-2011 12:09, Tech Madhu escreveu: [This is not a performance question, next time post at the appropriate list, that is -general] Everything works fine (w.r.t replication), but the pg_xlog size grows continuously, though i had no operations going on. Also the archiving to the other side filled up the other side FS. ls -l /var/postgres/data/pg_xlog | wc -l 103 Did you consider using pg_archivecleanup [1]? At start, there were only 15 files. The max_wal_segments is 32, but not sure why iam seeing 103 files. Also the archiving dir size doubled (w.r.t number of files archived). and filled up the filesystem. I manually logged into postgres and run checkpoint; did not see any file reduction max_wal_segments [2] is *not* related to archiving activity. [1] http://www.postgresql.org/docs/9.0/static/pgarchivecleanup.html [2] http://www.postgresql.org/docs/9.0/static/runtime-config-wal.html#GUC-WAL-KEEP-SEGMENTS -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] pgbench to the MAXINT
Em 10-01-2011 05:25, Greg Smith escreveu:
Euler Taveira de Oliveira wrote:
Em 07-01-2011 22:59, Greg Smith escreveu:
setrandom: invalid maximum number -2147467296
It is failing at atoi() circa pgbench.c:1036. But it just the first
one. There are some variables and constants that need to be converted
to int64 and some functions that must speak 64-bit such as getrand().
Are you working on a patch?
http://archives.postgresql.org/pgsql-hackers/2010-01/msg02868.php
http://archives.postgresql.org/message-id/4c326f46.4050...@2ndquadrant.com
Greg, I just improved your patch. I tried to work around the problems pointed
out in the above threads. Also, I want to raise some points:
(i) If we want to support and scale factor greater than 21474 we have to
convert some columns to bigint; it will change the test. From the portability
point it is a pity but as we have never supported it I'm not too worried about
it. Why? Because it will use bigint columns only if the scale factor is
greater than 21474. Is it a problem? I don't think so because generally people
compare tests with the same scale factor.
(ii) From the performance perspective, we need to test if the modifications
don't impact performance. I don't create another code path for 64-bit
modifications (it is too ugly) and I'm afraid some modifications affect the
32-bit performance. I'm in a position to test it though because I don't have a
big machine ATM. Greg, could you lead these tests?
(iii) I decided to copy scanint8() (called strtoint64 there) from backend
(Robert suggestion [1]) because Tom pointed out that strtoll() has portability
issues. I replaced atoi() with strtoint64() but didn't do any performance tests.
Comments?
[1] http://archives.postgresql.org/pgsql-hackers/2010-07/msg00173.php
--
Euler Taveira de Oliveira
http://www.timbira.com/
diff --git a/contrib/pgbench/pgbench.c b/contrib/pgbench/pgbench.c
index 7c2ca6e..e9eb720 100644
*** a/contrib/pgbench/pgbench.c
--- b/contrib/pgbench/pgbench.c
***
*** 60,65
--- 60,67
#define INT64_MAX INT64CONST(0x7FFF)
#endif
+ #define MAX_RANDOM_VALUE64 INT64_MAX
+
/*
* Multi-platform pthread implementations
*/
*** usage(const char *progname)
*** 364,378
progname, progname);
}
/* random number generator: uniform distribution from min to max inclusive */
! static int
! getrand(int min, int max)
{
/*
* Odd coding is so that min and max have approximately the same chance of
* being selected as do numbers between them.
*/
! return min + (int) (((max - min + 1) * (double) random()) / (MAX_RANDOM_VALUE + 1.0));
}
/* call PQexec() and exit() on failure */
--- 366,451
progname, progname);
}
+ /*
+ * strtoint64 -- convert a string to 64-bit integer
+ *
+ * this function is a modified version of scanint8() from
+ * src/backend/utils/adt/int8.c.
+ *
+ * XXX should it have a return value?
+ *
+ */
+ static int64
+ strtoint64(const char *str)
+ {
+ const char *ptr = str;
+ int64 result = 0;
+ int sign = 1;
+
+ /*
+ * Do our own scan, rather than relying on sscanf which might be broken
+ * for long long.
+ */
+
+ /* skip leading spaces */
+ while (*ptr && isspace((unsigned char) *ptr))
+ ptr++;
+
+ /* handle sign */
+ if (*ptr == '-')
+ {
+ ptr++;
+
+ /*
+ * Do an explicit check for INT64_MIN. Ugly though this is, it's
+ * cleaner than trying to get the loop below to handle it portably.
+ */
+ if (strncmp(ptr, "9223372036854775808", 19) == 0)
+ {
+ result = -INT64CONST(0x7fff) - 1;
+ ptr += 19;
+ goto gotdigits;
+ }
+ sign = -1;
+ }
+ else if (*ptr == '+')
+ ptr++;
+
+ /* require at least one digit */
+ if (!isdigit((unsigned char) *ptr))
+ fprintf(stderr, "invalid input syntax for integer: \"%s\"\n", str);
+
+ /* process digits */
+ while (*ptr && isdigit((unsigned char) *ptr))
+ {
+ int64 tmp = result * 10 + (*ptr++ - '0');
+
+ if ((tmp / 10) != result) /* overflow? */
+ fprintf(stderr, "value \"%s\" is out of range for type bigint\n", str);
+ result = tmp;
+ }
+
+ gotdigits:
+
+ /* allow trailing whitespace, but not other trailing chars */
+ while (*ptr != '\0' && isspace((unsigned char) *ptr))
+ ptr++;
+
+ if (*ptr != '\0')
+ fprintf(stderr, "invalid input syntax for integer: \"%s\"\n", str);
+
+ return ((sign < 0) ? -result : result);
+ }
+
/* random number generator: uniform distribution from min to max inclusive */
! static int64
! getrand(int64 min, int64 max)
{
/*
* Odd coding is so that min and max have approximately the same chance of
* being selected as do numbers between them.
*/
! return min + (int64) (((max - min + 1) * (double) random()
Re: [PERFORM] pgbench to the MAXINT
Em 07-01-2011 22:59, Greg Smith escreveu: setrandom: invalid maximum number -2147467296 It is failing at atoi() circa pgbench.c:1036. But it just the first one. There are some variables and constants that need to be converted to int64 and some functions that must speak 64-bit such as getrand(). Are you working on a patch? It doesn't throw any error during the initialization step, neither via client or database logs, even though it doesn't do anything whatsoever. It just turns into the quickest pgbench init ever. That's the exact threshold, because this works: AFAICS that is because atoi() is so fragile. So where we're at now is that the maximum database pgbench can create is a scale of 21474. That's because 21475 * 100,000 > INT_MAX. We must provide an alternative to atoi() that deals with 64-bit integers. -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] a heavy duty operation on an "unused" table kills my server
Eduardo Piombino escreveu: > Maybe it does not get logged at all until the ALTER is completed? > This feature [1] was implemented a few months ago and it will be available only in the next PostgreSQL version (8.5). [1] http://archives.postgresql.org/pgsql-committers/2009-11/msg00018.php -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Issues with \copy from file
Sigurgeir Gunnarsson escreveu: > What I'm wondering about is what parameters to tweak to improve the > operation and shorten the time of the \copy ? I think I have tweaked > most of the available in postgresql.conf, that is shared_buffer, > temp_buffers, work_mem, maintenance_work_mem, max_fsm_pages. Maybe > someone could point out the one really related to \copy ? > You don't show us your table definitions. You don't say what postgresql version you're using. Let's suppose archiving is disabled, you're bulk loading table foo and, you're using version >= 8.3. Just do: BEGIN; TRUNCATE TABLE foo; COPY foo FROM ...; COMMIT; PostgreSQL will skip WAL writes and just fsync() the table at the end of the command. Also, take a look at [1]. [1] http://www.postgresql.org/docs/current/interactive/populate.html -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] table full scan or index full scan?
旭斌 裴 escreveu: > The postgresql database uses the table full scan.but in oracle, the > similar SQL uses the index full scanning,speed quickly many than > postgresql. > This was discussed many times on the pgsql mailing lists. Search the archives. Also, take a look at [1]. [1] http://wiki.postgresql.org/wiki/Slow_Counting -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Index row requires 9324 bytes maximum size is 8191
solAris23 escreveu: > I am trying to index a field in my database of size about 16K rows, but i m > getting this error. > Why are you want to index such a big field? BTW, it'll be worthless. > " Index row requires 9324 bytes maximum size is 8191 " > That is a known limitation; but even if it would be possible I don't think it would be a good idea. Why on Earth would I search using a big field? What kind of content are you trying to index? -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Best settings to load a fresh database
Kenneth Marshall escreveu: > I have found that increasing maintenance_work_mem speeds > index rebuilds, turn off synchronous_commit or fsync if > you really can afford to start over. Another big help is > to use the parallel pg_restore from PostgreSQL 8.4.0 to > perform the restore. > And make sure archive mode is turned off. Otherwise, you can't use the WAL bypass facility. -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Why is PostgreSQL so slow on Windows ( Postgres 8.3.7) version
parimala escreveu: [Don't repeat your answer. It's a PITA to receive multiple identical copies] > We are using Postgres 8.3.7 in our java application. We are doing > performances tuning and load testing in our setup. we have noticed that > ,some of our queries to the database taking long time to return the > results.Please find our setup details belows. > > We observed that postgres is running in windows is slower than the linux . > That is true and it will be for quite some time. Windows port is very recent if we compare it with the long road Unix support. > Postgresql configuration: > > shared_buffers: 1 GB I don't use Windows but I read some Windows users saying that it isn't appropriate to set the shared_buffers too high. Take a look at the archives. > Effective_cache_size: 2GB > fsync: off (even we tested this parameter is on ,we observed the same > slowness ) > What about the other parameters that are different from default (uncommented parameters)? Also, don't turn off the fsync unless you're pretty sure about the consequences. > We have 300k row's in PolledData Table.In each STATSDATA table ,we have > almost 12 to 13 million rows. Every one minute interval ,we insert data > into to STATSDATA table. In our application ,we use insert and select > query to STATSDATA table at regular interval. Please let us know why the > below query takes more time to return the results. is there any thing we > need to do to tune the postgres database ? > It seems very strange that your queries are not using the indexes. Do you have autovacuum turn on? Do you recently analyze your tables? > Merge Cond: (statsdata8_21_2009.pollid = > ((polleddata.id)::numeric)) Out of curiosity, why does foreign key have different datatype of its primary key? -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] current transaction in productive database
m...@bortal.de escreveu: > is there a way to find out, how many transactions my currenc productive > database is doing? > If you're looking for number of transactions then you can query the catalogs as: $ export myq="select sum(xact_commit+xact_rollback) from pg_stat_database" $ psql -U postgres -c "$myq" && sleep 60 && psql -U postgres -c "$myq" sum --- 178992891 (1 row) sum --- 178996065 (1 row) $ bc -q scale=3 (178996065-178992891)/60 52.900 Depending on your workload pattern, it's recommended to increase the sleep time. -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] "tablespace" for tranaction log?
Thomas Finneid escreveu: > But my question is, since I can do this with tablespace for tables, > indexes and so on, is there any possibilites to do a similar thing for > the transaction log from inside postgres? as in > No. Because transaction log is for the entire cluster and it is too risky to do such a change when the server is running. You need to stop the database and make the change. Also, you can setup the xlog to a different location at initdb time. -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] index
Maksim Sosnovskiy escreveu: > The primary key is a set of values. And the key does include IP > Address as well. Will it be more efficient to also add index on IP > Address to speedup lookups by IP? > It depends on what set of queries that you run. If the most frequent queries use ip_field, then you need an index on it. Maybe you could try ip4r [1]. [1] http://pgfoundry.org/projects/ip4r/ -- Euler Taveira de Oliveira http://www.timbira.com/ -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
Re: [PERFORM] Performance problems inside a stored procedure.
Matthew Lunnon wrote: Ahh, sorry, I have been too aggressive with my cutting, I am running 8.2.6 and the function is below. $BODY$ LANGUAGE 'sql' VOLATILE; ^^ I suspect that it's because you're using VOLATILE (so no good optimizations is done); did you try STABLE? Could you show us the EXPLAIN ANALYZE of query and function? -- Euler Taveira de Oliveira http://www.timbira.com/ ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
