>Have you tried monitoring server B via 'top' or something and see what
>processes are taking up CPU time and/or swapping.
Hello,
I have monitored top for a bit. It is definitely MySQL-Max
that is taking up the CPU and placing the load on the server. I have
run strace on some of the pids and see nothing normal. I have
attached here one of the listings from processlist just in case you
see something:
+-----+----------------------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| Id | User | Host | db | Command | Time
| State | Info
|
+-----+----------------------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
| 509 | {user} | localhost | {database} | Query |
0 | Sorting result | SELECT sid,title,hits from stories
WHERE uid > 0 and Hits > 0 ORDER BY Hits desc LIMIT 10 |
| 513 | {user} | localhost | {database} | Sleep |
7 | |
|
| 516 | {user} | localhost | {database} | Query |
1 | Sending data | SELECT COUNT(*) FROM comments WHERE sid
= 'ical' |
| 521 | {user} | localhost | {database} | Sleep |
5 | |
|
| 523 | {user} | localhost | {database} | Sleep |
2 | |
|
| 525 | {user} | localhost | {database} | Sleep |
0 | |
|
| 526 | {user} | localhost | {database} | Query |
1 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021001061844504' ORDER BY date desc |
| 529 | {user} | localhost | {database} | Sleep |
1 | |
|
| 531 | {user} | localhost | {database} | Query |
1 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021001061844504' ORDER BY date desc |
| 534 | {user} | localhost | {database} | Query |
1 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021002055217828' ORDER BY date desc |
| 535 | {user} | localhost | {database} | Query |
0 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021001061844504' ORDER BY date desc |
| 538 | {user} | localhost | {database} | Query |
2 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021002055217828' ORDER BY date desc |
| 541 | {user} | localhost | {database} | Query |
1 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021001061844504' ORDER BY date desc |
| 542 | {user} | localhost | {database} | Query |
2 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021001061844504' ORDER BY date desc |
| 544 | {user} | localhost | {database} | Init DB |
1 | Writing to net |
|
| 553 | {user} | localhost | {database} | Query |
0 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021002062208512' ORDER BY date desc |
| 556 | {user} | localhost | {database} | Query |
1 | | SELECT imageurl FROM topics WHERE tid =
'apps' |
| 557 | {user} | localhost | {database} | Query |
0 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021002063014627' ORDER BY date desc |
| 561 | {user} | localhost | {database} | Query |
2 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021002062208512' ORDER BY date desc |
| 562 | {user} | localhost | {database} | Query |
0 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021002063853736' ORDER BY date desc |
| 563 | {user} | localhost | {database} | Query |
2 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021002063014627' ORDER BY date desc |
| 565 | {user} | localhost | {database} | Query |
2 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021002063853736' ORDER BY date desc |
| 566 | {user} | localhost | {database} | Init DB |
0 | |
|
| 567 | {user} | localhost | {database} | Sleep |
4 | |
|
| 575 | {user} | localhost | {database} | Query |
2 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021002063853736' ORDER BY date desc |
| 576 | {user} | localhost | {database} | Sleep |
4 | |
|
| 577 | {user} | localhost | {database} | Sleep |
4 | |
|
| 583 | {user} | localhost | {database} | Sleep |
3 | |
|
| 584 | {user} | localhost | {database} | Sleep |
3 | |
|
| 586 | {user} | localhost | {database} | Query |
4 | Copying to tmp table | SELECT distinct *, count(*) as dups,
comments.cid,comments.sid,stories.sid,stories.title,max(UNIX_TI |
| 587 | {user} | localhost | {database} | Query |
2 | Copying to tmp table | SELECT distinct *, count(*) as dups,
comments.cid,comments.sid,stories.sid,stories.title,max(UNIX_TI |
| 588 | {user} | localhost | {database} | Query |
2 | Sorting result | SELECT UNIX_TIMESTAMP(date) AS day FROM
comments WHERE sid = '20021002063853736' ORDER BY date desc |
| 590 | {user} | localhost | {database} | Query |
3 | Copying to tmp table | SELECT distinct *, count(*) as dups,
comments.cid,comments.sid,stories.sid,stories.title,max(UNIX_TI |
| 591 | {user} | localhost | {database} | Query |
3 | Copying to tmp table | SELECT distinct *, count(*) as dups,
comments.cid,comments.sid,stories.sid,stories.title,max(UNIX_TI |
| 592 | root | localhost | | Query | 0
| | show processlist
|
| 594 | {user} | localhost | {database} | Query |
0 | Sorting result | SELECT *,UNIX_TIMESTAMP(date) AS day
FROM stories where UNIX_TIMESTAMP(date) > 1033794000 ORDER BY U |
| 595 | {user} | localhost | | Connect |
| login |
|
| 598 | {user} | localhost | {database} | Sleep |
0 | |
|
| 599 | {user} | localhost | {database} | Sleep |
0 |
|
|
| 600 | {user} | localhost | | Connect |
| login |
|
| 601 | unauthenticated user | localhost | | Connect |
| login |
|
+-----+----------------------+-----------+-----------+---------+------+----------------------+------------------------------------------------------------------------------------------------------+
Here is my /etc/my.cnf too:
[mysqld]
port = 3306
socket = /var/lib/mysql/mysql.sock
skip-locking
set-variable = key_buffer=256M
set-variable = max_allowed_packet=1M
set-variable = table_cache=512
set-variable = sort_buffer=1M
set-variable = record_buffer=1M
set-variable = myisam_sort_buffer_size=64M
set-variable = thread_cache=64
set-variable = wait_timeout=30
set-variable = max_connections=350
set-variable = max_connect_errors=25
# Try number of CPU's*2 for thread_concurrency
set-variable = thread_concurrency=8
#log-bin
#server-id = 1
# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname
[mysqldump]
quick
set-variable = max_allowed_packet=16M
[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
[isamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[myisamchk]
set-variable = key_buffer=128M
set-variable = sort_buffer=128M
set-variable = read_buffer=2M
set-variable = write_buffer=2M
[mysqlhotcopy]
interactive-timeout
Thanks
James
>
>Bhavin.
>----- Original Message -----
>From: "James Riordon" <[EMAIL PROTECTED]>
>To: <[EMAIL PROTECTED]>
>Sent: Saturday, October 05, 2002 9:31 PM
>Subject: Server comparison running Mysql
>
>
>> Hi,
>>
>> I have two servers.
>>
>> Server A:
>> Dual 1GHz processors
>> 1GB of RAM
>> MySQL-Max 3.23.49a
>> PHP 4.2.1
>>
>> Server B:
>> Single 1.7GHz processors
>> 1.5GB of RAM
>> MySQL-Max 3.23.49a
>> PHP 4.2.2
>>
>> Server A has a site that uses about 75GB of bandwidth per
>> month all pulled through PHP and MySQL. Load average is 0.4 over
>> time. Processlist is never above 2-5 process showing at a time. All
>> in all a very nice server.
>>
>> When I switch the site to Server B - same database, same
>> program, same my.cnf, my load average on Server B jumps to 25-35, the
>> process list jumps to 30-50 processes, the server comes to a grinding
>> hault and then sits there, all within 30-60 seconds of redirecting
>> the site.
>>
>> I thought Server B would be able to happily handle the large
>> load but it apparently is not.
>>
>> I am trying to figure out it Server B just can't handle the
>> load, or if there is a problem somewhere. Can anyone give me their
>> experienced thoughts on which I might be experiencing? Any suggested
>> things I should try?
>>
>> I am kind of in a bind here as I wish to get rid of the Dual
>> processor machine for the colo it is in is quite expensive and the
>> bandwidth is quite low on a monthly basis. The new colo does not seem
>> to offer dual processor machines. Time is of the essence for me so
>> any thoughts or suggestions would be greatfully accepted.
>>
>> Thanks
>>
>> James
>>
>> ---------------------------------------------------------------------
>> Before posting, please check:
>> http://www.mysql.com/manual.php (the manual)
>> http://lists.mysql.com/ (the list archive)
>>
>> To request this thread, e-mail <[EMAIL PROTECTED]>
>> To unsubscribe, e-mail
><[EMAIL PROTECTED]>
>> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>>
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
