Hi Geeks,

We are having serious MySQL(InnoDB) performance issues at a moment when we do:

• (10-20) insertions on TABLE1
• (10-20) updates on TABLE2

Note: Both above operations happens within fraction of a second. And this occurs every few (10-15) minutes.

And all online users (approx 400-600) doing read operation on join of TABLE1 & TABLE2 every 1 second.

Here is our mysql configuration info: http://docs.google.com/View?id=dfrswh7c_117fmgcmb44

Issues:

• Lot queries wait and expire later (saw it from phpmyadmin / processes).
• My poor MySQL server crashes sometimes

Questions

• Q1: Any suggestions to optimize at MySQL level?
• Q2: I thinking to use persistent connections at application level, is it right?

Info Added Later:

• Database Engine: InnoDB
• TABLE1 : 400,000 rows (inserting 8,000 daily) & TABLE2: 8,000 rows
• 1 second query: SELECT b.id, b.user_id, b.description, b.debit, b.created, b.price, u.username, u.email, u.mobile FROM TABLE1 b, TABLE2 u WHERE b.credit = 0 AND b.user_id = u.id AND b.auction_id = "12345" ORDER BY b.id DESC LIMIT 10; // there are few more but they are not so critical.
• Indexing is good, we are using them wisely. In above query all id's are indexed
• And TABLE1 has frequent insertions and TABLE2 has frequent updates.