Professional Web Applications Themes

MySQL + LARGE innodb = thrashing HDD - MySQL

I have the following scenario. A mysql database running 3 databases. It is version 5.0.27 on Windows XP Prof.. All innodb databases. The one database is particularly large (7.8GB of data)...pretty much held in 1 table....there are probabably 30 tables in the rest of the databases....combined they probably take up 200MB. The machine is pretty well spec'ed AMD X2 4600+, 2GB RAM, SATA RAID1. Normally the services that use the databases are idle until our clients come online. Then it gets moderately busy. At the start of this a single (possibly 2) query will "hang"....it will take 8-10 minutes to ...

  1. #1

    Default MySQL + LARGE innodb = thrashing HDD

    I have the following scenario.

    A mysql database running 3 databases. It is version 5.0.27 on Windows
    XP Prof.. All innodb databases. The one database is particularly large
    (7.8GB of data)...pretty much held in 1 table....there are probabably
    30 tables in the rest of the databases....combined they probably take
    up 200MB. The machine is pretty well spec'ed AMD X2 4600+, 2GB RAM,
    SATA RAID1. Normally the services that use the databases are idle until
    our clients come online. Then it gets moderately busy. At the start of
    this a single (possibly 2) query will "hang"....it will take 8-10
    minutes to complete....it is always a insert or update. During this
    time MySQL will write to the error log saying innodb semaphores are
    timing out (but that makes sense). After this time the query completes
    and the system runs normally.....running the same queries that it got
    "stuck" on for a while, but this time taking microseconds to complete.

    I've done some profiling using perfmon. During this "hang" the IO
    writes byte for the mysql process goes from about <1MB per second to >
    12MB per second. I then further ran some dianostics to see where it was
    writing data using filemon...and it seems to be writing a ton to
    c:\$logfile, which I understand is part of the NTFS transactional
    system. I thought it might be the swap file, but I disabled this (set
    it to 0MB) and the problem persists.

    I have these servers set as masters for tion, so it is using
    binary logging.

    Can anyone think why this might happen?

    Regards

    D.

    daniel@mfaconsulting.com Guest

  2. #2

    Default Re: MySQL + LARGE innodb = thrashing HDD

    com wrote: 

    You said you are running XP Professional on AMDx2 4600+. Is this classed as a
    server or desktop. Is it XP Professional Desktop or Server. There is a big
    difference in both cost and performance. If you want to us MS crap, us Sql
    Server on Windows/<NT/XP/flavor of the month>. If you need a database server to
    run MySQL - use Linux as the backend.

    But a stab in the dark as to your problem, it sounds like your tion piece
    needs to write a whole bunch of logs at the point in time when you start.

    And lastly 7.8GB for MySQL or any database is a drop in the ocean - so, in order
    to keep from embarassing your self with such comments - "particularly large"
    begins at around 500-600GB. Moderately large is ~1-2TB, REALLY large is on the
    order of 60-100TB and insanely large is >1PB. :) :) :)


    --
    Michael Austin.
    Database Consultant
    Domain Name Registration and
    Web Hosting available at http://www.spacelots.com
    Michael Guest

  3. #3

    Default Re: MySQL + LARGE innodb = thrashing HDD

    Michael Austin wrote: 
    >
    > You said you are running XP Professional on AMDx2 4600+. Is this classed
    > as a server or desktop. Is it XP Professional Desktop or Server. There
    > is a big difference in both cost and performance. If you want to us MS
    > crap, us Sql Server on Windows/<NT/XP/flavor of the month>. If you need
    > a database server to run MySQL - use Linux as the backend.
    >[/ref]

    MySQL runs quite well in Windows platforms - although I agree it runs
    better on Linux.
     

    A good possibility. My recommendation would be to issue a couple of
    "dummy" requests when the system starts up as a test. Then watch the
    log files to see what changes. Any significant here could account for it.
     
    >
    >[/ref]

    Yep. 8Gb is not a big database.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. #39509 [NEW]: mySQL without InnoDB
    By tinu at humbapa dot ch in forum PHP Bugs
    Replies: 2
    Last Post: November 15th, 04:30 PM
  2. Question on MySQL 5 and innodb
    By JoeT in forum MySQL
    Replies: 0
    Last Post: May 9th, 11:17 PM
  3. CFMX7 - MySQL 4.0 - INNODB table type
    By drmaves in forum Coldfusion Database Access
    Replies: 2
    Last Post: September 23rd, 04:26 PM
  4. php.ini causes disk thrashing in mysql
    By Marin in forum PHP Development
    Replies: 0
    Last Post: June 27th, 03:48 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139