1
bjbtexas
MySQL Optimization
  • 2005/3/15 19:51

  • bjbtexas

  • Not too shy to talk

  • Posts: 106

  • Since: 2004/7/3 2


I've been bugging my web host, because I've been getting so many "unable to connect" errors on my site.

The site is running XOOPS 2.07 and there are no password issues.

I got my host to move me to a new server, because they couldn't do anything about the problem on the old server.

I notice that I am not getting as many errors as of late yesterday -- none today, but it is still slow.

(They told me I wouldn't know when I was moved because they used RSYNC.)

I've got everything cached modules and blocks that I can cache and when I update my site I change the cache times to force the update.

Are there any tips to optimize mySQL / XOOPS for someone with full root access?

Thanks,

2
VideoDude
Re: MySQL Optimization
  • 2005/3/15 21:36

  • VideoDude

  • Friend of XOOPS

  • Posts: 60

  • Since: 2004/6/4 1


Hey BJB... I slip away from this forum for months, and then post a half-dozen messages in a day... go figure.

Not sure if this helps, but here's a possible clue...
I too was having somewhat similar problems in the past. What I discovered was that it wasn't my server at all. Instead, the slowness related to external content that I had added to my site. I can't remember specifically what the content was... maybe weather data. When someone would visit my site the page wouldn't load until the external data had been received. As soon as I removed the offending content, the pages loaded very quickly again. Looks like you might have some external content being loaded into your site through affiliate advertising. What happens if you temporarily disable these listings? Does the problem go away?

One other suggestion... since you have root access, you probably have access to your server "access" and "error" logs. I'd take a close look at the error log to see if there are any hints to the problem in it.

-jp

3
AAINC
Re: MySQL Optimization
  • 2005/3/15 22:18

  • AAINC

  • Not too shy to talk

  • Posts: 121

  • Since: 2003/10/18


Here is a module for XOOPS that will optimize your MySQL db.

https://xoops.org/modules/repository/singlefile.php?cid=17&lid=1203

4
brash
Re: MySQL Optimization
  • 2005/3/15 22:30

  • brash

  • Friend of XOOPS

  • Posts: 2206

  • Since: 2003/4/10


Might also be worth having a scan of this post I made a while back. I also wrote this MySQL query counter and PHP script execution timer which only takes about 5 minutes to install and will give you a very good idea of what system resources are being used.

5
bjbtexas
Re: MySQL Optimization
  • 2005/3/15 23:57

  • bjbtexas

  • Not too shy to talk

  • Posts: 106

  • Since: 2004/7/3 2


I added the page timer. I also added the Optimize Module and ran it and it seems to be faster now!

Thank you!

What about MySQL root access.

Is there anything to tune up there?

I don't know enough about the logs to know what I'd be looking at -- I'm a MS admin by trade .

The level3 support people tell me there is nothing going on. I had them turn on full logging and this is what the came up with:

I understand your problem but as I can see thevar/lib/mysql/server.advenquest.net.err you have restarted MySQL server too many times that day. It is possible that when your MySQL was down the M$ spider was digging you site and you got such result.

I was complaining because I was #1 on MSN for the term "Snow Skiing" on my site www.snow-skiing.com I’m back to # 1 without the "unable to connect to database..." error as the text on the page.

They then said they shut off auto restart on the mySQL? -- It didn't help.

6
brash
Re: MySQL Optimization
  • 2005/3/16 0:55

  • brash

  • Friend of XOOPS

  • Posts: 2206

  • Since: 2003/4/10


Is your database hosted on a different server to your website? If so, are you using persistent or non persistent connections? The times returned by the MySQL counter and page timer seem ok, the MySQL query count for the front page is consistently around 60 queries which is a little on the high side, but nothing over the top. I did notice that the page execution time did spike up well over 1 second every now and then, but if you are using a shared hosting service this is not unusual.

7
bjbtexas
Re: MySQL Optimization
  • 2005/3/16 1:37

  • bjbtexas

  • Not too shy to talk

  • Posts: 106

  • Since: 2004/7/3 2


The database is on the same server as part of my total disk quota. I have a Virtual Private Server with:

Guaranteed RAM: 256+
Max RAM: 8GB
Guaranteed CPU: 208.8+ Mhz 4
Max CPU: 2x3133.4Mhz

Before I upgraded, my shared server was faster! I feel they are not delivering what they promised, but I haven't figure out a way to prove it.

8
brash
Re: MySQL Optimization
  • 2005/3/16 1:56

  • brash

  • Friend of XOOPS

  • Posts: 2206

  • Since: 2003/4/10


You should be able to get an idea by typing;

top -U<username>

at the command prompt.

Login

Who's Online

207 user(s) are online (95 user(s) are browsing Support Forums)


Members: 0


Guests: 207


more...

Donat-O-Meter

Stats
Goal: $100.00
Due Date: Mar 31
Gross Amount: $0.00
Net Balance: $0.00
Left to go: $100.00
Make donations with PayPal!

Latest GitHub Commits