Mysql Server Optimization

Question

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 (daily insertions 3,000) & TABLE2: 8,000 rows

• 1 second queries:

  • SELECT {5 columns} FROM TABLE1 b, users u WHERE b.credit = 0 AND b.user_id = u.id AND b.auction_id = "12345" ORDER BY b.id DESC LIMIT 10;
  • SELECT {6 columns} FROM TABLE2 WHERE id = '12345'
  • // there are few more on some non-critical tables
• 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.