MySQL, YourSQL, OurSQL
- Posted by Mike on October 11th, 2007 filed in Technical
In response to my last post, someone asked, via the comments, what the deal was with MySQL on PowWeb. It’s also been asked on the PowWeb forums just shy of a billion times. I figured I’d take this opportunity to write a blog post about it, and sum up the issues, and what we’re doing about it.
First, let me say, from a high level, that we know MySQL is a problem. A rather large one, at that. If you call support, and the level one person you speak to won’t acknowledge the issue, ask to speak to their supervisor. Like I said last time, I have very little control over things from a Tier1 level. I feel your pain, and it bugs me to read some of the things I see people saying about our first level of support. With everything else, it’s a constant struggle to improve.
As I’ve only been here for a few short months now (though it seems like forever), MySQL’s been an issue ever since. I don’t deny that, my boss doesn’t deny it, and his boss doesn’t, either. Let me start by summing up the problem:
PowWeb is a community that is rich in knowledge and programming skill. That means that a lot of you PowWeb folks are utilizing MySQL applications, and even writing your own. That’s great, and I encourage you to continue doing so. The issue comes in when we’ve got too many things happening at once, on the same MySQL server. When there’s too many connections on the server, the server queues up the next connections, until one frees up.
This was an enormous problem when we were running MySQL 4 on all of the servers, as it didn’t limit connections at all. The first step in the right direction was upgrading to MySQL 5, which you all know we did a few weeks/months ago. Now that all the servers are running MySQL 5, we can limit connections based on username. This has helped, somewhat, and is a good start.
The next step that we’re planning on doing is being beta tested right now. According to one of our NetOps guys, MySQL is 20% write, and 80% read. He had the idea that doubling the amount of platters (hard drives) in the MySQL server would decrease the amount of data that needed to be read from one platter. We rolled this out onto one server already, which is our guinea pig, so to speak. (No, it wasn’t a PowWeb server that we’re testing on.) So far, the results have been extremely promising. Kudos to NetOps for coming up with this.
Since the test was so successful, we went ahead and placed orders for the new drives for all of the other MySQL servers, and are waiting for them to arrive at our data center(s) to be installed. This will get MySQL to a point where it’s usable for everyone. There should be a huge improvement across the entire farm of MySQL servers, and everyone should be much happier with performance.
I’m not entirely clear on how they’re setting up these new drives, or how this is going to get around the number of connections that MySQL allows. I didn’t take the time to ask. Once I saw how much of an improvement it made on the server we’re testing it on, I didn’t care about the details.
Ideally, I’d love to tell you the exact time and date you can expect these changes to be in place. Realistically, we don’t have an exact date. All I can say is that it’ll be done as soon as humanly possible.
If you do, however, need to call in to support about slowness with your site, and you think it’s MySQL related, here’s my suggestion: When you get someone on the phone (or chat, or e-mail support), let them know immediately what the issue is. Ie;
You: Hello Support Rep #41573, my site seems to be running extremely slow right now. I’m running a PHP based application with a MySQL backend.
Support Rep #41573: Just a moment, sir/ma’am, I’ll get someone to look into the MySQL server you’re on now.
I’m sure this goes without saying, and you all provide as much detail as possible to the Tier1 reps when you call in. However, there’s still a large number of people who call in, and don’t give that information. That one little sentence “I’m running a PHP based application with a MySQL backend.” will save the Tier1 rep from running half a dozen tests (and keeping you on hold the entire time) and they’ll skip right to checking the MySQL server. This will ultimately result in someone fixing whatever issue is causing the slowness. (Usually someone hogging resources on the MySQL server.)
I hope my summation helps, somewhat. I know it certainly doesn’t fix the issue, and doesn’t show any promise of having the issue fixed yesterday. But I assure you, as soon as we can, we’re going to fix this. For good.
October 22nd, 2007 at 2:19 pm
Mike,
First off let me say THANKS!! for the explanation and the time you’ve taken to provide said explanation. I was becoming so frustrated and angry with the response (or lack thereof) I’ve received on my queries about the MySQL issues we’re having, that I’ve seriously considered leaving Powweb for good. I’m now more inclined to stick it out and wait for “the fix”. I only wish I knew about this BLOG 2 weeks ago, so I could let my site visitors know what was going on. I WILL NOW THOUGH!
I can only hope “the fix” will be much sooner rather than later, but it’s good to know that it’s being worked at a higher level.
THANKS!
Bill Frazier
aka Chubby
October 28th, 2007 at 2:59 am
Thanks for the explanation, but it does seem a little late for the fix. I look after 4 different sites and they were hosted with PowWeb. Given the slowness of their sites, they have all moved off to other hosts, where this problem you are experiencing, does not affect their sites.
The issues with the MySQL servers has been plaging some of my clients for some months and some of them blamed me for the issues, which nearly cost me my clients. Now that they have moved, the sites work fine.
I have been a client of PowWeb for some 5 years and have previously recommended you to new clients. However, the problems with MySQL have just been such, that it is better for me to suggest other hosting services and not PowWeb. As of today, I too have moved to another host, also as a result of site issues, related to MySQL issues.
Thanks for all the previous service, but now knowing when this issue is really going to be resloved, is just not acceptable to me, or my clients.
Thanks anyway.
Hans
November 1st, 2007 at 8:40 pm
>> doubling the amount of platters (hard drives) in the MySQL server would decrease the amount of data that needed to be read from one platter.
That’s database administration 101. The progression for boosting SQL client-server database performance goes something like this:
1. First, run the web server and SQL server (MySQL or whatever) on separate boxes. SQL products moved to the client-server model two decades ago to distribute the processing (the “many hands make light work” principle).
2. MySQL and other SQL platforms support journaling (logging) to permit recovery. Just as a web server will exhibit better performance by logging on a separate disk drive, so will an SQL server.
3. Think about minimizing head thrashing. If the operating system, DBMS software, database and database journal are all resident on the same hard drive, the heads must move to one part of the drive to load software, another part to read or write to the database and so on.
Disk drives are cheap so put the database on a separate drive from the operating system and software. Put the database journal on yet another drive.
4. Use plenty of RAM so the database manager can operate with data in a cache instead of having to do a disk i/o.
5. Another performance boost is to use RAID. The database information is partitioned to enable reading/writting across several disk platters concurrently.
6. And then of course there are clusters built with Infiniband or other high-speed interconnects.
As for users:
1. Learn about connection pooling.
2. Learn to use prepare-and-execute queries and stored procedures.
3. Avoid SELECT * queries on tables with large numbers of rows. Use filters to reduce the result set size (such as WHERE ZipCode = …).
4. Cache static information and query results. For example, the two-character codes for US states (NJ, NY, CA) aren’t dynamic data so don’t execute multiple SQL queries per day to retrieve the state codes.
November 2nd, 2007 at 9:00 am
It is good to hear that Powweb is finally using a test server. One wonders why such was not used prior to the migration to the new platform.
Like others in the comments here, I’m in the middle of a migration as well. I can’t put up with this either.
November 4th, 2007 at 7:41 pm
I’m not holding my breath that the MySQL issue will fix this way overdue problem, but I’m glad someone is finally acknowledging that there is a problem. I can’t tell you how many times I’ve struggled to get customer support to at least give lip service to the problem. Thanks for the post.
November 18th, 2007 at 3:45 pm
[…] (which I’m running) for many months. The official POWWEB blog post from October 15, 2007, “MySQL, YourSQL, OurSQL” sheds some light on these issues, as does the more recent post “The State of MySQL […]
November 26th, 2007 at 5:28 pm
Thank you for being forthcoming about the issues facing Powweb. It can be disheartening not to have access to one’s blog (as I am having today), but it is also encouraging to know that the light is at the end of the tunnel.
December 11th, 2007 at 4:41 pm
For me MySQL issues had been solved… now put sftp!!!
lol
January 1st, 2008 at 1:37 am
Like others here, I have experienced the slowness of my site and as a result contacted customer service. The response I got was that “we are aware of the mySQL problem and we are working to fix it by upgrading our servers which will take a few weeks”.
After asking a few time since I could not believe the answer - I was convinced that Powweb actually meant it - and that my site will be slow at least for a few weeks, and maybe more - there was no exact date for when things will be ok again.
I patched my site to make it run a bit faster and immediately moved to a new hosting service - with which I have no problems.
This is one of the things that can kill a company like Powweb - what amazes me is not just the lack of technical ability - but the way the company dealt with the problem - first rule of any good public relations is: if you know you have a problem and your customers would obviously find out their sites don’t run… be the first to announce it - send me an email and let me know - dont wait for me to contact customer service…
I am done with Powweb for good - too little and too late for me.