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 '[info]'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

« phpmyAdmin incosistency... || Pattern match problem »


antivirus | apache | asp | blogging | browser | bugtracking | cms | crm | css | database | ebay | ecommerce | google | hosting | html | java | jsp | linux | microsoft | mysql | offshore | offshoring | oscommerce | php | postgresql | programming | rss | security | seo | shopping | software | spam | spyware | sql | technology | templates | tracker | virus | web | xml | yahoo | home