I have MySQl running for VoIP Telecom, where we have more than 5,000 agents logged in every seconds. currently our DB size is 2.5 GB.
But every day we are facing problem slow down MySQl, and some time totally not responding our radius (Freeradius) server.
Currently MySQL type is myISAM but some of my friend suggest me to move InnoDB, because myISAM is locking whole table while inserting and updating any row, but innoDB will lock the row, and we have very huge quries for inserting and updating tables.
So please suggest me type of configuration i have to apply in mySQl to avoide slow and crashing problem?
myISAM is alot faster than innodb. it is not good on transaction and db administration though. (eg, you cannot commit, rollback, or define size storgae etc on myISAM, but you can on innodb). implication for this would be if you were bulkloading say 5000 records and somehow there is connection malfunction and only 50 records were inserted, you cannot redo or rollback and start over again.
i think there is slower response because you might be inserting in a table that has index(es).
Does your table have indexes? are there any index corruptions in the table?
one thing you should know about inserting in an indexed table, inserting records when there are indexes in a table causes slow-downs. if you are using indexes in your table, i suggest dropping the indexes and recreating them after the insert commit.
I have similar type of system like you , more to CDR data every seconds , my program did update,select and now it moves to 5 millions no purge, all that 5 millions in 1 table.
Try indexing or check your program code, how the transacntion to database is going on, if you do have program codes
That is how we manage it here, it comes from the code, we redesign our codes to make it fast.
We use MyIsam