On Thursday 2017-01-12 17:21:05 Alan Brown wrote:
> On 12/01/17 14:14, Josip Deanovic wrote:
> > So if one is for some reason locked to specific old version of a
> > specific old proprietary application one can't do much than continue
> > with the MyISAM as innodb is not an option and external search engines
> > are not supported either.
>
> I've had to deal with such software:
>
>
> Once the database is created "UPDATE TABLE table ENGINE=MYISAM" works
> just fine in 99.9% of cases.
This is only the last step.
Before that you will need to analyze your database and carefully prepare
the server for the migration. Especially if your MySQL server is heavily
optimized for MyISAM only setup.
For example, if you have optimized the server for MyISAM you probably
chose not to use large pages (which also requite some kernel tweaking)
because MyISAM doesn't benefit from it.
If you are preparing to migrate to innodb you might consider to optimize
the server for the innodb only setup which has somewhat different
requirements relating the way memory and disk is accessed.
If the server wasn't really optimized for MyISAM and after the migration
you didn't monitor the server and optimized it for innodb then it is
questionable if it was really needed (performance wise, of course there
is still benefit from better chances for data consistency but then again
that depends of the configuration and level of optimization).
> The only table I ever had trouble with was in GLPI, where one search
> function failed using innodb, but that was fixed in a more recent
> version of mysql anyway.
Exactly. As Phil mentioned it's the fulltext search feature.
> > I am not saying that innodb isn't a better mysql storage engine when
> > compared to MyISAM. I am saying that sometimes the innodb is not the
> > option and you have to stick with MyISAM for some time.
>
> At the risk of offending someone:
I estimate the risk as low. :-)
> 1: If your database load is high enough that you really need to worry
> about myisam vs innodb for performance reasons then you should be
> considering using something like PostgreSQL anyway.
Again, it depends on the application db support and whether you
can deal with it or not.
We went off-topic in this thread as we started to talk about the myisam
and innodb in general and not just in context of Bacula support.
I find myself guilty. :-)
> 2: If Bacula has caused memory consumption of MySQL to grow larger than
> 4GB, it's time to switch to PostgreSQL.
I have just checked one of my bacula setups. The database is 16G in size
and there are no performance or any other issues whatsoever.
Interesting thing is that this particular database I have checked
resides on vmware virtual machine (with fast disks), few CPUs and just
few gigabytes of RAM.
I have to admit that the number of servers in this particular setup
is less than 100 and that jobs are not executing concurrently. Might
be a bit different story otherwise.
> 3: If your database must have utterly reliable recovery from a crash
> (including write recovery), it's time to look at using something other
> than mysql.
Absolutely agree.
But as I already said, it depends on the application db support.
One would first need to move from the old application if possible.
> I'm not "dissing" MySQL. It's a brilliant database for what it's
> designed to do - a small, fast, simple query-optimised database for
> things like webservers.
I believe that "brilliant" is a too strong word in this context. :-)
> It's not designed to be used in a write-heavy multiuser environment or
> with lots of complex queries. Once the system load (cpu and memory) of
> mysql exceeds that of another database (or queries are executed
> noticeably slower) then it's time to switch.
Yes, these are the MySQL's weak points (among others).
> If you only have a hammer then every problem looks like a nail.
>
> Even if you "only know mysql", it's not difficult to change to something
> else - and once you do, you'll be happily surprised to notice how
> little tuning pg_sql needs (almost none: Everything is designed to be
> automatically optimising and there's a pg_tune script which will do all
> the startup stuff for you)
I am too much into mysql and postgres to be able to agree with this.
In my opinion in order to get most out of these databases it would
take yeeears of hard work, reading books and manuals, experimenting,
reading their source, cursing, banging desk with a head, debugging,
cursing again, monitoring, graphing and so on (at least that's what it
took me to get it).
In short if you master only one of those databases there is a whole
nee world that awaits to be mastered when you start working with the
other one.
> Example: The Bacula system here needed regular MySQL maintenance. After
> switching to PGsql I find that I only ever look at it to confirm it's
> still working ok.
Interesting. I don't have any problems whatsoever and I only have to
deal with jobs, not with the database (once it is setup and optimized)
regardless whether it is mysql or postgres.
> On a very small (sub 1GB) system, PostgreSQL vs MySQL will (on paper)
> favour mysql because of the no-load/no-data footprints, but on just
> about any x86 system made in the last 10 years there's no fundamental
> disadvantage to using PostgreSQL from the outset (and doing so avoids
> possibly crossing paths with Oracle in future)
>
>
> If you are backing up databases:
>
>
> The "best" strategy for DB table backup is DO NOT DO IT - generate a DB
> dump using the appropriate tools and back that up. Anything else will
> cause problems when it's restoration time.
There are however tools for both mysql and postgres that can safely
copy the datadir without dump. But of course this is not the table
backup you have been referring to.
The problem with dump and restore is that they take awful lot of time
and sometimes they are impractical. Fortunately there are other tools
and approaches.
Regards
--
Josip Deanovic
------------------------------------------------------------------------------
Developer Access Program for Intel Xeon Phi Processors
Access to Intel Xeon Phi processor-based developer platforms.
With one year of Intel Parallel Studio XE.
Training and support from Colfax.
Order your platform today. http://sdm.link/xeonphi
_______________________________________________
Bacula-users mailing list
Bacula-users AT lists.sourceforge DOT net
https://lists.sourceforge.net/lists/listinfo/bacula-users
|
