Re: [HACKERS] [PERFORM] Sun Donated a Sun Fire T2000 to the PostgreSQL
Arjen van der Meijden wrote: Here is a graph of our performance measured on PostgreSQL: http://achelois.tweakers.net/~acm/pgsql-t2000/T2000-schaling-postgresql.png ... The "perfect" line is based on the "Max" value for 1 core and then just multiplied by the amount of cores to have a linear reference. The "Bij 50" and the "perfect" line don't differ too much in color, but the top-one is the "perfect" line. Sureky the 'perfect' line ought to be linear? If the performance was perfectly linear, then the 'pages generated' ought to be G times the number (virtual) processors, where G is the gradient of the graph. In such a case the graph will go through the origin (o,o), but you graph does not show this. I'm a bit confused, what is the 'perfect' supposed to be? Thanks David
Re: [PERFORM] filesystem performance with lots of files
David Lang wrote: ext3 has an option to make searching directories faster (htree), but enabling it kills performance when you create files. And this doesn't help with large files. The ReiserFS white paper talks about the data structure he uses to store directories (some kind of tree), and he says it's quick to both read and write. Don't forget if you find ls slow, that could just be ls, since it's ls, not the fs, that sorts this files into alphabetical order. how long would it take to do a tar-ftp-untar cycle with no smarts Note that you can do the taring, zipping, copying and untaring concurrentlt. I can't remember the exactl netcat command line options, but it goes something like this Box1: tar czvf - myfiles/* | netcat myserver:12345 Box2: netcat -listen 12345 | tar xzvf - Not only do you gain from doing it all concurrently, but not writing a temp file means that disk seeks a reduced too if you have a one spindle machine. Also condsider just copying files onto a network mount. May not be as fast as the above, but will be faster than rsync, which has high CPU usage and thus not a good choice on a LAN. Hmm, sorry this is not directly postgres anymore... David
Re: [PERFORM] Partitioning / Clustering
For an interesting look at scalability, clustering, caching, etc for a large site have a look at how livejournal did it. http://www.danga.com/words/2004_lisa/lisa04.pdf They have 2.6 Million active users, posting 200 new blog entries per minute, plus many comments and countless page views. Although this system is of a different sort to the type I work on it's interesting to see how they've made it scale. They use mysql on dell hardware! And found single master replication did not scale. There's a section on multimaster replication, not sure if they use it. The main approach they use is to parition users into spefic database clusters. Caching is done using memcached at the application level to avoid hitting the db for rendered pageviews. It's interesting that the solution livejournal have arrived at is quite similar in ways to the way google is set up. David ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Hash index vs. b-tree index (PostgreSQL 8.0)
*Note: * Testing has shown PostgreSQL's hash indexes to perform no better than B-tree indexes, and the index size and build time for hash indexes is much worse. For these reasons, hash index use is presently discouraged. May I know for simple = operation query, for Hash index vs. B-tree index, which can provide better performance please? If you trust the documentation use a b-tree. If you don't trust the documentation do your own tests. please don't cross post. ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send unregister YourEmailAddressHere to [EMAIL PROTECTED])
Re: [PERFORM] Table stats
Should there not be at least one Index Scan showing in the stats? not if there was a table scan ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] COPY vs INSERT
COPY invokes all the same logic as INSERT on the server side (rowexclusive locking, transaction log, updating indexes, rules). The difference is that all the rows are inserted as a single transaction. This reduces the number of fsync's on the xlog, which may be a limiting factor for you. You'll want to crank WAL_BUFFERS and CHECKPOINT_SEGMENTS to match, though. One of my streams has 6K records; I run with WB=1000, CS=128. So what's the difference between a COPY and a batch of INSERT statements. Also, surely, fsyncs only occur at the end of a transaction, no need to fsync before a commit has been issued, right? David ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] Index bloat problem?
On Fri, 22 Apr 2005 10:06:33 -0400, Tom Lane [EMAIL PROTECTED] said: David Roussel [EMAIL PROTECTED] writes: |dave_data_update_eventsr 1593600.0 40209 |dave_data_update_events_event_id_key i 1912320.0 29271 Hmm ... what PG version is this, and what does VACUUM VERBOSE on that table show? PG 7.4 The disparity seems to have sorted itself out now, so hampering futher investigations. I guess the regular inserts of new data, and the nightly deletion and index recreation did it. However, we did suffer reduced performance and the strange cardinality for several days before it went away. For what it's worth.. ndb=# vacuum verbose iso_pjm_data_update_events; INFO: vacuuming public.iso_pjm_data_update_events INFO: index iso_pjm_data_update_events_event_id_key now contains 1912320 row versions in 29271 pages DETAIL: 21969 index pages have been deleted, 2 are currently reusable. CPU 6.17s/0.88u sec elapsed 32.55 sec. INFO: index iso_pjm_data_update_events_lds_idx now contains 1912320 row versions in 7366 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 3.52s/0.57u sec elapsed 14.35 sec. INFO: index iso_pjm_data_update_events_obj_id_idx now contains 1912320 row versions in 7366 pages DETAIL: 0 index pages have been deleted, 0 are currently reusable. CPU 3.57s/0.58u sec elapsed 12.87 sec. INFO: iso_pjm_data_update_events: found 0 removable, 1912320 nonremovable row versions in 40209 pages DETAIL: 159384 dead row versions cannot be removed yet. There were 745191 unused item pointers. 0 pages are entirely empty. CPU 18.26s/3.62u sec elapsed 74.35 sec. VACUUM After each insert is does this... VACUUM ANALYZE iso_pjm_DATA_UPDATE_EVENTS VACUUM ANALYZE iso_pjm_CONTROL Each night it does this... BEGIN DROP INDEX iso_pjm_control_obj_id_idx DROP INDEX iso_pjm_control_real_name_idx DROP INDEX iso_pjm_data_update_events_lds_idx DROP INDEX iso_pjm_data_update_events_obj_id_idx CREATE UNIQUE INDEX iso_pjm_control_obj_id_idx ON iso_pjm_control(obj_id) CLUSTER iso_pjm_control_obj_id_idx ON iso_pjm_control CREATE UNIQUE INDEX iso_pjm_control_real_name_idx ON iso_pjm_control(real_name) CREATE INDEX iso_pjm_data_update_events_lds_idx ON iso_pjm_data_update_events(lds) CREATE INDEX iso_pjm_data_update_events_obj_id_idx ON iso_pjm_data_update_events(obj_id) COMMIT Note there is no reference to iso_pjm_data_update_events_event_id_key which is the index that went wacky on us. Does that seem weird to you? Thanks David ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[PERFORM] How can an index be larger than a table
Hi, I have a series of tables with identical structure. Some contain a few thousand rows and some contain 3,000,000 rows. Another applicate writes the rows and my applicate reads then just by selecting where pk last_seen_pk limit 2000. I've found that one of the tables, when selecting from it that one of the tables is many times slower than the others. For instance when reading data in batches of 2000 rows, it seems to take 26 seconds to query from dave_data_update_events with 1593600, but only 1 or two seconds to query from jane_data_update_events with 310 rows! This is ther SQL used | |select | events.event_id, ctrl.real_name, events.tsds, events.value, | events.lds, events.correction, ctrl.type, ctrl.freq |from dave_data_update_events events, dave_control ctrl |where events.obj_id = ctrl.obj_id and |events.event_id 32128893::bigint |order by events.event_id |limit 2000 | Here is the structure of the tables... | |CREATE TABLE dave_control ( |obj_id numeric(6,0) NOT NULL, |real_name character varying(64) NOT NULL, |type numeric(2,0) NOT NULL, |freq numeric(2,0) NOT NULL |); | |CREATE TABLE dave_data_update_events ( |lds numeric(13,0) NOT NULL, |obj_id numeric(6,0) NOT NULL, |tsds numeric(13,0) NOT NULL, |value character varying(22) NOT NULL, |correction numeric(1,0) NOT NULL, |delta_lds_tsds numeric(13,0) NOT NULL, |event_id bigserial NOT NULL |); | |CREATE UNIQUE INDEX dave_control_obj_id_idx ON dave_control USING btree (obj_id); |ALTER TABLE dave_control CLUSTER ON dave_control_obj_id_idx; | |CREATE UNIQUE INDEX dave_control_real_name_idx ON dave_control USING btree (real_name); | |CREATE INDEX dave_data_update_events_lds_idx ON dave_data_update_events USING btree (lds); | |CREATE INDEX dave_data_update_events_obj_id_idx ON dave_data_update_events USING btree (obj_id); | |ALTER TABLE ONLY dave_control |ADD CONSTRAINT dave_control_obj_id_key UNIQUE (obj_id); | |ALTER TABLE ONLY dave_control |ADD CONSTRAINT dave_control_real_name_key UNIQUE (real_name); | |ALTER TABLE ONLY dave_data_update_events |ADD CONSTRAINT dave_data_update_events_event_id_key UNIQUE (event_id); | There are several pairs of tables, but with names like rod, jane, fredie, etc.. instead of dave. The first thing to note about the scheme (not designed by me) is that the control table is clustered on obj_id, but the data_update_events table is not clustered. Does that mean the rows will be stored in order of insert? That might be ok, because data_update_events table is like a queue and I read it in the order the new rows are inserted. What also seems weird to me is that the control table has some unique indexes created on it, but the data_upate_events table just has a unique constraint. Will postgres use an index in the background to enforce this constraint? When looking at the indexes on the all the tables in DbVisualiser my colleague noticed that the cardinality of the indexes on the rod, jane and fredie tables was consistent, but for dave the cardinality was strange... | |SELECT relname, relkind, reltuples, relpages FROM pg_class WHERE relname LIKE 'dave_data%'; | |relnamerelkind reltuples relpages |=== === = |dave_data_update_eventsr 1593600.0 40209 |dave_data_update_events_event_id_keyi1912320.0 29271 |dave_data_update_events_event_id_seqS 1.0 1 |dave_data_update_events_lds_idxi 1593600.0 6139 |dave_data_update_events_obj_id_idx i 1593600.0 6139 |iso_pjm_data_update_events_obj_id_idxi1593600.0 6139 | Note that there are only 1593600 rows in the table, so why the 1912320 figure? Of course I checked that the row count was correct... | |EXPLAIN ANALYZE |select count(*) from iso_pjm_data_update_events | |QUERY PLAN |Aggregate (cost=60129.00..60129.00 rows=1 width=0) (actual time=35933.292..35933.293 rows=1 loops=1) | - Seq Scan on iso_pjm_data_update_events (cost=0.00..56145.00 rows=1593600 width=0) (actual time=0.213..27919.497 rows=1593600 loops=1) |Total runtime: 35933.489 ms | and... | |select count(*) from iso_pjm_data_update_events | |count |1593600 | so it's not that there are any undeleted rows lying around. So any comments on the index structure? Any ideas why the cardinality of the index is greater than the number of rows in the table? Was it because the table used to be larger? Also any ideas on how else to track down the big performance difference between tables of the same structure? ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
