Query Routinely Crashing Server

03-09-2008, 05:32 PM

I am having a problem where the following query crashes mySQL periodically. I can't see a reason for it in the error log, but suspect a memory issue due to the subselects or the views or a memory issue that is evoking a bug in mySQL. The OS is 32bit Redhat. Fairly standard settings. All tables in this query are innodb, innodb_buffer_pool = 2200m, tmp_table_size = 32m, max_heap_table = 16m, system has 8 gigs of ram, connections during failure are sometimes as low as 2 or 3, system is
dedicated to mySQL only.

The query is various forms of the following. This query is a key reporting query in our application and the underlying tables are highly optimized to make this query perform well at least from the standapoint of an explain. This query typically
processes hundreds of thousands of rows in about .05 seconds so performance wise it is acceptable, but the crashes are not.

Any help would be great. I realize there are methods to improve this query, particularly the abundance of correlated subqueries. However, the correlated subqueries are there because the math done in the query is modular and breaking the query up improves our maintenance and adding of different formulas. For now I am stuck with this query format and need to determine what the bug/issue is that is leading to a crash.