I really need advice and tip on how to clean up the database. its full of junk.
Permalink
Hello.
My database is starting to get full of junk. page version history and such.
Im working at two places and do not use a common database for both places.
So i need to export and import the databases to at thouse places to get uptodate version.
However PhpAdmin is having a hard time importing the database. on both machines.
They are not some 386 pc. Thery are new modern uptodate pc's.
But whenever i try to import the database with phpadmin its timeing out.
I tried to increase the timeout values in the php settings file. but it doesnt help.
So i end up using a mysql frontend program instead.
It takes about 10-12 minutes to import the database which is only 2.27MB big.
But i cant use this when im going to uploade the database to my online host.
Im looking in the database and see example the table "collectionversionblocks" has 14,327 record... what the H***.
I have other tables that has well above 2k records in them too.
I am getting frustrated.
The Automated job. > Remove Old Page Versions .
At first it seems it did somewhat a good job. but after looking more closely it doesnt.
It does a shitty job in cleaning up.
But i have run this like 1000 times now.
I have also cleaned out all the stack history. but meh. its doesent help.
Does anyone got some SQL querys that will cleanup: unused blocks id's, pages and so that is not in use anymore???
<Rant>
At the current stated of C5 i feel that i could never reccomend it to anyone.
And i am starting to regret i wasted so much time on it.
<End Rant>
My database is starting to get full of junk. page version history and such.
Im working at two places and do not use a common database for both places.
So i need to export and import the databases to at thouse places to get uptodate version.
However PhpAdmin is having a hard time importing the database. on both machines.
They are not some 386 pc. Thery are new modern uptodate pc's.
But whenever i try to import the database with phpadmin its timeing out.
I tried to increase the timeout values in the php settings file. but it doesnt help.
So i end up using a mysql frontend program instead.
It takes about 10-12 minutes to import the database which is only 2.27MB big.
But i cant use this when im going to uploade the database to my online host.
Im looking in the database and see example the table "collectionversionblocks" has 14,327 record... what the H***.
I have other tables that has well above 2k records in them too.
I am getting frustrated.
The Automated job. > Remove Old Page Versions .
At first it seems it did somewhat a good job. but after looking more closely it doesnt.
It does a shitty job in cleaning up.
But i have run this like 1000 times now.
I have also cleaned out all the stack history. but meh. its doesent help.
Does anyone got some SQL querys that will cleanup: unused blocks id's, pages and so that is not in use anymore???
<Rant>
At the current stated of C5 i feel that i could never reccomend it to anyone.
And i am starting to regret i wasted so much time on it.
<End Rant>
Thanks for your reply
I tried looking into the mysql logs, but there wasnt anything there that gave me any hints regarding the slowness.
I'll look into the setting of my xampp "mysql" my.ini settings and see if somethings up there.
I tried looking into the mysql logs, but there wasnt anything there that gave me any hints regarding the slowness.
I'll look into the setting of my xampp "mysql" my.ini settings and see if somethings up there.
If you are on 5.6, you can try my 'Extreme Clean' job, free in the marketplace.
If it's taking you 10 minutes to import a few megs of database data, then I'd suggest something is wrong with your database itself - like you some like database logging enabled that shouldn't be.
We regularly move concrete5 sites between servers, which means moving the database data, and the size of the database is pretty much never a problem. On a properly configured phpmyadmin install (ie in cPanel), I've never had a database import time out. Sometimes it can take several minutes to _upload_ the SQL file itself, but generally the processing takes no more than a minute or two. An average concrete5 database tends to take no more than a few seconds to import.
The only time I've had to actually worry about database size is when a concrete5 site with custom coding has been behaving incorrectly. It was creating a new page record on every page request... the database grew to a few hundred megabytes and the site ground to a halt. Even then, I was still able to export and import the database locally and work out what was going on.
If you are running 5.6 or earlier, one table to check is PageStatistics. When statistics is on, this table can continue to grow, and if you don't use the built in statistics the data isn't useful.
But from the size you've described I don't think you need to be trying to trim down your database, it shouldn't be necessary - I'd be investigating why your database is performing so poorly.