MySQL with more than 600 connections

I have Database Server and 4 other client servers.

No of connection at a time from the client servers is more than 600.

It has so many slow queries, which are due to table locking.

I have changed one of table to InnoDB, which gets more update and insert queries.

Please help me in optimizing the database. I have a dedicated server for the database.

Following is my.cnf settings.

[mysqld]  
tmpdir=/tmp  
open_files_limit=33628   
log-slow-queries=/slow-query.txt  
long_query_time=1  
log-queries-not-using-indexes=1  
concurrent_insert=2  


old_passwords=1  
datadir=/var/lib/mysql  

safe-show-database  
tmp_table_size = 128M  
max_heap_table_size = 128M  
query_cache_limit=8M  
query_cache_size=128M ## 32MB for every 1GB of RAM  
query_cache_type=1  
max_user_connections=800  
max_connections=800  
collation_server=utf8_unicode_ci  
character_set_server=utf8  


delayed_insert_timeout=40  

interactive_timeout=10  
wait_timeout=3600  
connect_timeout=20  
thread_cache_size=128  
key_buffer=128M ## 32MB for every 1GB of RAM  
join_buffer=1M  
max_connect_errors=20  
max_allowed_packet=16M  
table_cache=400  
record_buffer=1M  
sort_buffer_size=4M ## 1MB for every 1GB of RAM  
read_buffer_size=4M ## 1MB for every 1GB of RAM  
read_rnd_buffer_size=4M  ## 1MB for every 1GB of RAM  
thread_concurrency=8 ## Number of CPUs x 2  
myisam_sort_buf开发者_运维问答fer_size=32M  
server-id=1  

[mysql.server]  
user=mysql  


[safe_mysqld]  
open_files_limit=33628  
err-log=/var/log/mysqld.log  
pid-file=/var/lib/mysql/mysql.pid 

Currently i am using non persist connection, is persist connection can improve my performance?

One solution is to setup additional slave db servers for all your read operations and only write to your master.

But from the sound of it, you would probably benefit most by moving from myisam to innodb for all your tables that are causing slow queries, and building proper indexes so that your queries do not take so long.

as far as innodb goes, giving your buffer pool lots of memory significantly improves performance:

# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 6144M

but really optimizing the config is not going to magically solve problems at the query level.

Without seeing the queries, structures and indexes, I too believe with @SliverbackNet about queries / table design killing you. If you have some queries that are somewhat static with content, I would try to pre-aggregate some tables on a daily basis to simplify others of getting some results, then tune-in and optimize some of your more critical queries. Find which queries might be causing the biggest bottleneck problems.