MySQL reliability


#1

Hi all!

I’m a potential new DreamHost customer, I must say I’m really impressed with the hosting plans and prices for the most part. Not to mention the amount of favourable things existing members have to say about the level of customer service they’ve received.

My Concern:

I’m re-vamping my existing website which employs mostly PHP and Perl scripts, with a non-transactional MySQL database. One of the requirements of my new design is transactional database support (preferrably either MySQL or PostgreSQL). Unfortunately, DreamHost does not currently support either for their shared hosting plans. I realize that this is not unique to DreamHost, most hosting companies are the same when it comes to this issue.

My question is therefore intended for veteran MySQL users at DreamHost: have any of you experienced lost connections when using MySQL with any of your pages/scripts? Those of you who are familiar with ACID will know why this is a concern for me. Basically, I don’t want to risk placing my database’s data in a “bad” state if a connection is lost for some reason. I know that MySQL in particular can be nasty when the server is under extrreme loads - previously opened connections can be “lost”.

All of my re-designed scripts (both PHP and Perl) use custom classes to re-use a single database connection for the entirety of the script, and close it when finished.

I’m not exactly sure how many queries or connections the old version of my website received, but I can tell you that at peak times I was receiving an average of 1700 page views per day according to Webalizer stats.

If enough (unbiased) people convince me that there should be no risk, then I may very well signup soon!

–Brad Barkhouse
Be a Contender!
http://www.beacontender.com
brad.barkhouse@beacontender.com


#2

I just had another thought. Is it possible that I could be setup with a separate mysqld instance which had the transaction-safe tables enabled? I would certainly be willing to pay extra for it, and it would prevent my site from affecting others. I can easily change my scripts to enable them to access the separate mysqld instance on any machine or port.

–Brad Barkhouse
Be a Contender!
http://www.beacontender.com
brad.barkhouse@beacontender.com


#3

First off triple posting your questions reduces the odds you’ll get an answer not increases them…

I can’t give you an answer on your main question because my site is database generated rather than database driven…

And the question you posted after the first post is best sent directly to sales – Sales@DreamHost.com


#4

Sorry about the triple post thing, I was anxious. :frowning: I won’t be doing it again.

You’ve perked my curiousity, what’s the difference between database-generated and database-driven?


#5

Database-generated means that data came from a database, perhaps like a web page where the data is stored in a table. Database-driven means that it goes back to the database to decide what action to take.

  • wil

#6

In that case, my site is both database-generated AND driven.

–Brad Barkhouse
Be a Contender!
http://www.beacontender.com
brad.barkhouse@beacontender.com


#7

Actually Database Generated means the files are generated each time the site is rebuilt rather than drawing the freshest data out of the database each time someone views the page – wastes drives space saves processer time…


#8

We’re currently investigating the possible use of MySQL’s InnoDB functionality to provide a transactional database to our customers. This isn’t currently incredibly high on the priority list simply because not too many people have requested this functionality (you can change that by telling your friends to ask for it!)

How to provide InnoDB is another question too, as it is run in a different manner. Indeed, setting it up on a dedicate mysqld instance might be a good approach for testing purposes. For large scale offerings however, it would make more sense from a resources perspective to have multiple customers databases on a single mysqld instances (up to a limit of course).

We’re also unsure about the impact it would have on stability and performance for the rest of the machine (we still have to provide a quality non-transactional MySQL service to most of our customers on the same machine). And of course, we have to figure out a fair price to charge for this service that won’t leave us losing (too much) money.

For the time being unfortunately, other priorities are taking precedent. Though I can’t promise that we’ll ever offer it, I can give you the beginnings of a tentative time scale by telling you that we’re considering utilizing InnoDB on a limited basis for some of our backend services. Utilizing it ourselves will greatly assist us in the decision-making/implementation process.

Jason


#9

[quote]My question is therefore intended for veteran MySQL
users at DreamHost: have any of you experienced lost
connections when using MySQL with any of your pages/scripts?

[/quote]

Yes, that is certainly a concern.
The databases we use internally to run our centralized services (such as the Webpanel: https://panel.dreamhost.com) host only our databases and only our code interacts with them. In that sense, it’s a very closed environment, easy to control. We rarely (never?) lose those connections, because we are in control.

However, in a more open environment (like the machine that hosts your database along with hundreds of other customer databases), one bad user can potentially bring the machine to a halt. This rarely happens as well. We’ve put safeguards in place recently (such as limiting the number of connections a single user can consume). However, such calamities do happen. The only way to avoid it currently is to purchase a dreamserver (www.dreamservers.com).

Having said that, many users utilize our non-transactional MySQL service to store sensitive information that need be in a consistent state (for example, financial information). While a non-transactional db is not ideal for such functions, I think it is fine for the vast majority of our users.

[quote]Those of you who are familiar with ACID will know why
this is a concern for me.

[/quote]

Are you referring to Atomicity Consistency Isolation Durability?
http://openacs.org/philosophy/why-not-mysql.html

Or are you referring to some other program/utility named ACID?

Yes, I’m familiar with the former. I understand your concerns (we’ve had them too!). Maybe you could elaborate on the type of application you plan on using. One can often write code to more safely handle sensitive data that needs to be in a consistent state, and many applications already have that intelligence written in.

You might also want to read the following:
http://www.mysql.com/doc/en/ANSI_diff_Transactions.html

[quote]All of my re-designed scripts (both PHP and Perl)
use custom classes to re-use a single database
connection for the entirety of the script, and close
it when finished.

[/quote]

That’s perfect! So long as they test each connection occassionally (or each time) before performing a query, you should be safe and fast.

[quote]I’m not exactly sure how many queries or connections
the old version of my website received, but I can
tell you that at peak times I was receiving an average
of 1700 page views per day according to Webalizer stats.

[/quote]

Volume of queries shouldn’t matter that much (until you get to hundreds or thousands of queries per second).

[quote]If enough (unbiased) people convince me that there
should be no risk, then I may very well signup soon!

[/quote]

I think you’ll be fine with a non-transactional DB, but then again, you should never trust your hosting company! =)

Jason


#10

[ A bit of context here - Jason is a senior member of the admin staff, an all around Swell Guy, and a very crazy man. He’s also the main person who deals with MySQL. While I realize the OP was mainly looking for input from customers, I asked Jason to stop by and address the bit about support for transaction safe tables etc. ]


#11

Thanks for the input Jason, it’s greatly appreciated.

I WAS referring to the ACID standard, not an “ACID” application. My PHP and Perl scripts employ custom classes which make attempts to re-connect and re-issue a query a configurable number of times in the event a connection is lost, before giving up altogether. So there is some fault-tolerance. I’ve decided that I may be able to get away with using non-transactional tables for now, given my relatively low volume of overall transactions.

The main reason I prefer transactions is simple: some functions of my website will need to update several different tables at once. If one of those were to fail, then the data integrity is essentially broken. However, I also perform logging of such problems when they happen, so I should be able to catch them and perform a custom reconciliation of some sort to rectify the problem.

I’ve already signed up, and can’t wait to get my site up and running in order to test out my code in production. (I test everything on my own personal network first :slight_smile:

Thanks again Jason!

–Brad Barkhouse
Be a Contender!
http://www.beacontender.com
brad.barkhouse@beacontender.com