Database Auto-Optimizer
 

This is a module to automatically optimize all of the tables in your database overnight through the use of a cronjob. Also performs repairs as needed.

First create a file in your Xcart root directory named: db_optimizer.php

Put the following inside that file:
Next create a cronjob that runs sometime each day whenever your site has the least amount of traffic.
Use the cron command: php /absolute-path-to-this-file/db_optimizer.php

Substitute the data in the constants section of this file with your specific information. Also replace absolute-path-to's with the appropriate path.

That's all there is to it. If there are any problems, let me know! :wink:

Hi NuAlpha

Thank you for sharing this file. I've been looking for something like this for sometime. I ran the script from my browser without any problems.

Next I'll try to set up the cron file. Just need to learn a bit more about doing this.

Once again, thank you

Here is an update of the code. Noticed a potential security issue. This fix might disable the script on some people's servers so if it does and you have another solution, then just remove the condition. The condition if (empty($_SERVER['REMOTE_ADDR']) && empty($_SERVER['SERVER_ADDR'])) below is meant to prevent anyone but the cronjob from running the script.

I have also added the ability to optimize more than one database. This is useful if, for instance, you have your stie stats recorded to a seperate database.

Not being one that is all that familiar with mysql (thats what I pay others for), what happens to a database when it's being optimised?? i.e what does it actually do?

From the MySQL manual...
"Deleted records are maintained in a linked list and subsequent INSERT operations reuse old record positions. You can use OPTIMIZE TABLE to reclaim the unused space and to defragment the data file."

Basically, optimizing the tables keeps record access as fast as possible. This module also detects and auto-repairs any damaged tables as well. :)

I guess I just run a bash script via cron that optimizes my tables and then backs them up.

Looks good. Some people can't access bash in that manner because of security restrictions set in place by the company which provides their servers. Would work fine for a dedicated server though.

Great job NuAlpha 8)

And with regards to security mentioned further up in thread, if you can run cronjobs on your account then just put db_optimizer.php in a non web-accessible directory ie:
one directory up from your public_html directory

HAH!! Good idea! And here I was rewriting the code to solve a simple problem. For some reason clever code always seems like the best solution in my mind even though there is probably some simpler way of taking care of the issue at hand. :lol:

When I run db_optimizer.php, I get the following mySQL error:


I've tried the variations of db_optimizer.php given here, but they both result in the same error.

I would prefer to run the bash script rather than the php anyway, and I have access to bash, but I have no idea how set up and run bash scripts.

Any help on getting either method to work is much appreciated. Thanks!

===========
mySQL: 4.1.11
Apache/1.3.33 (Unix) PHP/4.3.11 mod_perl/1.29
xcart: 4.0.15

Just out of interest before i run this, is there any danger of this script screwing up the database???

Not that I am aware of. Should do just the opposite and correct periodic problems with the database! 8)

I'm wondering if someone can tell me whether IБ─≥m doing this correctly.

I created and pasted the first code and saved it as db_optimised.php in the root directory of the cart (as instructed). I edited this file to include my URL with the ' /log' extension. I then opened my browser and entered my URL with the 'db_optimised.php' extension. When I hit enter, all I see is my home page reloading. Does the optimising happen this quickly or am I doing something wrong?

Oh, and regarding cron jobsБ─╕ IБ─≥ve never done them so IБ─≥m just happy to do it this way (manually) once a month or does it have to be done daily for best results? :?

You don't want to enter your URL. You should enter absolute path to your files.

After you run this script you won't see anything as it is not designed to show any such output.

If you are going to run it manually I recommend doing so every 5 days or so, depending on how heavy your site traffic is or how often your database tables are modified.

NuAlpha,

Thank you for sharing your script.

Can you echo the start time and end time into a log file even the script runs sucessfully? In that way, we can monitor the cron job which is usually managed by hosting workers.

Regards,

Jeff

in the config.php file



changing that to '=true', would that have a good effect?

Probably a good idea. That is what we have set.

Don't forget to get your backup before running the scripts mentioned here.

My xcart_languages table turned out to be empty after running one of those scripts.

mysqldump --add-drop-table -u username -ppassword xcart | mysql -u username -ppassword -C xcart_daily

Run this script in the shell to make a backup in the same mysql server. That way you can easily access to your ols data through sql queries.

I would save this command in a file and put that file under /etc/cron.dailly on Linux systems.

Try also cron.weekly, cron.hourly or cron.monthly folder at your convenience.

If you are on a cpanel system using the web based cron you don't have to worry about the daily/monthly/hourly crons. Just pic the right times and days and you are done.

I am consider just running an hourly cron php against my system that calls a generic scheduling php script of mine. Then within that control what gets run when (reports, database auto optimize, etc.). Then as all of these cool mods come out there is only script you have to remember running. Helps when it comes to moving and/or upgrading.

P

So how can I tell if this is working? I am trying it on my local test copy and can't see any difference in the SQL coding. I have no idea if it is doing anything or not. I know I had some settings wrong in the first few tries but I think I have it all correct now but nothing still seems to be happening.

Thanks

Is there any difference between optimizing like this and just clicking repair for the database -- from the mysql section in cpanel?

optimizer for litecommerce?
 

Hello,

would this work for a litecommerce installation? Could it be made to work or is there something like this out there for litecommerce?

It should work for no matter what shopping cart system you are using.

currently using this on my site, thank you very much!

Is there a difference between optimizing a database and repairing a database??

Repairing tables instead of optimizing them can be signifigant slower in some cases.

An example of this happening can be seen in the comments on this MySQL documentation page:
http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html

Can this script crash your database in any way?

Re: Database Auto-Optimizer
 

Has anyone used this on a 4.1 database? Anything to know about? Thanks!

Jeremy

Re: Database Auto-Optimizer
 

Anyone want to make $20 to add this to my site? I am on a VPS with Plesk control panel. I don't think I have it right and it seems like a very useful script to run.

Re: Database Auto-Optimizer
 

Same here. when I put this "http://localhost/inspa/db_optimizer.php" in the browser i just get a blank page - I have a test site on my localhost, so this is how I have the db_optomizer.php set up:
-----------------------------------------------
# Ensure the script execution request is by the server.
if (empty($_SERVER['REMOTE_ADDR']) && empty($_SERVER['SERVER_ADDR'])) {

# Define Constants
define('ERROR_LOG', 'http://localhost/inspa/log/db_optimization_errors.html');
define('DB_LINK', mysql_connect('localhost','',''));
define('DB_NAME', 'inspa');
----------------------------------------------
also, do I need to create this file? db_optimization_errors.html or does the optimizer do it for you? I don't want to run it on my live site until I make sure I'm doing it correctly.
What am I doing wrong?

Re: Database Auto-Optimizer
 

Like 2coolbaby, I too would like someone to set this up - cPanel here...

Re: Database Auto-Optimizer
 

NuAlpha,
I went through the steps, even set up the cron. But found I had misplace my sql pasword, so I changed it. Now I get this error when trying to access my site:

Warning: mysql_connect(): Access denied for user: 'acecase_xuser@web20' (Using password: YES) in /hsphere/local/home/acecase/acecase.com/store/include/func.php on line 44

Warning: mysql_select_db(): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /hsphere/local/home/acecase/acecase.com/store/include/func.php on line 48

Warning: mysql_select_db(): A link to the server could not be established in /hsphere/local/home/acecase/acecase.com/store/include/func.php on line 48
Could not connect to SQL db

Help...anyone...

Re: Database Auto-Optimizer
 

Open config.php get your original password and set it back to that or change the password field in config.php. That should get you going again.

Re: Database Auto-Optimizer
 

Thank you!

Re: Database Auto-Optimizer
 

This is really a nice script! :-)

Re: Database Auto-Optimizer
 

I'm sorry to bump this thread, but it was the logical place to put this question.

Originally our site was slow, (Even when there was no data on it, and a blank template.)

Once I'd custimized and imported our data I found that the site was still very slow. 10 to 15 seconds sometimes just to flick to different catagories.

I've followed all the recomendations here from the the site below for speeding up X-Cart. All except this one.

http://www.websitecm.com/news/14/Speed-up-your-XCart-Installation.html

If you have say a DB of 3000-4000 products, what kind of impact speed wise can such a script have?

I've followed the instructions and recommendations as mentioned in the thread. I'd prefer to manually do this, and I have been from the absolute path. It appears to be working, but other then a blank page how do I know it's doing anything at all? How long should it take?

Re: Database Auto-Optimizer
 

Will this work with the 4.1.x branch?

Re: Database Auto-Optimizer
 

It should work with any version of ... anything, really, as long as it has a mySQL Database. I wouldn't recommend it on any other type of Database, like msSQL. It does nothing to X-Cart itself, just cleans up the mySQL Database so that it's more organised & works faster.

Re: Database Auto-Optimizer
 

Hi,
Is there a way to tell if this is actually working? (ie ...a test or a report?)

Re: Database Auto-Optimizer
 

Yes, today I started getting sql_query failure too, as shown by the snap shot below. My email is also full or related notifications

=======================
INVALID SQL: 0 :
SQL QUERY FAILURE: SELECT count(*) FROM xcart_sessions_data WHERE sessid='681572c3424d8ed7f5dbb16c57dda74c'
SQL QUERY FAILURE: UPDATE xcart_sessions_data SET data='a:13:{s:5:\"login\";s:0:\"\";s:11:\"editor_m ode\";s:0:\"\";s:8:\"sns_flag\";s:0:\"\";s:13:\"sn s_do_action\";s:0:\"\";s:8:\"is_robot\";s:1:\"N\"; s:5:\"robot\";s:0:\"\";s:11:\"is_location\";s:0:\" \";s:9:\"adaptives\";a:1:{s:14:\"is_first_start\"; s:1:\"Y\";}s:10:\"login_type\";s:0:\"\";s:6:\"logg ed\";s:0:\"\";s:11:\"top_message\";s:0:\"\";s:16:\ "current_language\";s:2:\"US\";s:17:\"merchant_pas sword\";s:0:\"\";}' WHERE sessid='681572c3424d8ed7f5dbb16c57dda74c'
INVALID SQL: 0 :
SQL QUERY FAILURE: UPDATE xcart_sessions_data SET data='a:13:{s:5:\"login\";s:0:\"\";s:11:\"editor_m ode\";s:0:\"\";s:8:\"sns_flag\";s:0:\"\";s:13:\"sn s_do_action\";s:0:\"\";s:8:\"is_robot\";s:1:\"N\"; s:5:\"robot\";s:0:\"\";s:11:\"is_location\";s:0:\" \";s:9:\"adaptives\";a:1:{s:14:\"is_first_start\"; s:1:\"Y\";}s:10:\"login_type\";s:0:\"\";s:6:\"logg ed\";s:0:\"\";s:11:\"top_message\";s:0:\"\";s:16:\ "current_language\";s:2:\"US\";s:17:\"merchant_pas sword\";s:0:\"\";}' WHERE sessid='681572c3424d8ed7f5dbb16c57dda74c'
=======================

As a result my site was useless. I researched this on the internet. It appears that many x-cart sites have experienced similar problems at one time or the other. Yes, I agree with balinor and others elsewhere in this forum that it is not x-cart direct problem. Nonetheless, I think x-cart should still research this and supply an integrated solution as part of HA and store maintenance feature.

Here is the solution that eventually worked for me to remedy the problem. It may work for you too. Note that I have a dedicated server so I was able to login and perform these steps manually. I recommend manual operation.

1. First backup you DB. Multiple ways to do this. In my case, I simply stopped mysqld (/etc/init.d/mysqld stop) and then made a zipped tarball of the DB (tar cvzf mysql_bak.tgz mysql ). Then start the DB (/etc/init.d/mysqld start)

2. Review the error(s) with this
mysqlcheck -s -u [Your_db_root_uname] -p[Hispasswd] [TheDBname]

where [text] is what you must supply as your correct value w/o the delimiting square brackets.

3. Then do the repair with this
mysqlcheck -r -q -u [Your_db_root_uname] -p[Hispasswd] [TheDBname]

4. Hopefully the above fixes all the problem as evident by the OK's it spits out. Just to be sure, repeat Step 2 to verify all the errors are gone:
mysqlcheck -s -u [Your_db_root_uname] -p[Hispasswd] [TheDBname]

If all is well, the last command should just return a prompt. If not, if you still have errors, you may need a professional to step in.

Cheers