I overloaded my hosts SQL Server!!!
-
-
- William E Dooley
- Elite Rocketeer
- Posts: 740
- Thanks: 1
- Web Developer
-
MY host just chmoded my SQL database into nonexistance, I tried to go to my sites home page and was greeted with a "There are no Items to display". I called my host and they said I overloaded their sql database server because I had too many queries going on. They said I should contact the providers of the program I was using and make sure I have the newest version and check to see if there are any known issues to have caused this!lol So has anyone else experienced this? Does anyone know of something in the code somewhere that causes a bunch of queries to be opened that may have caused this? They told me that since march 25th, 2007 to april 17th, 2007 there was 7GB of traffic on my SQL database, and that since the start of the database (When I created the site), which is about 2 months ago, there has been approximately 478 queries per second. That sounds high to even me!LOL So I am asking if anyone knows what may be causing this, or if it's something I did and some area or something may need to be optimized? I don't know what the cause of the problem is, so I am no where near a solution. If anyone has any idea, I would greatly appreciate to hear from you!
Thank you,
David Henderson
Edit: I forgot to mention, I took a look at the Show MySQL runtime information link on my phpmyadmin section and saw 10 values in red meaning they were unusually high, Here are a few of them that seem like they may be likely to be the symptom of this problem:
Server traffic: These tables show the network traffic statistics of this MySQL server since its startup.
Traffic ø per hour
Received 3 GB 7 MB
Sent 2 GB 4 MB
Total 5 GB 11 MB
Connections ø per hour %
max. concurrent connections 651 --- ---
Handler_read_rnd 527 M The number of requests to read a row based on a fixed position. This is high if you are doing a lot of queries that require sorting of the result. You probably have a lot of queries that require MySQL to scan whole tables or you have joins that don't use keys properly.
Handler_read_rnd_next 3.06 G The number of requests to read the next row in the data file. This is high if you are doing a lot of table scans. Generally this suggests that your tables are not properly indexed or that your queries are not written to take advantage of the indexes you have.
Select_full_join 3 M The number of joins that do not use indexes. If this value is not 0, you should carefully check the indexes of your tables.
Select_range_check 6 k The number of joins without keys that check for key usage after each row. (If this is not 0, you should carefully check the indexes of your tables.)
Table_locks_waited 612 k The number of times that a table lock could not be acquired immediately and a wait was needed. If this is high, and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
Those are the different areas that where highlighted red indicating a problem somewhere. I am a newbie on sql databases though, so I have no idea what could be causing this, or what to do about it.
-
-
-
- GollumX
- Elite Rocketeer
- Posts: 2817
- Thanks: 0
- Have you posted on forum.J!?
- Say no to Internet Explorer 6.
twitter.com/mark_up
-
-
-
- Mack
- Elite Rocketeer
- Posts: 535
- Thanks: 0
- Check your logs files on your host. They will tell you exactly what you need to know. If you need help parsing them or finding the culprit PM me and I'll send you a couple utilities. If you can't access them or can't go back far enough, send an email to your host and ask them for your logs and place them in your web directory compressed. Any host should be able to do that for you, they have to keep them.
-
-
-
- William E Dooley
- Elite Rocketeer
- Posts: 740
- Thanks: 1
- Web Developer
-
Cool, thanks mack:) Will get them on the phone today:)
GullomX, yes, I posted on the J!forums and on the VM forums:)
-
-
-
- William E Dooley
- Elite Rocketeer
- Posts: 740
- Thanks: 1
- Web Developer
- Well the funniness continues! I go to my site, and see it is up and running, pulling from the database again, so it seems they have restored the data, or rather changed the chmod. So I log into my phpmyadmin so that I can start to work on trying to do what I can to optimize the database, but I cannot see the database!LOL It's there and working, but I cannot see it. They must have set the wrong chmod values:(
-
-
-
- Mack
- Elite Rocketeer
- Posts: 535
- Thanks: 0
-
William E Dooley wrote:
Well the funniness continues! I go to my site, and see it is up and running, pulling from the database again, so it seems they have restored the data, or rather changed the chmod. So I log into my phpmyadmin so that I can start to work on trying to do what I can to optimize the database, but I cannot see the database!LOL It's there and working, but I cannot see it. They must have set the wrong chmod values:(
Remain calm, don't panic, here's what you do.
1. Send a trouble ticket to support asking them to restore your permission.
2. Forward your office phone to voicemail.
3. Take the battery out of your cell phone and put it in your desk drawer.
4. Finish reading this before you do step 5.
5. Shut down the PC. Smile, stand up, walk slowly to the exit.
6. Get in your car, drive home.
7. Drink heavily.
8. Turn on the TV.
9. Watch cartoons or The Three Stooges, nothing else.
10. Drink heavily.
When you go to work tomorrow the problem will still be there. The good part is you'll be so hungover you won't care.
-
-
-
- drstonyhills
- Hero Rocketeer
- Posts: 412
- Thanks: 0
-
lol
;D - No money to extend membership
-
-
-
- William E Dooley
- Elite Rocketeer
- Posts: 740
- Thanks: 1
- Web Developer
- Heh, it's fixed:) Took them 4 hours of twidling their thumbs, then another two hours of my sitting on the phone with them when I finally called after the ticket wasn't looked at in 4 hours. However, the end result is, I am back to working on the site:) That's some funny stuff though for sure Mack:)
-
-
-
- William E Dooley
- Elite Rocketeer
- Posts: 740
- Thanks: 1
- Web Developer
-
OK, still trying to figure out exactly what is required in order to index my database. Anyone know of any links to sites that explain optimizing your SQL database? My host had some documentation on it, but I started reading it and realized in about 15 minutes that what I was reading made no sense to me. So if anyone knows of any sites with explanations that would be a bit more layman or a site with tutorials on optimizing your databases..please post them here so I can try and get this done:)
Thank you,
David Henderson
-
-
-
- Mack
- Elite Rocketeer
- Posts: 535
- Thanks: 0
-
I use this on every site. It not only does an automated backup of the DB, it zips it and mails it to me. Additionally, it has DB repair and optimize buttons built in. I say buttons because that's it, simple as pushing a button. It's really a great extension. One DB I have 20,000+ items in it. It was 20mb in size. After optimizing it was 15 and it responded 3x as fast.
extensions.joomla.org/component/option,c...nk_id,324/Itemid,35/
-
Time to create page: 0.064 seconds