MYSQLD taking high CPU system usage
 

Hii All,
We are using wordpress with MYSQL. Both the app and DB server are different with 6CPU and 6GB RAM and 32bit processor.

We had noticed recently that mysqld process is taking too much of system usage- ranging upto 100% of CPU utilization while having the load of 1200-1600 concurrent users.

Pasting d my.cnf file-
# The following options will be passed to all MySQL clients
[client]
port = 3306
socket = path
bind-address = hostname
datadir = path
# Here follows entries for some specific programs

# The MySQL server
[mysqld]
skip-name-resolve
port = 3306
socket = path
datadir = path
bind-address = host name
skip-locking
open-files=10000
bulk_insert_buffer_size = 32M
connect_timeout=10
interactive_timeout=400
join_buffer=32M
join_buffer_size = 4M
key_buffer = 2048M
key_buffer_size = 1024M
max_allowed_packet=16M
max_connect_errors=10
max_connections=1500
max_heap_table_size = 1024M
table_cache=4096
sort_buffer_size = 512M
net_buffer_length = 8K
read_buffer_size = 8M
read_rnd_buffer_size = 80M
myisam_sort_buffer_size = 1024M
myisam_max_sort_file_size =1024M
max_heap_table_size = 1024M
query_cache_type =1
query_cache_limit = 256M
query_cache_size = 512M
query_prealloc_size = 65K
query_alloc_block_size = 128K
record_buffer = 1M
tmp_table_size = 2048M
thread_cache_size=1024
thread_concurrency=16
wait_timeout=500
max_write_lock_count = 1 #To force MySQL to temporarily elevate the priority of all SELECT statements that are waiting for a table after a specific number of inserts to the table occur. This allows READ locks after a certain number of WRITE locks.
low_priority_updates = 1
open_files_limit = 32768
max_join_size =2048M #added
flush
flush_time=3600
######concurrent_insert=2 #added for insert very slow

# Replication Master Server (default)
# binary logging is required for replication
log-bin=mysql-bin

# binary logging format - mixed recommended
binlog_format=mixed

# required unique id between 1 and 2^32 - 1
# defaults to 1 if master-host is not set
# but will not function as a master if omitted
server-id = 1

[mysqldump]
quick
max_allowed_packet = 64M

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates

[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer = 32M
write_buffer = 32M

[isamchk]
key_buffer_size = 128M
sort_buffer_size = 128M
read_buffer_size = 32M
write_buffer_size = 32M

[mysqlhotcopy]
interactive-timeout



Kindly help us if we are missing something. And also help us with the high system usage not the user usage?

Thanks for your time...

Varsha

No way for us to know. First, what version of MySQL, and on what version/distro of Linux? Aside from that, there are MANY things to check:

• How are the servers connected?
• Same LAN or different?
• What speed are the network connections?
• Firewalls between the two?
• Any network changes recently?
• Any changes to the web code recently?
• Channel bonding, or single NIC's?
• How big is the database?
• Version of Wordpress?
• Changes/updates to Wordpress, MySQL, or Linux recently??

There are many pieces to this puzzle.

Hi,
MYSQL version 5.1.41
Linux Version - 2.6.18-238.9.1.el5PAE (mockbuild@x86-004.build.bos.redhat.com) (gcc version 4.1.2 20080704 (Red Hat 4.1.2-50)) #1 SMP
Wordpress Version - 3.0.1

• Connected in a LAN
• Network speed is 1 gbps
• No Firewall
• Changes made in my.cnf for tuning - please check are they properly configured?
• No bonding as ther are no multiple network cards
• This is a VMware Virtual machine
• Done partitioning of tables by primary key
• Database size in what terms - has 48 tables- predefined tables for wordpress + few tables for OWA and rolescoper plugin. Currently OWA is deactivated from application end but the tables are still there.
• No changes made in wordpress n linux.

Plz revert in case of any other queries.

Ok...are these servers in the same rack? Building? Connected to the same switch, or through different switches? Has the switch been examined, to see if it's getting overloaded?
All the way from one server to another? What else is running on those boxes? Is the network connection getting saturated?
No way for us to know what "properly" is. As said before, there are many variables.
Depending on your usage of the existing network link, that may not be a problem.
Uhhh...really?? BOTH of them are, or just one? What are the resources you've got dedicated to the DB and Web servers?
Spell out your words. Text-speak garbage isn't nice for anyone to read.

Database size in number of records and/or disk space used. If you've got 48 tables, and they've only got 10 records a piece, that's trivial. If you've got 4 tables, each with 10 million records each...that's far different, even though you've got fewer tables. Chances are you're having a problem because this is running in a VM, rather than on a dedicated server. VM has overhead associated with it, and depending on the resources you've got assigned to it, you're overloading it because it's running as fast as it can. Can only ask so much of a machine. If this is a mission-critical machine, then I'd strongly suggest putting it on dedicated servers - NOT VM.

And since you're using RHEL...the best step you could take would be to CALL REDHAT SUPPORT, since you're paying for it, right?