[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?
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.
Are you referring to Atomicity Consistency Isolation Durability?
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:
[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.
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.
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!
I think you’ll be fine with a non-transactional DB, but then again, you should never trust your hosting company! =)