SmartCJ Support Forum

Heres the situation, I have a master, connected by 120 slaves. during this whole month, some of those slaves expired in domains and i dont renew them, so dead sites. and yesterday, my whole network is conjested and jam. I ask the admin, he says the mysql of the master is accessed by repeated connections from all the slaves. some of them alive sites and some are dead. and today I can no log access to any scj sites on the server, the admin says mysql is overloaded by too much of access.

Ok, so i want to log in to my master control panel and disconnect the dead slave sites, and do some cleanup, but I cannot access to it and its not loading at all. I ask my admin to restart mysql and reset apache, no does not work, it become congested matter of second.

So heres my question, how do I solve this problem, it happend to me few times, everytime I just didn't do a cleanup of the slaves and they jam everything. How do I access to the master list of where it connects all the slaves and do some disconnecting without having to access to control panel.

Ask admin to limit access to mysql for 1 user for example
or limit access to those sites for you only so you can be the only one who can load site = load mysql
this way you'll be able to login and do a clean up.

admin wrote:Ask admin to limit access to mysql for 1 user for example
or limit access to those sites for you only so you can be the only one who can load site = load mysql
this way you'll be able to login and do a clean up.

I clean up dead sites but still master mysql is so heavy loaded and access by other slaves, I can't even login to any sites on the server.

what other solutions?

kenhirai wrote:

admin wrote:Ask admin to limit access to mysql for 1 user for example
or limit access to those sites for you only so you can be the only one who can load site = load mysql
this way you'll be able to login and do a clean up.

I clean up dead sites but still master mysql is so heavy loaded and access by other slaves, I can't even login to any sites on the server.

what other solutions?

heres one of the recent log

| 3126 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 249 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 3843 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 162 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 4067 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 142 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 4210 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 133 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 4449 | kenhirai_cxm | localhost | kenhirai_cxm | Query | 87 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 4585 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 73 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 4821 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 72 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |



currently 640 processes just for master database.

MySQL is hitting max connections at 1024, because of all that backup.

Reload mysql and check what has been changed
Since you didn't change the script I guess some other conditions have been changed.

admin wrote:Reload mysql and check what has been changed
Since you didn't change the script I guess some other conditions have been changed.

Ok i will tell u how I use ur script so u have better understanding.

1. I dont setup crons and rot for all my sites, only for the master, all slaves have no crons and rot.php.
2. all slaves connect to the master

simple as that, and i never touch my server, I reset the mysql 10 tens time, and now they overload in mysql as what I told u, I already gave u all the best details, and I think u know this script better than anyone else. What cause the slave keeps hitting the master and why they dont close connections. is it because I dont use cron or rot? or is it because other stuff. help me, the server has been down for 16 hours.

Slave sites connect to a master when they need some data from DB to create a page.
So maybe you have a lot of spiders at your sites for example - you can check webserver's logs.

admin wrote:Slave sites connect to a master when they need some data from DB to create a page.
So maybe you have a lot of spiders at your sites for example - you can check webserver's logs.

I check and no spider, take a look the recent mysql entry

| 94 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 503 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 176 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 484 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 276 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 464 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 342 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 444 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 365 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 432 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 409 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 414 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 592 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 391 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 644 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 378 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 734 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 362 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 865 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 334 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 868 | kenhirai_cxd3 | localhost | kenhirai_cxd3 | Query | 307 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 919 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 312 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 930 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 311 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1059 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 282 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1062 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 283 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1227 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 251 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1274 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 242 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1383 | kenhirai_tama | localhost | kenhirai_tama | Query | 230 | Checking table | CHECK TABLE `rot_page_items` |
| 1385 | kenhirai_thait | localhost | kenhirai_thait | Query | 230 | Checking table | CHECK TABLE `rot_page_items` |
| 1530 | kenhirai_cxm | localhost | kenhirai_cxm | Query | 188 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1727 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 101 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1771 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 101 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1787 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 102 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1795 | kenhirai_fblowj | localhost | kenhirai_fblowj | Query | 99 | Opening tables | SHOW TABLE STATUS |
| 1817 | kenhirai_maturef | localhost | kenhirai_maturef | Query | 92 | Opening tables | SHOW TABLE STATUS |
| 1832 | kenhirai_cxf2 | localhost | kenhirai_cxf2 | Query | 97 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1844 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 82 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1847 | kenhirai_fpg | localhost | kenhirai_fpg | Query | 80 | Opening tables | SHOW TABLE STATUS |
| 1866 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 82 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1884 | kenhirai_tlporn | localhost | kenhirai_tlporn | Query | 4 | Opening tables | SHOW TABLE STATUS |
| 1893 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 9 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1899 | kenhirai_cxm | localhost | kenhirai_cxm | Query | 6 | Opening tables | SELECT GROUP_CONCAT(tag_id) as item_groups FROM rot_gal2tag WHERE gallery_md5 = '88742f4127f517afb73 |
| 1907 | kenhirai_cxa | localhost | kenhirai_cxa | Query | 1 | Opening tables | select * from rot_content where content_id = '3302' |
| 1908 | kenhirai_cxm | localhost | kenhirai_cxm | Query | 8 | Opening tables | select * from rot_content where content_id = '4728' |
| 1955 | kenhirai_tsluts | localhost | kenhirai_tsluts | Query | 5 | Opening tables | SHOW TABLE STATUS |
| 1963 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 8 | Opening tables | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 1965 | kenhirai_cxm | localhost | kenhirai_cxm | Query | 4 | Opening tables | select * from rot_content where content_id = '3328' |
| 1967 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 1 | Sending data | SELECT id, g.gallery_slug, g.gallery_md5, sponsor_id, url, source_url, rot_content_id, embed_templat |
| 1990 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 5 | Opening tables | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 2005 | kenhirai_trikep | localhost | kenhirai_trikep | Query | 58 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 2026 | kenhirai_flady1 | localhost | kenhirai_flady1 | Query | 0 | Sending data | SELECT id, gallery_slug, g.gallery_md5, sponsor_id, url, source_url, rot_content_id, embed_templat |
| 2033 | kenhirai_flady1 | localhost | kenhirai_flady1 | Query | 47 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 2037 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 8 | Opening tables | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 2046 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 8 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 2075 | kenhirai_cbp | localhost | kenhirai_cbp | Query | 41 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 2097 | kenhirai_cxm | localhost | kenhirai_cxm | Query | 5 | Opening tables | SELECT GROUP_CONCAT(tag_id) as item_groups FROM rot_gal2tag WHERE gallery_md5 = '5a682224f5c660a22df |
| 2100 | kenhirai_cxb | localhost | kenhirai_cxb | Query | 1 | Opening tables | select * from rot_content where content_id = '168' |
| 2101 | kenhirai_fwife | localhost | kenhirai_fwife | Query | 30 | Opening tables | SELECT gr.name as category_name, gr_data.custom_name as category_custom_name,
gr_data.descript |
| 2111 | kenhirai_cxa | localhost | kenhirai_cxa | Query | 1 | Opening tables | select * from rot_content where content_id = '3302' |
| 2114 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 6 | Opening tables | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 2120 | kenhirai_cxd3 | localhost | kenhirai_cxd3 | Query | 13 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 2126 | kenhirai_cxn1 | localhost | kenhirai_cxn1 | Query | 13 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 2127 | kenhirai_cxm | localhost | kenhirai_cxm | Query | 4 | Opening tables | SELECT GROUP_CONCAT(tag_id) as item_groups FROM rot_gal2tag WHERE gallery_md5 = '14a702dc0e57e0d4e18 |
| 2129 | kenhirai_flady1 | localhost | kenhirai_flady1 | Query | 13 | Copying to tmp table | SELECT DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if |
| 2143 | kenhirai_fsexy | localhost | kenhirai_fsexy | Query | 14 | Creating tmp table | SELECT * FROM rot_sponsors as s, rot_sponsors_data as sd
WHERE s.sponsor_id = sd.sponsor_id and |
| 2157 | kenhirai_cxp2 | localhost | kenhirai_cxp2 | Query | 4 | Opening tables | select * from rot_content where content_id = '854' |
| 2158 | kenhirai_fsexy | localhost | kenhirai_fsexy | Query | 8 | Opening tables | SELECT * FROM rot_sponsors as s, rot_sponsors_data as sd
WHERE s.sponsor_id = sd.sponsor_id and |
| 2164 | kenhirai_cxm | localhost | kenhirai_cxm | Query | 4 | Opening tables | SELECT GROUP_CONCAT(tag_id) as item_groups FROM rot_gal2tag WHERE gallery_md5 = '3512aab6328468100fc |
| 2169 | kenhirai_fpa1 | localhost | kenhirai_fpa1 | Query | 4 | Opening tables | SELECT gallery_md5 FROM rot_galleries WHERE gallery_slug = 'black-amateur-girlfriend-beautiful-blowj |
| 2183 | kenhirai_cxp2 | localhost | kenhirai_cxp2 | Query | 1 | Copying to tmp table | SELECT SQL_CALC_FOUND_ROWS DISTINCT(g.id), g.gallery_md5, gs.total_shows as thumb_casts, gs.total_c |
| 2184 | da_admin | localhost | NULL | Query | 0 | NULL | show processlist |
+------+------------------+-----------+------------------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
61 rows in set (0.00 sec)


Does it look overloaded to you? I have disconnected all slaves from master and still the mysql is overloaded and congested hang the whole server.

These are regular queries to select thumbs to create a page, there's nothing wrong about it, but for some reason you have a lot of it or it takes a lot of time to execute each of them.
2 reasons - unexpectedly a lot of traffic or server slowdown caused by something ( not the script if you checked logs and there's no traffic)

Ok, now theres a message everytime the page is loaded, its take a long time to load.

Mysql error:: 0 () (db_link) in query INSERT into rot_page_items SET page_id = '5', page_crc = '1403123927399292', items = '10447|10397|7639|11548|7644|7730|7843|8009|8042|24339|3040|14911|16842|2822|3404|6161|23945|10779|7111|7168|10439|17066|17112|17163|21332|21660|21757|24346|24419|25049|25142|25191|24517|3103|1323|2581|18417|21697|21730|24432|15059|304|1316|17078|17127|17144|22740|24215|24418|24571|25058|307|349|170|1320|1393|3122|10443|10429|21607|25095|25636|9696|25668|14984|327|2701|14943|169|7665|190|328|16406|16675|969|7685|24553|25012|25127|25479|25481|25571|3450|25645|25647|20749|352|298|351|1087|1354|20781|25036|25471|9665|25562|25589|3116|354|165|207|396|937|2706|1157|1207|7885|10148|10130|18493|18571|25022|25450|25485|3056|25638|25642|25666|292|178|15863|16612|16475|3758|13581|10125|9806|18621|18678|5689|24158|25141|21298|25552|25635|18146|15178|18739|395|16835|16780|16530|24410|8864|18229|3770|18062|20778|15099|9764|18715|23988|18304|18075|18519|381|2974|3385|6342|3766|10470|18557|13113|6244|25452|25475|25857|18000|15064|11748|378|384|16785|16731|16571|3751|5656|10425|9804|20721|20759|25821|18496|1193|7821|16638|16836|15693|20747|4947|4951|4975|5001|5046|5053|5065', date = '2014-03-12' ON DUPLICATE KEY UPDATE items = '10447|10397|7639|11548|7644|7730|7843|8009|8042|24339|3040|14911|16842|2822|3404|6161|23945|10779|7111|7168|10439|17066|17112|17163|21332|21660|21757|24346|24419|25049|25142|25191|24517|3103|1323|2581|18417|21697|21730|24432|15059|304|1316|17078|17127|17144|22740|24215|24418|24571|25058|307|349|170|1320|1393|3122|10443|10429|21607|25095|25636|9696|25668|14984|327|2701|14943|169|7665|190|328|16406|16675|969|7685|24553|25012|25127|25479|25481|25571|3450|25645|25647|20749|352|298|351|1087|1354|20781|25036|25471|9665|25562|25589|3116|354|165|207|396|937|2706|1157|1207|7885|10148|10130|18493|18571|25022|25450|25485|3056|25638|25642|25666|292|178|15863|16612|16475|3758|13581|10125|9806|18621|18678|5689|24158|25141|21298|25552|25635|18146|15178|18739|395|16835|16780|16530|24410|8864|18229|3770|18062|20778|15099|9764|18715|23988|18304|18075|18519|381|2974|3385|6342|3766|10470|18557|13113|6244|25452|25475|25857|18000|15064|11748|378|384|16785|16731|16571|3751|5656|10425|9804|20721|20759|25821|18496|1193|7821|16638|16836|15693|20747|4947|4951|4975|5001|5046|5053|5065', date = '2014-03-12' .