I have large DB around 25 GBs and the most busy table is around 9.5 GBs, database is increasing @ 10% per month, I am using mysql with MyISAM engine.
Problem started 3 days back, there is long list of queries in mysql status showing table level lock, it's taking long time to open the application. To keep the application running I set
wait timeout = 8. Application is running but not executing long queries.
Kill 22 DELAYED localhost eximstats Delayed insert 3 Waiting for INSERT --- Kill 155 DELAYED localhost eximstats Delayed insert 58 Waiting for INSERT --- Kill 23123 office_eoffice localhost office_nw Query 0 Sending data select data_type, fund from transaction where ID = '539329' AND productCode = 'RC11' AND folioNumber Kill 23124 common localhost common Sleep 0 --- --- Kill 31879 office_eoffice localhost office_nw Query 10 Sending data SELECT * FROM transaction WHERE transDate = (SELECT MIN(transDate) FROM transaction WHERE ID = '4473 Kill 32289 eximstats localhost eximstats Sleep 3 --- --- Kill 32635 office_eoffice localhost office_nw Query 0 statistics SELECT * FROM clients_map WHERE map_from = '434301' AND arn_id = '396' Kill 32643 common localhost common Sleep 0 --- --- Kill 32644 root localhost None Sleep 0 --- --- Kill 32645 root localhost mysql Query 0 --- SHOW PROCESSLIST Kill 32651 common localhost common Query 0 statistics select nav, nav_date from nav_new where productCode='CB87' and nav_date ='2004-10-11' order by nav_
What is the permanent and long term solution?