Tag: database

More MySQL Woes

The database gods are laughing at me. This time, the mysql database for my blog fell over. Died. Unresponsive. It was an ex-database. Pining, as it were, for the fjords.

The blog dropped offline for an hour or so, and when it eventually came back, there were all kinds of scary database error messages being spewed into the log. I took a closer look, and the Drupal "cache" table was wedged and corrupted. Thankfully, it's just a cache, so I nuked the table and recreated it fresh. Easy peasy. I've got lots of backup snapshots, so it would have been almost as easy to restore any other table, but with the potential of lost content (posts and/or comments).

It looks like everything is back up again. Poking around the admin side of the blog, it looks like there was another nasty evil spam attack this morning, with 99% of it blocked by spam.module, but some actually got through. No idea if the database crash was related to the spam attack.

Update: Nope. It wasn't that simple. Now I'm getting the MySQL "Lost connection to MySQL server during query" error again. Crap. And phpMyAdmin is locking me out. Hopefully, that just means Dreamhost is working on the database from the other side. I'll try to stay away for awhile. Dangit.

Update 2: It's back up, and feeling much more responsive. I've tweaked the throttle settings, which disable some of the more database-intensive sidebar blocks when lots of users hit the site. The throttle has already been thrown, with 257 simultaneous users (256 of them anonymous). Hopefully that will help prevent the database from wedging again. 

The database gods are laughing at me. This time, the mysql database for my blog fell over. Died. Unresponsive. It was an ex-database. Pining, as it were, for the fjords.

The blog dropped offline for an hour or so, and when it eventually came back, there were all kinds of scary database error messages being spewed into the log. I took a closer look, and the Drupal "cache" table was wedged and corrupted. Thankfully, it's just a cache, so I nuked the table and recreated it fresh. Easy peasy. I've got lots of backup snapshots, so it would have been almost as easy to restore any other table, but with the potential of lost content (posts and/or comments).

It looks like everything is back up again. Poking around the admin side of the blog, it looks like there was another nasty evil spam attack this morning, with 99% of it blocked by spam.module, but some actually got through. No idea if the database crash was related to the spam attack.

Update: Nope. It wasn't that simple. Now I'm getting the MySQL "Lost connection to MySQL server during query" error again. Crap. And phpMyAdmin is locking me out. Hopefully, that just means Dreamhost is working on the database from the other side. I'll try to stay away for awhile. Dangit.

Update 2: It's back up, and feeling much more responsive. I've tweaked the throttle settings, which disable some of the more database-intensive sidebar blocks when lots of users hit the site. The throttle has already been thrown, with 257 simultaneous users (256 of them anonymous). Hopefully that will help prevent the database from wedging again. 

Help – Slow MySQL Insert?

I’ve been struggling with this for what feels like months. On a project, we’re using a third party hosting provider, who offers us space on a managed server, complete with everything we need to run Drupal in a shared hosting environment. We’re running a copy of Provisionator on the server to help us deploy lots of Drupal sites easily.

Here’s where it gets messy. We can create new databases just fine, but importing a .sql file takes for freaking ever. Imports that take 3 seconds on my Powerbook can take 90 – 300 seconds on this server. Running the import on a dual G5 XServe with the same version of MySQL finishes the job in about a second.

I’ve tried removing variables from the equation. Using full-on Provisionator. Using a separate custom .php script. Using phpMyAdmin. Each take so long that browsers often time out before completing the import (leaving partially imported databases). I’ve tried command line mysql directly on the server, with the same range of very slow times.

I can’t seem to find anything that might make imports of a smallish .sql (~400K) file take so long. Unfortunately, it’s making the process of deploying new sites essentially useless for now.

The curious thing is that once a database has (eventually) been populated, the select queries seem to run at normalish speed.

Any ideas on how to get the server back up to speed? It’s running MySQL 4.1.20 with MyISAM tables on a RedHat Linux box. I don’t have access to tools like top, ps, netstat, or even env, so exact details of the box’s config are shrouded in a veil of mystery and obscurity.

I’ve been struggling with this for what feels like months. On a project, we’re using a third party hosting provider, who offers us space on a managed server, complete with everything we need to run Drupal in a shared hosting environment. We’re running a copy of Provisionator on the server to help us deploy lots of Drupal sites easily.

Here’s where it gets messy. We can create new databases just fine, but importing a .sql file takes for freaking ever. Imports that take 3 seconds on my Powerbook can take 90 – 300 seconds on this server. Running the import on a dual G5 XServe with the same version of MySQL finishes the job in about a second.

I’ve tried removing variables from the equation. Using full-on Provisionator. Using a separate custom .php script. Using phpMyAdmin. Each take so long that browsers often time out before completing the import (leaving partially imported databases). I’ve tried command line mysql directly on the server, with the same range of very slow times.

I can’t seem to find anything that might make imports of a smallish .sql (~400K) file take so long. Unfortunately, it’s making the process of deploying new sites essentially useless for now.

The curious thing is that once a database has (eventually) been populated, the select queries seem to run at normalish speed.

Any ideas on how to get the server back up to speed? It’s running MySQL 4.1.20 with MyISAM tables on a RedHat Linux box. I don’t have access to tools like top, ps, netstat, or even env, so exact details of the box’s config are shrouded in a veil of mystery and obscurity.

Godaddy Database Goofup

So, it’s 4:50pm Friday afternoon. I’m about to click “Publish” on a post about the random image rotator dealie. Boom. WordPress throws up a big old “Error establishing a database connection” error screen. Crap.

I login to my Godaddy account page, hit the database manager, and PHPMyAdmin can connect. The database is there, and running. WTF. I notice my ShortStat table has ballooned to over 100MB of data. That’s insane, so I truncate it. I’ll remove the plugin. I check again – maybe I was over my DB quota or something – and it’s still no joy from WP. I try Referrer Karma – it uses the same MySQL database, and throws a scarier – but more helpful – error message. “Warning: mysql_connect(): User ‘dnorman’ has exceeded the ‘max_connections’ resource (current value: 50)”

Ahah! Something wrong with the database server. But… I haven’t added any new apps or anything, and am in a relatively low traffic load (compared to, say the week of WWDC), so my blog shouldn’t be crippling the database server.

I decide to go to the GoDaddy support page – they’ll have a “Status” section that indicates that Apache is up, MySQL is up, etc… Right? No. OK, So I fill in the email form, even though it says they won’t be able to respond to email for an estimated 10 hours. About 1 minute after clicking “Send”, I break into a cold sweat.

My blog is dead in the water. So, I call their phone support line. In the back of my head, I’m thinking “So, this is how they keep their rates so low – I’ll get gouged on long distance support, since they don’t offer a toll-free line.” At least it isn’t a 1-900 number or anything. I get connected, pass through voicemail menu hell (why does it ask me to type in my account number if the person who eventually picks up will just be asking me to repeat it anyway?). I eventually get through, and the support tech is nice enough. He doesn’t know what’s wrong, but would I mind if he put me on hold while he goes to talk to someone with a clue? Sure. The hold music doesn’t suck.

13 minutes on the phone, long distance, to GoDaddy tech support. Only to hear “oh, yeah. that’s a known issue. They’ve got their wrenches and hammers out, and are working on it. It should only be a couple of hours. Uh, yeah, your data is probably safe. Can I send you an email survey so you can let us know you were satisfied with this call?”

3 hours later, and it looks like things are back up and running. How about sending me a survey about that?

So, it’s 4:50pm Friday afternoon. I’m about to click “Publish” on a post about the random image rotator dealie. Boom. WordPress throws up a big old “Error establishing a database connection” error screen. Crap.

I login to my Godaddy account page, hit the database manager, and PHPMyAdmin can connect. The database is there, and running. WTF. I notice my ShortStat table has ballooned to over 100MB of data. That’s insane, so I truncate it. I’ll remove the plugin. I check again – maybe I was over my DB quota or something – and it’s still no joy from WP. I try Referrer Karma – it uses the same MySQL database, and throws a scarier – but more helpful – error message. “Warning: mysql_connect(): User ‘dnorman’ has exceeded the ‘max_connections’ resource (current value: 50)”

Ahah! Something wrong with the database server. But… I haven’t added any new apps or anything, and am in a relatively low traffic load (compared to, say the week of WWDC), so my blog shouldn’t be crippling the database server.

I decide to go to the GoDaddy support page – they’ll have a “Status” section that indicates that Apache is up, MySQL is up, etc… Right? No. OK, So I fill in the email form, even though it says they won’t be able to respond to email for an estimated 10 hours. About 1 minute after clicking “Send”, I break into a cold sweat.

My blog is dead in the water. So, I call their phone support line. In the back of my head, I’m thinking “So, this is how they keep their rates so low – I’ll get gouged on long distance support, since they don’t offer a toll-free line.” At least it isn’t a 1-900 number or anything. I get connected, pass through voicemail menu hell (why does it ask me to type in my account number if the person who eventually picks up will just be asking me to repeat it anyway?). I eventually get through, and the support tech is nice enough. He doesn’t know what’s wrong, but would I mind if he put me on hold while he goes to talk to someone with a clue? Sure. The hold music doesn’t suck.

13 minutes on the phone, long distance, to GoDaddy tech support. Only to hear “oh, yeah. that’s a known issue. They’ve got their wrenches and hammers out, and are working on it. It should only be a couple of hours. Uh, yeah, your data is probably safe. Can I send you an email survey so you can let us know you were satisfied with this call?”

3 hours later, and it looks like things are back up and running. How about sending me a survey about that?