Alan,
My file table currently has 434026569 (It grows to over 600Million
rows)., it is running on a server with 16GB of ram and a decent drive
array. We have dedicated most of the ram in the box to mysql. With
these extra indexes we end up with a lot of IO wait and much slower
backups.
Before I run the DB check I use this command to setup the two indexes:
alter table File add index idxFI (FilenameId), add index idxPI
(PathId);
I do it this way instead of the way that the DB check does as this will
setup the indexes in one pass instead of two and take 1/2 the time.
-Jason
On Mon, 2011-03-07 at 15:46 +0000, Alan Brown wrote:
> From: "Jason A. Kates" <[email protected]>
>
> > The day to day Mysql performance for normal Bacula operation is not
> > palatable with the indexes setup on the tables. (IE mysql is so slow
> > it's not usable with bacula IMO).
>
> Mysql was "fast enough" for us until we got to ~150 million entries, but
> you MUST tune it and provide enough ram to operate without swapping.
>
> The defaults are intended for a small database on a desktop, sub-64Mb
> machine. Even the "large ram" sample uses a tiny amount of memory by
> today's server standards.
>
> I switched to Postgres for a number of reasons, but they don't really
> become a major driver for changing until there are ~50-100 million
> entries in the database. Below that MySQL has an advantage.
>
> Locking has never been a problem for us. As Kern has explained, it's
> only used on batch inserts.
>
> Having the extra indexes caused no noticeable slowdown on MySQL inserts,
> but was a huge win for dbcheck.
>
>
>
>
>
> ------------------------------------------------------------------------------
> What You Don't Know About Data Connectivity CAN Hurt You
> This paper provides an overview of data connectivity, details
> its effect on application quality, and explores various alternative
> solutions. http://p.sf.net/sfu/progress-d2d
> _______________________________________________
> Bacula-devel mailing list
> [email protected]
> https://lists.sourceforge.net/lists/listinfo/bacula-devel
--
----------------------------------------------------------------------------
Jason A. Kates ([email protected])
Fax: 208-975-1514
Phone: 660-960-0070
============================================================================
------------------------------------------------------------------------------
What You Don't Know About Data Connectivity CAN Hurt You
This paper provides an overview of data connectivity, details
its effect on application quality, and explores various alternative
solutions. http://p.sf.net/sfu/progress-d2d
_______________________________________________
Bacula-devel mailing list
[email protected]
https://lists.sourceforge.net/lists/listinfo/bacula-devel
