MySQL Forums
Forum List  »  Performance

After switching to 4 CPUs server selects work 8x slower
Posted by: Przemyslaw Dargiel
Date: April 04, 2005 06:04AM

I've swiched from a 2xPII 800 with 1Gb RAM and WinNT 4.0 to a new
SunFire3800 with 4xUltraSpacIII 750MHz and 4GB Ram with Solaris 8.
After that a exemplary selecty wich executed it self for 5 sec. now
executes for 40 sec.
WinNT is a 24/7 server and SHOW PROCESSLIST shows at most 10
processes at a time. At this time the processors usage is at 100% for
each. The new machine is currently in test phase. Although there is
one application working on it but it can be ignored. SHOW PROCESSLIST
sometimes shows a single query. The same query on the same table
structures and with the same portion of data executes 8x slower on
Solaris 8 and the processor usage is at max 20%. I've tryed
changeing the settings in my.cnf according to
http://dev.mysql.com/doc/mysql/en/server-parameters.html but the
effects ware minimal - from 40 to 39 sec. at time to time. Finaly I've
used my-large.cnf from the distro catalog. I've checked the indexes in
the tables, they are the same. I did an analysis of the table and
found no changes. I've checked the local and remote querys, no
changes.
I've tried useing HIGH_PRIRITY select with no effect. What's strange
is that on solaris the processor usage is at max 20-25% and IDLE rest
of the time.

What wrong is with my configuration?
Thanks in advance.

Details:
Server 1:
2xPentium III 800Mhz
1Gb Ram
WinNT4.0
Mysql 4.0.15-nt (MYISAM Engine)
Apache with PHP and a oracle client installed

Server 2: SunFire 3800
4x UltraSparcIII 750Mhz
4Gb Ram
SunOS 5.8
Mysql 4.0.21-max-log (MYISAM Engine)
Apache with PHP and a oracle client installed

my.cnf from SunFire3800
[client]
port = 3306
socket = /tmp/mysql.sock
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
key_buffer = 256M
max_allowed_packet = 1M
table_cache = 256
sort_buffer_size = 1M
read_buffer_size = 1M
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size= 16M
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
default-character-set=latin2
log-bin
server-id = 1
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[isamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 128M
sort_buffer_size = 128M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout


The select I've used:(this select returns single records, meanly 3-5)

select o.id osoba_id, o.imie, o.nazwisko, gt.symbol gt_symbol, o.tel_s, o.kom_s,
count(distinct gzd2.id) liczba_zadan
from gt_zadanie gzd, gt_osoby_gt gog, gts_gt ggt,
osoby o, gt_user_terminarz gut, gts_cykl_pracy gcp, gts_gt gt
left join gts_kompetencja as gtsk on(gzd.rodzaj_sprzetu=gtsk.rodzaj_sprzetu and gzd.produkt=gtsk.produkt and gzd.typ=gtsk.typ_sprzetu and gzd.rodzaj_zlecenia=gtsk.rodzaj_zlecenia)
join gt_user_komp as guk on(gtsk.id=guk.komp and guk.user=gut.user)
left join miejscowosc as ma on(gzd.id_miejscowoscA=ma.id_miejscowosc and gzd.obszar=ma.id_obszar)
left join gmina as ga on(ma.id_gmina=ga.id_gmina and ma.id_obszar=ga.id_obszar)
join gt_user_powiat as gup on(gog.user=gup.user and gup.id_powiat=ga.id_powiat)
left join gt_przydzial as gprz2 on(gprz2.osoba=o.id)
left join gt_zadanie as gzd2 on(gprz2.zadanie=gzd2.id and gzd2.status=3)
where ((unix_timestamp(gut.data)+time_to_sec(gcp.start)<=unix_timestamp(gzd.termin_od)
and unix_timestamp(gut.data)+time_to_sec(gcp.start)+gcp.godziny*3600>=unix_timestamp(gzd.termin_od))
or
(unix_timestamp(gut.data)+time_to_sec(gcp.start)<=unix_timestamp(gzd.termin_do)
and unix_timestamp(gut.data)+time_to_sec(gcp.start)+gcp.godziny*3600>=unix_timestamp(gzd.termin_do))
or
(unix_timestamp(gut.data)+time_to_sec(gcp.start)>=unix_timestamp(gzd.termin_od)
and unix_timestamp(gut.data)+time_to_sec(gcp.start)+gcp.godziny*3600<=unix_timestamp(gzd.termin_do))
)
and gut.user=o.id
and gut.cykl=gcp.id
and ((gzd.typ_zlecenia=ggt.typ_zlecenia and gzd.typ_zlecenia!=4) or (gzd.typ_zlecenia=4))
and ggt.id=gog.gt
and gog.user=o.id
and gog.gt=gt.id
and gt.id_obszar=gzd.obszar
and gzd.id=11991
and gcp.dyzur=''
and o.id not in (2182,2184)
group by o.id
order by o.nazwisko


its explain is the same on both servers:

+-------+--------+----------------------------------------------------+---------
-------+---------+-----------------------+------+-------------------------------
--+
| table | type | possible_keys | key
| key_len | ref | rows | Extra
|
+-------+--------+----------------------------------------------------+---------
-------+---------+-----------------------+------+-------------------------------
--+
| gzd | const | PRIMARY,id,obszar,typ_zlecenia | PRIMARY
| 4 | const | 1 | Using temporary; Using filesor
t |
| gt | ref | PRIMARY,id_obszar | id_obsza
r | 5 | const | 12 | Using where
|
| gtsk | ref | rodzaj_sprzetu,typ_sprzetu,rodzaj_zlecenia,produkt | produkt
| 5 | const | 4 |
|
| guk | ref | user,komp | komp
| 5 | gtsk.id | 8 | Using where
|
| gut | ref | user,cykl | user
| 5 | guk.user | 106 | Using where
|
| gcp | eq_ref | PRIMARY,dyzur | PRIMARY
| 4 | gut.cykl | 1 | Using where
|
| o | eq_ref | PRIMARY,id | PRIMARY
| 4 | gut.user | 1 | Using where
|
| gog | ref | user,gt | user
| 5 | o.id | 1 | Using where
|
| ggt | eq_ref | PRIMARY,typ_zlecenia | PRIMARY
| 4 | gog.gt | 1 | Using where
|
| ma | ref | ID_OBSZAR,ID_MIEJSCOWOSC | ID_MIEJS
COWOSC | 5 | const | 2 |
|
| ga | ref | ID_GMINA | ID_GMINA
| 5 | ma.ID_GMINA | 2 |
|
| gup | ref | user_powiat,user,id_powiat | user_pow
iat | 10 | gog.user,ga.ID_POWIAT | 1 | Using where; Using index
|
| gprz2 | ref | osoba | osoba
| 5 | o.id | 112 |
|
| gzd2 | eq_ref | PRIMARY,id,status | PRIMARY
| 4 | gprz2.zadanie | 1 | Using where
|
+-------+--------+----------------------------------------------------+---------
-------+---------+-----------------------+------+-------------------------------
--+
14 rows in set (0.10 sec)

Options: ReplyQuote


Subject
Views
Written By
Posted
After switching to 4 CPUs server selects work 8x slower
2539
April 04, 2005 06:04AM


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.