[PERFORM] Slow index
Hi all. I'm having an interesting time performance-wise with a set of indexes. Any clues as to what is going on or tips to fix it would be appreciated. My application runs lots of queries along the lines of: SELECT * from table where field IN (.., .., ..); There is always an index on the field in the table, but the table is not necessarily clustered on the index. There are six different indexes which the application hits quite hard, that I have investigated. These are: gene__key_primaryidentifier (index size 20MB) (table size 72MB) gene__key_secondaryidentifier(index size 20MB) (table size 72MB) ontologyterm__key_name_ontology (index size 2.5MB) (table size 10MB) protein__key_primaryacc (index size 359MB) (table size 1.2GB) publication__key_pubmed (index size 12MB) (table size 48MB) synonym__key_synonym (index size 3GB) (table size 3.5GB) These six indexes all perform very differently. These are the results from a few thousand queries on each index, from our application logs. Generally, the same value is not used more than once in all the queries. (1) (2) (3) (4)(5) gene__key_primaryidentifier 2217417 19 24.5 gene__key_secondaryidentifier8.5 5.3 212.43.9 ontologyterm__key_name_ontology 6.5 6.5 9.4 1.41.4 protein__key_primaryacc 738.1 164 2.220 publication__key_pubmed 5231156 3.05.0 synonym__key_synonym 335 66245 0.73.7 (1) - Average number of values in the IN list. (2) - Average number of rows returned by the queries. (3) - Average time taken to execute the query, in ms. (4) - Average time per value in the IN lists. (5) - Average time per row returned. All the queries are answered with a bitmap index scan on the correct index. I have also plotted all the log entries on an XY graph, with number of elements in the IN list against time taken, which is at http://wakeling.homeip.net/~mnw21/slow_index1.png. It is clear that the gene__key_primaryidentifier index runs a lot slower than some of the other indexes. The thing is, the table and the index are both small. The machine has 16GB of RAM, and its disc subsystem is a RAID array of 16 15krpm drives with a BBU caching RAID controller. The entire table and index should be in the cache. Why it is taking 20 milliseconds per value is beyond me. Moreover, the synonym index is MUCH larger, has bigger queries, and performs better. If we concentrate on just this index, it seems that some queries are answered very quickly indeed, while others are answered a lot slower. I have plotted just this one index on an XY graph, with two colours for values in the IN list and actual rows returned, which is at http://wakeling.homeip.net/~mnw21/slow_index2.png. It is clear that there is a gap in the graph between the slow queries and the fast queries. Is there something else going on here which is slowing the system down? The table is not bloated. There is quite heavy simultaneous write traffic, but little other read traffic, and the 16 spindles and BBU cache should take care of that quite happily. I don't think it's slow parsing the query, as it seems to manage on other queries in a millisecond or less. Any ideas welcome. Also, the mailing list server doesn't seem to be able to cope with image attachments. Matthew -- import oz.wizards.Magic; if (Magic.guessRight())... -- Computer Science Lecturer -- 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] Slow index
Matthew Wakeling <[EMAIL PROTECTED]> writes: > Hi all. I'm having an interesting time performance-wise with a set of > indexes. > Any clues as to what is going on or tips to fix it would be appreciated. Are the indexed columns all the same datatype? (And which type is it?) It might be helpful to REINDEX the "slow" index. It's possible that what you're seeing is the result of a chance imbalance in the btree, which reindexing would fix. regards, tom lane -- 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] Slow index
On Thu, 25 Sep 2008, Tom Lane wrote: Matthew Wakeling <[EMAIL PROTECTED]> writes: Hi all. I'm having an interesting time performance-wise with a set of indexes. Any clues as to what is going on or tips to fix it would be appreciated. Are the indexed columns all the same datatype? (And which type is it?) Gene.key_primaryidentifier is a text column Gene.key_secondaryidentifier is a text column followed by an integer OntologyTerm.key_name_ontology is a text column followed by an integer Protein.key_primaryacc is a text column Publication.key_pubmed is a text column Synonym.key_synonym is an integer, two texts, and an integer In most cases, the first text will be enough to uniquely identify the relevant row. It might be helpful to REINDEX the "slow" index. It's possible that what you're seeing is the result of a chance imbalance in the btree, which reindexing would fix. That's unlikely to be the problem. When the application starts, the database has just been loaded from a dump, so the indexes are completely fresh. The behaviour starts out bad, and does not get progressively worse. I don't know - is there likely to be any locking getting in the way? Our write traffic is fairly large chunks of binary COPY in. Could it be locking the index while it adds the write traffic to it? Matthew -- Most books now say our sun is a star. But it still knows how to change back into a sun in the daytime. -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] Slow updates, poor IO
I've just had an interesting encounter with the slow full table update problem that is inherent with MVCC The system is 64 bit linux with 2.6.25 kernel feeding scsi disks. the table is CREATE TABLE file ( fileid integer NOT NULL, fileindex integer DEFAULT 0 NOT NULL, jobid integer NOT NULL, pathid integer NOT NULL, filenameid integer NOT NULL, markid integer DEFAULT 0 NOT NULL, lstat text NOT NULL, md5 text NOT NULL, perms text ); ALTER TABLE ONLY file ADD CONSTRAINT file_pkey PRIMARY KEY (fileid); CREATE INDEX file_fp_idx ON file USING btree (filenameid, pathid); CREATE INDEX file_jobid_idx ON file USING btree (jobid); There are 2.7M rows. runningupdate file set perms='0664' took about 10 mins during this period, vmstat reported Blocks Out holding in the 4000 to 6000 range. When I dropped the indexes this query ran in 48sec. Blocks out peaking at 55000. So there is a double whammy. MVCC requires more work to be done when indexes are defined and then this work results in much lower IO, compounding the problem. Comments anyone? --john -- 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] Slow updates, poor IO
On Thu, Sep 25, 2008 at 1:24 PM, John Huttley <[EMAIL PROTECTED]> wrote: > I've just had an interesting encounter with the slow full table update > problem that is inherent with MVCC > > The system is 64 bit linux with 2.6.25 kernel feeding scsi disks. > > the table is > > CREATE TABLE file ( > fileid integer NOT NULL, > fileindex integer DEFAULT 0 NOT NULL, > jobid integer NOT NULL, > pathid integer NOT NULL, > filenameid integer NOT NULL, > markid integer DEFAULT 0 NOT NULL, > lstat text NOT NULL, > md5 text NOT NULL, > perms text > ); > > ALTER TABLE ONLY file > ADD CONSTRAINT file_pkey PRIMARY KEY (fileid); > > CREATE INDEX file_fp_idx ON file USING btree (filenameid, pathid); > CREATE INDEX file_jobid_idx ON file USING btree (jobid); > > There are 2.7M rows. > > runningupdate file set perms='0664' took about 10 mins So, how many rows would already be set to 0664? Would adding a where clause speed it up? update file set perms='0664' where perms <> '0664'; > during this period, vmstat reported Blocks Out holding in the 4000 to 6000 > range. > > > When I dropped the indexes this query ran in 48sec. > Blocks out peaking at 55000. > > So there is a double whammy. > MVCC requires more work to be done when indexes are defined and then this > work > results in much lower IO, compounding the problem. That's because it becomes more random and less sequential. If you had a large enough drive array you could get that kind of performance for updating indexes, since the accesses would tend to hit different drives most the time. Under heavy load on the production servers at work we can see 30 to 60 Megs a second random access with 12 drives, meaning 2.5 to 5Megs per second per drive. Sequential throughput is about 5 to 10 times higher. What you're seeing are likely the effects of running a db on insufficient drive hardware. -- 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] Slow updates, poor IO
On Thursday 25 September 2008, John Huttley <[EMAIL PROTECTED]> wrote: > > Comments anyone? Don't do full table updates? This is not exactly a news flash. -- Alan -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
[PERFORM] CPU load
Hello, postmaster heavily loads processor. The database is accessed from java aplication (with several threads), C applications and from PHP scripts. It seems that one php script, called periodicaly, rises the load but the script is very simple, something like this: $var__base = new baza($dbhost,$dbport,$dbname,$dbuser,$dbpasswd); $pok_baza = new upit($var__base->veza); $upit_datum="SELECT * FROM system_alarm WHERE date= '$danas' AND time>=(LOCALTIME - interval '$vrijeme_razmak hours') ORDER BY date DESC, time DESC"; The statment is executed in approximately 0.6 sec. The number of open connections is constantly 107. The operating system is Debian GNU/Linux kernel 2.6.18-4-686. Database version is PostgreSQL 8.2.4. Thank you very much for any help. Maja Stula _ The result of the top command: top - 20:44:58 up 5:36, 1 user, load average: 1.31, 1.39, 1.24 Tasks: 277 total, 2 running, 275 sleeping, 0 stopped, 0 zombie Cpu(s): 11.5%us, 2.2%sy, 0.0%ni, 86.3%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 3370808k total, 1070324k used, 2300484k free,49484k buffers Swap: 1951888k total,0k used, 1951888k free, 485396k cached PID USER PR NI VIRT RES SHR S %CPU %MEMTIME+ COMMAND 4990 postgres 25 0 41160 19m 18m R 100 0.6 1:36.74 postmaster 15278 test 24 0 1000m 40m 5668 S9 1.2 1:42.37 java 18892 root 15 0 2468 1284 884 R0 0.0 0:00.05 top 1 root 15 0 2044 696 596 S0 0.0 0:02.51 init 2 root RT 0 000 S0 0.0 0:00.00 migration/0 3 root 34 19 000 S0 0.0 0:00.12 ksoftirqd/0 4 root RT 0 000 S0 0.0 0:00.00 migration/1 5 root 34 19 000 S0 0.0 0:00.00 ksoftirqd/1 6 root RT 0 000 S0 0.0 0:00.00 migration/2 7 root 34 19 000 S0 0.0 0:00.00 ksoftirqd/2 __ The result of vmstat command: kamis03:/etc# vmstat 1 procs ---memory-- ---swap-- -io -system-- cpu r b swpd free buff cache si sobibo in cs us sy id wa 2 0 0 2271356 49868 50525200 232 40 83 6 1 93 0 2 0 0 2271232 49868 50530400 0 2348 459 1118 14 2 84 0 3 0 0 2271232 49868 50530400 016 305 1197 11 2 87 0 3 0 0 2270984 49868 50543200 0 8 407 1821 15 3 82 0 2 0 0 2270984 49868 50543200 0 0 271 1328 11 2 87 0 1 0 0 2270984 49868 50544000 024 375 1530 5 1 94 0 2 0 0 2270488 49868 50544000 0 1216 401 1541 12 2 86 0 __ The cpu configuration is: processor : 0 vendor_id : GenuineIntel cpu family : 6 model : 15 model name : Intel(R) Xeon(R) CPU E5310 @ 1.60GHz stepping: 7 cpu MHz : 1596.076 cache size : 4096 KB physical id : 0 siblings: 4 core id : 0 cpu cores : 4 fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 10 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm bogomips: 3194.46 processor : 1 vendor_id : GenuineIntel cpu family : 6 model : 15 model name : Intel(R) Xeon(R) CPU E5310 @ 1.60GHz stepping: 7 cpu MHz : 1596.076 cache size : 4096 KB physical id : 0 siblings: 4 core id : 1 cpu cores : 4 fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 10 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss ht tm pbe nx lm constant_tsc pni monitor ds_cpl vmx tm2 cx16 xtpr lahf_lm bogomips: 3191.94 processor : 2 vendor_id : GenuineIntel cpu family : 6 model : 15 model name : Intel(R) Xeon(R) CPU E5310 @ 1.60GHz stepping: 7 cpu MHz : 1596.076 cache size : 4096 KB physical id : 0 siblings: 4 core id : 2 cpu cores : 4 fdiv_bug: no hlt_bug : no f00f_bug: no coma_bug: no fpu : yes fpu_exception : yes cpuid level : 10 wp : yes flags : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge mca cmov pat pse36 clflush dts acpi mmx fxsr sse sse2 ss
Re: [PERFORM] CPU load
2008/9/25 <[EMAIL PROTECTED]>: > The result of the top command: > > top - 20:44:58 up 5:36, 1 user, load average: 1.31, 1.39, 1.24 > Tasks: 277 total, 2 running, 275 sleeping, 0 stopped, 0 zombie > Cpu(s): 11.5%us, 2.2%sy, 0.0%ni, 86.3%id, 0.0%wa, 0.0%hi, 0.0%si, > 0.0%st > Mem: 3370808k total, 1070324k used, 2300484k free,49484k buffers > Swap: 1951888k total,0k used, 1951888k free, 485396k cached SNIP > The result of vmstat command: > > kamis03:/etc# vmstat 1 > procs ---memory-- ---swap-- -io -system-- cpu > r b swpd free buff cache si sobibo in cs us sy id wa > 2 0 0 2271356 49868 50525200 232 40 83 6 1 > 93 0 > 2 0 0 2271232 49868 50530400 0 2348 459 1118 14 2 > 84 0 > 3 0 0 2271232 49868 50530400 016 305 1197 11 2 > 87 0 > 3 0 0 2270984 49868 50543200 0 8 407 1821 15 3 If that's what it looks like your server is running just fine. Load of 1.31, 85+% idle, no wait time. Or is that top and vmstat output from when the server is running fine? -- 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] Slow updates, poor IO
On Fri, 26 Sep 2008, John Huttley wrote: runningupdate file set perms='0664' took about 10 mins What do you have checkpoint_segments and shared_buffers set to? If you want something that's doing lots of updates to perform well, you need to let PostgreSQL have a decent size chunk of memory to buffer the index writes with, so it's more likely they'll get combined into larger and therefore more easily sorted blocks rather than as more random ones. The randomness of the writes is why your write rate is so slow. You also need to cut down on the frequency of checkpoints which are very costly on this type of statement. Also: which version of PostgreSQL? 8.3 includes an improvement aimed at updates like this you might benefit from. -- * Greg Smith [EMAIL PROTECTED] http://www.gregsmith.com Baltimore, MD -- 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] CPU load
> If that's what it looks like your server is running just fine. Load > of 1.31, 85+% idle, no wait time. Or is that top and vmstat output > from when the server is running fine? Don't forget that there are 8 CPUs, and the backend will only run on one of them. But I concur that this seems ok. How many rows are returned? Is 0.6 seconds an unacceptable time for that? If there is a lot of sorting going on and the pages are residing in the buffer, I would expect high CPU load. Normally, I am quite happy if my database is CPU bound. I start worrying if I/O wait grows too high. Yours, Laurenz Albe -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
