Is it time to start thinking about the future of MySQL?

[Peek2much2]

Matt,
I like you have been in this business a long time. We all know how Oracle operates and how they are all about money at the cost of freedom and openness. Mr. Ellison is not a friend of giving stuff away forever.
Wikipedia announced this week that they have begun to migrate to MariaDB and plan to move their English servers to it by the first quarter of next year.
What are your thoughts on this?
Here's what I see happening. One of these days we are going to wake up and read that our friends at Oracle have changed the game and either canned the community version or gone license crazy.
For those of us who have been around for a while, don't forget what SCO tried to do about 10 years ago and that if it hadn't been for IBM we'd be in trouble today.
Do not trust Oracle please! Start thinking of a plan B. Take it from the guys at Wikipedia they have seen the signs.

That's my humble post Matt.

John A. @ QR

[mflorell]

The alternative is that Oracle could release MySQL to the Apache foundation like it did with OpenOffice.org because of it's plummeting market share due to their meddling. I don't think that will necessarily happen with MySQL, but it is a possibility. Remember that since MySQL is open source there is really no threat of it just disappearing and not working some day. The older source code is still available, and you will still be able to install it even after a license change on a newer version.

We have clients that have tried MariaDB, and they have claimed good results. We are never fast to change versions without a lot of testing, and I'm sure after we finish getting Asterisk 1.8 integrated we will take some time to look at the database options available.

Thanks for your comments.

[Acidshock]

foxpro thats the way to go!

[deephack]

I'm just about to test a new dialler setup with one of our clients where I will one component at a time upgrade from this:-

Ubuntu 10.04
Mysql 5.1
HDD based RAID for DB storage

To this:-

Ubuntu 12.04
MariaDB 5.5
SDD based RAID for DB storage

I'm sure some of this will be good, some no different and some will be a pain in the arse. I'll report back with my findings. So far have found that compiling DAHDI support in asterisk 1.4 on newer kernels is fiddly but I did get it to work in the end. Will post instructions.

[mflorell]

Thank you for committing to testing and reporting your results on this.

We have had several cases where mysql 5.5 caused problems, and downgrading back to 5.1 fixed the problem, so I am very eager to hear how your testing goes.

Also, please include details on what table engine you are using, as well as whether you are using 64bit/32bit Linux and 64bit/32bit database.

[deephack]

No problem, happy to help where I can. I'll post back with all information next week.

[deephack]

Okay have been a bit delayed in my testing, will now commence tomorrow. Sorry for the wait!

[mflorell]

Since OpenSuSE is now officially switching to MariaDB it looks like this is going to go ahead sooner than we anticipated in the vicibox project at least.

We have looked into MariaDB and compatibility, and can't see any issues from what we've read in switching. As we get more information we will post it here as well.

[williamconley]

I don't know why I didn't see that coming. Fine. We'll start looking at loading MariaDB. I wonder which way Ubuntu is going?

[bmorrison]

Here's a story from yesterday regarding recent announcements of a couple of linux distros switching to MariaDB. It does a fairly decent job on the background of what led openSUSE and Fedora to make the change, including a good bit about Michael Widenius:

http://www.itwire.com/opinion-and-analy ... ndon-mysql

[Vince-0]

As far as I understand it - MariaDB is a drop in replacement for MySQL and MariaDB supports MyISAM table types so there shouldn't be any problem replacing MySQL. Now to test it...
--
Vin

[williamconley]

Let us know how your test goes Vin! Seriously! I am very curious.

[deephack]

Okay have now finished switching our test site over to MariaDB and am pleased to report that it was completely painless. On Ubuntu 12.04 I simply run the following commands.

sudo apt-get install python-software-properties
sudo apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db
sudo add-apt-repository 'deb http://mirror2.hs-esslingen.de/mariadb/repo/5.5/ubuntu precise main'
sudo apt-get update
sudo apt-get install mariadb-server

I'm running version 5.5 with InnoDB tables. My feeling is that responsiveness is improved but will see what the numbers say after running some tests.

[mflorell]

I would strongly recommend NOT using InnoDB. As your log tables get larger you will run into deadlock issues that will cause problems and lead to inaccuracies in the log data.

[williamconley]

Seriously. InnoDB is NOT more responsive than MyISAM. It will bog down under heavy load and you'll have a dead system. There could probably be SOME tables that would work with InnoDB, but if you put everything in it ... you're limiting your dialer to a "mini-dialer" status. Experience has proven this with everyone who has tried it to date. (But if you do insist on InnoDB, please share your results ... more empirical data is helpful!)

[deephack]

Hi Guys,

Thanks for the heads up on InnoDB. I didn't realise it was an issue. In this instance I think that it may actually be XtraDB tables as MariaDB reports them as InnoDB for compatibility. Does it suffer the same sort of issues?

[mflorell]

Yes, there are several storage engines that are based upon InnoDB, and to my knowledge they all suffer from the same issues that make them unsuitable for Vicidial use.

[williamconley]

Excellent for Banking (transaction safe). But they are too slow for Vicidial which requires very fast response on heavily used machines.

[ruben23]

Is MyISAM storage engine on mysql renamed on MariaDB..? or still MyISAM

[mflorell]

https://kb.askmonty.org/en/aria-storage-engine/

"The Aria storage engine is compiled in by default in MariaDB 5.1 and it is required to be 'in use' when mysqld is started.

Additionally, internal on-disk tables are in the Aria table format instead of the MyISAM table format. This should speed up some GROUP BY and DISTINCT queries because Aria has better caching than MyISAM. The inclusion of Aria is one of the differences between MariaDB 5.1 and MySQL 5.1."

[deephack]

I'll try switching over to aria tables on my testing system and see how I get on. Thanks again for the tips, looks like I'm being saved from some real problems in the future.

[williamconley]

I don't suspect you will see a difference at all on a testing system unless it is specifically designed to "overload". There are some test scenarios capable of overload that The Vicidial Group has included in the distro, of course. I would like to hear the results from that sort of test run.

[puccibox73]

We succesfully use in prduction a vicidial cluster bused upon 2 Asterisk Dialer/ Webserver with 4 T1/E1 lines each configured with one DB Server made with Mysql-PERCONA ... we have about 70 live agents at a time ... and our db server performs very well... and even setupping it didn't require nothing more than normal mysql configuration....

[mflorell]

The Percona engine is based upon InnoDB, and if you are using the default percona storange engine instead of MyISAM then over time it will become more slow and you will run into issues with deadlocking. You should check what storage engine you are using.

[puccibox73]

since we are developing a lot for our back office application ... we have added some database and or tables to default vicidial db...
we have left the vici db in myisam and it is the one with a lot of records... we use innodb engine of percona for our databases / stored procedures ...
it works great ! ... better than with the standard mysql... and when we used it we had less agents and same number of servers...

[ruben23]

guys, does Mysql 5.5 comes with MYisam as defautl engine..? or only in Mysql 5.1..? Thanks

[williamconley]

afaik myisam is the default engine throughout mysql dispos.

[dtucny]

In 2010 I migrated from MyISAM to InnoDB for most of the heavily used tables on a system I was managing.
The reason for doing this was the fact that we were running into performance problems using MyISAM as the database had grown over time and the number of agents had increased.
Agents and managers were more and more reporting issues such as slowness in the web interface, that calls were taking longer to be connected to them, predictive dialling getting worse connect rates over time and that there were some strange issues with logs and leads not always getting updates.
The issues we were experiencing almost entirely boiled down to table locks, in most cases on queries that with InnoDB would only require a row lock, allowing multiple updates to happen in parallel.
Switching to InnoDB on it's own was simple, just switch the engine in MySQL, however, testing highlighted some issues, these were mostly with queries not using keys. These issues were handled in two ways, 1) adding additional keys on tables where needed and 2) rewriting queries in VICIdial to better use keys, something that was much more important for updates on InnoDB where there is really something to be gained.

When this went live all the scaling issues we'd had were gone. 2 years later, everything on the database was still running smoothly, even if the company wasn't.
In that 2 years there were 0 deadlocks, all identified causes of deadlocks had been resolved during testing.
The only noticed downside to using InnoDB that we experienced was that the Lists screen was slower to load due to getting a count of leads in all lists being slower.

So, in summary, it is possible to use InnoDB. My experience is that is can give some very useful improvements, however, there is a decent chunk of work needed in adjusting queries in VICIdial to get the most benefit from it and avoid potential issues, but, that was worth it for us.

I'll be looking at this again soon, specifically from a Percona angle and will feed back my findings.

[williamconley]

You should post a diff against the version of vicidial you are using to the vicidial issue tracker with these settings.

Also of note: later versions have improved the count issue when showing the lists screen. So providing your solution may resolve your last problem if your changes become part of the code. now is a good time as the system is already being switched from mysql to mysqli. Even if your InnoDB change isn't made, the indexing changes may be helpful.

[mflorell]

Without the details this account doesn't mean much,
- number of agents
- number of servers
- DB hardware used(with CPU/RAM/drive specs)
- my.cnf settings
- queries per second

Having had email conversations with Percona engineers, I can assure you that even they don't recommend using InnoDB-based engines for Vicidial.

[williamconley]

True. But posting the diffs could mean someone else takes it to another stage. Presently it is merely in one system to which no one has access. If someone else takes it farther ... eventually this could be of benefit. Some of it may benefit without InnodB involvement as well. Knowledge is power. Post it!

[Vince-0]

Does MyISAM table locking provide a mechanism for queuing data access?
A race condition for example: the log table will be queried for available agents but an agents status update is delayed.

Does MyISAM perform better on inserts/updates when compared to InnoDB?
I'm not sure of any disadvantages besides the above premise.
Differences between MyISAM to InnoDB include table locking vs row locking, transactions, referential integrity (at the cost of performance?).

MySQL might outlive most of us.
Vin.

[Acidshock]

I was thinking about this and had a general question about it. If this was implemented couldn't we use the threadpooling feature that MariaDB has and quite possibly drastically improve performance?

[williamconley]

We're all waiting to hear your results. Try it!

[deephack]

I'm just getting around to fixing this on our production servers now and plan on using the following command to convert from InnoDB to MyISAM:-

ALTER TABLE t1 ENGINE=MyISAM;

Does this look okay to you guys? I'll obviously be taking a backup

[deephack]

Okay just went ahead and did this and it worked perfectly, furthermore server load is less than a quarter of what it was under InnoDB. This is running on MariaDB 5.5 with no other changes. I'm wondering if the improvements dtuncy saw when he switched to InnoDB was actually the result of making changes to the keys and queries.

[williamconley]

Okay just went ahead and did this and it worked perfectly, furthermore server load is less than a quarter of what it was under InnoDB. This is running on MariaDB 5.5 with no other changes. I'm wondering if the improvements dtuncy saw when he switched to InnoDB was actually the result of making changes to the keys and queries.

The "Vicidial Experience" is highly personalized. He could be running with 5 agents but pulling 100 channels per agent ... and having issues with 'table level locking' in myisam to generate the hopper ... or he could be running with 200 agents on inbound and manual dial and not really be using the hopper at all, but having lots of report calls and client data exchanges.

The intricacies in interaction of a mysql server are often backwards of what is expected, but most importantly: The deep testing of the Vicidial Community has shown that myisam kicks butt on innodb for almost all call center usage. So you'd have to check with that particular user to see what his business model is that causes his peculiar results. Could be educational for all of us.