MySQL Forums
Forum List  »  Optimizer & Parser

CPU load very very high !!!!, Please assist !!
Posted by: Sayed Ali
Date: January 22, 2013 06:49AM

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
==================

Options: ReplyQuote


Subject
Views
Written By
Posted
CPU load very very high !!!!, Please assist !!
8843
January 22, 2013 06:49AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.