Welcome to Centmin Mod Community
Register Now

Sysadmin MYSQL slow on large data

Discussion in 'System Administration' started by SFLC, Mar 2, 2017.

  1. SFLC

    SFLC Active Member

    224
    59
    28
    Dec 4, 2016
    The Canadas
    Ratings:
    +112
    Local Time:
    8:55 PM
    1
    10
    Good day,

    So I don't believe there is an actual issue that's causing this, maybe it's just my servers' limited resources but I figure I'd ask those more sql inclined.

    One of my sites loads pages that contain data through sql with very large fields, they're mediumtext and these fields could have up to 1 million characters of data to load up. The site runs just fine and sql performs well but when it comes to loading pages that start to get 400k-ish chars from the table the page takes 10+ seconds, which would be fine but it takes so long on mobile phones that it causes my browser to become unresponsive (this could be an issue with my phone specifically), regardless it's quite slow.

    There's nothing in the sql slow query log and I'm not sure if what's happening with the slow speed is normal given the large amount of data, although the whole database is 2MB so I don't know why it's taking so long to load up.

    I'm gonna experiment with putting this db on a remote google cloud sql instance and see how it's going to perform.

    I personally don't think theres anything wrong with sql as my other sites are blazing fast, just seems to have issues with large fields. So weird.
     
  2. eva2000

    eva2000 Administrator Staff Member

    30,178
    6,785
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +10,138
    Local Time:
    4:55 AM
    Nginx 1.13.x
    MariaDB 5.5
    have you checked disk i/o performance ? if that is slow, underlying database access/writes will be slow too

    also optimise your queries and indexes is a must so profile and explain your mysql queries.
     
  3. SFLC

    SFLC Active Member

    224
    59
    28
    Dec 4, 2016
    The Canadas
    Ratings:
    +112
    Local Time:
    8:55 PM
    1
    10
    For IO I don't think it's an issue

    using dd:

    1073741824 bytes (1.1 GB) copied, 7.25835 s, 148 MB/s

    I'm not sure what you mean by optimizing the queries, it's just getting text from a field. I'll have to do some sql related research as my knowledge is limited on this subject.
     
  4. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    2:55 AM
    1.11
    10.2
    What's the complete command you use for this?
    I can say it's Slow.

    Using this command:
    Code:
    dd if=/dev/zero of=test bs=64k count=16k conv=fdatasync && rm -rf test
    On my server I got.
     
  5. SFLC

    SFLC Active Member

    224
    59
    28
    Dec 4, 2016
    The Canadas
    Ratings:
    +112
    Local Time:
    8:55 PM
    1
    10
    nice, i tried your command and got:

    Code:
    1073741824 bytes (1.1 GB) copied, 7.04119 s, 152 MB/s1073741824 bytes (1.1 GB) copied, 7.04119 s, 152 MB/s
    Do you have an ssd, cuz i dont on this server
     
  6. RoldanLT

    RoldanLT Well-Known Member

    3,901
    949
    113
    May 25, 2014
    Phillipines
    Ratings:
    +1,298
    Local Time:
    2:55 AM
    1.11
    10.2
    Yes I got SSD.
    SSD is a must for MYSQL server.
     
    • Agree Agree x 2