CPU load very very high !!!!, Please assist !!

[Sayed Ali]

Hello All

My MySQL server hard configuration is as follows :-

CentOS 5.8
Dual Quad Core Processor
3 GB ram
MySQL Ver 14.14 Distrib 5.1.61

It's Stock Market website, Concurrent connections reach to 200 and the CPU usage reach 90% +

When i run the command "show processlist" i get "88 Rows in set" or more.

Most of the queries that we seee in the process list is "SELECT vtiger_contactdetails.firstname " statement !!!!!

The client is worried about the CPU load reaching that high. How can i optimize my server to sever the above SELECT query in the least amount of time and server maximum user request.

I am attaching along with this mail my sql configuration file for every one's reference for suggestion,

Thank you,

Since the board DOES NOT support attachment, i am pasting my configuration file here in this post.

PLEASE ACCEPT MY APOLOGIES !!!!

========================
[mysqld]
2 datadir=/var/lib/mysql
3 socket=/var/lib/mysql/mysql.sock
4 user=mysql
5
6 server-id=1
7 #replicate-same-server-id=0
8 log-bin=newcrmdb1-bin
9 #binlog_do_db=crmdb
10 binlog_do_db=newcrmdb
11 binlog-ignore-db=mysql,test
12 #replicate_do_db=crmdb
13 #replicate_do_db=newcrmdb
14 #auto-increment-increment = 3
15 #auto-increment-offset = 1
16 #log-slave-updates
17
18 skip_name_resolve
19
20 #master-host = 192.168.0.203
21 #master-user = replication
22 #master-password = rplpassword
23 #master-port = 3306
24
25 innodb_flush_log_at_trx_commit=0
26 innodb_flush_method=O_DIRECT
27 sync_binlog=0
28 slave-skip-errors = 1062
29
30 back_log = 75
31 # skip-innodb
32 max_connections = 100
33 #thread_stack= 150M
34 key_buffer = 256M
35 key_buffer_size = 256M
36 myisam_sort_buffer_size = 16M
37 join_buffer_size = 4M
38 read_buffer_size = 16M
39 sort_buffer_size = 8M
40 table_cache = 1024
41 table_definition_cache = 4800
42 thread_cache_size = 128
43 wait_timeout = 100
44 connect_timeout = 60
45 tmp_table_size = 300M
46 max_heap_table_size = 300M
47 max_allowed_packet = 64M
48 #max_connect_errors = 10000
49 read_rnd_buffer_size = 8M
50 bulk_insert_buffer_size = 8M
51 query_cache_size = 256M
52 query_cache_limit = 4M
53 query_cache_type = 1
54 query_prealloc_size = 65536
55 query_alloc_block_size = 131072
56 default-storage-engine = InnoDB
57 innodb_buffer_pool_size = 2G
58 #innodb_log_file_size = 768M #25% of pool size
59 innodb_lock_wait_timeout = 120
60 innodb_use_sys_malloc = 0
61 long_query_time = 1
62 log-slow-queries = /var/log/mysql-slow-query.log
63
64 # Default to using old password format for compatibility with mysql 3.x
65 # clients (those using the mysqlclient10 compatibility package).
66 #old_passwords=1
67
68 # Disabling symbolic-links is recommended to prevent assorted security risks;
69 # to do so, uncomment this line:
70 # symbolic-links=0
==================