Re: [Bacula-users] restore performance
2013-01-22 23:21:23
-----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.schuerkamp AT nionex DOT net] Sent: Tuesday, January 15, 2013
> 10:32 AM To: Novosielski, Ryan Cc: John Drescher
> <drescherjm AT gmail DOT com>; bacula-users
> <Bacula-users AT lists.sourceforge DOT 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 AT lists.sourceforge DOT net
> https://lists.sourceforge.net/lists/listinfo/bacula-users
>
- --
- ---- _ _ _ _ ___ _ _ _
|Y#| | | |\/| | \ |\ | | |Ryan Novosielski - Sr. Systems Programmer
|$&| |__| | | |__/ | \| _| |novosirj AT umdnj DOT 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 AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
<Prev in Thread] |
Current Thread |
[Next in Thread>
|
- Re: [Bacula-users] restore performance, (continued)
- Re: [Bacula-users] restore performance, Novosielski, Ryan
- Re: [Bacula-users] restore performance, John Drescher
- Re: [Bacula-users] restore performance, Uwe Schuerkamp
- Re: [Bacula-users] restore performance, John Drescher
- Re: [Bacula-users] restore performance, Uwe Schuerkamp
- Re: [Bacula-users] restore performance, Novosielski, Ryan
- Re: [Bacula-users] restore performance, Novosielski, Ryan
- Re: [Bacula-users] restore performance, Uwe Schuerkamp
- Re: [Bacula-users] restore performance, Novosielski, Ryan
- Re: [Bacula-users] restore performance,
Novosielski, Ryan <=
- Re: [Bacula-users] restore performance, Jérôme Blion
|
|
|