List:General Discussion« Previous MessageNext Message »
From:Greg Whalin Date:May 7 2005 1:16am
Subject:Re: MySQL not using optimum disk throughput.
View as plain text  
Kevin Burton wrote:
> Greg Whalin wrote:
> 
>> We have seen the exact same thing here.  We used the deadline 
>> scheduler and saw an immediate improvement.  However, we still saw 
>> much worse performance on our Opteron's (compared to our older Xeon 
>> boxes).  We ended up rolling back to Fedora Core 1 
>> 2.4.22-1.2199.nptlsmp kernel and shut down NPTL and now our Opteron's 
>> are much much faster than our Xeons.
>>
> Sweet... I'm going to take a look at that!
> 
> Two votes for the deadline scheduler.  Though I'm an NPTL fan but I'm 
> not sure our DB boxes need this as they don't use THAT many threads.

Deadline was much faster.  Using sysbench:

test:
sysbench --num-threads=16 --test=fileio --file-total-size=20G 
--file-test-mode=rndrw run

results:
2.6.10-1.14_FC2smp on dual Opteron 248s w/ 4GB RAM

default scheduler (anticaptory):
Operations performed: 6004 Read, 3996 Write, 12800 Other = 22800 Total 
Read 93.812Mb Written 62.438Mb Total transferred 156.25Mb (2.9186Mb/sec)

  186.79 Requests/sec executed

Test execution summary:

    total time:                          53.5363s
    total number of events:              10000
    total time taken by event execution: 376.0398
    per-request statistics:
         min:                            0.0000s
         avg:                            0.0376s
         max:                            18446744073709.4961s
         approx.  95 percentile:         0.1106s

Threads fairness:

    distribution:                        70.15/87.92
    execution:                           88.48/93.88


deadline scheduler:
Operations performed: 6006 Read, 3994 Write, 12800 Other = 22800 Total 
Read 93.844Mb Written 62.406Mb Total transferred 156.25Mb (4.4464Mb/sec)

  284.57 Requests/sec executed

Test execution summary:

    total time:                          35.1411s
    total number of events:              10000
    total time taken by event execution: 289.2953
    per-request statistics:
         min:                            0.0000s
         avg:                            0.0289s
         max:                            0.3520s
         approx.  95 percentile:         0.0870s

Threads fairness:

    distribution:                        84.92/92.89
    execution:                           90.52/96.58

The 2.4 scheduler showed similar results to deadline under 2.6.

>> The thing I find strange about this is that our experience (@ Meetup) 
>> seems to match that of Friendsters (I know of a few other high traffic 
>> sites that have mentioned similar issues), in that Mysql on Opteron 
>> and Linux 2.6 is not a good solution.  Yet, Mysql recommends exactly 
>> this config and in fact, does not seem to even support (via support 
>> contract) a 2.4 solution for Opteron + Mysql.
>>
> Wow... whats the consensus on Opteron here then?  It seems to be a clear 
> winner since you can give the mysql process more memory for caching.
> 
> Is it an OS issue since few of the distributions seem to support Opteron 
> (well).


I suspect this is an OS issue.  Our Opteron's were completing large data 
update queries aprox 2-3 times slower than our Xeons when running under 
2.6.  After a switch to 2.4, Opteron's are faster than the Xeons.  I 
mentioned NPTL being shut off (LD_ASSUME_KERNEL=2.4.19 in init script). 
  When we left NPTL running, we saw almost instant deadlocks just 
watching replication catching up (no other site traffic directed to the 
machine).  This is in 2.4 btw, so this is the backported NPTL kernels 
from Fedora.  I somewhat suspect NPTL being a problem in 2.6 as well due 
to impressions I get from sifting through mysql's bug tracking system. 
The IO scheduler was also an obvious culprit.

Other issues I have noticed w/ Opteron ver of mysql ...

- Under 2.6, if we took the db offline and ran myisamchk on a table w/ 
fulltext indexes, and then started back up again, the table would nearly 
instantly crash (upon first writes to it).  Running repair table would 
seg fault.  Shutting down to run myisamchk would only cause the table to 
crash again upon 1st write.  Only solution ... alter table tablename 
engine=myisam;  Then the table would run fine.  We have since dropped 
all fulltext indexes and moved to Lucene (much more flexible and waaaaay 
faster anyhow).

- Under 2.4 (just happened to me tonight and this is a scary one), we 
routinely archive and cleanup large tables w/ seldom used old data. 
After doing a "DELETE FROM table WHERE ctime < '2005-05-01'", we would 
see a select count(*) show around 160k rows remaining (from 1st of the 
month).  I would call repair table on the table, and the remaining rows 
would be deleted.  Repair would make mention of dropping row count from 
165k to 0.  Yikes!  This happened on both Opterons and did not happen on 
the Xeons (thank god ... was able to save the data).

In any rate, I am 100% confidant in saying that Mysql (w/ myisam table 
engine ... not tried innodb yet) on linux on Opterons is not yet stable 
or speedy.  Though we usually only see problems under large data 
cleanups (moving, deleting, repairing, etc).

Greg

Thread
MySQL not using optimum disk throughput.Kevin Burton6 May
  • Re: MySQL not using optimum disk throughput.Dan Nelson7 May
RE: MySQL not using optimum disk throughput.Dathan Pattishall7 May
  • Re: MySQL not using optimum disk throughput.Greg Whalin7 May
    • Re: MySQL not using optimum disk throughput.Kevin Burton7 May
      • Re: MySQL not using optimum disk throughput.Greg Whalin7 May
        • Re: MySQL not using optimum disk throughput.Kevin Burton7 May
          • Re: MySQL not using optimum disk throughput.Kevin Burton7 May
            • Re: MySQL not using optimum disk throughput.Greg Whalin7 May
              • Re: MySQL not using optimum disk throughput.Kevin Burton7 May
                • Re: MySQL not using optimum disk throughput.Atle Veka7 May
                  • Re: MySQL not using optimum disk throughput.Kevin Burton7 May
                    • Re: MySQL not using optimum disk throughput.Atle Veka7 May
                  • Re: MySQL not using optimum disk throughput.Dan Nelson8 May
              • Re: MySQL not using optimum disk throughput.Peter Zaitsev7 May
                • Re: MySQL not using optimum disk throughput.Greg Whalin7 May
                  • Re: MySQL not using optimum disk throughput.Peter Zaitsev1 Jun
                    • RE: MySQL not using optimum disk throughput.Richard Dale1 Jun
        • Re: MySQL not using optimum disk throughput.Kevin Burton9 May
          • Re: MySQL not using optimum disk throughput.Greg Whalin9 May
            • Re: MySQL not using optimum disk throughput.Kevin Burton9 May
              • Re: MySQL not using optimum disk throughput.Greg Whalin9 May
                • Re: MySQL not using optimum disk throughput.Kevin Burton9 May
Re: MySQL not using optimum disk throughput.John David Duncan7 May
  • Re: MySQL not using optimum disk throughput.Peter Zaitsev7 May