Ubuntu, php web app

sojoudian,
Please provide some useful information about the circumstances of the described problem. If you create database applications, you have an idea what the useful information could be, and if the problem happens regularly, it shouldn't be difficult to collect it:

• processlists from the affected server(s);
• error logs;
• configuration files;
• stack traces from the running server;
• output of resource monitors.

@Elena Stepanova
innodb_status_6_nov_after_reboot.txt innodb_status_6_nov.txt master_status_6_nov_after_reboot.txt master_status_6_nov.txt processlist_6_nov_after_reboot.txt processlist_6_nov.txt status_6_nov_after_reboot.txt status_6_nov.txt
As you can see in the process list file, we have some process/transactions which is running about 24 hours and we can't kill them (the KILL command doesn't work, it only changes the state of them to KILLED) and because we're using InnoDB row locking (for update) inside of those transactions their records are not accessible until we restart the database. I should mention, it's not true for all of the transactions which have row locking, only 20-30 transactions each day.

As I said before, we face another issue which is suddenly the number of connected threads on master DB goes high without any patterns every day. Sometimes we stop replications (executing STOP SLAVE) on each slave and it works, but sometimes we have to restart the master DB. because the
number of connected threads keep increasing and when it reaches 100k connected threads, it won't respond to any new connections and the applications will be down. The logs I attached are from two situations, one of them is before restarting the DB (the innodb_max_dirty_pages_pct was set to 0 to make shutdown time a little bit faster) and the other one is after restarting the DB. (when everything was stable)

PS : please check the attachments