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.
