Huge Node Revisions table - what can i do ?

We encourage users to post events happening in the community to the community events group on https://www.drupal.org.
jggarley's picture

The node_revisions table in my D6 database is too huge (2.6gb). I don't use node revisions and have turn off node revision per content type. This has resulted to shouting my website down because the database is over its allocated size. Below is the email I just received. I tried deleting old notes using the below query but 0 record deleted

DELETE FROM {node_revisions} nr WHERE nr.vid NOT IN (SELECT n.vid FROM (node) n)');


It has come to our attention that your site is using an excessive amount of MySQL resources on your Bluehost account. This is causing performance problems not only on your own website, but for other customers that are on this same server. When left unchecked, it can potentially cause crashes or service interruptions and lead to additional downtime.

Our research shows that server performance degrades when the MySQL usage is over 1000 tables and/or 3 GB on a single account or 1000 tables and/or 2 GB on a single database. In order to ensure optimal performance for your account and the others in your shared hosting environment, we request that you reduce the MySQL usage on your account to under these limits by 07/01/2013.

Your account information:
Total MySQL Database Size: 3835.83 MB
Total MySQL Tables: 244

Largest MySQL Database Size: 3825.03 MB
Largest MySQL Table Count: 170
AttachmentSize
2013-06-23 20 19 11.png91.49 KB

Comments

There is always one record in

michaelraasch's picture

There is always one record in node_revision associated for each record in node even when you have disabled revisioning. If you enable revisions, then there will be multiple records in node_revision.

See this code bit in D6 node_save()

// Generate the node table query and the node_revisions table query.
if ($node->is_new) {
_node_save_revision($node, $user->uid);
drupal_write_record('node', $node);
db_query('UPDATE {node_revisions} SET nid = %d WHERE vid = %d', $node->nid, $node->vid);
$op = 'insert';
}

Running a
select count(nid) from node
and
select count(nid) from node_revisions
should give you the same result.

My advice would be to consider moving to a different hosting package or hoster if the DB size causes an issue.

Thanks for your reply.

jggarley's picture

Thanks for your reply. mikeytown2 comment solved my problem.

OPTIMIZE TABLE

mikeytown2's picture

Run "OPTIMIZE TABLE" on it. 2.6GB of Overhead on 208 rows is saying that table needs to be optimized. http://stackoverflow.com/questions/565997/in-mysql-what-does-overhead-me...

Thanks a lot. That solved my

jggarley's picture

Thanks a lot. That solved my problem. Now my database size is 96.23 MB

For drupal 7

koffer's picture

Could be possible to use something similar in drupal 7?

sure you can and you can also

CarstenHarnisch's picture

sure you can and you can also do a mysqlcheck (if you have access to the command line). anyway if tables getting fragmentation this definitely has a reason, e.g. data is getting inserted and deleted again or been updated pretty often.

Check this here, might help https://drupal.org/project/db_maintenance

I try to do a optimize

koffer's picture

and send me this message;

Table does not support optimize, doing recreate + analyze instead

InnoDB

Countzero's picture

This is what MySQL says when the engine used is InnoDB. AFAIK it works as well as in the other cases.