no that i've created the indexes , the things are going a bit better but it's still not so fast : here's some output from mytop :
------------------------------------- Queries Total: 442,666 Avg/Sec: 7.74 Now/Sec: 16.27 Slow:36,449 Threads Total: 212 Active: 65 Cached: 0 Key Efficiency: 99.99% Bytes in: 53,284,729 Bytes out: 178,102,128 -------------------------------------
after this, mytop lists 65 requests , from which 30 are here for more than 20 seconds !!
the longer one is at 64 seconds.
you've guessed it : it's hard to stand.
the load of the server (dual pIV , with 1Go ram and scsi disks (no raid) is now 20,23,30.
my.cnf includes this :
------------------------------------- set-variable = thread_stack=128K set-variable = key_buffer=256M set-variable = max_allowed_packet=1M set-variable = table_cache=128 set-variable = sort_buffer=512K set-variable = net_buffer_length=8K set-variable = myisam_sort_buffer_size=8M set-variable = max_connections=500 -------------------------------------
i take the risk to say that it's not a disk pb , iostat looks good , and my key efficiency is near 100% wich AFAIK means that my keys are read from memory , and not from disk.
mysqldumpslow gave me the top 3 hard queries , and all those queries are using the indexes created yesterday (i analyzed and optimized the tables , and "explained" the selects) :
-------------------------------------
Count: 596 Time=28.77s (17145s) Lock=13.57s (8085s) Rows=8.3 (4959), [EMAIL PROTECTED]
SELECT more, pays, date, nbvote, id, sexe, login, age, poid, taille, region, v_ann FROM v2easy1_users WHERE nblogin>'S' AND moderation='S' AND sexe='S' AND age>='S' AND age<='S' AND pays='S' AND region='S' AND pix='S' ORDER BY lastlogin DESC LIMIT N,N
Count: 592 Time=28.98s (17155s) Lock=13.32s (7885s) Rows=9.3 (5534), [EMAIL PROTECTED]
SELECT more, pays, date, nbvote, id, sexe, login, age, poid, taille, region, v_ann FROM v2easy1_users WHERE nblogin>'S' AND moderation='S' AND sexe='S' AND age>='S' AND age<='S' AND pays='S' AND region='S' ORDER BY lastlogin DESC LIMIT N,N
Count: 566 Time=32.72s (18521s) Lock=12.50s (7073s) Rows=10.0 (5660), [EMAIL PROTECTED]
SELECT id, login, nbvoteweek, age, region, sexe FROM v2easy0_users WHERE nblogin>'S' AND sexe='S' AND pix='S' AND moderation='S' ORDER by nbvoteweek DESC, date LIMIT N, N
-------------------------------------
i'm almost ready to assume that whatever i could do in optimization , i'll never be able to drastically reduce the amount of slow query , and that my only ways now are php-caching , or a new server =(
if someone have got a clue on something i could try to improve this , i'd be grateful !!
bye.
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
