-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 I added the indices back and performance dramatically increased. I assume my tables needed a repair and that that was the initial problem (maybe caused by the upgrade I had recently done from 2.2.8 to 5.0.3). While restores essentially hung before I dropped the indices and dropped to maybe 30 seconds to build a ~15GB directory tree, after adding them back, it's now nearly instantaneous.
On 01/15/2013 11:03 AM, Novosielski, Ryan wrote: > I would say it depends on your read of that article and either way > whether or not you believe it. I'm not quite sure what to think. I > believe I will re-add the indices I dropped and see what happens. > > ----- Original Message ----- From: Uwe Schuerkamp > [mailto:uwe.schuerk...@nionex.net] Sent: Tuesday, January 15, 2013 > 10:32 AM To: Novosielski, Ryan Cc: John Drescher > <dresche...@gmail.com>; bacula-users > <Bacula-users@lists.sourceforge.net> Subject: Re: [Bacula-users] > restore performance > > On Mon, Jan 14, 2013 at 09:01:32AM -0500, Novosielski, Ryan wrote: > >>> http://wiki.bacula.org/doku.php?id=faq#restore_takes_a_long_time_to_retrieve_sql_results_from_mysql_catalog >> >> >>> Actually, >>> >> after re-reading that, I'm a little confused. I don't know >> whether it says to "drop all indexes except for primary and then >> drop the listed indices" or "drop everything except for primary >> and except for the listed indices." >> >> In any case, this solved the problem for me. I wonder if a >> repair would have also and maybe that's why? >> > > I have the following indexes defined on the "File" table: > > MariaDB [bacula]> show indexes in File ; > +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ > > | Table | Non_unique | Key_name | Seq_in_index | Column_name | > Collation | Cardinality | Sub_part | Packed | Null | Index_type | > Comment | Index_comment | > +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ > > | File | 0 | PRIMARY | 1 | FileId | A > | 947714927 | NULL | NULL | | BTREE | | > | | File | 1 | JobId | 1 | JobId | A > | 2513 | NULL | NULL | | BTREE | | > | | File | 1 | JobId_2 | 1 | JobId | A > | 2513 | NULL | NULL | | BTREE | | > | | File | 1 | JobId_2 | 2 | PathId | A > | 30571449 | NULL | NULL | | BTREE | | > | | File | 1 | JobId_2 | 3 | FilenameId | A > | 947714927 | NULL | NULL | | BTREE | | > | > +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ > > 5 rows in set (0.00 sec) > > MariaDB [bacula]> select count(*) from File ; +-----------+ | > count(*) | +-----------+ | 947749966 | +-----------+ 1 row in set > (0.00 sec) > > So it seems I'm more or less in the clear regarding our index > setup, or am I missing something? > > Cheers & thanks in advance, Uwe > > > > ------------------------------------------------------------------------------ > > Master SQL Server Development, Administration, T-SQL, SSAS, SSIS, SSRS > and more. Get SQL Server skills now (including 2012) with > LearnDevNow - 200+ hours of step-by-step video tutorials by > Microsoft MVPs and experts. SALE $99.99 this month only - learn > more at: http://p.sf.net/sfu/learnmore_122512 > _______________________________________________ Bacula-users > mailing list Bacula-users@lists.sourceforge.net > https://lists.sourceforge.net/lists/listinfo/bacula-users > - -- - ---- _ _ _ _ ___ _ _ _ |Y#| | | |\/| | \ |\ | | |Ryan Novosielski - Sr. Systems Programmer |$&| |__| | | |__/ | \| _| |novos...@umdnj.edu - 973/972.0922 (2-0922) \__/ Univ. of Med. and Dent.|IST/EI-Academic Svcs. - ADMC 450, Newark -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.11 (GNU/Linux) Comment: Using GnuPG with undefined - http://www.enigmail.net/ iEYEARECAAYFAlD/ZFUACgkQmb+gadEcsb7QjACgh2I9brotUCzV17wIqaUfEaX7 4D8AoNNw+SvElefUPWV1t175yl7RTPDt =RwzM -----END PGP SIGNATURE----- ------------------------------------------------------------------------------ Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS, MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft MVPs and experts. ON SALE this month only -- learn more at: http://p.sf.net/sfu/learnnow-d2d _______________________________________________ Bacula-users mailing list Bacula-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/bacula-users
