Optimize Mysql

classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

Optimize Mysql

Petar Kozić
Hi,

My bacula servers works now about 2 months with about 3 TB backuped data, on about 10 servers.
I see it a little slowed down, and much backup job is in queue.

I want to do optimize mysql.

What is the safest way to do that ? 

Thank you in advanced.



Petar Kozić
System Administrator


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|

Re: Optimize Mysql

francisco javier funes nieto
Change the DB to PostgreSQL maybe ?
MySQLtuner.pl Script ?

What's the size of the DB? 

J.


 

2017-06-01 10:38 GMT+02:00 Petar Kozić <[hidden email]>:
Hi,

My bacula servers works now about 2 months with about 3 TB backuped data, on about 10 servers.
I see it a little slowed down, and much backup job is in queue.

I want to do optimize mysql.

What is the safest way to do that ? 

Thank you in advanced.



Petar Kozić
System Administrator


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users




--
_____________________________________________

Francisco Javier Funes Nieto [[hidden email]]
CANONIGOS
Servicios Informáticos para PYMES.
Cl. Cruz 2, 1º Oficina 7
Tlf: 958.536759 / 661134556
Fax: 958.521354
GRANADA - 18002

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|

Re: Optimize Mysql

Petar Kozić
Thank you for answer but I wish to stay on MySQL.
I was alredy tuning mysql, I don’t need that.

I was read somewhere in bacula documentation, that bacula have some script for mysql optimization.

My question is for that. Thank you.


Petar Kozić
System Administrator

On June 1, 2017 at 11:34:06 AM, Francisco Javier Funes Nieto ([hidden email]) wrote:

Change the DB to PostgreSQL maybe ?
MySQLtuner.pl Script ?

What's the size of the DB? 

J.


 

2017-06-01 10:38 GMT+02:00 Petar Kozić <[hidden email]>:
Hi,

My bacula servers works now about 2 months with about 3 TB backuped data, on about 10 servers.
I see it a little slowed down, and much backup job is in queue.

I want to do optimize mysql.

What is the safest way to do that ? 

Thank you in advanced.



Petar Kozić
System Administrator


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users




--
_____________________________________________

Francisco Javier Funes Nieto [[hidden email]]
CANONIGOS
Servicios Informáticos para PYMES.
Cl. Cruz 2, 1º Oficina 7
Tlf: 958.536759 / 661134556
Fax: 958.521354
GRANADA - 18002

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|

Re: Optimize Mysql

Josip Deanovic
On Thursday 2017-06-01 05:14:14 Petar Kozić wrote:
> Thank you for answer but I wish to stay on MySQL.
> I was alredy tuning mysql, I don’t need that.
>
> I was read somewhere in bacula documentation, that bacula have some
> script for mysql optimization.
>
> My question is for that. Thank you.

You mean dbcheck tool that comes with bacula?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|

Re: Optimize Mysql

Petar Kozić
Yes, how to use ?

On Jun 1, 2017 2:35 PM, "Josip Deanovic" <[hidden email]> wrote:
On Thursday 2017-06-01 05:14:14 Petar Kozić wrote:
> Thank you for answer but I wish to stay on MySQL.
> I was alredy tuning mysql, I don’t need that.
>
> I was read somewhere in bacula documentation, that bacula have some
> script for mysql optimization.
>
> My question is for that. Thank you.

You mean dbcheck tool that comes with bacula?

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|

Re: Optimize Mysql

Josip Deanovic
On Thursday 2017-06-01 15:49:03 Petar Kozić wrote:
> Yes, how to use ?

If you have a time window when no backup is performed I suggest
you run it during that time.
As a precaution I also like to stop the bacula-sd and bacula-dir
every time I do manual modifications on the database or on the
volumes.

It can be run like this: dbcheck -c /etc/bacula/bacula-dir.conf

In this case the tool will read the database connection parameters
from the bacula-dir config file.

For more info use "dbcheck -?" or check the excellent Bacula
documentation.

Dbcheck has an interactive interface and it is used by default.
It has an option to actually modify the database and it is
not turned on by default. Make sure to turn it on if you
want to actually do the modifications to the database.

Some of the available options in the menu will offer you
to temporary create indexes. It can greatly reduce the time
so I suggest that you use it. Otherwise it could take more
than a day to go through it (depending on the amount of the
data in the database).

If you chose to use temporary indexes (I suggest that you do)
your file system containing the database files might need
the additional free space up to the space which is already
occupied by the database files.

Make sure that you have enough space on the file system
used by the database files otherwise you will fail to
successfully use the dbcheck tool with temporary indexes.

--
Josip Deanovic

------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|

Re: Optimize Mysql

Alan Brown
In reply to this post by Petar Kozić
On 01/06/17 14:49, Petar Kozić wrote:
> Yes, how to use ?

dbcheck checks the integrity of the databases, not the tuning of them.



With regard to tuning of mysql
   - bear in mind that  I'm saying this from the point of view of having
spent years doing exactly that for Bacula.


DON'T DO IT.

If you you think you need to - DON'T.
If you already are - STOP.

If you're having to spend hours tuning MySQL then you're operating
outside of the intended operating parameters for the database and you'll
probably have to keep returning to the problem as the load changes.
MySQL's memory requirements crank up rapidly and end up driving server
requirements to extreme levels.

Once you're at that position (and preferably well before it), it's time
to move to PostgreSQL.

It may seem daunting, and is a big step (changing your DB engine always
is), but it's a necessary one if your backup sets are growing.

When you're into the "tens of millions of files in the database"
territory, you'll find that PgSQL uses less memory, less CPU and is a
_lot_ faster than MySQL for pretty much everything. The time spent
learning how to use it (which is about 8-10 hours at most and usually
more like 2-3) is more than made up by both the reduction in overheads
keeping a MySQL-based Bacula system going AND the cost reduction in
server hardware to support the MySQL database.


If your Backup sets are large enough that you need to consider "tuning"
MySQL, then that time is better spent installing PgSQL.
MySQL tuning for a backup system at largeish scales ends up being a
"black hole" in terms of administrative time and can lead to substantial
recovery delays when (not if) MySQL breaks.

I'm sure this will trigger religious arguments about databases, but bear
in mind that I held off moving to PgSQL for 7-8 years because of fears
over complexity and difficulty - which proved to be baseless. The
hardest part of the transition turned out to be making a MySQL dump that
was properly portable - read the man pages because a standard mysqldump
is not ansi compliant.

(The same database in PgSQL uses less than 1/2 the system resources and
under 1/4 memory that it used in MySQL, so this transition was worth it
when adding another 48GB ram to the system would have cost over $2000.
It's still worth it today despite memory being cheaper, because for the
most part PgSQL is self-tuning, meaning far less administrative overhead
on a stressed system)


For similar reasons, I would strongly _discourage_ installing a new
Bacula system based on MySQL. Changing databases is a lot harder than
choosing one in the first place which will handle growth without needing
substantial administrative time.



--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.


------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users
Reply | Threaded
Open this post in threaded view
|

Re: Optimize Mysql

Uwe Schuerkamp
Dear all,

I'm usually not prone to responding to posts w/r to
"DB religions", but OTOH I believe that most of the mysql problems may
have resulted from historical issues, like using myisam as the storage
backend, *not* using mariadb and so on.

We run 5 bacula instances, all backed by mariadb (5.x and 10.x), and
they tick along just fine. The largest instance has 600 million rows
in the File table (it had over one *billion* at some point in the
past) with a total DB size of around 140GB, and backup performance is
just fine (the db server which also runs the bacula processes has a
mere 48GB of RAM, we've limited mariadb / innodb's buffer pool size to
exactly half of that; it also serves as an smb / nfs share for other
backup systems like Oracle Enterprise manager, Veeam and so on).

Sure, restores can take a while when bacula pulls together all the
needed information from fulls, incrementals, diffs and what not so
you'll be able to get a coffee (and probably finish it) until you'll
be greeted with the restore marker prompt in bconsole, but so what?
Restores aren't needed all that often, and as long as backups (we run
16 parallel jobs to disk storage) run more or less maxxing out the
disk / network throughput, I don't see any reason to change a running
system. There's always brestore / bscan to help you restore stuff
instantly from any given job if you don't have the 15 minutes it might
take bacula to piece together a complete file list.

I don't recall exactly when we switched from Amanda to Bacula, but I
think we're probably approaching a decade of operating bacula in a
production environment. The issues I've had with bacula that can be
traced to having been caused by using MySQL / MariaDB as the db
backend can be counted on one hand, and yes, that includes the
artificial limbs of pirate captains too as the number is closer to
zero than to one. ;-)

Don't get me wrong, I love postgres and have used it successfully in
other projects in the past, but I still prefer mysql / mariadb for its
ease of installation and administration. One of these days I *will*
get around to using pg for my bacula catalog, I promise ;-)

As to the original poster's query: Maybe your problems aren't related
to mysql, but some other issue like a network / disk / tape
bottleneck? Some more information on the bacula version, director / db
machine specs and backup media / tech used would be really helpful in
tracking down your problem.

All the best,

Uwe

--
Uwe Schürkamp | email: <[hidden email]>
Arvato Systems S4M GmbH | Sitz Köln | Amtsgericht Köln HRB 27038








------------------------------------------------------------------------------
Check out the vibrant tech community on one of the world's most
engaging tech sites, Slashdot.org! http://sdm.link/slashdot
_______________________________________________
Bacula-users mailing list
[hidden email]
https://lists.sourceforge.net/lists/listinfo/bacula-users