I saw a very slow SQL through show processlist mysql. Seems to be piwik's monthly statistics sql. The execution of more than 19 hours. And our MySQL server only 1G memory.
This particular query is caused by the Unique Visitors and Unique Users metrics. you can disable it for your Piwik (for all periods) as documented in: http://piwik.org/faq/how-to/faq_113/