MySQL optimization help - 40,000 member site

Hello,

I run Amember pro on a powerful server with Dual 'Nehalem' Quad Core Xeon 5520 + 12GB RAM. I have about 40,000 users in 20+ products that are mostly live events. So in my situation some times I have as much as 1000 or 2000 members trying to sign up or login using AmemberPro (specially during promotional free events).

I have noticed few MySQL errors in the past such as user exceeded max connections, could not connect to mysql db.

I have a big event coming up next month and I am wondering what tuning of mysql/apache I could do to avoid down time or slow loading.

I recently ran the tuning-primer.sh (a mysql analyzer at http://www.day32.com/MySQL/).

Following recommendations were flagged in red so I though i should seek help from this forum on how to fix it.

Code:

QUERY CACHE
Query cache is enabled
Current query_cache_size = 128 M
Current query_cache_used = 2 M
Current query_cache_limit = 1 M
Current Query cache Memory fill ratio = 2.19 %
Current query_cache_min_res_unit = 4 K
Query Cache is 21 % fragmented
Run "FLUSH QUERY CACHE" periodically to defragment the query cache memory
If you have many small queries lower 'query_cache_min_res_unit' to reduce fragmentation.
Your query_cache_size seems to be too high.
Perhaps you can use these resources elsewhere
MySQL won't cache query results that are larger than query_cache_limit in size

Code:

TEMP TABLES
Current max_heap_table_size = 1.00 G
Current tmp_table_size = 1.00 G
Of 4235433 temp tables, 49% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

My current my.cnf file is configured as

Code:

[mysqld]
skip_name_resolve
set-variable = max_connections=2000
set-variable = thread_cache_size=42
set-variable = long_query_time=1
set-variable = thread_cache_size=42
set-variable = innodb_buffer_pool_size=128M
set-variable = innodb_thread_concurrency=4
set-variable = wait_timeout=10
set-variable = query_cache_type=1
set-variable = query_cache_size=128M
set-variable = query_cache_limit=1M
set-variable = join_buffer_size=1M
set-variable = table_cache=1024
set-variable = tmp_table_size=1024M
set-variable = max_heap_table_size=1024M
set-variable = key_buffer=384M
set-variable = key_buffer_size=1024M
safe-show-database
log-slow-queries
set-variable = long_query_time=1
#set-variable = max_heap_table_size=4096M
#set-variable = tmp_table_size=4096M

I really appreciate any help/suggestions any one can give me for this issue. I am a newbie to server administration and my 3rd party admin does not provide any support unless I specifically ask him! so any suggestions on apache/php is also appreciated.

Thank you very much.

What is your site?