few months ago i launched a website, as number of concurrent users raised i noticed my server got slow and sometimes mysql made server crash ( about 7000 concurrent users ) !
i have a powerful server with 32gb of ram and a xeon quad-core CPU dedicated to 1 website, also the programming is neat and lightweight, there is nothing special on DB side ... just some simple tables, no triggers, no complex relations.
can you guys suggest anything that i can do to improve mysql performance or to replace it with ? im hoping for some same experiences and case studies from you.
Update one of the tables that slows down the performance is where i collect and compare user IPs, so that if the IP is new, i add a new record on this table and store users IP ... i feel im doing it wrong ... so im open to constructive criticism :-D is it even good to add index in this table considering im inserting in it a lot (but not often as i use search in it ... ) ?
The question is a bit vague in that it is a general problem that your are experiencing however there are some steps that you can take to determine the underlying cause of the problem.
Profile the hardware:
Check your cpu, memory and disk io statistics to eliminate hardware issues. High Disk IO in particular can cause severe performance issues. There are several articles on how to profile your hardware out there. Go google it for your platform.
Profile the SQL queries:
Enable the slow query log on the MySQL server check this link for more information: http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html.
Identify your queries that run the longest and do a EXPLAIN statement on these queries.If you find that most of your queries that are slow are SELECT/read type statements you might be missing indexes. If you find that UPDATE/INSERT/DELETE(writes) are causing the problems you might have too many indexes or you are facing a IO bottleneck on the disks.
Remember this rule: adding more indexes is going to slow down insert/deletes/updates and speed up select.
The bottom line is that you need to profile the server and the database to determine where you need to make changes to increase performance and scalability. Once you have identified the problem areas then you can take more specific actions.
Let us know if you need help optimizing your server based on your findings.