looking something to clean mysql tables

[jet rex]

if on VB more 2000 users / min
too much mysql errors
a lots of people tell me to clean database
I want to clean at first table like

-rw-rw---- 1 mysql mysql 125M Jul 16 13:10 word.MYD
-rw-rw---- 1 mysql mysql 139M Jul 16 13:10 xperience_stats_changes.MYI
-rw-rw---- 1 mysql mysql 149M Jul 16 13:02 pmtext.MYD
-rw-rw---- 1 mysql mysql 169M Jul 16 13:10 xperience_stats_changes.MYD
-rw-rw---- 1 mysql mysql 174M Jul 16 13:10 postindex.MYD
-rw-rw---- 1 mysql mysql 210M Jul 16 13:10 postindex.MYI
-rw-rw---- 1 mysql mysql 300M Jul 6 19:50 postedithistory.MYD
-rw-rw---- 1 mysql mysql 492M Jul 16 13:11 postparsed.MYD
-rw-rw---- 1 mysql mysql 618M Jul 16 13:10 attachment.MYD
-rw-rw---- 1 mysql mysql 945M Jul 16 13:11 post.MYD

[Lynne]

What are the database errors you are getting?

[jet rex]

mysql databases overload - and restart every 2 min

[Lynne]

If mysql is restarting every couple of minutes, then it sounds like you should be looking at the mysql error logs to see why. You may need to change some of your settings in my.cnf.

Do you ever optimize/repair your tables? Do they ever crash? Exactly what kind of 'cleaning' are you talking about doing for your database?

[jet rex]

I have a big size of databases hat I wrote in first post.
I think a big size overload mysql and i want to clean it - delete old info.
optimize/repair I did from AdminCP every 2 days - but it not help
for example of error

Database error in vBulletin 3.8.4:

Invalid SQL:

					UPDATE subscriptionlog
					SET status = 1,
					pusergroupid = 2,
					regdate = 1288646280,
					expirydate = 1296595080
					WHERE userid = 154680 AND
						subscriptionid = 16;

MySQL Error   : Lost connection to MySQL server during query
Error Number  : 2013
Request Date  : Monday, November 1st 2010 @ 09:18:10 PM
Error Date    : Monday, November 1st 2010 @ 09:18:32 PM
Script        : http://mydomain.com/admincp/subscriptions.php?do=status
Referrer      : http:/mydomain.com/admincp/subscriptions.php?do=adjust&subscriptionid=16&s=
IP Address    : xxx.xxx.xxx.xxx
Username      : admin
Classname     : vB_Database_MySQLi
MySQL Version : 

[Lynne]

"Lost connect to MySQL server during query" is a server issue and you should talk to your host about it.

As for cleaning it... you are saying you want to delete old posts? You may delete threads via admincp > threads & posts > prune

[jet rex]

not post, i want to clean at first
-rw-rw---- 1 mysql mysql 300M Jul 6 19:50 postedithistory.MYD
I don`t need history of edit for years

About connect to mysql
I use 16Gb ram 8 core on 2 servers
1 server just mysql
2 server just files for VB
if connected users in sec not more 1800 - all good
if users near 1900-2000-2100 - I have a lost mysql connect
connect from mysql server to VB server by short link 1Gbit

[Lynne]

I would suggest posting over in the Server Configuration forum on vbulleitn.com. George would probably have some advice on what you can change setting wise to help your server.

As for cleaning stuff out, you can prune logs in the admicnp > stats & logs but I doubt that is going to change much on the site. I assume you already have all attachments/avatars/etc in the filesystem, not the database. You can prune users that haven't visited in x amount of time or threads that haven't been posted in for x amount of time.

[jet rex]

avatars/attachments - move to file system, prune old posts and edit history - done - but size of base not decreased very much
rebuild databases and post chache index done
Posted configuration of mysql server here