Thread

Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-03T12:36:10Z

Hi,

Today we concluded test for database performance. Attached are results and the 
schema, for those who have missed earlier discussion on this.

We have (almost) decided that we will partition the data across machines. The 
theme is, after every some short interval a burst of data will be entered in 
new table in database, indexed and vacuume. The table(s) will be inherited so 
that query on base table will fetch results from all the children. The 
application has to consolidate all the data per node basis. If the database is 
not postgresql, app. has to consolidate data across partitions as well.

Now we need to investigate whether selecting on base table to include children 
would use indexes created on children table.

It's estimated that when entire data is gathered, total number of children 
tables would be around 1K-1.1K across all machines. 

This is in point of average rate of data insertion i.e. 5K records/sec and 
total data size, estimated to be 9 billion rows max i.e. estimated database 
size is 900GB. Obviously it's impossible to keep insertion rate on an indexed 
table high as data grows. So partitioning/inheritance looks better approach. 

Postgresql is not the final winner as yet. Mysql is in close range. I will keep 
you guys posted about the result.

Let me know about any comments..

Bye
 Shridhar

--
Price's Advice:	It's all a game -- play it to have fun.

Re: Large databases, performance

Charles H. Woloszynski <chw@clearmetrix.com> — 2002-10-03T12:54:29Z

Can you comment on the tools you are using to do the insertions (Perl, 
Java?) and the distribution of data (all random, all static), and the 
transaction scope (all inserts in one transaction, each insert as a 
single transaction, some group of inserts as a transaction).

I'd be curious what happens when you submit more queries than you have 
processors (you had four concurrent queries and four CPUs), if you care 
to run any additional tests.  Also, I'd report the query time in 
absolute (like you did) and also in 'Time/number of concurrent queries". 
 This will give you a sense of how the system is scaling as the workload 
increases.  Personally I am more concerned about this aspect than the 
load time, since I am going to guess that this is where all the time is 
spent.  

Was the original posting on GENERAL or HACKERS.  Is this moving the 
PERFORMANCE for follow-up?  I'd like to follow this discussion and want 
to know if I should join another group?

Thanks,

Charlie

P.S.  Anyone want to comment on their expectation for 'commercial' 
databases handling this load?  I know that we cannot speak about 
specific performance metrics on some products (licensing restrictions) 
but I'd be curious if folks have seen some of the databases out there 
handle these dataset sizes and respond resonably.


Shridhar Daithankar wrote:

>Hi,
>
>Today we concluded test for database performance. Attached are results and the 
>schema, for those who have missed earlier discussion on this.
>
>We have (almost) decided that we will partition the data across machines. The 
>theme is, after every some short interval a burst of data will be entered in 
>new table in database, indexed and vacuume. The table(s) will be inherited so 
>that query on base table will fetch results from all the children. The 
>application has to consolidate all the data per node basis. If the database is 
>not postgresql, app. has to consolidate data across partitions as well.
>
>Now we need to investigate whether selecting on base table to include children 
>would use indexes created on children table.
>
>It's estimated that when entire data is gathered, total number of children 
>tables would be around 1K-1.1K across all machines. 
>
>This is in point of average rate of data insertion i.e. 5K records/sec and 
>total data size, estimated to be 9 billion rows max i.e. estimated database 
>size is 900GB. Obviously it's impossible to keep insertion rate on an indexed 
>table high as data grows. So partitioning/inheritance looks better approach. 
>
>Postgresql is not the final winner as yet. Mysql is in close range. I will keep 
>you guys posted about the result.
>
>Let me know about any comments..
>
>Bye
> Shridhar
>
>--
>Price's Advice:	It's all a game -- play it to have fun.
>
>
>  
>
>------------------------------------------------------------------------
>
>Machine 								
>Compaq Proliant Server ML 530								
>"Intel Xeon 2.4 Ghz Processor x 4, "								
>"4 GB RAM, 5 x 72.8 GB SCSI HDD "								
>"RAID 0 (Striping) Hardware Setup, Mandrake Linux 9.0"								
>"Cost - $13,500 ($1,350 for each additional 72GB HDD)"								
>								
>Performance Parameter				MySQL 3.23.52  		MySQL 3.23.52  		PostgreSQL 7.2.2  		
>						WITHOUT InnoDB 		WITH InnoDB for 	with built-in support 		
>						for transactional 	transactional support	for transactions
>						support								
>Complete Data								
>								
>Inserts + building a composite index								
>"40 GB data, 432,000,000 tuples"		3738 secs		18720 secs		20628 secs		
>"about 100 bytes each, schema on 
>'schema' sheet"								
>"composite index on 3 fields 
>(esn, min, datetime)"								
>						
>Load Speed					115570 tuples/second	23076 tuples/second	20942 tuples/second
>						
>Database Size on Disk				48 GB			87 GB			111 GB
>						
>Average per partition						
>						
>Inserts + building a composite index						
>"300MB data, 3,000,000 tuples,"			28 secs			130 secs		150 secs
>"about 100 bytes each, schema on 
>'schema' sheet"						
>"composite index on 3 fields 
>(esn, min, datetime)"						
>						
>Select Query  					7 secs			7 secs			6 secs
>based on equality match of 2 fields						
>(esn and min) - 4 concurrent queries 
>running
>						
>Database Size on Disk				341 MB			619 MB			788 MB
>  
>
>------------------------------------------------------------------------
>
>Field Name	Field Type	Nullable	Indexed
>type		int		no		no
>esn		char (10)	no		yes
>min		char (10)	no		yes
>datetime	timestamp	no		yes
>opc0		char (3)	no		no
>opc1		char (3)	no		no
>opc2		char (3)	no		no
>dpc0		char (3)	no		no
>dpc1		char (3)	no		no
>dpc2		char (3)	no		no
>npa		char (3)	no		no
>nxx		char (3)	no		no
>rest		char (4)	no		no
>field0		int		yes		no
>field1		char (4)	yes		no
>field2		int		yes		no
>field3		char (4)	yes		no
>field4		int		yes		no
>field5		char (4)	yes		no
>field6		int		yes		no
>field7		char (4)	yes		no
>field8		int		yes		no
>field9		char (4)	yes		no
>
>  
>
>------------------------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 2: you can get off all lists at once with the unregister command
>    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>  
>

-- 


Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com

Re: Large databases, performance

Nigel J. Andrews <nandrews@investsystems.co.uk> — 2002-10-03T12:56:03Z

Shridhar,

It's one hell of a DB you're building. I'm sure I'm not the only one interested
so to satisfy those of us who are nosey: can you say what the application is?

I'm sure we'll all understand if it's not possible for you mention such
information.


--
Nigel J. Andrews


On Thu, 3 Oct 2002, Shridhar Daithankar wrote:

> Hi,
> 
> Today we concluded test for database performance. Attached are results and the 
> schema, for those who have missed earlier discussion on this.
> 
> We have (almost) decided that we will partition the data across machines. The 
> theme is, after every some short interval a burst of data will be entered in 
> new table in database, indexed and vacuume. The table(s) will be inherited so 
> that query on base table will fetch results from all the children. The 
> application has to consolidate all the data per node basis. If the database is 
> not postgresql, app. has to consolidate data across partitions as well.
> 
> Now we need to investigate whether selecting on base table to include children 
> would use indexes created on children table.
> 
> It's estimated that when entire data is gathered, total number of children 
> tables would be around 1K-1.1K across all machines. 
> 
> This is in point of average rate of data insertion i.e. 5K records/sec and 
> total data size, estimated to be 9 billion rows max i.e. estimated database 
> size is 900GB. Obviously it's impossible to keep insertion rate on an indexed 
> table high as data grows. So partitioning/inheritance looks better approach. 
> 
> Postgresql is not the final winner as yet. Mysql is in close range. I will keep 
> you guys posted about the result.
> 
> Let me know about any comments..
> 
> Bye
>  Shridhar

Re: Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-03T14:03:30Z

On 3 Oct 2002 at 13:56, Nigel J. Andrews wrote:
> It's one hell of a DB you're building. I'm sure I'm not the only one interested
> so to satisfy those of us who are nosey: can you say what the application is?
> 
> I'm sure we'll all understand if it's not possible for you mention such
> information.

Well, I can't tell everything but somethings I can..

1) This is a system that does not have online capability yet. This is an 
attempt to provide one.

2) The goal is to avoid costs like licensing oracle. I am sure this would make 
a great example for OSDB advocacy, which ever database wins..

3) The database size estimates, I put earlier i.e. 9 billion tuples/900GB data 
size, are in a fixed window. The data is generated from some real time systems. 
You can imagine the rate.

4) Further more there are timing restrictions attached to it. 5K inserts/sec. 
4800 queries per hour with response time of 10 sec. each. It's this aspect that 
has forced us for partitioning..

And contrary to my earlier information, this is going to be a live system 
rather than a back up one.. A better win to postgresql.. I hope it makes it.

And BTW, all these results were on reiserfs. We didn't found much of difference 
in write performance between them. So we stick to reiserfs. And of course we 
got the latest hot shot Mandrake9 with 2.4.19-16 which really made difference 
over RHL7.2..

Bye
 Shridhar

--
QOTD:	"Do you smell something burning or is it me?"		-- Joan of Arc

Re: Large databases, performance

Charles H. Woloszynski <chw@clearmetrix.com> — 2002-10-03T14:26:59Z

Forgive my ignorance, but what about 2.4.19-16 is that much faster?  Are 
we talking about 2x improvement for your tests?  We are currently on 
2.4.9 and looking at the performance and wondering... so any comments 
are appreciated.

Charlie

Shridhar Daithankar wrote:

>And BTW, all these results were on reiserfs. We didn't found much of difference 
>in write performance between them. So we stick to reiserfs. And of course we 
>got the latest hot shot Mandrake9 with 2.4.19-16 which really made difference 
>over RHL7.2..
>
>Bye
> Shridhar
>
>--
>QOTD:	"Do you smell something burning or is it me?"		-- Joan of Arc
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>  
>

-- 

Charles H. Woloszynski

ClearMetrix, Inc.
115 Research Drive
Bethlehem, PA 18015

tel: 610-419-2210 x400
fax: 240-371-3256
web: www.clearmetrix.com

Re: Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-03T15:50:16Z

On 3 Oct 2002 at 10:26, Charles H. Woloszynski wrote:

> Forgive my ignorance, but what about 2.4.19-16 is that much faster?  Are 
> we talking about 2x improvement for your tests?  We are currently on 
> 2.4.9 and looking at the performance and wondering... so any comments 
> are appreciated.

Well, for one thing, 2.4.19 contains backported O(1) scheduler patch which 
improves SMP performance by heaps as task queue is per cpu rather than one per 
system. I don't think any system routinely runs thousands of processes unless 
it's a web/ftp/mail server. In that case improved scheduling wuld help as 
well..

Besides there were major VM rewrites/changes after 2.4.10 which corrected 
almost all the major VM fiaskos on linux. For anything VM intensive it's 
recommended that you run 2.4.17 at least.

I would say it's worth going for it.

Bye
 Shridhar

--
Sturgeon's Law:	90% of everything is crud.

Re: Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-03T15:56:43Z

On 3 Oct 2002 at 19:33, Shridhar Daithankar wrote:

> On 3 Oct 2002 at 13:56, Nigel J. Andrews wrote:
> > It's one hell of a DB you're building. I'm sure I'm not the only one interested
> > so to satisfy those of us who are nosey: can you say what the application is?
> > 
> > I'm sure we'll all understand if it's not possible for you mention such
> > information.
> 
> Well, I can't tell everything but somethings I can..
> 
> 1) This is a system that does not have online capability yet. This is an 
> attempt to provide one.
> 
> 2) The goal is to avoid costs like licensing oracle. I am sure this would make 
> a great example for OSDB advocacy, which ever database wins..
> 
> 3) The database size estimates, I put earlier i.e. 9 billion tuples/900GB data 
> size, are in a fixed window. The data is generated from some real time systems. 
> You can imagine the rate.

Read that fixed time window..

> 
> 4) Further more there are timing restrictions attached to it. 5K inserts/sec. 
> 4800 queries per hour with response time of 10 sec. each. It's this aspect that 
> has forced us for partitioning..
> 
> And contrary to my earlier information, this is going to be a live system 
> rather than a back up one.. A better win to postgresql.. I hope it makes it.
> 
> And BTW, all these results were on reiserfs. We didn't found much of difference 
> in write performance between them. So we stick to reiserfs. And of course we 
> got the latest hot shot Mandrake9 with 2.4.19-16 which really made difference 
> over RHL7.2..

Well, we were comparing ext3 v/s reiserfs. I don't remember the journalling 
mode of ext3 but we did a 10 GB write test. Besides converting the RAID to RAID-
0 from RAID-5 might have something to do about it.

There was a discussion on hackers some time back as in which file system is 
better. I hope this might have an addition over it..


Bye
 Shridhar

--
	"What terrible way to die."	"There are no good ways."		-- Sulu and Kirk, "That 
Which Survives", stardate unknown

Re: Large databases, performance

Robert Treat <xzilla@users.sourceforge.net> — 2002-10-03T15:57:29Z

NOTE: Setting follow up to the performance list

Funny that the status quo seems to be if you need fast selects on data
that has few inserts to pick mysql, otherwise if you have a lot of
inserts and don't need super fast selects go with PostgreSQL; yet your
data seems to cut directly against this. 

I'm curious, did you happen to run the select tests while also running
the insert tests? IIRC the older mysql versions have to lock the table
when doing the insert, so select performance goes in the dumper in that
scenario, perhaps that's not an issue with 3.23.52? 

It also seems like the vacuum after each insert is unnecessary, unless
your also deleting/updating data behind it. Perhaps just running an
ANALYZE on the table would suffice while reducing overhead.

Robert Treat

On Thu, 2002-10-03 at 08:36, Shridhar Daithankar wrote:
> Machine 								
> Compaq Proliant Server ML 530								
> "Intel Xeon 2.4 Ghz Processor x 4, "								
> "4 GB RAM, 5 x 72.8 GB SCSI HDD "								
> "RAID 0 (Striping) Hardware Setup, Mandrake Linux 9.0"								
> "Cost - $13,500 ($1,350 for each additional 72GB HDD)"								
> 								
> Performance Parameter				MySQL 3.23.52  		MySQL 3.23.52  		PostgreSQL 7.2.2  		
> 						WITHOUT InnoDB 		WITH InnoDB for 	with built-in support 		
> 						for transactional 	transactional support	for transactions
> 						support								
> Complete Data								
> 								
> Inserts + building a composite index								
> "40 GB data, 432,000,000 tuples"		3738 secs		18720 secs		20628 secs		
> "about 100 bytes each, schema on 
> 'schema' sheet"								
> "composite index on 3 fields 
> (esn, min, datetime)"								
> 						
> Load Speed					115570 tuples/second	23076 tuples/second	20942 tuples/second
> 						
> Database Size on Disk				48 GB			87 GB			111 GB
> 						
> Average per partition						
> 						
> Inserts + building a composite index						
> "300MB data, 3,000,000 tuples,"			28 secs			130 secs		150 secs
> "about 100 bytes each, schema on 
> 'schema' sheet"						
> "composite index on 3 fields 
> (esn, min, datetime)"						
> 						
> Select Query  					7 secs			7 secs			6 secs
> based on equality match of 2 fields						
> (esn and min) - 4 concurrent queries 
> running
> 						
> Database Size on Disk				341 MB			619 MB			788 MB
> ----

Re: Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-03T16:07:55Z

On 3 Oct 2002 at 8:54, Charles H. Woloszynski wrote:

> Can you comment on the tools you are using to do the insertions (Perl, 
> Java?) and the distribution of data (all random, all static), and the 
> transaction scope (all inserts in one transaction, each insert as a 
> single transaction, some group of inserts as a transaction).

Most proably it's all inserts in one transaction spread almost uniformly over 
around 15-20 tables. Of course there will be bunch of transactions..

> I'd be curious what happens when you submit more queries than you have 
> processors (you had four concurrent queries and four CPUs), if you care 
> to run any additional tests.  Also, I'd report the query time in 
> absolute (like you did) and also in 'Time/number of concurrent queries". 
>  This will give you a sense of how the system is scaling as the workload 
> increases.  Personally I am more concerned about this aspect than the 
> load time, since I am going to guess that this is where all the time is 
> spent.  

I don't think so. Because we plan to put enough shared buffers that would 
almost contain the indexes in RAM if not data. Besides number of tuples 
expected per query are not many. So more concurrent queries are not going to 
hog anything other than CPU power at most.

Our major concern remains load time as data is generated in real time and is 
expecetd in database with in specified time period. We need indexes for query 
and inserting into indexed table is on hell of a job. We did attempt inserting 
8GB of data in indexed table. It took almost 20 hours at 1K tuples per second 
on average.. Though impressive it's not acceptable for that load..
> 
> Was the original posting on GENERAL or HACKERS.  Is this moving the 
> PERFORMANCE for follow-up?  I'd like to follow this discussion and want 
> to know if I should join another group?

Shall I subscribe to performance?  What's the exat list name? Benchmarks? I 
don't see anything as performance mailing list on  this page..
http://developer.postgresql.org/mailsub.php?devlp

> P.S.  Anyone want to comment on their expectation for 'commercial' 
> databases handling this load?  I know that we cannot speak about 
> specific performance metrics on some products (licensing restrictions) 
> but I'd be curious if folks have seen some of the databases out there 
> handle these dataset sizes and respond resonably.

Well, if something handles such kind of data with single machine and costs 
under USD20K for entire setup, I would be willing to recommend that to client..

BTW we are trying same test on HP-UX. I hope we get some better figures on 64 
bit machines..

Bye
 Shridhar

--
Clarke's Conclusion:	Never let your sense of morals interfere with doing the 
right thing.

Re: [HACKERS] Large databases, performance

Justin Clift <justin@postgresql.org> — 2002-10-03T16:16:06Z

Shridhar Daithankar wrote:
<snip>
> > Was the original posting on GENERAL or HACKERS.  Is this moving the
> > PERFORMANCE for follow-up?  I'd like to follow this discussion and want
> > to know if I should join another group?
> 
> Shall I subscribe to performance?  What's the exat list name? Benchmarks? I
> don't see anything as performance mailing list on  this page..
> http://developer.postgresql.org/mailsub.php?devlp

It's a fairly new mailing list.  :)

pgsql-performance@postgresql.org

Easiest way to subscribe is by emailing majordomo@postgresql.org with:

subscribe pgsql-performance

as the message body.

:-)

Regards and best wishes,

Justin Clift

<snip> 
> Bye
>  Shridhar
> 
> --
> Clarke's Conclusion:    Never let your sense of morals interfere with doing the
> right thing.
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

Re: Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-03T16:17:03Z

On 3 Oct 2002 at 11:57, Robert Treat wrote:

> NOTE: Setting follow up to the performance list
> 
> Funny that the status quo seems to be if you need fast selects on data
> that has few inserts to pick mysql, otherwise if you have a lot of
> inserts and don't need super fast selects go with PostgreSQL; yet your
> data seems to cut directly against this. 

Well, couple of things..

The number of inserts aren't few. it's 5000/sec.required in the field Secondly 
I don't know really but postgresql seems doing pretty fine in parallel selects. 
If we use mysql with transaction support then numbers are really close..

May be it's time to rewrite famous myth that postgresql is slow. When properly 
tuned or given enough head room, it's almost as fast as mysql..

> I'm curious, did you happen to run the select tests while also running
> the insert tests? IIRC the older mysql versions have to lock the table
> when doing the insert, so select performance goes in the dumper in that
> scenario, perhaps that's not an issue with 3.23.52? 

IMO even if it locks tables that shouldn't affect select performance. It would 
be fun to watch when we insert multiple chunks of data and fire queries 
concurrently. I would be surprised if mysql starts slowing down..

> It also seems like the vacuum after each insert is unnecessary, unless
> your also deleting/updating data behind it. Perhaps just running an
> ANALYZE on the table would suffice while reducing overhead.

I believe that was vacuum analyze only. But still it takes lot of time. Good 
thing is it's not blocking..

Anyway I don't think such frequent vacuums are going to convince planner to 
choose index scan over sequential scan. I am sure it's already convinced..

Regards,
 Shridhar

-----------------------------------------------------------
Shridhar Daithankar
LIMS CPE Team Member, PSPL.
mailto:shridhar_daithankar@persistent.co.in
Phone:- +91-20-5678900 Extn.270
Fax  :- +91-20-5678901 
-----------------------------------------------------------

Re: [HACKERS] Large databases, performance

Greg Copeland <greg@copelandconsulting.net> — 2002-10-03T16:23:28Z

On Thu, 2002-10-03 at 10:56, Shridhar Daithankar wrote:
> Well, we were comparing ext3 v/s reiserfs. I don't remember the journalling 
> mode of ext3 but we did a 10 GB write test. Besides converting the RAID to RAID-
> 0 from RAID-5 might have something to do about it.
> 
> There was a discussion on hackers some time back as in which file system is 
> better. I hope this might have an addition over it..

Hmm.  Reiserfs' claim to fame is it's low latency with many, many small
files and that it's journaled.  I've never seem anyone comment about it
being considered an extremely fast file system in an general computing
context nor have I seen any even hint at it as a file system for use in
heavy I/O databases.  This is why Reiserfs is popular with news and
squid cache servers as it's almost an ideal fit.  That is, tons of small
files or directories contained within a single directory.  As such, I'm
very surprised that reiserfs is even in the running for your comparison.

Might I point you toward XFS, JFS, or ext3, ?  As I understand it, XFS
and JFS are going to be your preferred file systems for for this type of
application with XFS in the lead as it's tool suite is very rich and
robust.  I'm actually lacking JFS experience but from what I've read,
it's a notch or two back from XFS in robustness (assuming we are talking
Linux here).  Feel free to read and play to find out for your self.  I'd
recommend that you start playing with XFS to see how the others
compare.  After all, XFS' specific claim to fame is high throughput w/
low latency on large and very large files.  Furthermore, they even have
a real time mechanism that you can further play with to see how it
effects your throughput and/or latencies.

Greg

Re: Large databases, performance

Robert Treat <xzilla@users.sourceforge.net> — 2002-10-03T16:26:34Z

On Thu, 2002-10-03 at 12:17, Shridhar Daithankar wrote:
> On 3 Oct 2002 at 11:57, Robert Treat wrote:
> May be it's time to rewrite famous myth that postgresql is slow. 

That myth has been dis-proven long ago, it just takes awhile for
everyone to catch on ;-)

When properly 
> tuned or given enough head room, it's almost as fast as mysql..
> 
> > I'm curious, did you happen to run the select tests while also running
> > the insert tests? IIRC the older mysql versions have to lock the table
> > when doing the insert, so select performance goes in the dumper in that
> > scenario, perhaps that's not an issue with 3.23.52? 
> 
> IMO even if it locks tables that shouldn't affect select performance. It would 
> be fun to watch when we insert multiple chunks of data and fire queries 
> concurrently. I would be surprised if mysql starts slowing down..
> 

Hmm... been awhile since I dug into mysql internals, but IIRC once the
table was locked, you had to wait for the insert to complete so the
table would be unlocked and the select could go through. (maybe this is
a myth that I need to get clued in on)

> > It also seems like the vacuum after each insert is unnecessary, unless
> > your also deleting/updating data behind it. Perhaps just running an
> > ANALYZE on the table would suffice while reducing overhead.
> 
> I believe that was vacuum analyze only. But still it takes lot of time. Good 
> thing is it's not blocking..
> 
> Anyway I don't think such frequent vacuums are going to convince planner to 
> choose index scan over sequential scan. I am sure it's already convinced..
> 

My thinking was that if your just doing inserts, you need to update the
statistics but don't need to check on unused tuples. 

Robert Treat

Re: [HACKERS] Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-03T16:30:18Z

On 3 Oct 2002 at 11:23, Greg Copeland wrote:

> On Thu, 2002-10-03 at 10:56, Shridhar Daithankar wrote:
> > Well, we were comparing ext3 v/s reiserfs. I don't remember the journalling 
> > mode of ext3 but we did a 10 GB write test. Besides converting the RAID to RAID-
> > 0 from RAID-5 might have something to do about it.
> > 
> > There was a discussion on hackers some time back as in which file system is 
> > better. I hope this might have an addition over it..
> 
> 
> Hmm.  Reiserfs' claim to fame is it's low latency with many, many small
> files and that it's journaled.  I've never seem anyone comment about it
> being considered an extremely fast file system in an general computing
> context nor have I seen any even hint at it as a file system for use in
> heavy I/O databases.  This is why Reiserfs is popular with news and
> squid cache servers as it's almost an ideal fit.  That is, tons of small
> files or directories contained within a single directory.  As such, I'm
> very surprised that reiserfs is even in the running for your comparison.
> 
> Might I point you toward XFS, JFS, or ext3, ?  As I understand it, XFS
> and JFS are going to be your preferred file systems for for this type of
> application with XFS in the lead as it's tool suite is very rich and
> robust.  I'm actually lacking JFS experience but from what I've read,
> it's a notch or two back from XFS in robustness (assuming we are talking
> Linux here).  Feel free to read and play to find out for your self.  I'd
> recommend that you start playing with XFS to see how the others
> compare.  After all, XFS' specific claim to fame is high throughput w/
> low latency on large and very large files.  Furthermore, they even have
> a real time mechanism that you can further play with to see how it
> effects your throughput and/or latencies.

I would try that. Once we are thr. with tests at our hands..

Bye
 Shridhar

--
	"The combination of a number of things to make existence worthwhile."	"Yes, 
the philosophy of 'none,' meaning 'all.'"		-- Spock and Lincoln, "The Savage 
Curtain", stardate 5906.4

Re: Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-03T16:35:24Z

On 3 Oct 2002 at 12:26, Robert Treat wrote:

> On Thu, 2002-10-03 at 12:17, Shridhar Daithankar wrote:
> > On 3 Oct 2002 at 11:57, Robert Treat wrote:
> > May be it's time to rewrite famous myth that postgresql is slow. 
> 
> That myth has been dis-proven long ago, it just takes awhile for
> everyone to catch on ;-)

:-)

> Hmm... been awhile since I dug into mysql internals, but IIRC once the
> table was locked, you had to wait for the insert to complete so the
> table would be unlocked and the select could go through. (maybe this is
> a myth that I need to get clued in on)

If that turns out to be true, I guess mysql will nose dive out of window.. May 
be time to run a test that's nearer to real world expectation, especially in 
terms on concurrency..

I don't think tat will be an issue with mysql with transaction support. The 
vanilla one might suffer.. Not the other one.. At least theoretically..

> My thinking was that if your just doing inserts, you need to update the
> statistics but don't need to check on unused tuples. 

Any other way of doing that other than vacuum analyze? I thought that was the 
only way..

Bye
 Shridhar

--
"Even more amazing was the realization that God has Internet access.  Iwonder 
if He has a full newsfeed?"(By Matt Welsh)

Re: Large databases, performance

Manfred Koizar <mkoi-pg@aon.at> — 2002-10-03T16:44:09Z

On Thu, 03 Oct 2002 18:06:10 +0530, "Shridhar Daithankar"
<shridhar_daithankar@persistent.co.in> wrote:
>Machine 								
>Compaq Proliant Server ML 530								
>"Intel Xeon 2.4 Ghz Processor x 4, "								
>"4 GB RAM, 5 x 72.8 GB SCSI HDD "								
>"RAID 0 (Striping) Hardware Setup, Mandrake Linux 9.0"

Shridhar,

forgive me if I ask what has been said before:  Did you run at 100%
CPU or was IO bandwidth your limit?  And is the answer the same for
all three configurations?

Servus
 Manfred

Re: [HACKERS] Large databases, performance

Hans-Jürgen Schönig <postgres@cybertec.at> — 2002-10-03T16:51:05Z

Shridhar Daithankar wrote:

>On 3 Oct 2002 at 11:57, Robert Treat wrote:
>
>  
>
>>NOTE: Setting follow up to the performance list
>>
>>Funny that the status quo seems to be if you need fast selects on data
>>that has few inserts to pick mysql, otherwise if you have a lot of
>>inserts and don't need super fast selects go with PostgreSQL; yet your
>>data seems to cut directly against this. 
>>    
>>
>
>Well, couple of things..
>
>The number of inserts aren't few. it's 5000/sec.required in the field Secondly 
>I don't know really but postgresql seems doing pretty fine in parallel selects. 
>If we use mysql with transaction support then numbers are really close..
>
>May be it's time to rewrite famous myth that postgresql is slow. When properly 
>tuned or given enough head room, it's almost as fast as mysql..
>  
>

In the case of concurrent transactions MySQL does not do as well due to 
very bad locking behavious. PostgreSQL is far better because it does row 
level locking instead of table locking.
If you have many concurrent transactions MySQL performs some sort of 
"self-denial-of-service". I'd choose PostgreSQL in order to make sure 
that the database does not block.

>>I'm curious, did you happen to run the select tests while also running
>>the insert tests? IIRC the older mysql versions have to lock the table
>>when doing the insert, so select performance goes in the dumper in that
>>scenario, perhaps that's not an issue with 3.23.52? 
>>    
>>
>
>IMO even if it locks tables that shouldn't affect select performance. It would 
>be fun to watch when we insert multiple chunks of data and fire queries 
>concurrently. I would be surprised if mysql starts slowing down..
>  
>

In the case of concurrent SELECTs and INSERT/UPDATE/DELETE operations 
MySQL will slow down for sure. The more concurrent transactions you have 
the worse MySQL will be.

>>It also seems like the vacuum after each insert is unnecessary, unless
>>your also deleting/updating data behind it. Perhaps just running an
>>ANALYZE on the table would suffice while reducing overhead.
>>    
>>
>
>I believe that was vacuum analyze only. But still it takes lot of time. Good 
>thing is it's not blocking..
>
>Anyway I don't think such frequent vacuums are going to convince planner to 
>choose index scan over sequential scan. I am sure it's already convinced..
>  
>

PostgreSQL allows you to improve execution plans by giving the planner a 
hint.
In addition to that: if you need REAL performance and if you are running 
similar queries consider using SPI.

Also: 7.3 will support PREPARE/EXECUTE.

If you are running MySQL you will not be able to add features to the 
database easily.
In the case of PostgreSQL you have a broad range of simple interfaces 
which make many things pretty simple (eg. optimized data types in < 50 
lines of C code).

PostgreSQL is the database of the future and you can perform a lot of 
tuning.
MySQL is a simple frontend to a filesystem and it is fast as long as you 
are doing SELECT 1+1 operations.

Also: Keep in mind that PostgreSQL has a wonderful core team. MySQL is 
built on Monty Widenius and the core team = Monty.
Also: PostgreSQL = ANSI compilant, MySQL = Monty compliant

In the past few years I have seen that there is no database system which 
can beat PostgreSQL's flexibility and stability.
I am familiar with various database systems but believe: PostgreSQL is 
the best choice.

    Hans

>Regards,
> Shridhar
>
>-----------------------------------------------------------
>Shridhar Daithankar
>LIMS CPE Team Member, PSPL.
>mailto:shridhar_daithankar@persistent.co.in
>Phone:- +91-20-5678900 Extn.270
>Fax  :- +91-20-5678901 
>-----------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 6: Have you searched our list archives?
>
>http://archives.postgresql.org
>  
>

-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at 
<http://cluster.postgresql.at>, www.cybertec.at 
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>

Re: [HACKERS] Large databases, performance

Manfred Koizar <mkoi-pg@aon.at> — 2002-10-03T16:53:32Z

On Thu, 03 Oct 2002 21:47:03 +0530, "Shridhar Daithankar"
<shridhar_daithankar@persistent.co.in> wrote:
>I believe that was vacuum analyze only.

Well there is

	VACUUM [tablename]; 

and there is

	ANALYZE [tablename];

And

	VACUUM ANALYZE [tablename];

is VACUUM followed by ANALYZE.

Servus
 Manfred

Re: Large databases, performance

Ron Johnson <ron.l.johnson@cox.net> — 2002-10-03T17:38:49Z

On Thu, 2002-10-03 at 11:17, Shridhar Daithankar wrote:
> On 3 Oct 2002 at 11:57, Robert Treat wrote:
> 
[snip]
> > I'm curious, did you happen to run the select tests while also running
> > the insert tests? IIRC the older mysql versions have to lock the table
> > when doing the insert, so select performance goes in the dumper in that
> > scenario, perhaps that's not an issue with 3.23.52? 
> 
> IMO even if it locks tables that shouldn't affect select performance. It would 
> be fun to watch when we insert multiple chunks of data and fire queries 
> concurrently. I would be surprised if mysql starts slowing down..

What kind of lock?  Shared lock or exclusive lock?  If SELECT
performance tanked when doing simultaneous INSERTs, then maybe there
were exclusive table locks.

-- 
+------------------------------------------------------------+
| Ron Johnson, Jr.     mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson  |
|                                                            |
| "What other evidence do you have that they are terrorists, |
|  other than that they trained in these camps?"             |
|   17-Sep-2002 Katie Couric to an FBI agent regarding the 5 |
|   men arrested near Buffalo NY                             |
+------------------------------------------------------------+

Re: [HACKERS] Large databases, performance

Ron Johnson <ron.l.johnson@cox.net> — 2002-10-03T20:55:35Z

On Thu, 2002-10-03 at 11:51, Hans-Jürgen Schönig wrote:
> Shridhar Daithankar wrote:
> 
> >On 3 Oct 2002 at 11:57, Robert Treat wrote:
[snip]
> PostgreSQL allows you to improve execution plans by giving the planner a 
> hint.
> In addition to that: if you need REAL performance and if you are running 
> similar queries consider using SPI.

What is SPI?

-- 
+------------------------------------------------------------+
| Ron Johnson, Jr.     mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson  |
|                                                            |
| "What other evidence do you have that they are terrorists, |
|  other than that they trained in these camps?"             |
|   17-Sep-2002 Katie Couric to an FBI agent regarding the 5 |
|   men arrested near Buffalo NY                             |
+------------------------------------------------------------+

Re: [HACKERS] Large databases, performance

Andrew Sullivan <andrew@libertyrms.info> — 2002-10-03T21:09:20Z

On Thu, Oct 03, 2002 at 06:51:05PM +0200, Hans-J?rgen Sch?nig wrote:

> In the case of concurrent transactions MySQL does not do as well due to 
> very bad locking behavious. PostgreSQL is far better because it does row 
> level locking instead of table locking.

It is my understanding that MySQL no longer does this on InnoDB
tables.  Whether various bag-on-the-side table types are a good thing
I will leave to others; but there's no reason to go 'round making
claims about old versions of MySQL any more than there is a reason to
continue to talk about PostgreSQL not being crash safe.  MySQL has
moved along nearly as quickly as PostgreSQL. 

A

-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Liberty RMS                           Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110

use [PERF] instead of

Jean-Luc Lachance <jllachan@nsd.ca> — 2002-10-03T21:12:02Z

May I suggest that instead of [pgsql-performance] that [PERF] be used to
save some of the subject line.

Ron Johnson wrote:
> 
> On Thu, 2002-10-03 at 11:51, Hans-Jürgen Schönig wrote:
> > Shridhar Daithankar wrote:
> >
> > >On 3 Oct 2002 at 11:57, Robert Treat wrote:
> [snip]
> > PostgreSQL allows you to improve execution plans by giving the planner a
> > hint.
> > In addition to that: if you need REAL performance and if you are running
> > similar queries consider using SPI.
> 
> What is SPI?
> 
> --
> +------------------------------------------------------------+
> | Ron Johnson, Jr.     mailto:ron.l.johnson@cox.net          |
> | Jefferson, LA  USA   http://members.cox.net/ron.l.johnson  |
> |                                                            |
> | "What other evidence do you have that they are terrorists, |
> |  other than that they trained in these camps?"             |
> |   17-Sep-2002 Katie Couric to an FBI agent regarding the 5 |
> |   men arrested near Buffalo NY                             |
> +------------------------------------------------------------+
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Re: [HACKERS] Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-04T08:00:54Z

On 3 Oct 2002 at 18:53, Manfred Koizar wrote:

> On Thu, 03 Oct 2002 21:47:03 +0530, "Shridhar Daithankar"
> <shridhar_daithankar@persistent.co.in> wrote:
> >I believe that was vacuum analyze only.
> 
> Well there is
> 
> 	VACUUM [tablename]; 
> 
> and there is
> 
> 	ANALYZE [tablename];
> 
> And
> 
> 	VACUUM ANALYZE [tablename];
> 
> is VACUUM followed by ANALYZE.

I was using vacuum analyze. 

Good that you pointed out. Now I will modify the postgresql auto vacuum daemon 
that I wrote to analyze only in case of excesive inserts. I hope that's lighter 
on performance compared to vacuum analyze..

Bye
 Shridhar

--
Mix's Law:	There is nothing more permanent than a temporary building.	There is 
nothing more permanent than a temporary tax.

Re: [HACKERS] Large databases, performance

scott.marlowe <scott.marlowe@ihs.com> — 2002-10-04T16:05:10Z

On Thu, 3 Oct 2002, Hans-Jürgen Schönig wrote:

> In the case of concurrent transactions MySQL does not do as well due to 
> very bad locking behavious. PostgreSQL is far better because it does row 
> level locking instead of table locking.
> If you have many concurrent transactions MySQL performs some sort of 
> "self-denial-of-service". I'd choose PostgreSQL in order to make sure 
> that the database does not block.

While I'm no big fan of MySQL, I must point out that with innodb tables, 
the locking is row level, and the ability to handle parallel read / write 
is much improved.

Also, Postgresql does NOT use row level locking, it uses MVCC, which is 
"better than row level locking" as Tom puts it.

Of course, hot backup is only 2,000 Euros for an innodb table mysql, while 
hot backup for postgresql is free. :-)

That said, MySQL still doesn't handle parallel load nearly as well as 
postgresql, it's just better than it once was.

> Also: Keep in mind that PostgreSQL has a wonderful core team. MySQL is 
> built on Monty Widenius and the core team = Monty.
> Also: PostgreSQL = ANSI compilant, MySQL = Monty compliant

This is a very valid point.  The "committee" that creates and steers 
Postgresql is very much a meritocracy.  The "committee" that steers MySQL 
is Monty.  

I'm much happier knowing that every time something important needs to be 
done we have a whole cupboard full of curmudgeons arguing the fine points 
so that the "right thing" gets done.

Re: [HACKERS] Large databases, performance

Hans-Jürgen Schönig <hs@cybertec.at> — 2002-10-04T16:30:47Z

MVCC = great ...
I know that is not row level locking but that's the way things can be 
explained more easily. Many people are asking my how things work and 
this way it is easier to understand. Never tell a trainee about deadlock 
detection and co *g*.

I am happy that the PostgreSQL core team + all developers are not like 
Monty ...
I am happy to PostgreSQL has developers such as Bruce, Tom, Jan, Marc, 
Vadim, Joe, Neil, Christopher, etc. (just to name a few) ...

Yes, it is said to be better than it was but that's not the point:
MySQL = Monty SQL <> ANSI SQL ...

Believe me, the table will turn and finally the better system will succeed.
One we have clustering, PITR, etc. running people will see how real 
databases work :).

    Hans



scott.marlowe wrote:

>On Thu, 3 Oct 2002, Hans-Jürgen Schönig wrote:
>
>  
>
>>In the case of concurrent transactions MySQL does not do as well due to 
>>very bad locking behavious. PostgreSQL is far better because it does row 
>>level locking instead of table locking.
>>If you have many concurrent transactions MySQL performs some sort of 
>>"self-denial-of-service". I'd choose PostgreSQL in order to make sure 
>>that the database does not block.
>>    
>>
>
>While I'm no big fan of MySQL, I must point out that with innodb tables, 
>the locking is row level, and the ability to handle parallel read / write 
>is much improved.
>
>Also, Postgresql does NOT use row level locking, it uses MVCC, which is 
>"better than row level locking" as Tom puts it.
>
>Of course, hot backup is only 2,000 Euros for an innodb table mysql, while 
>hot backup for postgresql is free. :-)
>
>That said, MySQL still doesn't handle parallel load nearly as well as 
>postgresql, it's just better than it once was.
>
>  
>
>>Also: Keep in mind that PostgreSQL has a wonderful core team. MySQL is 
>>built on Monty Widenius and the core team = Monty.
>>Also: PostgreSQL = ANSI compilant, MySQL = Monty compliant
>>    
>>
>
>This is a very valid point.  The "committee" that creates and steers 
>Postgresql is very much a meritocracy.  The "committee" that steers MySQL 
>is Monty.  
>
>I'm much happier knowing that every time something important needs to be 
>done we have a whole cupboard full of curmudgeons arguing the fine points 
>so that the "right thing" gets done.
>  
>

-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at 
<http://cluster.postgresql.at>, www.cybertec.at 
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>

Re: Large databases, performance

Michael Paesold <mpaesold@gmx.at> — 2002-10-04T16:38:21Z

Andrew Sullivan <andrew@libertyrms.info> wrote:

> On Thu, Oct 03, 2002 at 06:51:05PM +0200, Hans-J?rgen Sch?nig wrote:
>
> > In the case of concurrent transactions MySQL does not do as well due to
> > very bad locking behavious. PostgreSQL is far better because it does row
> > level locking instead of table locking.
>
> It is my understanding that MySQL no longer does this on InnoDB
> tables.  Whether various bag-on-the-side table types are a good thing
> I will leave to others; but there's no reason to go 'round making
> claims about old versions of MySQL any more than there is a reason to
> continue to talk about PostgreSQL not being crash safe.  MySQL has
> moved along nearly as quickly as PostgreSQL.

Locking and transactions is not fine in MySQL (with InnoDB) though. I tried
to do selects on a table I was concurrently inserting to. In a single thread
I was constantly inserting 1000 rows per transaction. While inserting I did
some random selects on the same table. It often happend that the insert
transactions were aborted due to dead lock problems. There I see the problem
with locking reads.
I like PostgreSQL's MVCC!

Regards,
Michael Paesold

Pinning a table into memory

David Blood <david@matraex.com> — 2002-10-04T16:46:57Z

In Oracle you can Pin large objects into memory to prevent frequent
reloads. Is there anyway to do this with Postgres?  It appears that some
of our tables that get hit a lot may get kicked out of memory when we
access some of our huge tables. Then they have to wait for I/O to get
loaded back in. 


David Blood
Matraex, Inc

Re: Pinning a table into memory

Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-04T18:47:47Z

"David Blood" <david@matraex.com> writes:
> In Oracle you can Pin large objects into memory to prevent frequent
> reloads. Is there anyway to do this with Postgres?

I can never understand why people think this would be a good idea.
If you're hitting a table frequently, it will stay in memory anyway
(either in Postgres shared buffers or kernel disk cache).  If you're
not hitting it frequently enough to keep it swapped in, then whatever
is getting swapped in instead is probably a better candidate to be
occupying the space.  ISTM that a manual "pin this table" knob would
mostly have the effect of making performance worse, whenever the
system activity is slightly different from the situation you had in
mind when you installed the pin.

Having said that, I'll freely concede that our cache management
algorithms could use improvement (and there are people looking at
that right now).  But a manual pin doesn't seem like a better answer.

			regards, tom lane

Re: Large databases, performance

Curt Sampson <cjs@cynic.net> — 2002-10-07T02:27:04Z

On Thu, 3 Oct 2002, Shridhar Daithankar wrote:

> Well, we were comparing ext3 v/s reiserfs. I don't remember the journalling
> mode of ext3 but we did a 10 GB write test. Besides converting the RAID to RAID-
> 0 from RAID-5 might have something to do about it.

That will have a massive, massive effect on performance. Depending on
your RAID subsystem, you can except RAID-0 to be between two and twenty
times as fast for writes as RAID-5.

If you compared one filesystem on RAID-5 and another on RAID-0,
your results are likely not at all indicative of file system
performance.

Note that I've redirected followups to the pgsql-performance list.
Avoiding cross-posting would be nice, since I am getting lots of
duplicate messages these days.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

Re: Large databases, performance

Curt Sampson <cjs@cynic.net> — 2002-10-07T02:30:57Z

On Thu, 3 Oct 2002, Shridhar Daithankar wrote:

> Our major concern remains load time as data is generated in real time and is
> expecetd in database with in specified time period.

If your time period is long enough, you can do what I do, which is
to use partial indexes so that the portion of the data being loaded
is not indexed. That will speed your loads quite a lot. Aftewards
you can either generate another partial index for the range you
loaded, or generate a new index over both old and new data, and
then drop the old index.

The one trick is that the optimizer is not very smart about combining
multiple indexes, so you often need to split your queries across
the two "partitions" of the table that have separate indexes.

> Shall I subscribe to performance?

Yes, you really ought to. The list is pgsql-performance@postgresql.org.

cjs
-- 
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC

cross-posts (was Re: Large databases, performance)

Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-07T03:20:33Z

Curt Sampson <cjs@cynic.net> writes:
> ... Avoiding cross-posting would be nice, since I am getting lots of
> duplicate messages these days.

Cross-posting is a fact of life, and in fact encouraged, on the pg
lists.  I suggest adapting.  Try sending
	set all unique your-email-address
to the PG majordomo server; this sets you up to get only one copy
of each cross-posted message.

			regards, tom lane

Re: Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-07T09:37:29Z

On 3 Oct 2002 at 8:54, Charles H. Woloszynski wrote:

> I'd be curious what happens when you submit more queries than you have 
> processors (you had four concurrent queries and four CPUs), if you care 
> to run any additional tests.  Also, I'd report the query time in 
> absolute (like you did) and also in 'Time/number of concurrent queries". 
>  This will give you a sense of how the system is scaling as the workload 
> increases.  Personally I am more concerned about this aspect than the 
> load time, since I am going to guess that this is where all the time is 
> spent.  

OK. I am back from my cave after some more tests are done. Here are the 
results. I am not repeating large part of it but answering your questions..

Don't ask me how these numbers changed. I am not the person who conducts the 
test neither I have access to the system. Rest(or most ) of the things remains 
same..

MySQL 3.23.52 with innodb transaction support: 

4 concurrent queries 	:-  257.36 ms
40 concurrent queries	:-  35.12 ms

Postgresql 7.2.2 

4 concurrent queries 		:- 257.43 ms
40 concurrent 	queries		:- 41.16 ms

Though I can not report oracle numbers, suffice to say that they fall in 
between these two numbers.

Oracle seems to be hell lot faster than mysql/postgresql to load raw data even 
when it's installed on reiserfs. We plan to run XFS tests later in hope that 
that would improve mysql/postgresql load times. 

In this run postgresql has better load time than mysql/innodb ( 18270 sec v/s 
17031 sec.) Index creation times are faster as well (100 sec v/s 130 sec). 
Don't know what parameters are changed.

Only worry is database size. Postgresql is 111GB v/s 87 GB for mysql. All 
numbers include indexes. This is really going to be a problem when things are 
deployed. Any idea how can it be taken down? 

WAL is out, it's not counted.

Schema optimisation is later issue. Right now all three databases are using 
same schema..

Will it help in this situation if I recompile posgresql with block size say 32K 
rather than 8K default? Will it saev some overhead and offer better performance 
in data load etc?

Will keep you guys updated..

Regards,
 Shridhar

-----------------------------------------------------------
Shridhar Daithankar
LIMS CPE Team Member, PSPL.
mailto:shridhar_daithankar@persistent.co.in
Phone:- +91-20-5678900 Extn.270
Fax  :- +91-20-5678901 
-----------------------------------------------------------

Re: [pgsql-performance] Large databases, performance

Hans-Jürgen Schönig <hs@cybertec.at> — 2002-10-07T10:01:32Z

I wonder if the following changes make a difference:

- compile PostgreSQL with CFLAGS=' -O3 '
- redefine commit delays

also: keep in mind that you might gain a lot of performance by using the 
SPI if you are running many similar queries

try 7.3 - as far as I remeber there is a mechanism which caches recent 
execution plans.
also: some overhead was reduced (tuples, backend startup).

    Hans


>Ok. I am back from my cave after some more tests are done. Here are the 
>results. I am not repeating large part of it but answering your questions..
>
>Don't ask me how these numbers changed. I am not the person who conducts the 
>test neither I have access to the system. Rest(or most ) of the things remains 
>same..
>
>MySQL 3.23.52 with innodb transaction support: 
>
>4 concurrent queries 	:-  257.36 ms
>40 concurrent queries	:-  35.12 ms
>
>Postgresql 7.2.2 
>
>4 concurrent queries 		:- 257.43 ms
>40 concurrent 	queries		:- 41.16 ms
>
>Though I can not report oracle numbers, suffice to say that they fall in 
>between these two numbers.
>
>Oracle seems to be hell lot faster than mysql/postgresql to load raw data even 
>when it's installed on reiserfs. We plan to run XFS tests later in hope that 
>that would improve mysql/postgresql load times. 
>
>In this run postgresql has better load time than mysql/innodb ( 18270 sec v/s 
>17031 sec.) Index creation times are faster as well (100 sec v/s 130 sec). 
>Don't know what parameters are changed.
>
>Only worry is database size. Postgresql is 111GB v/s 87 GB for mysql. All 
>numbers include indexes. This is really going to be a problem when things are 
>deployed. Any idea how can it be taken down? 
>
>WAL is out, it's not counted.
>
>Schema optimisation is later issue. Right now all three databases are using 
>same schema..
>
>Will it help in this situation if I recompile posgresql with block size say 32K 
>rather than 8K default? Will it saev some overhead and offer better performance 
>in data load etc?
>
>Will keep you guys updated..
>
>Regards,
> Shridhar
>
>-----------------------------------------------------------
>Shridhar Daithankar
>LIMS CPE Team Member, PSPL.
>mailto:shridhar_daithankar@persistent.co.in
>Phone:- +91-20-5678900 Extn.270
>Fax  :- +91-20-5678901 
>-----------------------------------------------------------
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>  
>


-- 
*Cybertec Geschwinde u Schoenig*
Ludo-Hartmannplatz 1/14, A-1160 Vienna, Austria
Tel: +43/1/913 68 09; +43/664/233 90 75
www.postgresql.at <http://www.postgresql.at>, cluster.postgresql.at 
<http://cluster.postgresql.at>, www.cybertec.at 
<http://www.cybertec.at>, kernel.cybertec.at <http://kernel.cybertec.at>

Re: [HACKERS] cross-posts (was Re: Large databases,

Larry Rosenman <ler@lerctr.org> — 2002-10-07T11:50:59Z

On Sun, 2002-10-06 at 22:20, Tom Lane wrote:
> Curt Sampson <cjs@cynic.net> writes:
> > ... Avoiding cross-posting would be nice, since I am getting lots of
> > duplicate messages these days.
> 
> Cross-posting is a fact of life, and in fact encouraged, on the pg
> lists.  I suggest adapting.  Try sending
> 	set all unique your-email-address
> to the PG majordomo server; this sets you up to get only one copy
> of each cross-posted message.
That doesn't seem to work any more:

>>>> set all unique ler@lerctr.org
**** The "all" mailing list is not supported at
**** PostgreSQL User Support Lists.

What do I need to send now? 

Marc? 


-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Re: [HACKERS] cross-posts (was Re: Large databases,

Michael Paesold <mpaesold@gmx.at> — 2002-10-07T12:01:25Z

> On Sun, 2002-10-06 at 22:20, Tom Lane wrote:
> > Curt Sampson <cjs@cynic.net> writes:
> > > ... Avoiding cross-posting would be nice, since I am getting lots of
> > > duplicate messages these days.
> >
> > Cross-posting is a fact of life, and in fact encouraged, on the pg
> > lists.  I suggest adapting.  Try sending
> > set all unique your-email-address
> > to the PG majordomo server; this sets you up to get only one copy
> > of each cross-posted message.
> That doesn't seem to work any more:
>
> >>>> set all unique ler@lerctr.org
> **** The "all" mailing list is not supported at
> **** PostgreSQL User Support Lists.
>
> What do I need to send now?
>
> Marc?

it is:
set ALL unique your-email

if you also don't want to get emails that have already been cc'd to you, you
can use:

set ALL eliminatecc your-email

for a full list of set options send:

help set

to majordomo.

Regards,
Michael Paesold

Re: [HACKERS] cross-posts (was Re: Large databases,

Larry Rosenman <ler@lerctr.org> — 2002-10-07T12:04:33Z

On Mon, 2002-10-07 at 07:01, Michael Paesold wrote:
> > On Sun, 2002-10-06 at 22:20, Tom Lane wrote:
> > > Curt Sampson <cjs@cynic.net> writes:
> > > > ... Avoiding cross-posting would be nice, since I am getting lots of
> > > > duplicate messages these days.
> > >
> > > Cross-posting is a fact of life, and in fact encouraged, on the pg
> > > lists.  I suggest adapting.  Try sending
> > > set all unique your-email-address
> > > to the PG majordomo server; this sets you up to get only one copy
> > > of each cross-posted message.
> > That doesn't seem to work any more:
> >
> > >>>> set all unique ler@lerctr.org
> > **** The "all" mailing list is not supported at
> > **** PostgreSQL User Support Lists.
> >
> > What do I need to send now?
> >
> > Marc?
> 
> it is:
> set ALL unique your-email
> 
> if you also don't want to get emails that have already been cc'd to you, you
> can use:
> 
> set ALL eliminatecc your-email
> 
> for a full list of set options send:
> 
> help set
> 
> to majordomo.
Thanks.  That worked great.  (I use Mailman, and didn't realize the ALL
needed to be capitalized. 

LER


-- 
Larry Rosenman                     http://www.lerctr.org/~ler
Phone: +1 972-414-9812                 E-Mail: ler@lerctr.org
US Mail: 1905 Steamboat Springs Drive, Garland, TX 75044-6749

Re: Large databases, performance

Manfred Koizar <mkoi-pg@aon.at> — 2002-10-07T14:10:26Z

On Mon, 07 Oct 2002 15:07:29 +0530, "Shridhar Daithankar"
<shridhar_daithankar@persistent.co.in> wrote:
>Only worry is database size. Postgresql is 111GB v/s 87 GB for mysql. All 
>numbers include indexes. This is really going to be a problem when things are 
>deployed. Any idea how can it be taken down? 

Shridhar,

if i'm not mistaken, a char(n)/varchar(n) column is stored as a 32-bit
integer specifying the length followed by as many characters as the
length tells.  On 32-bit Intel hardware this structure is aligned on a
4-byte boundary.

For your row layout this gives the following sizes (look at the "phys
size" column):

| Field    Field     Null Indexed phys  mini
| Name     Type                   size      
|--------------------------------------------
| type     int        no    no       4     4
| esn      char (10)  no    yes     16    11
| min      char (10)  no    yes     16    11
| datetime timestamp  no    yes      8     8
| opc0     char (3)   no    no       8     4
| opc1     char (3)   no    no       8     4
| opc2     char (3)   no    no       8     4
| dpc0     char (3)   no    no       8     4
| dpc1     char (3)   no    no       8     4
| dpc2     char (3)   no    no       8     4
| npa      char (3)   no    no       8     4
| nxx      char (3)   no    no       8     4
| rest     char (4)   no    no       8     5
| field0   int        yes   no       4     4
| field1   char (4)   yes   no       8     5
| field2   int        yes   no       4     4
| field3   char (4)   yes   no       8     5
| field4   int        yes   no       4     4
| field5   char (4)   yes   no       8     5
| field6   int        yes   no       4     4
| field7   char (4)   yes   no       8     5
| field8   int        yes   no       4     4
| field9   char (4)   yes   no       8     5
|                                 ----- -----
|                                  176   116

Ignoring nulls for now, you have to add 32 bytes for a v7.2 heap tuple
header and 4 bytes for ItemIdData per tuple, ending up with 212 bytes
per tuple or ca. 85 GB heap space for 432000000 tuples.  Depending on
fill factor similar calculations give some 30 GB for your index.

Now if we had a datatype with only one byte for the string length,
char columns could be byte aligned and we'd have column sizes given
under "mini" in the table above.  The columns would have to be
rearranged according to alignment requirements.

Thus 60 bytes per heap tuple and 8 bytes per index tuple could be
saved, resulting in a database size of ~ 85 GB (index included).  And
I bet this would be significantly faster, too.

Hackers, do you think it's possible to hack together a quick and dirty
patch, so that string length is represented by one byte?  IOW can a
database be built that doesn't contain any char/varchar/text value
longer than 255 characters in the catalog?

If I'm not told that this is impossibly, I'd give it a try.  Shridhar,
if such a patch can be made available, would you be willing to test
it?

What can you do right now?  Try using v7.3 beta and creating your
table WITHOUT OIDS.  This saves 8 bytes per tuple; not much, but
better save 4% than nothing.

Servus
 Manfred

Re: Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-07T14:18:31Z

On 7 Oct 2002 at 16:10, Manfred Koizar wrote:
> if i'm not mistaken, a char(n)/varchar(n) column is stored as a 32-bit
> integer specifying the length followed by as many characters as the
> length tells.  On 32-bit Intel hardware this structure is aligned on a
> 4-byte boundary.

That shouldn't be necessary for a char field as space is always pre-allocated. 
Sounds like a possible area of imporvement to me, if that's the case..

> Hackers, do you think it's possible to hack together a quick and dirty
> patch, so that string length is represented by one byte?  IOW can a
> database be built that doesn't contain any char/varchar/text value
> longer than 255 characters in the catalog?

I say if it's a char field, there should be no indicator of length as it's not 
required. Just store those many characters straight ahead..

> 
> If I'm not told that this is impossibly, I'd give it a try.  Shridhar,
> if such a patch can be made available, would you be willing to test
> it?

Sure. But the server machine is not available this week. Some other project is 
using it. So the results won't be out unless at least a week from now.

> What can you do right now?  Try using v7.3 beta and creating your
> table WITHOUT OIDS.  This saves 8 bytes per tuple; not much, but
> better save 4% than nothing.

IIRC there was some header optimisation which saved 4 bytes. So without OIDs 
that should save 8. Would do that as first next thing.

I talked to my friend regarding postgresql surpassing mysql substantially in 
this test. He told me that the last test where postgresql took 23000+/150 sec 
for load/index and mysql took 18,000+/130 index, postgresql was running in 
default configuration. He forgot to copy postgresql.conf to data directory 
after he modified it.

This time results are correct. Postgresql loads data faster, indexes it faster 
and queries in almost same time.. Way to go..

Regards,
 Shridhar

-----------------------------------------------------------
Shridhar Daithankar
LIMS CPE Team Member, PSPL.
mailto:shridhar_daithankar@persistent.co.in
Phone:- +91-20-5678900 Extn.270
Fax  :- +91-20-5678901 
-----------------------------------------------------------

Re: [pgsql-performance] Large databases, performance

Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-07T14:30:37Z

"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> MySQL 3.23.52 with innodb transaction support: 

> 4 concurrent queries 	:-  257.36 ms
> 40 concurrent queries	:-  35.12 ms

> Postgresql 7.2.2 

> 4 concurrent queries 		:- 257.43 ms
> 40 concurrent 	queries		:- 41.16 ms

I find this pretty fishy.  The extreme similarity of the 4-client
numbers seems improbable, from what I know of the two databases.
I suspect your numbers are mostly measuring some non-database-related
overhead --- communications overhead, maybe?

> Only worry is database size. Postgresql is 111GB v/s 87 GB for mysql. All 
> numbers include indexes. This is really going to be a problem when things are
> deployed. Any idea how can it be taken down? 

7.3 should be a little bit better because of Manfred's work on reducing
tuple header size --- if you create your tables WITHOUT OIDS, you should
save 8 bytes per row compared to earlier releases.

			regards, tom lane

Re: [pgsql-performance] Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-07T14:39:55Z

On 7 Oct 2002 at 10:30, Tom Lane wrote:

> "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> > MySQL 3.23.52 with innodb transaction support: 
> 
> > 4 concurrent queries 	:-  257.36 ms
> > 40 concurrent queries	:-  35.12 ms
> 
> > Postgresql 7.2.2 
> 
> > 4 concurrent queries 		:- 257.43 ms
> > 40 concurrent 	queries		:- 41.16 ms
> 
> I find this pretty fishy.  The extreme similarity of the 4-client
> numbers seems improbable, from what I know of the two databases.
> I suspect your numbers are mostly measuring some non-database-related
> overhead --- communications overhead, maybe?

I don't know but three numbers, postgresql/mysql/oracle all are 25x.xx ms. The 
clients were on same machie as of server. So no real area to point at..
> 
> > Only worry is database size. Postgresql is 111GB v/s 87 GB for mysql. All 
> > numbers include indexes. This is really going to be a problem when things are
> > deployed. Any idea how can it be taken down? 
> 
> 7.3 should be a little bit better because of Manfred's work on reducing
> tuple header size --- if you create your tables WITHOUT OIDS, you should
> save 8 bytes per row compared to earlier releases.

Got it..

Bye
 Shridhar

--
Sweater, n.:	A garment worn by a child when its mother feels chilly.

Re: [pgsql-performance] Large databases, performance

Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-07T15:21:57Z

"Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> I say if it's a char field, there should be no indicator of length as
> it's not required. Just store those many characters straight ahead..

Your assumption fails when considering UNICODE or other multibyte
character encodings.

			regards, tom lane

Re: Large databases, performance

Manfred Koizar <mkoi-pg@aon.at> — 2002-10-07T15:22:41Z

On Mon, 07 Oct 2002 19:48:31 +0530, "Shridhar Daithankar"
<shridhar_daithankar@persistent.co.in> wrote:
>I say if it's a char field, there should be no indicator of length as it's not 
>required. Just store those many characters straight ahead..

This is out of reach for a quick hack ...

>Sure. But the server machine is not available this week. Some other project is 
>using it. So the results won't be out unless at least a week from now.

 :-)

>This time results are correct. Postgresql loads data faster, indexes it faster 
>and queries in almost same time.. Way to go..

Great!  And now let's work on making selects faster, too.

Servus
 Manfred

Re: [pgsql-performance] Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-08T05:44:11Z

On 7 Oct 2002 at 11:21, Tom Lane wrote:

> "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> > I say if it's a char field, there should be no indicator of length as
> > it's not required. Just store those many characters straight ahead..
> 
> Your assumption fails when considering UNICODE or other multibyte
> character encodings.

Correct but is it possible to have real char string when database is not 
unicode or when locale defines size of char, to be exact?

In my case varchar does not make sense as all strings are guaranteed to be of 
defined length. While the argument you have put is correct, it's causing a disk 
space leak, to say so.

Bye
 Shridhar

--
Boucher's Observation:	He who blows his own horn always plays the music	several 
octaves higher than originally written.

Re: [pgsql-performance] Large databases, performance

Martijn van Oosterhout <kleptog@svana.org> — 2002-10-08T07:20:47Z

On Tue, Oct 08, 2002 at 11:14:11AM +0530, Shridhar Daithankar wrote:
> On 7 Oct 2002 at 11:21, Tom Lane wrote:
> 
> > "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> > > I say if it's a char field, there should be no indicator of length as
> > > it's not required. Just store those many characters straight ahead..
> > 
> > Your assumption fails when considering UNICODE or other multibyte
> > character encodings.
> 
> Correct but is it possible to have real char string when database is not 
> unicode or when locale defines size of char, to be exact?
> 
> In my case varchar does not make sense as all strings are guaranteed to be of 
> defined length. While the argument you have put is correct, it's causing a disk 
> space leak, to say so.

Well, maybe. But since 7.1 or so char() and varchar() simply became text
with some length restrictions. This was one of the reasons. It also
simplified a lot of code.
-- 
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> There are 10 kinds of people in the world, those that can do binary
> arithmetic and those that can't.

Re: Pinning a table into memory

Jan Wieck <janwieck@yahoo.com> — 2002-10-08T13:32:50Z

Tom Lane wrote:
> 
> "David Blood" <david@matraex.com> writes:
> > In Oracle you can Pin large objects into memory to prevent frequent
> > reloads. Is there anyway to do this with Postgres?
> 
> I can never understand why people think this would be a good idea.
> If you're hitting a table frequently, it will stay in memory anyway
> (either in Postgres shared buffers or kernel disk cache).  If you're
> not hitting it frequently enough to keep it swapped in, then whatever
> is getting swapped in instead is probably a better candidate to be
> occupying the space. 

As I understand it, he's looking for a mechanism to prevent a single
sequential scan on a table, larger than the buffer cache, to kick out
everything else at once. But I agree with you that pinning other objects
is just mucking with the symptoms instead of curing the desease.

Jan

-- 

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #

Re: [GENERAL] Large databases, performance

Ron Johnson <ron.l.johnson@cox.net> — 2002-10-08T13:50:52Z

On Tue, 2002-10-08 at 02:20, Martijn van Oosterhout wrote:
> On Tue, Oct 08, 2002 at 11:14:11AM +0530, Shridhar Daithankar wrote:
> > On 7 Oct 2002 at 11:21, Tom Lane wrote:
> > 
> > > "Shridhar Daithankar" <shridhar_daithankar@persistent.co.in> writes:
> > > > I say if it's a char field, there should be no indicator of length as
> > > > it's not required. Just store those many characters straight ahead..
> > > 
> > > Your assumption fails when considering UNICODE or other multibyte
> > > character encodings.
> > 
> > Correct but is it possible to have real char string when database is not 
> > unicode or when locale defines size of char, to be exact?
> > 
> > In my case varchar does not make sense as all strings are guaranteed to be of 
> > defined length. While the argument you have put is correct, it's causing a disk 
> > space leak, to say so.

Not only that, but you get INSERT, UPDATE, DELETE and SELECT performance
gains with fixed length records, since you don't get fragmentation.

For example:
TABLE T
F1    INTEGER;
F2    VARCHAR(200)

INSERT INTO T VALUES (1, 'FOO BAR');
INSERT INTO T VALUES (2, 'SNAFU');

Next,
UPDATE T SET F2 = 'WIGGLE WAGGLE WUMPERSTUMPER' WHERE F1 = 1;

Unless there is a big gap on disk between the 2 inserted records, 
postgresql must then look somewhere else for space to put the new
version of T WHERE F1 = 1.

With fixed-length records, you know exactly where you can put the
new value of F2, thus minimizing IO.

> Well, maybe. But since 7.1 or so char() and varchar() simply became text
> with some length restrictions. This was one of the reasons. It also
> simplified a lot of code.

How much simpler can you get than fixed-length records?  

Of course, then there are 2 code paths, 1 for fixed length, and
1 for variable length.

-- 
+------------------------------------------------------------+
| Ron Johnson, Jr.     mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson  |
|                                                            |
| "they love our milk and honey, but preach about another    |
|  way of living"                                            |
|    Merle Haggard, "The Fighting Side Of Me"                |
+------------------------------------------------------------+

Re: [GENERAL] Large databases, performance

Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-08T14:38:02Z

Ron Johnson <ron.l.johnson@cox.net> writes:
> Not only that, but you get INSERT, UPDATE, DELETE and SELECT performance
> gains with fixed length records, since you don't get fragmentation.

That argument loses a lot of its force when you consider that Postgres
uses non-overwriting storage management.  We never do an UPDATE in-place
anyway, and so it matters little whether the updated record is the same
size as the original.

>> Well, maybe. But since 7.1 or so char() and varchar() simply became text
>> with some length restrictions. This was one of the reasons. It also
>> simplified a lot of code.

> How much simpler can you get than fixed-length records?  

It's not simpler: it's more complicated, because you need an additional
input item to figure out the size of any given column in a record.
Making sure that that info is available every place it's needed is one
of the costs of supporting a feature like this.

			regards, tom lane

Re: [GENERAL] Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-08T14:41:47Z

On 8 Oct 2002 at 10:38, Tom Lane wrote:

> Ron Johnson <ron.l.johnson@cox.net> writes:
> It's not simpler: it's more complicated, because you need an additional
> input item to figure out the size of any given column in a record.
> Making sure that that info is available every place it's needed is one
> of the costs of supporting a feature like this.

I understand. Can we put this in say page header instead of tuple header. While 
all the arguments you have put are really good, the stellar redundancy 
certainly can do with a mid-way solution.

Just a thought..

Bye
 Shridhar

--
bit, n:	A unit of measure applied to color.  Twenty-four-bit color	refers to 
expensive $3 color as opposed to the cheaper 25	cent, or two-bit, color that 
use to be available a few years ago.

Re: [GENERAL] Large databases, performance

Ron Johnson <ron.l.johnson@cox.net> — 2002-10-08T15:16:55Z

On Tue, 2002-10-08 at 09:38, Tom Lane wrote:
> Ron Johnson <ron.l.johnson@cox.net> writes:
> > Not only that, but you get INSERT, UPDATE, DELETE and SELECT performance
> > gains with fixed length records, since you don't get fragmentation.
> 
> That argument loses a lot of its force when you consider that Postgres
> uses non-overwriting storage management.  We never do an UPDATE in-place
> anyway, and so it matters little whether the updated record is the same
> size as the original.

Must you update any relative indexes, in order to point to the
new location of the record?

> >> Well, maybe. But since 7.1 or so char() and varchar() simply became text
> >> with some length restrictions. This was one of the reasons. It also
> >> simplified a lot of code.
> 
> > How much simpler can you get than fixed-length records?  
> 
> It's not simpler: it's more complicated, because you need an additional
> input item to figure out the size of any given column in a record.

With fixed-length, why?  From the metadata, you can compute the intra-
record offsets.  That's how it works with the commercial RDBMS that
I use at work.

On that system, even variable-length records don't need record-size
fields.  Any repeating text (more that ~4 chars) is replaced with
run-length encoding.  This includes the phantom spaces at the end
of the field.

> Making sure that that info is available every place it's needed is one
> of the costs of supporting a feature like this.

-- 
+------------------------------------------------------------+
| Ron Johnson, Jr.     mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson  |
|                                                            |
| "they love our milk and honey, but preach about another    |
|  way of living"                                            |
|    Merle Haggard, "The Fighting Side Of Me"                |
+------------------------------------------------------------+

Re: CHAR, VARCHAR, TEXT (Was Large Databases)

Josh Berkus <josh@agliodbs.com> — 2002-10-08T15:33:53Z

Ron, Shridhar,

Maybe I missed something on this thread, but can either of you give me
an example of a real database where the PostgreSQL approach of "all
strings are TEXT" versus the more traditional CHAR implementation have
resulted in measurable performance loss?

Otherwise, this discussion is rather academic ...

-Josh Berkus

Re: [GENERAL] Large databases, performance

Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-08T15:51:12Z

Ron Johnson <ron.l.johnson@cox.net> writes:
> On Tue, 2002-10-08 at 09:38, Tom Lane wrote:
>> That argument loses a lot of its force when you consider that Postgres
>> uses non-overwriting storage management.  We never do an UPDATE in-place
>> anyway, and so it matters little whether the updated record is the same
>> size as the original.

> Must you update any relative indexes, in order to point to the
> new location of the record?

We make new index entries for the new record, yes.  Both the old and new
records must be indexed (until one or the other is garbage-collected by
VACUUM) so that transactions can find whichever version they are
supposed to be able to see according to the tuple visibility rules.

>> It's not simpler: it's more complicated, because you need an additional
>> input item to figure out the size of any given column in a record.

> With fixed-length, why?  From the metadata, you can compute the intra-
> record offsets.

Sure, but you need an additional item of metadata than you otherwise
would (this is atttypmod, in Postgres terms).  I'm not certain that the
typmod is available everyplace that would need to be able to figure out
the physical width of a column.

> On that system, even variable-length records don't need record-size
> fields.  Any repeating text (more that ~4 chars) is replaced with
> run-length encoding.  This includes the phantom spaces at the end
> of the field.

Interesting that you should bring that up in the context of an argument
for supporting fixed-width fields ;-).  Doesn't any form of data
compression bring you right back into variable-width land?

Postgres' approach to data compression is that it's done per-field,
and only on variable-width fields.  We steal a couple of bits from the
length word to allow flagging of compressed and out-of-line values.
If we were to make CHAR(n) fixed-width then it would lose the ability
to participate in either compression or out-of-line storage.

Between that and the multibyte-encoding issue, I think it's very
difficult to make a case that the general-purpose CHAR(n) type should
be implemented as fixed-width.  If someone has a specialized application
where they need a restricted fixed-width string type, it's not that
hard to make a user-defined type that supports only a single column
width (and thereby gets around the typmod issue).  So I'm satisfied with
saying "define your own type if you want this".

			regards, tom lane

Re: CHAR, VARCHAR, TEXT (Was Large Databases)

Ron Johnson <ron.l.johnson@cox.net> — 2002-10-08T17:42:20Z

On Tue, 2002-10-08 at 10:33, Josh Berkus wrote:
> Ron, Shridhar,
> 
> Maybe I missed something on this thread, but can either of you give me
> an example of a real database where the PostgreSQL approach of "all
> strings are TEXT" versus the more traditional CHAR implementation have
> resulted in measurable performance loss?

??????

> Otherwise, this discussion is rather academic ...

-- 
+------------------------------------------------------------+
| Ron Johnson, Jr.     mailto:ron.l.johnson@cox.net          |
| Jefferson, LA  USA   http://members.cox.net/ron.l.johnson  |
|                                                            |
| "they love our milk and honey, but preach about another    |
|  way of living"                                            |
|    Merle Haggard, "The Fighting Side Of Me"                |
+------------------------------------------------------------+

Re: CHAR, VARCHAR, TEXT (Was Large Databases)

Josh Berkus <josh@agliodbs.com> — 2002-10-08T22:44:36Z

Ron,

> > Maybe I missed something on this thread, but can either of you give me
> > an example of a real database where the PostgreSQL approach of "all
> > strings are TEXT" versus the more traditional CHAR implementation have
> > resulted in measurable performance loss?
>
> ??????

In other words, if it ain't broke, don't fix it.

-- 
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco

Re: CHAR, VARCHAR, TEXT (Was Large Databases)

Josh Berkus <josh@agliodbs.com> — 2002-10-08T23:36:40Z

Ron,

> > > > Maybe I missed something on this thread, but can either of you give
> > > > me an example of a real database where the PostgreSQL approach of
> > > > "all strings are TEXT" versus the more traditional CHAR
> > > > implementation have resulted in measurable performance loss?
> > >
> > > ??????
> >
> > In other words, if it ain't broke, don't fix it.
>
> Well, does Really Slow Performance qualify as "broke"?

That's what I was asking.   Can you explain where your slow performance is 
attibutable to the CHAR implementation issues?   I missed that, if it was 
explained earlier in the thread.

-- 
Josh Berkus
josh@agliodbs.com
Aglio Database Solutions
San Francisco

Re: Large databases, performance

Manfred Koizar <mkoi-pg@aon.at> — 2002-10-09T08:00:03Z

On Mon, 07 Oct 2002 15:07:29 +0530, "Shridhar Daithankar"
<shridhar_daithankar@persistent.co.in> wrote:
>Only worry is database size. Postgresql is 111GB v/s 87 GB for mysql.

Shridhar,

here is an implementation of a set of user types: char3, char4,
char10.  Put the attached files into a new directory contrib/fixchar,
make, make install, and run fixchar.sql through psql.  Then create
your table as
	CREATE TABLE tbl (
	type		int,
	esn		char10,
	min		char10,
	datetime	timestamp,
	opc0		char3,
	...
	rest		char4,
	field0		int,
	field1		char4,
	...
	)

This should save 76 bytes per heap tuple and 12 bytes per index tuple,
giving a database size of ~ 76 GB.  I'd be very interested how this
affects performance.

Code has been tested for v7.2, it crashes on v7.3 beta 1.  If this is
a problem, let me know.

Servus
 Manfred

Re: Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-09T08:07:13Z

On 9 Oct 2002 at 10:00, Manfred Koizar wrote:

> On Mon, 07 Oct 2002 15:07:29 +0530, "Shridhar Daithankar"
> <shridhar_daithankar@persistent.co.in> wrote:
> >Only worry is database size. Postgresql is 111GB v/s 87 GB for mysql.
> 
> Shridhar,
> 
> here is an implementation of a set of user types: char3, char4,
> char10.  Put the attached files into a new directory contrib/fixchar,
> make, make install, and run fixchar.sql through psql.  Then create
> your table as
> 	CREATE TABLE tbl (
> 	type		int,
> 	esn		char10,
> 	min		char10,
> 	datetime	timestamp,
> 	opc0		char3,
> 	...
> 	rest		char4,
> 	field0		int,
> 	field1		char4,
> 	...
> 	)
> 
> This should save 76 bytes per heap tuple and 12 bytes per index tuple,
> giving a database size of ~ 76 GB.  I'd be very interested how this
> affects performance.
> 
> Code has been tested for v7.2, it crashes on v7.3 beta 1.  If this is
> a problem, let me know.

Thank you very much for this. I would certainly give it a try. Please be 
patient as next test is scheuled on monday.

Bye
 Shridhar

--
love, n.:	When it's growing, you don't mind watering it with a few tears.

Re: Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-09T08:25:28Z

On 9 Oct 2002 at 10:00, Manfred Koizar wrote:

> On Mon, 07 Oct 2002 15:07:29 +0530, "Shridhar Daithankar"
> <shridhar_daithankar@persistent.co.in> wrote:
> >Only worry is database size. Postgresql is 111GB v/s 87 GB for mysql.
> 
> Shridhar,
> 
> here is an implementation of a set of user types: char3, char4,
> char10.  Put the attached files into a new directory contrib/fixchar,
> make, make install, and run fixchar.sql through psql.  Then create
> your table as

I had a quick look in things. I think it's a great learning material for pg 
internals..;-)

I have a suggestion. In README, it should be worth mentioning that, new types 
can be added just by changin Makefile. e.g. Changing line

OBJS = char3.o char4.o char10.o

to

OBJS = char3.o char4.o char5.o char10.o 

would add the datatype char5 as well. 

Obviously this is for those who might not take efforts to read the source. ( 
Personally I wouldn't have, had it been part of entire postgres source dump. 
Just would have done ./configure;make;make install)

Thanks for the solution. It wouldn't have occurred to me in ages to create a 
type for this. I guess that's partly because never used postgresql beyond 
select/insert/update/delete. Anyway should have been awake..

Thanks once again

Bye
 Shridhar

--
But it's real.  And if it's real it can be affected ...  we may not be ableto 
break it, but, I'll bet you credits to Navy Beans we can put a dent in it.		-- 
deSalle, "Catspaw", stardate 3018.2

Re: [pgsql-performance] Large databases, performance

Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-09T13:32:50Z

Manfred Koizar <mkoi-pg@aon.at> writes:
> here is an implementation of a set of user types: char3, char4,
> char10.

Coupla quick comments on these:

> CREATE FUNCTION charNN_lt(charNN, charNN)
>     RETURNS boolean
>     AS '$libdir/fixchar'
>     LANGUAGE 'c';

> bool
> charNN_lt(char *a, char *b)
> {
> 	return (strncmp(a, b, NN) < 0);
> }/*charNN_lt*/

These functions are dangerous as written, because they will crash on
null inputs.  I'd suggest marking them strict in the function
declarations.  Some attention to volatility declarations (isCachable
or isImmutable) would be a good idea too.

Also, it'd be faster and more portable to write the functions with
version-1 calling conventions.

Using the Makefile to auto-create the differently sized versions is
a slick trick...

			regards, tom lane

Re: [pgsql-performance] Large databases, performance

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-09T13:41:09Z

On 9 Oct 2002 at 9:32, Tom Lane wrote:

> Manfred Koizar <mkoi-pg@aon.at> writes:
> > here is an implementation of a set of user types: char3, char4,
> > char10.
> 
> Coupla quick comments on these:
> 
> > CREATE FUNCTION charNN_lt(charNN, charNN)
> >     RETURNS boolean
> >     AS '$libdir/fixchar'
> >     LANGUAGE 'c';
> 
> > bool
> > charNN_lt(char *a, char *b)
> > {
> > 	return (strncmp(a, b, NN) < 0);
> > }/*charNN_lt*/
> 
> These functions are dangerous as written, because they will crash on
> null inputs.  I'd suggest marking them strict in the function
> declarations.  Some attention to volatility declarations (isCachable
> or isImmutable) would be a good idea too.

Let me add something. Using char* is bad idea. I had faced a situation recently 
on HP-UX 11 that with a libc patch, isspace collapsed for char>127. Fix was to 
use unsigned char. There are other places also where the input character is 
used as index to an array internally and can cause weird behaviour for values 
>127

I will apply both the correction here. Will post the final stuff soon.

Bye
 Shridhar

--
Hacker's Quicky #313:	Sour Cream -n- Onion Potato Chips	Microwave Egg Roll	
Chocolate Milk

problem with the Index

Jose Antonio Leo <jaleo8@storelandia.com> — 2002-10-09T16:56:41Z

I have a problem with the index of 1 table.

I hava a table created :
	CREATE TABLE "acucliart" (
   "cod_pto" numeric(8,0) NOT NULL,
   "cod_cli" varchar(9) NOT NULL,
   "mes" numeric(2,0) NOT NULL,
   "ano" numeric(4,0) NOT NULL,
   "int_art" numeric(5,0) NOT NULL,
   "cantidad" numeric(12,2),
   "ven_siv_to" numeric(14,2),
   "ven_civ_to" numeric(14,2),
   "tic_siv_to" numeric(14,2),
   "tic_civ_to" numeric(14,2),
   "visitas" numeric(2,0),
   "ult_vis" date,
   "ven_cos" numeric(12,2),
   "ven_ofe" numeric(12,2),
   "cos_ofe" numeric(12,2),
   CONSTRAINT "acucliart_pkey"
   PRIMARY KEY ("cod_cli")
);

if i do this select:
	explain select * from acucliart where cod_cli=10000;
		postgres use the index
		NOTICE:  QUERY PLAN:
		Index Scan using cod_cli_ukey on acucliart  (cost=0.00..4.82 rows=1
width=478)

and this select
		explain select * from acucliart where cod_cli>10000;
		Postgres don't use the index:
		NOTICE:  QUERY PLAN:
		Seq Scan on acucliart  (cost=0.00..22.50 rows=333 width=478)

why?


tk

Re: [SQL] problem with the Index

Stephan Szabo <sszabo@megazone23.bigpanda.com> — 2002-10-09T17:31:12Z

On Wed, 9 Oct 2002, Jose Antonio Leo wrote:

> I have a problem with the index of 1 table.
>
> I hava a table created :
> 	CREATE TABLE "acucliart" (
>    "cod_pto" numeric(8,0) NOT NULL,
>    "cod_cli" varchar(9) NOT NULL,
>    "mes" numeric(2,0) NOT NULL,
>    "ano" numeric(4,0) NOT NULL,
>    "int_art" numeric(5,0) NOT NULL,
>    "cantidad" numeric(12,2),
>    "ven_siv_to" numeric(14,2),
>    "ven_civ_to" numeric(14,2),
>    "tic_siv_to" numeric(14,2),
>    "tic_civ_to" numeric(14,2),
>    "visitas" numeric(2,0),
>    "ult_vis" date,
>    "ven_cos" numeric(12,2),
>    "ven_ofe" numeric(12,2),
>    "cos_ofe" numeric(12,2),
>    CONSTRAINT "acucliart_pkey"
>    PRIMARY KEY ("cod_cli")
> );
>
> if i do this select:
> 	explain select * from acucliart where cod_cli=10000;
> 		postgres use the index
> 		NOTICE:  QUERY PLAN:
> 		Index Scan using cod_cli_ukey on acucliart  (cost=0.00..4.82 rows=1
> width=478)
>
> and this select
> 		explain select * from acucliart where cod_cli>10000;
> 		Postgres don't use the index:
> 		NOTICE:  QUERY PLAN:
> 		Seq Scan on acucliart  (cost=0.00..22.50 rows=333 width=478)
>
> why?

Well, how many rows are in the table?  In the first case it estimates 1
row will be returned, in the second 333. Index scans are not always faster
than sequential scans as the percentage of the table to scan becomes
larger.  If you haven't analyzed recently, you probably should do so and
if you want to compare, set enable_seqscan=off and try an explain there
and see what it gives you.

Also, why are you comparing a varchar(9) column with an integer?

Re: [pgsql-performance] Large databases, performance

Manfred Koizar <mkoi-pg@aon.at> — 2002-10-09T18:09:03Z

On Wed, 09 Oct 2002 09:32:50 -0400, Tom Lane <tgl@sss.pgh.pa.us>
wrote:
>Coupla quick comments on these:

My first attempt on user types; thanks for the tips.

>These functions are dangerous as written, because they will crash on
>null inputs.  I'd suggest marking them strict in the function
>declarations.

I was not aware of this, just wondered why bpchar routines didn't
crash :-)  Fixed.

>Some attention to volatility declarations (isCachable
>or isImmutable) would be a good idea too.
>Also, it'd be faster and more portable to write the functions with
>version-1 calling conventions.

Done, too.  In the meantime I've found out why it crashed with 7.3:
INSERT INTO pg_opclass  is now obsolete, have to use  CREATE OPERATOR
CLASS ...

Servus
 Manfred

contrib/fixchar (Was: Large databases, performance)

Manfred Koizar <mkoi-pg@aon.at> — 2002-10-10T13:30:31Z

On Wed, 09 Oct 2002 10:00:03 +0200, I wrote:
>here is an implementation of a set of user types: char3, char4,
>char10.

New version available.  As I don't want to spam the list with various
versions until I get it right eventually, you can get it from
http://members.aon.at/pivot/pg/fixchar20021010.tgz if you are
interested.

What's new:

. README updated (per Shridhar's suggestion)
. doesn't crash on NULL (p. Tom)
. version-1 calling conventions (p. Tom)
. isCachable (p. Tom)
. works for 7.2 (as delivered) and for 7.3 (make for73)

Shridhar, you were concerned about signed/unsigned chars;  looking at
the code I can not see how this is a problem.  So no change in this
regard.

Thanks for your comments.  Have fun!

Servus
 Manfred

Re: contrib/fixchar (Was: Large databases, performance)

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-10T13:49:11Z

On 10 Oct 2002 at 15:30, Manfred Koizar wrote:

> On Wed, 09 Oct 2002 10:00:03 +0200, I wrote:
> >here is an implementation of a set of user types: char3, char4,
> >char10.
> 
> New version available.  As I don't want to spam the list with various
> versions until I get it right eventually, you can get it from
> http://members.aon.at/pivot/pg/fixchar20021010.tgz if you are
> interested.
> 
> What's new:
> 
> . README updated (per Shridhar's suggestion)
> . doesn't crash on NULL (p. Tom)
> . version-1 calling conventions (p. Tom)
> . isCachable (p. Tom)
> . works for 7.2 (as delivered) and for 7.3 (make for73)
> 
> Shridhar, you were concerned about signed/unsigned chars;  looking at
> the code I can not see how this is a problem.  So no change in this
> regard.

Well, this is not related to postgresql exactly but to summerise the problem, 
with libc patch PHCO_19090 or compatible upwards, on HP-UX11, isspace does not 
work correctly if input value is >127. Can cause lot of problem for an external 
app. It works fine with unsigned char

Does not make a difference from postgrersql point of view but would break non-
english locale if they want to use this fix under some situation.

But I agree, unless somebody reports it, no point fixing it and we know the fix 
anyway..

Bye
 Shridhar

--
Live long and prosper.		-- Spock, "Amok Time", stardate 3372.7

Re: contrib/fixchar (Was: Large databases, performance)

Giles Lean <giles@nemeton.com.au> — 2002-10-11T22:54:48Z

> Well, this is not related to postgresql exactly but to summerise the
> problem, with libc patch PHCO_19090 or compatible upwards, on
> HP-UX11, isspace does not work correctly if input value is >127.

o isspace() and such are defined in the standards to operate on characters
o for historic C reasons, 'char' is widened to 'int' in function calls
o it is platform dependent whether 'char' is a signed or unsigned type

If your platform has signed 'char' (as HP-UX does on PA-RISC) and you
pass a value that is negative it will be sign extended when converted
to 'int', and may be outside the range of values for which isspace()
is defined.

Portable code uses 'unsigned char' when using ctype.h features, even
though for many platforms where 'char' is an unsigned type it's not
necessary for correct functioning.

I don't see any isspace() or similar in the code though, so I'm not
sure why this issue is being raised?

Regards,

Giles

Re: contrib/fixchar (Was: Large databases, performance)

Tom Lane <tgl@sss.pgh.pa.us> — 2002-10-12T04:20:14Z

Giles Lean <giles@nemeton.com.au> writes:
> Portable code uses 'unsigned char' when using ctype.h features, even
> though for many platforms where 'char' is an unsigned type it's not
> necessary for correct functioning.

Yup.  Awhile back I went through the PG sources and made sure we
explicitly casted the arguments of ctype.h functions to "unsigned char"
if they weren't already.  If anyone sees a place I missed (or that
snuck in later) please speak up!

> I don't see any isspace() or similar in the code though, so I'm not
> sure why this issue is being raised?

Ditto, I saw no ctype.h usage in Manfred's code.  It matters not whether
you label strcmp's argument as unsigned...

			regards, tom lane

Re: contrib/fixchar (Was: Large databases, performance)

Shridhar Daithankar <shridhar_daithankar@persistent.co.in> — 2002-10-12T06:41:02Z

On 12 Oct 2002 at 8:54, Giles Lean wrote:

> Portable code uses 'unsigned char' when using ctype.h features, even
> though for many platforms where 'char' is an unsigned type it's not
> necessary for correct functioning.
> 
> I don't see any isspace() or similar in the code though, so I'm not
> sure why this issue is being raised?

Well, I commented on fixchar contrib module that it should use unsigned char 
rather than just char, to be on safer side on all platforms. Nothing much..

Bye
 Shridhar

--
brokee, n:	Someone who buys stocks on the advice of a broker.