Optimze MySQL or use a PHP accelerator?

B

BAlGaInTl

New Member
I have a few database driven sites running on my VPS and have noticed that some pages take a few seconds to load.

One of the problem childs is Gallery2. The other is actually SquirrelMail.

My server load is pretty low, and I just upgraded from Mtx to Ltx and that didn't really help the speed at all.

I'm wondering which may be more effective.

Installing a PHP accelerator such as XCache, or eAccelerator, or optimizing MySQL.

I'm not sure which one the problem really lies with. I figured I could start with optimizing the MySQL... does anyone have any good tutorials on doing this?

Any thoughts/inputs are appreciated.
 
I myself arn't a fan of accelerators, caching can be problematic and pointless if your running a site with 100% dynamic content.

Usually the site owners, if they know what they are doing should be optimizing the tables they are using anyway. You can setup a cronjob if you like to optimize the entire db using the command

mysqlcheck --optimize --repair --all-databases

if that doesn't work you will need to include the username and password for root by adding these options --user=root --password=*rootpassword*

Neil
 
well we are on vb3.6.7
and i am a hardcore fan of Xcache .using it for php & variable caching..its giving me positive output
 
NeilHerbert said:
I myself arn't a fan of accelerators, caching can be problematic and pointless if your running a site with 100% dynamic content.

Usually the site owners, if they know what they are doing should be optimizing the tables they are using anyway. You can setup a cronjob if you like to optimize the entire db using the command

mysqlcheck --optimize --repair --all-databases

if that doesn't work you will need to include the username and password for root by adding these options --user=root --password=*rootpassword*

Neil
Click to expand...

Is this done in cPanel? All I enter in the box is mysqlcheck --optimize --repair --all-databases and set the times?

I have ran the command in shell but as you know this has to be done manually when doing it that way.
 
Racejunkie,

That's a shell command yeah. If you want to set a cronjob for it then it will be a bit different. I don't know if you would need to set a cron for it, I wouldn't think so.
 
Dan said:
Racejunkie,

That's a shell command yeah. If you want to set a cronjob for it then it will be a bit different. I don't know if you would need to set a cron for it, I wouldn't think so.
Click to expand...

So if I run mysqlcheck --optimize --repair --all-databases in Shell this sets it up to optimize the db every day?

Sorry for not understanding all this VPS is new to me.
 
That would optimize and repair you DBs just once, but when you create the cron job you can chose to run the command automaticly daily.
 
my.cnf?

Ok....

So here is a stupid question...

I was following some guides last night in order to optimize apache and MySQL. I found one specific to a VPS of my size, but for the love of me, I can not find a my.cnf file on my server. Is the MySQL configuration file hidden somewhere else?

I did a "find / -name my.cnf" with no luck.

What am I mising here?

VPS LTx
Direct Admin
 
RaceJunkie said:
Ok, that's what I thought... Any how toos on how to do the cron?
Click to expand...

Racejunkie,

In order for this to work for all databases (yours and those owned by others) it would have to be part of the root crontab and so far as I know there isn't any gui configuration for this so you would have to add it through SSH.

The comand crontab -e will edit the crontab, opening it in nano.
In there you'll see things like:
Code: 
30 0 * * * /scripts/upcp
0 1 * * * /scripts/cpbackup
*/15 * * * * /usr/local/cpanel/whostmgr/bin/dnsqueue > /dev/null 2>&1
2,58 * * * * /usr/local/bandmin/bandmin
0 0 * * * /usr/local/bandmin/ipaddrmap
21 0 * * * /usr/local/cpanel/whostmgr/docroot/cgi/cpaddons_report.pl --notify
15 * * * * /usr/bin/freshclam --quiet --log-verbose --daemon-notify
At the bottom just add your line similar to this:
Code: 
[I]30 2 * * * /usr/bin/mysqlcheck --optimize --repair --all-databases[/I]
And that will set your command to run at 2:30 AM every day.

To exit hit ctrl+x. Press y to save. And then the enter key accepting the default file name. After exiting it should say that the new new crontab has been loaded.

Hope that helps!
 
BAlGaInTl said:
Ok....

So here is a stupid question...

I was following some guides last night in order to optimize apache and MySQL. I found one specific to a VPS of my size, but for the love of me, I can not find a my.cnf file on my server. Is the MySQL configuration file hidden somewhere else?

I did a "find / -name my.cnf" with no luck.

What am I mising here?

VPS LTx
Direct Admin
Click to expand...

Baigainti,

Try doing "locate my.cnf". On my server I found it in the /etc directory. That's where you will most often find configuration files.
 
Dan said:
Baigainti,

Try doing "locate my.cnf". On my server I found it in the /etc directory. That's where you will most often find configuration files.
Click to expand...

I tried both commands as well as manually looking at the /etc directory.

I will look again tonight, but something seems odd...
 
if you create a my.cnf file in /etc mysql will read it
it will also read my.cnf files in other locations such as /root

so the most common setup is to have all the mysql directives in /etc/my.cnf and then whm will write the mysql root password to /root/my.cnf thus keeping the password separate

iirc it will also look in a few other directories but i dont remember which. these files are all complimentary, so settings should only be included once in one of the files only
 
I tried the locate my.cnf. I get the following error:

warning: locate: warning: database /var/lib/slocate/slocate.db' is more than 8 days old

But shouldn't my previous command find it just as well?
 
I did some more research and found that if no my.cnf file exists, mysql simply uses default values.

Tonight when I get home, I will create the /etc/my.cnf and get to optimizing. Most likely, I will start with the config file that I found on WHT since the VPS specs were very similar to my VPS Ltx.

If anyone has any config file suggestions for the above plan, they are greatly appreciated.
 
Not sure if you need to add it to the cron or not, but if you want a gui simple version of adding to the root cron I believe you can use the virtuozzo panel/file manager and go to this directory and just click on edit it and then save it with the submit button. I've added root crons with this before and it works great for me.

/var/spool/cron/root
 
Might I suggest you run unix TOP command and find out of mySQL is your problem - see if the mysql process is using up your server resources. Also look into the mysql slow queries log to find out if your queries are causing problems. This would be my first step before even looking into fine tuning the server.
 
You must log in or register to reply here.
Top