Morning David
Not sure if this has been asked before. Is it possible to add an admin tool (page) to list database tables with the option (if needed) to optimize the tables. I've found scripts that run by cron but from experience I think its safer to backup first.
Chris
Morning David
We have automation setup which replaces the products table on each import all as you explained. I was more thinking of being able to optimize other tables ie reviews and custom tables with merchant details etc and thought the tool would be a useful addon if possible.
Chris
Hi Chris,
Sure - here's a quick tool to run OPTIMIZE TABLE from within /admin/. Create the following as optimise.php and upload / browse to from /admin/.
<?php
require("../includes/common.php");
$admin_checkPassword = TRUE;
require("../includes/admin.php");
if (isset($_GET["table"]))
{
$sql = "OPTIMIZE TABLE `".database_safe($_GET["table"])."`";
database_queryModify($sql,$result);
header("Location: optimise.php");
exit();
}
require("admin_header.php");
require("admin_menu.php");
print "<h2>Optimise</h2>";
$sql = "SHOW TABLES";
database_querySelect($sql,$rows);
foreach($rows as $row)
{
foreach($row as $k => $table)
{
print "<p><a href='?table=".urlencode($table)."'>".$table."</a></p>";
}
}
require("admin_footer.php");
?>
The above will show all tables in the database. If you have other scripts and want to restrict this to Price Tapestry tables specifically then in place of:
print "<p><a href='?table=".urlencode($table)."'>".$table."</a></p>";use:
if (substr($table,0,strlen($config_databaseTablePrefix))==$config_databaseTablePrefix)
{
print "<p><a href='?table=".urlencode($table)."'>".$table."</a></p>";
}Hope this helps!
Cheers,
David.
--
PriceTapestry.com
Hi David
Brilliant! Just the job. Thank you David.
Chris
Hi David.
It does not work with version 12/10B?
I installed it, I have the information on the screen, but no optimization is done.
I have no error in the log.
An idea?
Cheers,
Raoul
Hello Raoul,
As long as after clicking a table name the page returns to optimise.php it will be working fine - the script doesn't generate any output to indicate how optimisation has taken place, that is all down to MySQL internals - so may be extremely quick if there is very little optimisation required. Let me know if you're still not sure..
Cheers,
David.
--
PriceTapestry.com
Hi David,
Even leaving time, it does not work.
I'm on a server with SSD disk, very fast.
If I optimize the "feeds" or "products" (156 000) of the DB, nothing happens. I just check with PhpMyAdmin to compare and working on a field that is not optimized.
I have copied the code above named file as requested.
I added in the admin menu ( /admin/admin_menu.php ) :
print "<a href='optimise.php'>Table Optimizer</a> ";
I tried on other tables are not optimized with 1 million or more products, or with smaller tables is the same ...
Cheers,
Raoul
Hello Raoul,
Please could you try with database debug mode enabled - that will indicate if MySQL is generating an error rather than performing the optimisation. You can enable debug mode by changing line 6 of config.advanced.php as follows:
$config_databaseDebugMode = TRUE;Let me know if that displays any additional information; or of course if nothing is displayed and I'll investigate further with you...
Cheers,
David.
--
PriceTapestry.com
Hi David,
I have no message after activating the "debug_mode."
Nothing in the server error log.
Of course, the DB is still fragmented ...
I do not know what to give as more information.
Cheers,
Raoul
Hello Raoul,
Could you perhaps try phpMyAdmin's "Optimize" function on the table and see if that has any effect on the fragmentation?
Thanks,
David.
--
PriceTapestry.com
Hi David.
If I use the "Optimize" fonction in PhpMyadmin, it works perfectly.
I search but found no solution ...
Cheers,
Raoul
Hello Raoul,
My apologies - the table name should be back-ticked rather than quoted. Where you have this line:
$sql = "OPTIMIZE TABLE '".database_safe($_GET["table"])."'";...REPLACE with:
$sql = "OPTIMIZE TABLE `".database_safe($_GET["table"])."`";Cheers,
David.
--
PriceTapestry.com
Hi David,
Excellent!
I would have thought that ...
You're a leader :o)
I spend 1.2 GB to 890 MB, without having to go through PhpMyadmin.
It's more comfortable.
Thank you for your help!
Cheers,
Raoul
Hello Raoul,
Out of interest, how long did that optimisation take?
Are you using the update/insert mod in which products maintain the same ID if already in the database, as this will unavoidably result in fragmentation of the table - whereas the default import @ALL method TRUNCATEs the `products` table prior to import so it should always be fully optimal...
Thanks,
David.
--
PriceTapestry.com
Hi David.
For the field "products" of this DB.
899,000 products. Before optimization = 1.2 GB After approximately 1 GB
Optimization time is about 20 seconds.
Do not forget also that I am on a server with SSD drives. This is required if you want to manage large DB.
I always update catalogs mode @ ALL.
The manual optimization is mandatory when I joined new catalogs, or hen need to make changes.
At this time, many merchants stop their activities. I am often forced to intervene to remove catalogs.
Cheers,
Raoul
Hi Chris,
Are you using an automation process that imports all feeds at once? If so; the products table is truncated at the start of the process so in theory should never require optimising. If however you're import.php?filename=...., or have not yet set-up automation then the products table could almost certainly benefit from optimising, but in that case I'd actually recommend moving towards an automation strategy that does using @ALL.
All other tables in Price Tapestry are very small and static and shouldn't (theoretically) ever reach a stage where optimisation would be required. Having said that it would be straight forward to incorporate in the /admin/, let me know what you think...
Cheers,
David.
--
PriceTapestry.com