• Quick note - the problem with Youtube videos not embedding on the forum appears to have been fixed, thanks to ZiprHead. If you do still see problems let me know.

SQL advice, please!

bug_girl

Master Poster
B
Joined
Nov 30, 2003
Messages
2,994
Gah! It seems like every time we get our website up, something new happens and it's down again.

Here's the short version--I'm interested in opinions of you with more SQL experience--

1. running website using wordpress. Works great, but a bit unsightly, since we cobbled it together from a plone site.

2. Redesigned the skin using a template provided by the provost:http://provost.msu.edu/
site looks much better, but a little slow.

3. Sudden bump up in traffic at the beginning of semester brings site down--appears to be too much for the SQL, which starts timing out and throwing up errors.

4. Diagnosis by tech person: server doesn't have enough space/power to respond to all requests, so we need a new server.

My take is that we do, indeed, need a new server. However, I suspect that there is an additional factor causing the poor performance, and that the new shiny server will make things better, but not really fix the underlying problem.

Of course, I have No Idea what the underlying problem might be.
Can you think of anything in the code that might be causing extra SQL runtime? That would help me know where to start looking.

This may not be enough info for you to guess, but it's worth a try. I appreciate your advice! :)
 
bug_girl said:
Gah! It seems like every time we get our website up, something new happens and it's down again.

Here's the short version--I'm interested in opinions of you with more SQL experience--

1. running website using wordpress. Works great, but a bit unsightly, since we cobbled it together from a plone site.

2. Redesigned the skin using a template provided by the provost:http://provost.msu.edu/
site looks much better, but a little slow.

3. Sudden bump up in traffic at the beginning of semester brings site down--appears to be too much for the SQL, which starts timing out and throwing up errors.

4. Diagnosis by tech person: server doesn't have enough space/power to respond to all requests, so we need a new server.

My take is that we do, indeed, need a new server. However, I suspect that there is an additional factor causing the poor performance, and that the new shiny server will make things better, but not really fix the underlying problem.

Of course, I have No Idea what the underlying problem might be.
Can you think of anything in the code that might be causing extra SQL runtime? That would help me know where to start looking.

This may not be enough info for you to guess, but it's worth a try. I appreciate your advice! :)
Click to expand...
Additional load and an increase in data will often show problems in poorly tuned SQL, and SQL tables.

Often SQL 'indexes' will solve these types of problems. For instance, say I have a table with 100,000 rows in it. This table consists of the following information:
First Name
Last Name
Student Number
Registration Date

If I do nothing to this table, but try and query it for all records, say, where the Registration Date is within the year 2005, I'm going to run into problems. This is because the database engine has to look through each record in the table; essentially 'Is this registration date in 2005? Nope. Is this registration date in 2005? Nope.'. Now imagine doing that 100,000 times.

If we know that we are often querying for particular Registration Dates, we can put an 'index' on the Registration Date, which essentially will store a copy of the data sorted by Registration Date. So when we ask for records that have a registration date of 2005, the engine very quickly grabs only those records and returns them.

This is a long-winded way of saying that there is tons of optimization that can be done on SQL databases. I've seen queries that have gone from 2 hours to run, to 5 seconds.

Faster hardware does make a difference, but properly optimizing a database in most cases makes a much larger difference.
 
Thanks--that confirms my gut feeling that much more than hardware is involved. It's always nice to know my guts have good instincts :D
 
What platform are you running on? I have a few WordPress blogs on my servers and I don't notice the load from them at all. WordPress seems to be pretty efficient, so unless you're getting huge loads it should be fine.

One thing that's happened to me: If you're using MySQL, and have done a backup and restore of the database, that can cause the query optimiser to stop using indexes, which is a Bad Thing. You need to run an Analyse on the tables to get it working again.
 
2 errors--either the website is unreachable (timed out), or we get a wordpress database error.

Interestingly, I had them turn off all plugins for WP, and it now runs much better. (they also put in a bunch more ram to the server.)

That's a good tip, Pixy, I'll check on that.

THANK YOU, all of you. I really appreciate the advice and help I get here!!
--
edited to add: we have around 72,000-60,000 hits daily
 
Last edited:
What database server are you using? With MS Sql Server (and I assume this is for all sql servers) you should have at least enough ram to fit the entire db. It looks like you are running IIS 6. If the db server and web server are the same machine you may want to put a cap on the amount of memory IIS will use.
 
scotts said:
What database server are you using? With MS Sql Server (and I assume this is for all sql servers) you should have at least enough ram to fit the entire db. It looks like you are running IIS 6. If the db server and web server are the same machine you may want to put a cap on the amount of memory IIS will use.
Click to expand...

I think it's apache, but I'm not 100% sure. Unfortunately, it's a super busy time of year, so I just don't have the time to trouble-shoot on my own. We turned off any word press extensions, stuck in a bunch of ram, and it's limping along slowly.

We hope to have a new server in about 3 weeks (we need one anyway--this one was built from parts) with RAID 5, which should help. During spring break, I'll probably be back with more questions. That's when I'll actually be able to tease it all apart.
Thanks guys!! :)
 
While I can control (somewhat) the code, I have no control over what tech services buys. We will share the new server with several other units in the university, so our needs don't get to trump theirs (unfortunately).
 
Ripley Twenty-Nine said:
Additional load and an increase in data will often show problems in poorly tuned SQL, and SQL tables.

Often SQL 'indexes' will solve these types of problems. For instance, say I have a table with 100,000 rows in it. This table consists of the following information:
First Name
Last Name
Student Number
Registration Date

If I do nothing to this table, but try and query it for all records, say, where the Registration Date is within the year 2005, I'm going to run into problems. This is because the database engine has to look through each record in the table; essentially 'Is this registration date in 2005? Nope. Is this registration date in 2005? Nope.'. Now imagine doing that 100,000 times.

If we know that we are often querying for particular Registration Dates, we can put an 'index' on the Registration Date, which essentially will store a copy of the data sorted by Registration Date. So when we ask for records that have a registration date of 2005, the engine very quickly grabs only those records and returns them.

This is a long-winded way of saying that there is tons of optimization that can be done on SQL databases. I've seen queries that have gone from 2 hours to run, to 5 seconds.

Faster hardware does make a difference, but properly optimizing a database in most cases makes a much larger difference.
Click to expand...


Excellent post, and I agree, but would add a caveat:

Some webhosts give you a dedicated server that only has one drive (not a raid array, one physical drive) and if your memory maxes out, and you start swapping virtual memory to the drive, you run into bad times. Database optimization can cure much of what gets you to maxing out your memory as Ripley has noted, but you also might want to make sure your swap partition is not on the same physical drive as your db if possible.
 
scotts said:
What database server are you using? With MS Sql Server (and I assume this is for all sql servers) you should have at least enough ram to fit the entire db. It looks like you are running IIS 6. If the db server and web server are the same machine you may want to put a cap on the amount of memory IIS will use.
Click to expand...
I'm sorry, but that's a load of crap. The Wikipedia database (English articles) is 25.6 gigabytes of data alone--add the indexes, and the size goes to 30 gigs. I know this because I have a local copy on my home computer, a 64-bit Athlon with 1 GB RAM, running Linux. I'm also using a crapload of RAM in userspace. The mysqld process is using all of 2% of RAM, or 20 MB.

I just hammered my local wikipedia Apache server with 10 simultaneous requests (Random Articles link clicked 10 times in rapid succession, using a middle-mouseclick that opens links in new Firefox tabs). They generated over 500 separate queries against the database. I got all 10 articles back in 15 seconds.

There are databases out there with terabytes of data in them. Surely they don't have terabytes of RAM on their servers. Are you suggesting that a lightly loaded site such as the one the OP runs (less than 1 hit per second) needs to be able to cache the entire database in RAM?

Using good indexing and writing intelligent queries is what's probably needed here. You throw more hardware at the problem only if that doesn't work. For example, I doubt I'd get very good performance out of my Wikipedia database if I put it on to a Pentium II box with 256 MB RAM.
 
Database optimization can be tricky. (I recently had to deal with some major database performance issues where I work...)

In addition to the suggestions others have made about ensuring that there is proper indexing, most databases have additional tasks used to fine-tune performance. For example:

- If you are using Oracle databases, you may want to make sure you generate statistics. (statistics in Oracle tell the database engine how the data is distributed and can greatly speed performance.)

See: http://www.lc.leidenuniv.nl/awcourse/oracle/server.920/a96533/stats.htm

- If you are using Postgres, you may need to 'vaccum' the database to clean out unused space

Before spending money on new hardware, you may want to consider changing the operating system (if that's a possibility). For example, I've seen some claims that Oracle on Linux runs 15-30% faster than Oracle on Windows (with the same hardware configuration)
 
bug_girl said:
I think it's apache, but I'm not 100% sure.
Click to expand...
Apache is a web server, but to my knowledge it doesn't serve (directly) as a database server. The database server may be running on the same machine as the web server, but it will still be a separate application.

Depending on the amount of money your organization has, it will likely be Oracle or Microsoft SQL server (if you have money) or the free MySQL or PostgreSQL (if you don't have money).
Unfortunately, it's a super busy time of year, so I just don't have the time to trouble-shoot on my own. We turned off any word press extensions, stuck in a bunch of ram, and it's limping along slowly.
Click to expand...
Keep in mind that some software uses only a preset amount of memory, and adding more physical RAM won't speed things up much if the program isn't informed of the extra space. We had that problem once... an Oracle SQL database was running on a machine with 2 gig of RAM, but it was configured to only use 500 meg.
 
actually, she doesn't mind. She's hugely overloaded, and has nada for a budget. We both are in constant fire-stomping-out mode,which doesn't lend itself to thoughtful development.
 
OK, I think I see your problem. Under resourced in terms of people and money, having to fight fires instead of making considered long term plans. Makes it very hard. I think you are going to have to either learn, or find someone who knows, SQL tuning.
 
even if I learn, I don't have the time to do it :(

I have a student that I will point at the problem in a couple weeks, when he gets done with another database issue.

but yeah, always being reactive sucks.
 
You must log in or register to reply here.

Back
Top Bottom