vb.org Archive - looking something to clean mysql tables

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)

- - looking something to clean mysql tables (https://vborg.vbsupport.ru/showthread.php?t=252974)

jet rex

11-01-2010 01:15 PM

looking something to clean mysql tables

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

11-01-2010 02:17 PM

What are the database errors you are getting?

jet rex

11-01-2010 05:55 PM

mysql databases overload - and restart every 2 min

Lynne

11-01-2010 06:32 PM

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

11-01-2010 08:15 PM

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

HTML Code:

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

11-01-2010 10:38 PM

"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

11-02-2010 03:15 AM

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

11-02-2010 03:39 AM

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

11-02-2010 12:00 PM

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

All times are GMT. The time now is 03:53 PM.

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.00954 seconds Memory Usage 1,731KB Queries Executed 10 (?)
More Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)bbcode_html_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)post_thanks_navbar_search (1)printthread (9)printthreadbit (1)spacer_close (1)spacer_open Phrase Groups Available: global postbit showthread	Included Files: ./printthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/class_bbcode_alt.php ./includes/class_bbcode.php ./includes/functions_bigthree.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete printthread_start bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: