Optimze MySQL or use a PHP accelerator?

Discussion in 'Linux VPS/Dedicated - General' started by BAlGaInTl, Jul 3, 2007.

  1. BAlGaInTl

    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.
     
  2. NeilHerbert

    NeilHerbert YummyHost

    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
     
  3. Aussie_Boy

    Aussie_Boy Member

  4. Nneel

    Nneel New Member

    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
     
  5. RaceJunkie

    RaceJunkie Member

    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.
     
  6. Dan

    Dan Moderator

    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.
     
  7. RaceJunkie

    RaceJunkie Member

    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.
     
  8. Jleagle

    Jleagle New Member

    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.
     
  9. RaceJunkie

    RaceJunkie Member

    Ok, that's what I thought... Any how toos on how to do the cron?
     
  10. Jleagle

    Jleagle New Member

    If you have a control panel its pretty streight forward, for cPanel at least.
     
  11. RaceJunkie

    RaceJunkie Member

    Yea I have cPanel I have a couple crons already set up but like this one I would not know what commands to put in it.
     
  12. BAlGaInTl

    BAlGaInTl New Member

    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
     
  13. Dan

    Dan Moderator

    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!
     
  14. Dan

    Dan Moderator

    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.
     
  15. BAlGaInTl

    BAlGaInTl New Member

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

    I will look again tonight, but something seems odd...
     
  16. magic

    magic New Member

    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
     
  17. BAlGaInTl

    BAlGaInTl New Member

    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?
     
  18. BAlGaInTl

    BAlGaInTl New Member

    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.
     
  19. gameutopia

    gameutopia New Member

    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
     
  20. mixart

    mixart New Member

    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.
     

Share This Page