SmartCJ Support Forum

This is my problem right now. I have setup a master on a new server. with 150,000 galleries, alot of tags, and 650,000 thumbs. Then I setup 20 slaves site and all connect to the master. all of them are on the same server, mysql is all localhost. Here is the problem. All sites are loading very slow, and they "Can not connect to database server." or sometime "Too Many Mysql Connections". I have 20 crons job setup on the server. I suspect is open loop connection which overload mysql, because everytime I ask admin to reset the connection, it works fine. But when I install new copy of smartcj slave, it crashes the server again. Is there anyone having the same problem? My old network is using two servers, one mysql and another for the frontend. But the master for the old network only carries 8,500 galleries and less thumb.

2013-08-19 22:20:34 : Mysql error: 1053 (Server shutdown in progress) in query select g.id, s.thumb_id from rot_galleries as g
left join rot_gallery_stats as s ON g.id = s.thumb_id
where isnull(s.thumb_id)
2013-08-19 22:20:36 : Mysql error: 2006 (MySQL server has gone away) in query UPDATE functions_data SET value = '1376951045' WHERE name = 'process_deleted'
2013-08-19 22:20:37 : Mysql error: 2006 (MySQL server has gone away) in query SELECT SQL_CALC_FOUND_ROWS id, gallery_md5 FROM rot_galleries WHERE status = 12 LIMIT 0, 1
2013-08-19 22:20:50 : Mysql error: 1053 (Server shutdown in progress) in query select g.id, s.thumb_id from rot_galleries as g
left join rot_gallery_stats as s ON g.id = s.thumb_id
where isnull(s.thumb_id)
2013-08-19 22:21:04 : Mysql error: 2006 (MySQL server has gone away) in query UPDATE functions_data SET value = '1376951240' WHERE name = 'process_deleted'
2013-08-19 22:21:03 : Mysql error: 1053 (Server shutdown in progress) in query SELECT model_id, SUM( gs.total_ctr)/count(g.gallery_md5) AS total_ctr
FROM rot_gal2model AS m
LEFT JOIN rot_galleries AS g ON g.gallery_md5 = m.gallery_md5
LEFT JOIN rot_gallery_stats AS gs ON gs.thumb_id = g.id
WHERE g.status = 1 and gs.total_shows > 100
group by model_id
2013-08-19 22:21:13 : Mysql error: 1317 (Query execution was interrupted) in query select g.id, s.thumb_id from rot_galleries as g
left join rot_gallery_stats as s ON g.id = s.thumb_id
where isnull(s.thumb_id)
2013-08-19 22:21:28 : Mysql error: 2006 (MySQL server has gone away) in query SELECT url, id, gallery_slug, gi.gallery_md5, tags, gs.total_ctr as thumb_ctr FROM rot_galleries as g
LEFT JOIN rot_gallery_stats AS gs ON gs.thumb_id = g.id
LEFT JOIN rot_gallery_info AS gi ON gi.gallery_md5 = g.gallery_md5
LEFT JOIN rot_gallery_data AS gd ON gd.gallery_md5 = g.gallery_md5
WHERE g.rgroup = 0 and g.sponsor_id = 1 and gi.source_url = ''
2013-08-19 22:21:33 : Mysql error: 2006 (MySQL server has gone away) in query SELECT SQL_CALC_FOUND_ROWS id, gallery_md5 FROM rot_galleries WHERE status = 12 LIMIT 0, 1
2013-08-19 22:21:40 : Mysql error: 2006 (MySQL server has gone away) in query SELECT * FROM rot_import_sets WHERE status = 1 and period != 0 and next_grab < 1376950895 LIMIT 0, 1
2013-08-19 22:21:40 : Mysql error: 2006 (MySQL server has gone away) in query SELECT id FROM rot_galleries WHERE status = 4 OR status = 14
ORDER BY priority
LIMIT 0, 40
2013-08-19 22:21:40 : Mysql error: 2006 (MySQL server has gone away) in query UPDATE functions_data SET value = '1376951149' WHERE name = 'process_deleted'
2013-08-19 22:21:43 : Mysql error: 2006 (MySQL server has gone away) in query SELECT count(*) FROM rot_content WHERE status = 'to_grab'
2013-08-19 22:21:45 : Mysql error: 2006 (MySQL server has gone away) in query UPDATE rot_settings SET value = '0' WHERE name = 'rot_cron_running'
2013-08-19 22:21:53 : Mysql error: 2006 (MySQL server has gone away) in query SELECT SQL_CALC_FOUND_ROWS id, gallery_md5 FROM rot_galleries WHERE status = 12 LIMIT 0, 1
2013-08-19 22:23:01 : Mysql error: 2006 (MySQL server has gone away) in query SELECT * FROM rot_import_sets WHERE status = 1 and period != 0 and next_grab < 1376950981 LIMIT 0, 1
2013-08-19 22:23:20 : Mysql error: 2006 (MySQL server has gone away) in query SELECT id FROM rot_galleries WHERE status = 4 OR status = 14
ORDER BY priority
LIMIT 0, 40
2013-08-19 22:23:20 : Mysql error: 2006 (MySQL server has gone away) in query SELECT count(*) FROM rot_content WHERE status = 'to_grab'
2013-08-19 22:23:23 : Mysql error: 2006 (MySQL server has gone away) in query UPDATE rot_settings SET value = '0' WHERE name = 'rot_cron_running'
2013-08-19 22:24:25 : Mysql error: 2006 (MySQL server has gone away) in query SELECT * FROM rot_import_sets WHERE status = 1 and period != 0 and next_grab < 1376951065 LIMIT 0, 1
2013-08-19 22:24:31 : Mysql error: 2006 (MySQL server has gone away) in query SELECT id FROM rot_galleries WHERE status = 4 OR status = 14
ORDER BY priority
LIMIT 0, 40
2013-08-19 22:24:31 : Mysql error: 2006 (MySQL server has gone away) in query SELECT count(*) FROM rot_content WHERE status = 'to_grab'
2013-08-19 22:24:32 : Mysql error: 2006 (MySQL server has gone away) in query UPDATE rot_settings SET value = '0' WHERE name = 'rot_cron_running'
2013-08-19 22:26:47 : Mysql error: 2006 (MySQL server has gone away) in query UPDATE rot_settings SET value = '0' WHERE name = 'rot_cron_running'
2013-08-19 22:26:50 : Mysql error: 2006 (MySQL server has gone away) in query UPDATE functions_data SET value = '1376951671' WHERE name = 'check_db'
2013-08-19 22:26:50 : Mysql error: 2006 (MySQL server has gone away) in query select g.id, s.thumb_id from rot_galleries as g
left join rot_gallery_stats as s ON g.id = s.thumb_id
where isnull(s.thumb_id)
2013-08-19 22:26:50 : Mysql error: 2006 (MySQL server has gone away) in query UPDATE functions_data SET value = '1376951598' WHERE name = 'process_deleted'
2013-08-19 22:26:50 : Mysql error: 2006 (MySQL server has gone away) in query SELECT SQL_CALC_FOUND_ROWS id, gallery_md5 FROM rot_galleries WHERE status = 12 LIMIT 0, 1
2013-08-19 22:27:05 : Mysql error: 2006 (MySQL server has gone away) in query SELECT * FROM rot_import_sets WHERE status = 1 and period != 0 and next_grab < 1376951225 LIMIT 0, 1
2013-08-19 22:27:06 : Mysql error: 2006 (MySQL server has gone away) in query SELECT id FROM rot_galleries WHERE status = 4 OR status = 14
ORDER BY priority
LIMIT 0, 40
2013-08-19 22:27:06 : Mysql error: 2006 (MySQL server has gone away) in query SELECT count(*) FROM rot_content WHERE status = 'to_grab'
2013-08-19 22:27:07 : Mysql error: 2006 (MySQL server has gone away) in query UPDATE rot_settings SET value = '0' WHERE name = 'rot_cron_running'

2013-04-30 04:35:45: Allowed memory size of 134217728 bytes exhausted (tried to allocate 152989176 bytes) in /usr/home/filipin3/domains/filipinasexpics.org/public_html/scj/admin/files/logs.php on 0
21.31 select SQL_CALC_FOUND_ROWS g.id, g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if (gs.total_shows < 500, 1, 0) as new_thumb FROM rot_galleries as g JOIN rot_gallery_stats as gs on gs.thumb_id = g.id WHERE g.status = 1 AND g.rgroup != 0 and gs.best_thumb = 'yes' AND g.id IN ( SELECT gal_id FROM rot_gal2group as g2gr WHERE g2gr.group_id NOT IN ()) ORDER BY new_thumb ASC, gs.total_ctr DESC LIMIT 0, 196# queryitems:: :
21.31 select g.id, g.gallery_md5, gs.total_shows as thumb_casts, gs.total_clicks as clicks, if (gs.total_shows < 500, 1, 0) as new_thumb FROM rot_galleries as g JOIN rot_gallery_stats as gs on gs.thumb_id = g.id WHERE g.status = 1 AND g.rgroup != 0 AND g.id IN ( SELECT gal_id FROM rot_gal2group as g2gr WHERE g2gr.group_id NOT IN ()) AND gs.total_shows < 500 GROUP BY gallery_md5 LIMIT 0, 28# queryitems:: :
22.20 SELECT SQL_CALC_FOUND_ROWS g.id, if (gs.total_shows < 500, 1, 0) as new_thumb from rot_gal2group as g2gr
JOIN rot_gallery_stats as gs ON gs.thumb_id = g2gr.gal_id
JOIN rot_galleries as g ON g.id = g2gr.gal_id
WHERE g.status = 1 and g.rgroup != 0 and g2gr.group_id = '4'
and gs.best_thumb = 'yes'
and g.id NOT IN (1) order by new_thumb ASC, gs.total_ctr desc limit 0, 1 #cat0 :: :
22.20 UPDATE rot_linked_db SET category_thumb_ids = 'a:1:{i:0;i:1;}' WHERE rot_linked_id = '0' :: :
22.20 SHOW FULL PROCESSLIST:: :
22.20 SELECT gr.name as category_name, gr_data.custom_name as category_custom_name,
gr_data.description as category_description, gr.id as category_id,
gr_data.group_custom_var1, gr_data.group_custom_var2, gr_data.group_custom_var3, gr_data.status as group_status, gr.parent_id,
g.*, gs.total_shows
FROM rot_galleries AS g
JOIN rot_gallery_stats as gs on gs.thumb_id = g.id
JOIN rot_gallery_info AS gi ON gi.gallery_md5 = g.gallery_md5
JOIN rot_groups AS gr ON gi.url = gr.id
JOIN rot_groups_data AS gr_data ON gi.url = gr_data.group_id
where g.rgroup = 0 and g.sponsor_id = 0 and gi.url != 0
and content_type = '0'
and gr_data.site_id = '0' and gi.source_url = '' order by category_name asc:: :
22.20 SELECT SQL_CALC_FOUND_ROWS g.id, if (gs.total_shows < 500, 1, 0) as new_thumb from rot_gal2group as g2gr
JOIN rot_gallery_stats as gs ON gs.thumb_id = g2gr.gal_id
JOIN rot_galleries as g ON g.id = g2gr.gal_id
WHERE g.status = 1 and g.rgroup != 0 and g2gr.group_id = '4'
and gs.best_thumb = 'yes'
and g.id NOT IN (1) order by new_thumb ASC, gs.total_ctr desc limit 0, 1 #cat0 :: :
22.20 UPDATE rot_linked_db SET category_thumb_ids = 'a:1:{i:0;i:1;}' WHERE rot_linked_id = '0' :: :
22.20 SHOW FULL PROCESSLIST:: :
22.20 SELECT gr.name as category_name, gr_data.custom_name as category_custom_name,
gr_data.description as category_description, gr.id as category_id,
gr_data.group_custom_var1, gr_data.group_custom_var2, gr_data.group_custom_var3, gr_data.status as group_status, gr.parent_id,
g.*, gs.total_shows
FROM rot_galleries AS g
JOIN rot_gallery_stats as gs on gs.thumb_id = g.id
JOIN rot_gallery_info AS gi ON gi.gallery_md5 = g.gallery_md5
JOIN rot_groups AS gr ON gi.url = gr.id
JOIN rot_groups_data AS gr_data ON gi.url = gr_data.group_id
where g.rgroup = 0 and g.sponsor_id = 0 and gi.url != 0
and content_type = '0'
and gr_data.site_id = '0' and gi.source_url = '' order by category_name asc:: :
22.20 SHOW FULL PROCESSLIST:: :
22.20 SELECT gr.name as category_name, gr_data.custom_name as category_custom_name,
gr_data.description as category_description, gr.id as category_id,
gr_data.group_custom_var1, gr_data.group_custom_var2, gr_data.group_custom_var3, gr_data.status as group_status, gr.parent_id,
g.*, gs.total_shows
FROM rot_galleries AS g
JOIN rot_gallery_stats as gs on gs.thumb_id = g.id
JOIN rot_gallery_info AS gi ON gi.gallery_md5 = g.gallery_md5
JOIN rot_groups AS gr ON gi.url = gr.id
JOIN rot_groups_data AS gr_data ON gi.url = gr_data.group_id
where g.rgroup = 0 and g.sponsor_id = 0 and gi.url != 0
and content_type = '0'
and gr_data.site_id = '0' and gi.source_url = '' order by gs.total_ctr desc:: :
22.20 UPDATE rot_settings SET value = '0' WHERE name = 'rot_cron_running' :: :
22.20 SELECT * FROM rot_import_sets WHERE status = 1 and period != 0 and next_grab < 1376950832 LIMIT 0, 1:: :
22.20 SHOW FULL PROCESSLIST:: :
22.20 SELECT gr.name as category_name, gr_data.custom_name as category_custom_name,
gr_data.description as category_description, gr.id as category_id,
gr_data.group_custom_var1, gr_data.group_custom_var2, gr_data.group_custom_var3, gr_data.status as group_status, gr.parent_id,
g.*, gs.total_shows
FROM rot_galleries AS g
JOIN rot_gallery_stats as gs on gs.thumb_id = g.id
JOIN rot_gallery_info AS gi ON gi.gallery_md5 = g.gallery_md5
JOIN rot_groups AS gr ON gi.url = gr.id
JOIN rot_groups_data AS gr_data ON gi.url = gr_data.group_id
where g.rgroup = 0 and g.sponsor_id = 0 and gi.url != 0
and content_type = '0'
and gr_data.site_id = '0' and gi.source_url = '' order by gs.total_ctr desc:: :
22.20 UPDATE rot_settings SET value = '0' WHERE name = 'rot_cron_running' :: :
22.20 SELECT * FROM rot_import_sets WHERE status = 1 and period != 0 and next_grab < 1376950835 LIMIT 0, 1:: :
22.20 SELECT id FROM rot_galleries WHERE status = 4 OR status = 14
ORDER BY priority
LIMIT 0, 3:: :
22.20 SELECT id FROM rot_galleries WHERE status = 4 OR status = 14
ORDER BY priority
LIMIT 0, 3:: :
22.20 SELECT count(*) FROM rot_content WHERE status = 'to_grab' :: :
22.20 SELECT count(*) FROM rot_content WHERE status = 'to_grab' :: :
22.20 UPDATE rot_settings SET value = '0' WHERE name = 'rot_cron_running' :: :
22.20 UPDATE rot_settings SET value = '0' WHERE name = 'rot_cron_running' :: :
22.20 SELECT id, gallery_slug, g.gallery_md5, sponsor_id, url, source_url, rot_content_id, embed_template, thumb_url, embed_code,
flv_url, flv_size_x, flv_size_y, flv_thumb_url, tags, type, rgroup, content, content_type,
duration, date, activation_date, casts, clicks, rating, priority, crop_profile_id, status,
alt, description, custom_var1, custom_var2, custom_var3
FROM rot_galleries as g
JOIN rot_gallery_info as gi ON gi.gallery_md5 = g.gallery_md5
JOIN rot_gallery_data as gd ON gd.gallery_md5 = g.gallery_md5
WHERE id IN (13427,13302,13068,13215,13515) :: :
22.20 INSERT into rot_page_items
SET page_id = '36', page_crc = '1308191260192691', items = '9360|2112|1910|1134|12878|13280|13313|13013|2818|13462|1972|13068|13116|13042|13372|8519|13377|1836|13215|12868|1812|12825|13427|13545|13384|13276|2840|13515|13271|1865|13482|12021|13111|9367|13442|1723|13066|2017|13228|12875', date = '2013-08-19'
ON DUPLICATE KEY UPDATE items = '9360|2112|1910|1134|12878|13280|13313|13013|2818|13462|1972|13068|13116|13042|13372|8519|13377|1836|13215|12868|1812|12825|13427|13545|13384|13276|2840|13515|13271|1865|13482|12021|13111|9367|13442|1723|13066|2017|13228|12875', date = '2013-08-19' :: :
22.20 select * from rot_content where content_id = '4616' :: :

Did you tune mysql ?

Yes I did, and follow everything in guideline

What part of your server is overloaded ?

I check the mysql log and theres some error reguarding Sphinx. Finally, the problem is sphinx on the master having conflicts with slave. Maybe u should take a look later.

Why do you think so ?
what do you see in logs ?

Those logs above were ones I found. I have done a test, and when I removed sphinx, everything loads fast and online, when I add sphinx, it crash everything. I am sure I setup sphinx on both servers right way.

So you have to check why sphinx crashes your server.