I'm facing a very hard problem.
I have a mysql DB (MyISAM) with a main table (user) where I write a row for every new user (around 10 fields). Every INSERT triggers around 10 new INSERT in 10 different tables (counters, settings, etc).
In normal days (10 users/min) it works fine. In high-traffic days (100-200 users/min) my app just become unusable.
I worked a lot on system variables, tables indexes, etc., without any improvements. Today I figured out that probably the problem is on my main table (user): the slow query log shows that every INSERT can reach 180s to be executed!
This is the main query because I need the user ID in order to go on and let the user open and use the app. Moreover, this table receives a lot of SELECT, some directly and some in JOIN. It means that the locks for INSERT also block all the SELECT.
90% of new users, in high-traffic days, can't "pass" the signup, they get an error and they leave the app.
I'm reading a lot and trying a lot of new settings but probably the problem is not in mysql settings. It's in the app structure or in the first INSERT.
Do you have any suggestion for me? I'm completely open to anything. I would like to discuss with you possible solutions.
Thanks a lot in advance for any help.
p.s. I didn't put any info about DB, query, etc... If you need more information just let me know.
I don't have privilages for commenting so I'll add here this information:
1) do you mean changing InnoDB only the user table or the whole DB?
2) I just checked the values of Max_used_connections (it's 271) and Threads_running (it's 29). This is my file my.cnf