[mantisbt-dev] Mysql Engine(s)

[Paul Richards]

This is following up from a previous mailing list discussion [see below (A)] regarding changing to innodb.



I've submitted a PR to convert tables to innodb as a DB step. Note: I've *not* included any change at the moment to not install with innodb in the first place (although that would be obvious step)



Rationale for looking at this is:

a) I believe innodb is deemed to be more reliable for error recovery

b) gap between myisam/innodb for performance has closed

c) mysql seem to have moved to innodb by default rather than myisam



Test database for this has ~12,000 bugs, ~150,000 bug history records and ~20,000 users



Accessing Summary Page [5 concurrent users]




MYISAM

INNODB




~18.9 requests / min



46.7KB/sec throughput



~51.5 requests/min



132KB/s throughput



Obviously, need to do some more performance benchmarking then just one page, and test against different load types – I don’t think I was expecting innodb to appear on the surface to respond twice as fast, so I’ve put an early version of innodb conversion script up in the hope someone else might do some performance testing




Link to github PR so we can have a discussion on conversion process is at https://github.com/mantisbt/mantisbt/pull/290. I’ll close this pull request 6th September @ 9am UTC and create a final patch that deals with db installation etc for review, depending on results of performance testing.



Paul

One thing I've come to realise this week whilst looking at DB bits -
we currently force myisam as table format. Mysql changed default from
myisam to innodb in 5.5
In fact, I just browsed the release notes for the development 5.7
mysql version, and myisam is barely mentioned. (one of only references
I could find was "The MySQL test suite mysql-test-run.sh program now
starts the server with InnoDB rather than MyISAM as the default
storage engine.")
At the moment, we force the table format to myisam, should we be dropping this, and letting mysql pick [which probably makes the default innodb], or changing to innodb ?

I would say that InnoDB is definitely preferable, so let's make it the default.
The danger with leaving this empty is that we get inconsistent results on different installations, depending on what the user's defaults are
-> support nightmare.

[Avetis Avagyan]

Is it a requirement to force for any particular storage engine? Can't we simply leave this to the defaults of server where DB will run? One likes more InnoDB, another one MyISAM, and someone else something else - for number of good reasons.

Thanks,
Avetis

________________________________
Sent: Wednesday, September 3, 2014 11:54 PM
Subject: [mantisbt-dev] Mysql Engine(s)
This is following up from a previous mailing list discussion [see below (A)] regarding changing to innodb.
I've submitted a PR to convert tables to innodb as a DB step. Note: I've *not* included any change at the moment to not install with innodb in the first place (although that would be obvious step)
a) I believe innodb is deemed to be more reliable for error recovery
b) gap between myisam/innodb for performance has closed
c) mysql seem to have moved to innodb by default rather than myisam
Test database for this has ~12,000 bugs, ~150,000 bug history records and ~20,000 users
Accessing Summary Page [5 concurrent users]
MYISAM INNODB
~18.9 requests / min
46.7KB/sec throughput
~51.5 requests/min
132KB/s throughput
Obviously, need to do some more performance benchmarking then just one page, and test against different load types – I don’t think I was expecting innodb to appear on the surface to respond twice as fast, so I’ve put an early version of innodb conversion script up in the hope someone else might do some performance testing

Paul

One thing I've come to realise this week whilst looking at DB bits -
we currently force myisam as table format. Mysql changed default from
myisam to innodb in 5.5
In fact, I just browsed the release notes for the development 5.7
mysql version, and myisam is barely mentioned. (one of only references
I could find was "The MySQL test suite mysql-test-run.sh program now
starts the server with InnoDB rather than MyISAM as the default
storage engine.")
At the moment, we force the table format to myisam, should we be dropping this, and letting mysql pick [which probably makes the default innodb], or changing to innodb ?

I would say that InnoDB is definitely preferable, so let's make it the default.
The danger with leaving this empty is that we get inconsistent results on different installations, depending on what the user's defaults are
-> support nightmare.

------------------------------------------------------------------------------
Slashdot TV.
Video for Nerds. Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
mantisbt-dev mailing list
https://lists.sourceforge.net/lists/listinfo/mantisbt-dev

[P Richards]

“Is it a requirement to force for any particular storage engine? Can't we simply leave this to the defaults of server where DB will run? One likes more InnoDB, another one MyISAM, and someone else something else - for number of good reasons. Thanks, Avetis“



Currently we force myisam (this is probably incorrect behaviour). Especially given mysql 5.5 changes the default engines from myisam to innodb



Mariadb/percona etc implement different storage engines, which we may want to allow.



However, given myisam doesn’t support transactions and similar things – maybe a better question is “should we move away from myisam” as opposed to “should we use innodb”.



Avetis: from your point of view, do you think there’s people that would definitely want myisam? My understanding is that mysql themselves have stepped away from myisam a bit, and that it’s easier to get into a mess with corrupt tables on that engine.



From: Paul Richards [mailto:***@blueyonder.co.uk]
Sent: 03 September 2014 22:54
To: 'developer discussions'
Subject: [mantisbt-dev] Mysql Engine(s)



This is following up from a previous mailing list discussion [see below (A)] regarding changing to innodb.



I've submitted a PR to convert tables to innodb as a DB step. Note: I've *not* included any change at the moment to not install with innodb in the first place (although that would be obvious step)



Rationale for looking at this is:

a) I believe innodb is deemed to be more reliable for error recovery

b) gap between myisam/innodb for performance has closed

c) mysql seem to have moved to innodb by default rather than myisam



Test database for this has ~12,000 bugs, ~150,000 bug history records and ~20,000 users



Accessing Summary Page [5 concurrent users]




MYISAM

INNODB




~18.9 requests / min



46.7KB/sec throughput



~51.5 requests/min



132KB/s throughput



Obviously, need to do some more performance benchmarking then just one page, and test against different load types – I don’t think I was expecting innodb to appear on the surface to respond twice as fast, so I’ve put an early version of innodb conversion script up in the hope someone else might do some performance testing




Link to github PR so we can have a discussion on conversion process is at https://github.com/mantisbt/mantisbt/pull/290. I’ll close this pull request 6th September @ 9am UTC and create a final patch that deals with db installation etc for review, depending on results of performance testing.



Paul

One thing I've come to realise this week whilst looking at DB bits -
we currently force myisam as table format. Mysql changed default from
myisam to innodb in 5.5
In fact, I just browsed the release notes for the development 5.7
mysql version, and myisam is barely mentioned. (one of only references
I could find was "The MySQL test suite mysql-test-run.sh program now
starts the server with InnoDB rather than MyISAM as the default
storage engine.")
At the moment, we force the table format to myisam, should we be dropping this, and letting mysql pick [which probably makes the default innodb], or changing to innodb ?

I would say that InnoDB is definitely preferable, so let's make it the default.
The danger with leaving this empty is that we get inconsistent results on different installations, depending on what the user's defaults are
-> support nightmare.

[Avetis Avagyan]

Hi Paul,

Back to your question - I think that there is good number of people using MyISAM, but I don't have market share stats to support this statement. I quickly googled on the subject, but in vain.

I still think that going with 'local' defaults is a good option. It will be either defaults coming from MySQL (different depending on version) or defaults set by DBA. I don't see much benefits in forcing storage engine in attempt to 'override' defaults of 'local'.

Best,
Avetis

________________________________
Sent: Thursday, September 4, 2014 11:35 PM
Subject: Re: [mantisbt-dev] Mysql Engine(s)
“Is it a requirement to force for any particular storage engine? Can't we simply leave this to the defaults of server where DB will run? One likes more InnoDB, another one MyISAM, and someone else something else - for number of good reasons. Thanks, Avetis“
Currently we force myisam (this is probably incorrect behaviour). Especially given mysql 5.5 changes the default engines from myisam to innodb
Mariadb/percona etc implement different storage engines, which we may want to allow.
However, given myisam doesn’t support transactions and similar things – maybe a better question is “should we move away from myisam” as opposed to “should we use innodb”.
Avetis: from your point of view, do you think there’s people that would definitely want myisam? My understanding is that mysql themselves have stepped away from myisam a bit, and that it’s easier to get into a mess with corrupt tables on that engine.
Sent: 03 September 2014 22:54
To: 'developer discussions'
Subject: [mantisbt-dev] Mysql Engine(s)
This is following up from a previous mailing list discussion [see below (A)] regarding changing to innodb.
I've submitted a PR to convert tables to innodb as a DB step. Note: I've *not* included any change at the moment to not install with innodb in the first place (although that would be obvious step)
a) I believe innodb is deemed to be more reliable for error recovery
b) gap between myisam/innodb for performance has closed
c) mysql seem to have moved to innodb by default rather than myisam
Test database for this has ~12,000 bugs, ~150,000 bug history records and ~20,000 users
Accessing Summary Page [5 concurrent users]
MYISAM INNODB
~18.9 requests / min
46.7KB/sec throughput
~51.5 requests/min
132KB/s throughput
Obviously, need to do some more performance benchmarking then just one page, and test against different load types – I don’t think I was expecting innodb to appear on the surface to respond twice as fast, so I’ve put an early version of innodb conversion script up in the hope someone else might do some performance testing

Paul

One thing I've come to realise this week whilst looking at DB bits -
we currently force myisam as table format. Mysql changed default from
myisam to innodb in 5.5
In fact, I just browsed the release notes for the development 5.7
mysql version, and myisam is barely mentioned. (one of only references
I could find was "The MySQL test suite mysql-test-run.sh program now
starts the server with InnoDB rather than MyISAM as the default
storage engine.")
At the moment, we force the table format to myisam, should we be dropping this, and letting mysql pick [which probably makes the default innodb], or changing to innodb ?

I would say that InnoDB is definitely preferable, so let's make it the default.
The danger with leaving this empty is that we get inconsistent results on different installations, depending on what the user's defaults are
-> support nightmare.

------------------------------------------------------------------------------
Slashdot TV.
Video for Nerds. Stuff that matters.
http://tv.slashdot.org/
_______________________________________________
mantisbt-dev mailing list
https://lists.sourceforge.net/lists/listinfo/mantisbt-dev

[Damien Regad]

I still think that going with 'local' defaults is a good option. [...]
I don't see much benefits in forcing storage engine
in attempt to 'override' defaults of 'local'.

+1