max_user_connections problem
Date: 09/15/07
(MySQL Communtiy) Keywords: php, mysql, database, sql
I have a recurring problem with my site. It gets to max_user_connections very quickly. In php they advised me to make rapid-fire connections, and it works better than before, but it still builds up regularly. My host says that sometimes queries block the server. This is now a really serious problem and I'm in trouble with my host.
This happens since I tried to normalize the database by splitting information on other tables, instead of having fields with comma-separated lists. Thing is, now some query require several JOIN to get all the necessary information. Example:
$info_query = "SELECT $stories_table.id, $stories_table.series_id, $stories_table.story_num, $stories_table.story_title, $stories_table.pairing_extra, $stories_table.characters_extra, $stories_table.rating, $stories_table.spoilers, $stories_table.challenge, $stories_table.complete,
$series_table.user_id, $series_table.series_title,
$users_table.name,
$chapters_table.id as chid, CONCAT_WS('$chapter_separator', $chapters_table.chapter_num, $chapters_table.chapter_title) AS chapter_title, $chapters_table.chapter_num, $chapters_table.summary, $chapters_table.notes, DATE_FORMAT($chapters_table.date, '$date_format') AS date_f,
$ratings_table.name as rating_name
FROM $stories_table
LEFT JOIN $series_table ON $series_table.id=$stories_table.series_id
LEFT JOIN $users_table ON $users_table.id=$series_table.user_id
LEFT JOIN $chapters_table ON $stories_table.id = $chapters_table.story_id
LEFT JOIN $ratings_table ON $ratings_table.id = $stories_table.rating
WHERE $chapters_table.story_id = $story_id AND $chapters_table.chapter_num = $chapter_num";
Could this be the source of my problems? If so, do you know any alternative that doesn't load the server that much? I'm getting really desperate with this.
My host uses mysql version 4.0.27.
Thanks!
Source: http://community.livejournal.com/mysql/119223.html