I am new to database management.
My application is very database intensive so I've tried really hard to make sure the application and the MySQL database are working as efficiently as possible together.
Currently I'm tuning the MySQL query cache on a 6 gb RAM, quadcore processor computer. My current MySQL is configured as follows:
query_cache_size=1024M
query_cache_limit=100M
key_buffer_size=1024M
My rapidly growing table consists of 5,000,000 rows of ids, usernames and their corresponding display names and location (all of these are VARCHAR 255 except for the PRIMARY INT id). There are two actions which are constantly happening (and nothing also):
- I constantly have to search for a specific (unique) username (I have indexed the username).
- I am adding a lot of usernames at the same time.
I estimate that I am doing 10:1 reads vs inserts.
I believe my program is being limited by my database performance.
Can someone please give me advice on what the best settings for my MySQL server should be? Also, Is the query_cache_size too high? I know this is a very general question, but I would like some general advice for setting MySQL server variables for database-intensive programs as described above.