You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@subversion.apache.org by Jan Horák <ho...@gmail.com> on 2010/03/29 15:35:11 UTC
SQL backend database scheme
Hi everybody,
I've prepared a database scheme of the upcoming MySQL backend, it is
avaible at:
http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_mysql_design_v1.png
I have arisen from BDB and FSFS backend design, some previous analyses:
http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100309_dag_structures_sql.pdf
http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_backend_operations_analysis.pdf
and from historic MySQL backend design:
http://homepages.paradise.net.nz/~ejrh/subversion/mysql/
Any ideas or comments will be appreciated.
Jan Horak
horak.honza@gmail.com
Re: SQL backend database scheme
Posted by Jan Horák <ho...@gmail.com>.
Sorry about that, it should be OK already.
Dne 30.3.2010 08:05, Philipp Marek napsal(a):
> Hello Jan,
>
> On Montag, 29. März 2010, Jan Horák wrote:
>
>> I've prepared a database scheme of the upcoming MySQL backend, it is
>> avaible at:
>> http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_mysql_des
>> ign_v1.png
>>
> I just get a 403 here.
>
>
>
>> Any ideas or comments will be appreciated.
>>
> As soon as I can see the schema ;-)
>
>
> Regards,
>
> Phil
>
Re: SQL backend database scheme
Posted by Jan Horák <ho...@gmail.com>.
Sorry about that, it should be OK already.
Dne 30.3.2010 08:05, Philipp Marek napsal(a):
> Hello Jan,
>
> On Montag, 29. März 2010, Jan Horák wrote:
>
>> I've prepared a database scheme of the upcoming MySQL backend, it is
>> avaible at:
>> http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_mysql_des
>> ign_v1.png
>>
> I just get a 403 here.
>
>
>
>> Any ideas or comments will be appreciated.
>>
> As soon as I can see the schema ;-)
>
>
> Regards,
>
> Phil
>
Re: SQL backend database scheme
Posted by Greg Stein <gs...@gmail.com>.
2010/3/30 Philipp Marek <ph...@emerion.com>:
> Hello Jan,
>
> On Montag, 29. März 2010, Jan Horák wrote:
>> I've prepared a database scheme of the upcoming MySQL backend, it is
>> avaible at:
>> http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_mysql_des
>> ign_v1.png
>
> I just get a 403 here.
Strange... I was able to see the .png and both .pdf files, no problem.
>...
Cheers,
-g
Re: SQL backend database scheme
Posted by Philipp Marek <ph...@emerion.com>.
Hello Jan,
On Montag, 29. März 2010, Jan Horák wrote:
> I've prepared a database scheme of the upcoming MySQL backend, it is
> avaible at:
> http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_mysql_des
> ign_v1.png
I just get a 403 here.
> Any ideas or comments will be appreciated.
As soon as I can see the schema ;-)
Regards,
Phil
Re: SQL backend database scheme
Posted by Greg Stein <gs...@gmail.com>.
On Fri, Apr 2, 2010 at 01:35, Philipp Marek <ph...@emerion.com> wrote:
> Hello Greg,
> Hello Jan,
>
> On Donnerstag, 1. April 2010, Greg Stein wrote:
>> 2010/3/31 Jan Horák <ho...@gmail.com>:
>> > 30.3.2010 13:55, Philipp Marek wrote:
>> >...
>> >
>> >> * Furthermore, how about allowing the plain data to reside in files?
>> >> Would make the database much smaller, and then these data blocks
>> >> could possibly be shared among multiple repositories.
>> >> (Really easy, too, if they're named by their SHA1, for example).
>> >> That should allow for zero-copy IO, too (at least for sending data).
>> >
>> > The question is, how much faster it would be.. I would like to make a
>> > simple test to simulate this soon and estimate the percentage
>> > difference..
>>
>> My gut says "not that much faster". In most scenarios, the network
>> bandwidth between the client/server will be the bottleneck. Reading
>> the data off a disk (rather than from a DB) is not going to make the
>> WAN connection any faster.
>>
>> On a LAN, you might have enough network bandwidth to see bottlenecks
>> on the server's I/O channel, but really... I remain somewhat doubtful.
>
> It's not about the raw speed alone.
>
> Of course, having to tell a database which BLOBs to fetch (which might readily
> be stored out-of-line, eg. with PostGreSQL [pg_largeobject, TOAST]), getting
> them via a socket into a buffer, and writing the buffer to another socket,
> everything with protocol header adding/removing etc. *has* to be slower than a
> open()/sendfile()/close().
>
> It's a bit about latency (which might not be really an argument, as the
> database has to be queried anyway), and about CPU load.
Possibly. If you're bottlenecked on the network, then your CPU load
will (by definition) not be an issue. You have CPU load to spare.
That's for a single user. If you scale up to multiple users, then yes:
minimizing the CPU will be important. Each needs to do some work, then
sit on the outbound network. But you have almost no CPU. You're
waiting on the disk or the RDBMS or the network.
> For small setups the HTTP server and the database will be on the same host; if
> we can increase the performance by 10% it means that 10% of all people won't
> need to buy a faster/larger server.
That doesn't follow.
>...
In any case... it is hard to truly say what will happen without some
data. There are lots of variables :-P
Cheers,
-g
Re: SQL backend database scheme
Posted by Philipp Marek <ph...@emerion.com>.
Hello Greg,
Hello Jan,
On Donnerstag, 1. April 2010, Greg Stein wrote:
> 2010/3/31 Jan Horák <ho...@gmail.com>:
> > 30.3.2010 13:55, Philipp Marek wrote:
> >...
> >
> >> * Furthermore, how about allowing the plain data to reside in files?
> >> Would make the database much smaller, and then these data blocks
> >> could possibly be shared among multiple repositories.
> >> (Really easy, too, if they're named by their SHA1, for example).
> >> That should allow for zero-copy IO, too (at least for sending data).
> >
> > The question is, how much faster it would be.. I would like to make a
> > simple test to simulate this soon and estimate the percentage
> > difference..
>
> My gut says "not that much faster". In most scenarios, the network
> bandwidth between the client/server will be the bottleneck. Reading
> the data off a disk (rather than from a DB) is not going to make the
> WAN connection any faster.
>
> On a LAN, you might have enough network bandwidth to see bottlenecks
> on the server's I/O channel, but really... I remain somewhat doubtful.
It's not about the raw speed alone.
Of course, having to tell a database which BLOBs to fetch (which might readily
be stored out-of-line, eg. with PostGreSQL [pg_largeobject, TOAST]), getting
them via a socket into a buffer, and writing the buffer to another socket,
everything with protocol header adding/removing etc. *has* to be slower than a
open()/sendfile()/close().
It's a bit about latency (which might not be really an argument, as the
database has to be queried anyway), and about CPU load.
For small setups the HTTP server and the database will be on the same host; if
we can increase the performance by 10% it means that 10% of all people won't
need to buy a faster/larger server.
> I'd go with the "store content in the database" until performance
> figures (or a DBA) demonstrates it is a problem.
Ok.
Regards,
Phil
Re: SQL backend database scheme
Posted by Greg Stein <gs...@gmail.com>.
2010/3/31 Jan Horák <ho...@gmail.com>:
> 30.3.2010 13:55, Philipp Marek wrote:
>...
>> * Furthermore, how about allowing the plain data to reside in files?
>> Would make the database much smaller, and then these data blocks
>> could possibly be shared among multiple repositories.
>> (Really easy, too, if they're named by their SHA1, for example).
>> That should allow for zero-copy IO, too (at least for sending data).
>
> The question is, how much faster it would be.. I would like to make a simple
> test to simulate this soon and estimate the percentage difference..
My gut says "not that much faster". In most scenarios, the network
bandwidth between the client/server will be the bottleneck. Reading
the data off a disk (rather than from a DB) is not going to make the
WAN connection any faster.
On a LAN, you might have enough network bandwidth to see bottlenecks
on the server's I/O channel, but really... I remain somewhat doubtful.
I'd go with the "store content in the database" until performance
figures (or a DBA) demonstrates it is a problem.
Cheers,
-g
Re: SQL backend database scheme
Posted by Philipp Marek <ph...@emerion.com>.
Hello Jan!
On Mittwoch, 31. März 2010, Jan Horák wrote:
> 30.3.2010 13:55, Philipp Marek wrote:
> > I looked at that, and I'd like to post two wishes:
> > * Please provide representations with a SHA1 (or similar) field;
> > then it's possible to split blocks on manber-borders, to save
> > space.
>
> Thank you for respond, this is definitely a good point, I will involve it.
Fine, thank you.
Even if no Manber-blocks would be used at first, it would provide consistency
checks.
> > * Furthermore, how about allowing the plain data to reside in files?
> > Would make the database much smaller, and then these data blocks
> > could possibly be shared among multiple repositories.
> > (Really easy, too, if they're named by their SHA1, for example).
> > That should allow for zero-copy IO, too (at least for sending data).
>
> The question is, how much faster it would be.. I would like to make a
> simple test to simulate this soon and estimate the percentage difference..
That would be very interesting to me.
I just know of some similar system where bitmaps were fetched out of a
database - instead of just using Apache and static paths the whole data was
transferred multiple times over multiple sockets ...
> Another point is a simple backup of the SQL database, using an existed
> global backup system in a company, which is one of the main benefits of
> the SQL backend. Would the external plain data be a problem for global
> backup or not?
That's a good point against that idea, yes.
Regards,
Phil
Re: SQL backend database scheme
Posted by Jan Horák <ho...@gmail.com>.
30.3.2010 13:55, Philipp Marek wrote:
> Hello Jan!
>
> On Montag, 29. März 2010, Jan Horák wrote:
>
>> I've prepared a database scheme of the upcoming MySQL backend, it is
>> avaible at:
>> http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_mysql_des
>> ign_v1.png
>>
> I looked at that, and I'd like to post two wishes:
> * Please provide representations with a SHA1 (or similar) field;
> then it's possible to split blocks on manber-borders, to save
> space.
>
Thank you for respond, this is definitely a good point, I will involve it.
> * Furthermore, how about allowing the plain data to reside in files?
> Would make the database much smaller, and then these data blocks
> could possibly be shared among multiple repositories.
> (Really easy, too, if they're named by their SHA1, for example).
> That should allow for zero-copy IO, too (at least for sending data).
>
>
The question is, how much faster it would be.. I would like to make a
simple test to simulate this soon and estimate the percentage difference..
Another point is a simple backup of the SQL database, using an existed
global backup system in a company, which is one of the main benefits of
the SQL backend. Would the external plain data be a problem for global
backup or not?
> Regards,
>
> Phil
>
Re: SQL backend database scheme
Posted by Jan Horák <ho...@gmail.com>.
30.3.2010 13:55, Philipp Marek wrote:
> Hello Jan!
>
> On Montag, 29. März 2010, Jan Horák wrote:
>
>> I've prepared a database scheme of the upcoming MySQL backend, it is
>> avaible at:
>> http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_mysql_des
>> ign_v1.png
>>
> I looked at that, and I'd like to post two wishes:
> * Please provide representations with a SHA1 (or similar) field;
> then it's possible to split blocks on manber-borders, to save
> space.
>
Thank you for respond, this is definitely a good point, I will involve it.
> * Furthermore, how about allowing the plain data to reside in files?
> Would make the database much smaller, and then these data blocks
> could possibly be shared among multiple repositories.
> (Really easy, too, if they're named by their SHA1, for example).
> That should allow for zero-copy IO, too (at least for sending data).
>
>
The question is, how much faster it would be.. I would like to make a
simple test to simulate this soon and estimate the percentage difference..
Another point is a simple backup of the SQL database, using an existed
global backup system in a company, which is one of the main benefits of
the SQL backend. Would the external plain data be a problem for global
backup or not?
> Regards,
>
> Phil
>
Re: SQL backend database scheme
Posted by Philipp Marek <ph...@emerion.com>.
Hello Jan!
On Montag, 29. März 2010, Jan Horák wrote:
> I've prepared a database scheme of the upcoming MySQL backend, it is
> avaible at:
> http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_mysql_des
> ign_v1.png
I looked at that, and I'd like to post two wishes:
* Please provide representations with a SHA1 (or similar) field;
then it's possible to split blocks on manber-borders, to save
space.
* Furthermore, how about allowing the plain data to reside in files?
Would make the database much smaller, and then these data blocks
could possibly be shared among multiple repositories.
(Really easy, too, if they're named by their SHA1, for example).
That should allow for zero-copy IO, too (at least for sending data).
Regards,
Phil
RE: SQL backend database scheme
Posted by Geoff Rowell <ge...@gmail.com>.
Hi Jan,
Seems simple, but... if you avoid the use of plurals, the table names will
be shorter.
-Geoff
-----Original Message-----
From: Greg Stein [mailto:gstein@gmail.com]
Sent: Thursday, April 01, 2010 3:02 PM
To: Jan Horák
Cc: dev@subversion.apache.org
Subject: Re: SQL backend database scheme
Hey Jan,
A couple small nits:
* maybe use "txn_id" rather than "txt_id". the latter sounds like
"text" rather than "transaction", and "txn" is used in the APIs
* transactions_proplist has a txnprop_id which is never referenced.
I'd suggest making <txn_id, name> the primary key.
Cheers,
-g
2010/3/29 Jan Horák <ho...@gmail.com>:
> Hi everybody,
>
> I've prepared a database scheme of the upcoming MySQL backend, it is
avaible
> at:
>
http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_mysql_desi
gn_v1.png
>
> I have arisen from BDB and FSFS backend design, some previous analyses:
>
http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100309_dag_struct
ures_sql.pdf
>
http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_backend_op
erations_analysis.pdf
>
> and from historic MySQL backend design:
> http://homepages.paradise.net.nz/~ejrh/subversion/mysql/
>
> Any ideas or comments will be appreciated.
>
> Jan Horak
> horak.honza@gmail.com
>
RE: SQL backend database scheme
Posted by Geoff Rowell <ge...@gmail.com>.
Hi Jan,
Seems simple, but... if you avoid the use of plurals, the table names will
be shorter.
-Geoff
-----Original Message-----
From: Greg Stein [mailto:gstein@gmail.com]
Sent: Thursday, April 01, 2010 3:02 PM
To: Jan Horák
Cc: dev@subversion.apache.org
Subject: Re: SQL backend database scheme
Hey Jan,
A couple small nits:
* maybe use "txn_id" rather than "txt_id". the latter sounds like
"text" rather than "transaction", and "txn" is used in the APIs
* transactions_proplist has a txnprop_id which is never referenced.
I'd suggest making <txn_id, name> the primary key.
Cheers,
-g
2010/3/29 Jan Horák <ho...@gmail.com>:
> Hi everybody,
>
> I've prepared a database scheme of the upcoming MySQL backend, it is
avaible
> at:
>
http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_mysql_desi
gn_v1.png
>
> I have arisen from BDB and FSFS backend design, some previous analyses:
>
http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100309_dag_struct
ures_sql.pdf
>
http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_backend_op
erations_analysis.pdf
>
> and from historic MySQL backend design:
> http://homepages.paradise.net.nz/~ejrh/subversion/mysql/
>
> Any ideas or comments will be appreciated.
>
> Jan Horak
> horak.honza@gmail.com
>
Re: SQL backend database scheme
Posted by Greg Stein <gs...@gmail.com>.
Hey Jan,
A couple small nits:
* maybe use "txn_id" rather than "txt_id". the latter sounds like
"text" rather than "transaction", and "txn" is used in the APIs
* transactions_proplist has a txnprop_id which is never referenced.
I'd suggest making <txn_id, name> the primary key.
Cheers,
-g
2010/3/29 Jan Horák <ho...@gmail.com>:
> Hi everybody,
>
> I've prepared a database scheme of the upcoming MySQL backend, it is avaible
> at:
> http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_mysql_design_v1.png
>
> I have arisen from BDB and FSFS backend design, some previous analyses:
> http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100309_dag_structures_sql.pdf
> http://www.stud.fit.vutbr.cz/~xhorak50/diplomathesis/files/100329_backend_operations_analysis.pdf
>
> and from historic MySQL backend design:
> http://homepages.paradise.net.nz/~ejrh/subversion/mysql/
>
> Any ideas or comments will be appreciated.
>
> Jan Horak
> horak.honza@gmail.com
>