Making Sure mySQL is optimized correctly

nashville · #1 (**permalink**) 01-26-2006, 04:14 PM

Does anyone know if there is a service that will insure that all the mySQL services on my server are running correctly and that things run as fast as they could be?

I have my own VPS server and I just want to make sure that my sites all run as fast as they can.

Thank you

cyanide · #2 (**permalink**) 01-26-2006, 06:52 PM

I can help you out there, if you like.

The tricky thing about mysql, is that it's not really a one-off service. During the course of any given day, weeks, etc... - the load on mysql from varying applications can change greatly.

Is this a Virtuozzo?

**brian.mark** · #3 (**permalink**) 01-27-2006, 02:41 PM

Quote:

Originally Posted by cyanide

I can help you out there, if you like.

The tricky thing about mysql, is that it's not really a one-off service. During the course of any given day, weeks, etc... - the load on mysql from varying applications can change greatly.

Is this a Virtuozzo?

You'll also need to look at the queries. Much of the optimization can be done on that end and not in the database / db server setup.

Brian.

cyanide · #4 (**permalink**) 01-30-2006, 10:13 AM

Quote:

Originally Posted by brian.mark

You'll also need to look at the queries. Much of the optimization can be done on that end and not in the database / db server setup.

True, but for the average Joe, picking up a phpbb scripts or similar, that might be out of the question-

nashville · #5 (**permalink**) 02-17-2006, 10:44 AM

Sorry for not replying to this topic after posting, I must not have subscribed to replies..althought I thought I did.

There seems to be so places to look for problems. I suppose I really want to rule out issues with the hosting server. Yes this is a Virtuozzo VPS.

cyanide · #6 (**permalink**) 02-17-2006, 12:46 PM

Your website does seem to be a bit sluggish.

Server / Site speed can involve numerous things.
The ones you cannot really control (unless you move to another provider), would be location and network.

There are many things to look at regarding the server itself. However, some of the 'tweaks' involve going in as root(shell).
The Good News: You are running a vps, therefore you should have root access
The Bad News: If you are not familiar with root access, you will have to tread very carefully or better yet, hire someone to do it for you.

MySQL:
Before making any changes or tweaks with mysql, we need to know what it's currently doing.
At it's peak time, login as root/shell and type the following

Code:

mysqladmin status

or even

Code:

mysqladmin extended-status

(for a more detailed output)
and hit Enter

Now type:

Code:

vi /etc/my.cnf

hit Enter
Copy all of this code, as it is the settings for mysql

cPanel:
You mentioned in the pm that you are running cPanel, which means there are some tweaks there as well.
Log into the WebHost Manager console, and
- Click Statistics Software Configuration: Consider Disabling Webalizer and Analog. Awstats is the better of the 3. - You might have to disable under Tweak Settings as well.
- While you're in Tweak Settings, un-check Mailman, if you are not using it.

Other things:
- Is the physical server a single or dual processor? Dual is better
- Is the server running raid? Better if you are

Software:
A quick check shows you are running old everything.
It's best to keep up with new/current releases, such as Apache, php, etc...
- not to mention a prevention of vulnerabilities
- Consider installing Zend and eAccelorator, if they are not installed, to speed up php caching abilities

Easywebdev · #7 (**permalink**) 02-17-2006, 01:16 PM

There are a lot of good tutorials on webhostingtalk.com's vps forum on how to tweak your vps for the best performance -> http://www.webhostingtalk.com/forumdisplay.php?f=108

Have a read of those, there are some sample my.cnf files for different sized vps etc.

nashville · #8 (**permalink**) 02-17-2006, 06:18 PM

Thanks all for the great advice.

As first step, we are make the graphics smaller, that is some of the problem. And I think I will have some one look and see what else can be tuned.

I turned off two of the stats programs as cyanide suggested. The other stuff is over my head.

I did contact the hosting people, they say the latest stable PHP is installed and so is Zend. That must mean that eAccelorator is not installed.

I do not know if I am using Mailman or not. I do have a couple email system on the server but I do not know what they use.

It would be nice to have someone other than the host look into things. Cyanide, I am thinking about asking you if you can do this, but with a user name like Cyanide, I am not sure :-)

cyanide · #9 (**permalink**) 02-17-2006, 06:46 PM

Quote:

Originally Posted by nashville

but with a user name like Cyanide, I am not sure :-)

haha, that's not the first time I've heard that
Well, you can look at my post count, plus I've been here since day 1
A phone chat can be arranged... anyway, it's up to you

Here is some info on eaccelerator

According to my lookup, you are using Apache 1.3.33 and php 4.3.10, both of which are older versions.
When you have a vps, though, you are usually responsible for upgrading, not the host

sacx13 · #10 (**permalink**) 04-12-2006, 01:37 PM

[quote="nashville"]Does anyone know if there is a service that will insure that all the mySQL services on my server are running correctly and that things run as fast as they could be?

I have my own VPS server and I just want to make sure that my sites all run as fast as they can.

Thank you[/quot]

If you have mysql root access on your server you can do a check to see if si all alright. Some commands to help you :

1. explain sql syntax (explain select * from xxx leftjoin ......) is very used to see if you have all indexes
2. Optimize table xxx (optimizing the table)
3. create index xxx (for creating indexes for your tables)

It will be hard if you don't know very well the mysql ... but with this you will get the best from your querys ...

Best Regards
Adrian

**brian.mark** · #11 (**permalink**) 04-12-2006, 03:17 PM

Quote:

Originally Posted by nashville

Does anyone know if there is a service that will insure that all the mySQL services on my server are running correctly and that things run as fast as they could be?

I have my own VPS server and I just want to make sure that my sites all run as fast as they can.

Thank you

I just re-read your question, and it popped into my head this time that you're asking for something like this:

Quote:

Consultative Support
Remote Troubleshooting Yes
Schema Review Yes
Query Review Yes
Performance Tuning Yes

That's an exerpt from this page, which shows that it'll cost you $4995/year for the "official" MySQL service which handles this. I haven't really seen any third party solutions that seem decent, nor that I'd trust with looking at my database.

Brian.

Marty12 · #12 (**permalink**) 05-05-2006, 01:09 AM

Man am I glad I came to this forum I had some of the same questions.