[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

Re: [Bacula-devel] Alternative DB proposal


Hi, baculamaniacs ;)

If you remember, I held a series of tests on the speed Bacula.
I compared the two DB scheme: canonical vs alternative (to the
disclosure LStat in several columns).

So good news: canonical DB scheme won, was faster. Details are not yet writing.

However (and this is "bad" news ;), I concluded that my tests were not correct.

For the following reasons.

When I changed the test, generates 5M _unique_ Filename, Path and made
(as in real Bacula):

INSERT INTO Path (Path) SELECT a.Path FROM
   (SELECT DISTINCT Path FROM batch) AS a WHERE NOT EXISTS
   (SELECT Path FROM Path AS p WHERE p.Path = a.Path)

my MySQL "fell".

So I think that I wasted time spent on his "tests" ;(

Continue.

I have in the database Catalog following statistics:
Job   2,823 records
File  11,338,602 records
Filename 2,188,445
Path  25,929

At each Job there is an average of 4016 files (entries in the table File).
For each file (one entry in the table File) in average, 0.1930 entries
in the Filename table and
0.0022868 entries in the table Path.

In the tests need to use a similar proportion, that is, for example,
10M entries in the File table will be done 1,930,000 ent
ries in the table Filename and 22,868 entries in the table Path.

In this case, the test will be very similar to real work Bacula.

I am going to write a message in a mailing-list bacula-users and ask
to send me the sample type:
select count(*) from Job;
select count(*) from File;
select count(*) from Filename;
select count(*) from Path;

in order to calculate the average proportion of Job's, File's, etc.

Then make a new series of "right" tests.

I have not aimed to prove that the canonical DB scheme will be slower
than the alternative DB scheme. I want to bring the tests to real work
and watch some interesting dependencies (see attach).



PS. Negative role played as RAID5. The new tests will I use RAID1+0.

-- 
with best regards

Attachment: graph_all.png
Description: PNG image

# vmstat 300
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0 7551308  13568 325120    0    0   174   111  270 2472  3  2 92  3  0
 1  4      0 4807272  16600 2765556    0    0     1  7027 1221 68899 15  8 55 21  0
 1  8      0 2601188  19296 4909932    0    0     0  7211 1273 65618 14  9 48 29  0
 1  8      0 381340  22008 7068084    0    0     0  7158 1270 60988 15  8 48 29  0
 0  8      0  45360   7116 7408784    0    0     8  5806 1223 18783  4  2 67 26  0
 0  8      0  47192   7736 7406316    0    0    13  5070 1204 1721  0  0 74 25  0
 0  1      0  44468   8344 7408564    0    0    26  2300 1179 2790  1  0 74 24  0
 0  1      0  45708   8848 7406672    0    0    27  1368 1171 2242  1  0 75 25  0
 0  6      0  46176   9352 7405504    0    0    36  1300 1189 3968  1  1 75 24  0
 0  1      0  46372   9848 7404628    0    0    18  1080 1172 1872  0  0 75 25  0
 0  2      0  43944  10324 7406616    0    0    27  1165 1174 2401  0  0 75 24  0
 0  1      4  45220  11144 7401848    0    0    36  1171 1177 2779  0  0 75 24  0
 0  1    136  90672  12324 7353344    0    0   220  1128 1190 2837  0  1 75 24  0
 0  1    136  45264  12580 7399764    0    0   204  2014 1222 13762  3  2 75 21  0
 0  5    136  44728  12212 7401000    0    0   166  2007 1242 13448  3  2 75 21  0
 0  5    136  43640  12652 7401512    0    0     7  1782 1166  525  0  0 74 25  0
 0  5    136  46396  13084 7398236    0    0     7  1843 1168  512  0  0 75 25  0
 0  6    136  44484  13516 7399900    0    0     7  1804 1169  552  0  0 74 25  0
 0  5    136  44540  13948 7399492    0    0     7  1818 1168  574  0  0 74 25  0
 0  1    136  45708  14024 7398812    0    0    24  1727 1168 2132  1  0 75 25  0
 0  3    136  44824  14308 7399064    0    0    36  1684 1178 4340  1  1 75 24  0
 0  1    136  45480  14644 7397704    0    0    11   962 1172 1150  0  0 75 25  0
 0  1    136  44604  14784 7398000    0    0    18  1029 1176 1685  0  0 74 25  0
 0  1    136  46480  15012 7396008    0    0    17  1067 1174 1949  0  0 74 25  0
 0  1    136  90464  14952 7352332    0    0    20  1102 1174 1650  0  0 74 25  0
 0  1    136  44620  15456 7398812    0    0    19  1184 1174 1874  0  0 75 25  0
 0  1    136  45624  15656 7397752    0    0    18  1198 1174 1685  0  0 75 25  0
 0  1    136  44628  16048 7397312    0    0    16  1051 1175 1570  0  0 75 25  0
 0  1    136  44932  16164 7396976    0    0    15  1051 1175 1561  0  0 75 25  0
 0  3    136  45236  16620 7396276    0    0    15  1056 1174 1545  0  0 75 25  0
 0  2    136  45008  17096 7395884    0    0    12  1015 1172 1397  0  0 75 25  0
 0  4    136  45212  17456 7395328    0    0    26  1505 1182 3500  1  1 74 24  0
 0  1    136  46296  17924 7393336    0    0     7   981 1169 1198  0  0 74 25  0
 0  3    136  45816  18352 7393700    0    0    12  1041 1173 2519  0  0 74 25  0
 0  1    136  45612  18852 7393556    0    0    13  1039 1172 1339  0  0 74 25  0
 1  0    136  44456  17924 7386724    0    0  2174   682 1192  782  3  1 75 21  0

# vmstat 300
procs -----------memory---------- ---swap-- -----io---- --system-- -----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id wa st
 1  0      0 7708024  14776 219548    0    0    35    50  260  676  1  0 98  1  0
 1 11      0 4992848  17700 2583868    0    0     0  7031 1212 70920 14  9 54 23  0
 2  9      0 2652984  20524 4858708    0    0     0  7162 1262 63699 14  8 45 32  0
 1  8      0 333448  23344 7113328    0    0     0  6977 1256 64847 14  9 45 32  0
 2  7      0  46548   6928 7406640    0    0     5  5215 1202 18844  4  3 68 25  0
 0  6      0  46000   7612 7406196    0    0    13  5015 1199 3291  0  1 74 25  0
 0  1      0  46000   8172 7405892    0    0    22  2872 1179 4585  1  1 75 24  0
 0  1      0  43804   8676 7407552    0    0    21  1122 1173 1322  0  0 75 25  0
 0  1    136  46012   9352 7404772    0    0   161  2081 1222 13655  2  2 75 21  0
 0  2    136  44088  10036 7381320    0    0   323  2243 1273 16609  3  2 74 20  0
 0  6    136 112672  10312 7336548    0    0   131  2223 1238 2466  0  0 74 25  0
 0  5    136  89588  10972 7356752    0    0    12  2159 1170  898  0  0 74 26  0
 0  4    136  67292  11620 7380264    0    0    19  2145 1171  575  0  0 75 25  0
 0  5    136  43800  12116 7403988    0    0     8  2113 1166  630  0  0 74 25  0
 0  1    136  44752  12488 7402148    0    0    25  1955 1166 1565  0  0 75 25  0
 1  0    136  43792  12680 7404040    0    0    50  1770 1175 3186  1  0 75 24  0
 0  5    136  43996  13084 7403684    0    0    63  1558 1209 4108  1  1 75 24  0
 0  5    136  45308  13524 7401384    0    0     6  1130 1170  547  0  0 74 25  0
 0  6    136  44024  14000 7401876    0    0     6  1131 1169  599  0  0 74 25  0
 0  6    136  43792  14472 7401512    0    0     5  1151 1169  509  0  0 74 25  0
 0  1    136  46264  14932 7398416    0    0    18  1233 1171 1462  0  0 75 25  0
 0  1    136 114840  15332 7329020    0    0    18  1202 1169 1362  0  0 74 25  0
 0  1    136  46860  15848 7397156    0    0    64  1740 1195 6824  1  1 75 23  0
 0  2    136  49128  15980 7386456    0    0   438  1680 1228 8438  2  1 74 22  0

Attachment: README
Description: Binary data

-------------------------------------------------------------------------
This SF.Net email is sponsored by the Moblin Your Move Developer's challenge
Build the coolest Linux based applications with Moblin SDK & win great prizes
Grand prize is a trip for two to an Open Source event anywhere in the world
http://moblin-contest.org/redirect.php?banner_id=100&url=/
_______________________________________________
Bacula-devel mailing list
Bacula-devel@xxxxxxxxxxxxxxxxxxxxx
https://lists.sourceforge.net/lists/listinfo/bacula-devel


This mailing list archive is a service of Copilotco.