Newbie, Dont know where to start.. require ur assistance

04-11-2007, 09:16 AM

Hi,

I'm new to MySQL as I'm a Network Engineer. At the moment, I've set up a MySQL community server 5.0.33 on RHEL4 x64bit V3 updated kernel and without Hyperthreading. The physical server is Dell PowerEdge 6840 Series with RAID(10), 32GB Ram, 4 Xeon Duo Core processors.

I've to put the box into production. As I eagerly want to know what sort of the basic variables should be in place compare to my existing my.cnf (listed below). Currently the box is handling about 21,000 transaction from 1200 user entries within 3 hours. I'm using INNODB database.

At the peak hours, where my client are viewing their transaction list, and this is where the problem started. I notice the all CPU about 70%, memory utilization is about 3.2GB, mysqld spawning about 770 process, show processlist about 700 rows. About 75% of the rows, there's process executing and queue in the list. Many of the rows has the copy to tmp select statement in it. This resulting many of other functions not responding ... ie, data input, changing price, application log-in .... etc.

When I check the slowlog, it stated that the transaction viewing are causing the slowness as the statement has a copy to temp & left join in the select statement. The CPU utilization maximum capped at 75% on all 8 core.

Basically, I'm not a very knowledgeable guy in programming. So at the moment I'm clueless, as I should have a BIG box but I dont know where I should start troubleshooting ? How should I carry out the benchmark testing with the proper guideline or proper variables environment that suite my server box ? Btw how to calculate the database size ?

I would very much pleased as I hoping someone who can help me here.
Thank You

Comment

I've gone thru the messages with the my programmer. According to him, the statement has been fine tune to the maximum as there are few indexes in place. Moreover, it's the transaction table, so basically there's more than 20 data fields has been used. The worst scenario of this are the users view them at range of 300 transaction to 9000 transaction at a time.

To my latest tuning at the Java Web, seems all the congestion has been diverted from web to mysql server. Apparently, during the peakest point, we encounter signaficant increase of spawning process from 70-100 to 700-1400. At the processlist, it's increase to 1300 rows with about 1200 queue thread. Most of them are the same queries... listed earlier.

At my CPU utilisation, we only encounter about 40%-70% only. Maybe the we under utilised the thread concurrent for OS. However, at memory level, it's uses about 3Gb rams, and IO is still free.

We monitored the sql performance, however, the cache are not being used at all... I'm still struggling with the tuning.

I'll try to get my programmer assistant to generate the output for your perusal.