Re: [GNUnet-developers] MYSQL module

[Igor Wronsky]

On Fri, 9 Jan 2004, Hendrik Pagenhardt wrote:

> First I noticed, that the average row length is multiplied with the
> number of rows (costing two SQL statements), when MYSQL returns the size
> of the data file in the "Data_length" column of SHOW TABLE STATUS. So
> row counting should not be necessary. Secondly this calculation omits
> the space the table indexes use. As those currently add another 20% of
> the table data size to the space consumed by the content storage, I
> would suggest to either document that the quota is used for the data
> files only, or, preferably, to adjust the calculation to include the
> index size. That could be easily done by adding the "Data_length" and
> "Index_length" columns of SHOW TABLE STATUS, still saving the row count.

No. See the note before estimateAvailableBlocks(). As those
two fields do not update in real time, relying on them can
result in gnunetd trashing hundreds of megabytes from the
database if it gets full. And that did happen before.

> A way to shrink the indexes, and possibly speed up data access at a
> whole, might be to not use a primary key on the bucket tables. It could
> be replaced by an index over the first 3 bytes of the hash. Randomness
> assumed, this would be sufficient to distinctly identify 256^3 rows,
> which is an order of magnitude higher than what could be placed in a
> bucket. I tested this on my system with a copied GNUnet table (125MB
> data file, 25 MB index) and the index shrank to less than half (9 MB).
> Query performance was as fast or even faster this way. And the reduced
> index size should help caching.
> The only drawback is, that the "writeContent" method must be rewritten
> to prevent duplicates (f.i. by first trying to UPDATE the row, and if
> this results in 0 affected rows to then insert it). And of course the
> table definitions must be changed. Which could be done via some ALTER
> TABLE statements during init when required.

A question before dwelving further into optimization hacks,
is the database really a bottleneck on your system? That is,
can you afford so much upstream bandwidth that mysql can't
deliver blocks fast enough? If thats not the case, there
is not much point in optimizing further, imo, unless
you really wish to save a few cpu cycles or a few blocks from
the disk.

If you really wish to make gnunet more efficient, you might
look into the routing/trust business and suggest enhancements
for that. I suspect thats the place where extra efficiency
is called for most at the moment.


Igor