Database Issue

Post Reply
tubeman
Posts: 534
Joined: Sat Mar 02, 2013 11:33 am

Database Issue

Post by tubeman »

Hi,
On one of my sites I see that Database size is around 23 GB which is very strange as other sites with more traffic has database size of less than 3 GB, I am listing the tables which have maximum data:

out_ips MyISAM 17 401 455 51 261 446 Truncate
out_ips2trader MyISAM 3 484 243 10 575 344 Truncate
refs MyISAM 3 299 527 3 218 982 Truncate
ips MyISAM 397 687 1 168 031 Truncate

I ran repair but size dosent go down, even admin optimized the database and ran repair but same result. Trade numbers in script are also not accurate. Any idea what to do here to fix this issue? Please let me know.

Regards,
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Database Issue

Post by admin »

Why - because you dont run cron every minute and it does not truncate those tables

What to do - truncate manually then you need it
Don't forget to run script update
tubeman
Posts: 534
Joined: Sat Mar 02, 2013 11:33 am

Re: Database Issue

Post by tubeman »

Hi, the strange part is that the cron is running, its only giving the issue for truncating the tables, logs show the Cronjob is processing stats for site every minute. The error is only related to mysql in the logs:

2019-01-25 01:03:59 :: 2014 :: Commands out of sync; you can't run this command now :: UPDATE settings SET value = '0' WHERE name = 'cron_running'
2019-01-25 01:03:59 :: 2014 :: Commands out of sync; you can't run this command now :: select sm_id from script_messages where message = 'Mysql error 2014: Commands out of sync; you can\'t run this command now (UPDATE settings SET value = \'0\' WHERE name = \'cron_running\' ) '
2019-01-25 02:04:05 :: 2014 :: Commands out of sync; you can't run this command now :: UPDATE settings SET value = '0' WHERE name = 'cron_running'
2019-01-25 02:04:05 :: 2014 :: Commands out of sync; you can't run this command now :: select sm_id from script_messages where message = 'Mysql error 2014: Commands out of sync; you can\'t run this command now (UPDATE settings SET value = \'0\' WHERE name = \'cron_running\' ) '
2019-01-25 03:03:27 :: 2014 :: Commands out of sync; you can't run this command now :: UPDATE settings SET value = '0' WHERE name = 'cron_running'
2019-01-25 03:03:27 :: 2014 :: Commands out of sync; you can't run this command now :: select sm_id from script_messages where message = 'Mysql error 2014: Commands out of sync; you can\'t run this command now (UPDATE settings SET value = \'0\' WHERE name = \'cron_running\' ) '
2019-01-25 04:03:30 :: 2014 :: Commands out of sync; you can't run this command now :: UPDATE settings SET value = '0' WHERE name = 'cron_running'
2019-01-25 04:03:30 :: 2014 :: Commands out of sync; you can't run this command now :: select sm_id from script_messages where message = 'Mysql error 2014: Commands out of sync; you can\'t run this command now (UPDATE settings SET value = \'0\' WHERE name = \'cron_running\' ) '

How do I test to see if cron has some kind of issue?

2.) Is it safe to truncate the tables on high traffic site manually from script?

Please let me know.
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Database Issue

Post by admin »

Hi!

I think it's because it truncates logs at this time, if the issue persists - send me ssh, i'll find a way to fix it
Don't forget to run script update
tubeman
Posts: 534
Joined: Sat Mar 02, 2013 11:33 am

Re: Database Issue

Post by tubeman »

OK, is there a command I can run in SSH to truncate the tables for the site safely. I even tried the Optimize option a few times but still database size did not go down.
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Database Issue

Post by admin »

there's a link 'truncate' at the same screen as 'optimize'
Don't forget to run script update
tubeman
Posts: 534
Joined: Sat Mar 02, 2013 11:33 am

Re: Database Issue

Post by tubeman »

Is it safe to truncate these tables via the script?
out_ips
out_ips2trader
refs
ips
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Database Issue

Post by admin »

yes
Don't forget to run script update
tubeman
Posts: 534
Joined: Sat Mar 02, 2013 11:33 am

Re: Database Issue

Post by tubeman »

Hi,
I'm getting alot of such errors for mysql in my scripts:

2019-01-28 01:09:22 : Mysql error 1205: Lock wait timeout exceeded; try restarting transaction (UPDATE rot_search_queries SET items_found = '120', gal_id = '17788' <br> WHERE search_query = 'tits' )
2019-01-28 01:09:25 : Mysql error 1205: Lock wait timeout exceeded; try restarting transaction (UPDATE rot_search_queries SET items_found = '120', gal_id = '1079' <br> WHERE search_query = 'boobs' )
2019-01-28 01:07:44 : Mysql error 1205: Lock wait timeout exceeded; try restarting transaction (UPDATE rot_settings SET value = '0' WHERE name = 'rot_cron_running' )

2019-01-28 05:06:47 : Mysql error 1213: Deadlock found when trying to get lock; try restarting transaction (UPDATE LOW_PRIORITY rot_cell_data set cell_ctr = CASE WHEN show_place = 1 THEN '1' WHEN show_place = 2 THEN '0.94918830523038' WHEN show_place = 3 THEN '0.90078438525471' WHEN show_place = 4 THEN '0.90010748116977' WHEN show_place = 5 THEN '0.86887802097069' WHEN show_place = 6 THEN '0.87510457264252' WHEN show_place = 7 THEN '0.84892970904452' WHEN show_place = 8 THEN '0.85653594615674' WHEN show_place = 9 THEN '0.8468203120404' WHEN show_place = 10 THEN '0.85686518431827' WHEN show_place = 11 THEN '0.8553014789336' WHEN show_place = 12 THEN '0.86818437581847' END WHERE page_id = '81' )

Any idea how to fix these?
admin
Site Admin
Posts: 37202
Joined: Wed Sep 10, 2008 11:43 am

Re: Database Issue

Post by admin »

did you check hdd load?
Don't forget to run script update
Post Reply