Thread: MySQL on METH!

    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    3

    MySQL on METH!


    Hey guys,

    I just got a new Ubuntu server up and running. I've got a ton of ram, processor, and an ultra fast SSD

    This server serves records to about 500 queries per minute. By shear load however, > 90% of the complex joins and resulting data is made to and from our LAN. The rest is high volume, but low complexity and data.

    Now, since this servers only purpose is to be a mySQL server, I want to maximize the resources available to the DB.

    I am new to DB admin and it seems that under heavy queries, the server is using a small fraction of it's potential memory and proc; as if MySQL is throttling it to protect resources and some of those complex queries lag just a bit.


    In laymen terms I want to tell MySQL:
    Hey it's okay to use 90% of the available processor and 90% of the available memory, that's what it is there for!

    I want it to absolutely pump the data out.

    Remember the processor/memory dependent queries only account for about 3 of the 500 queries per minute. The other 497 are very simple and very light.

    Any advice would be great.
  2. #2
  3. No Profile Picture
    Lost in code
    Devshed Supreme Being (6500+ posts)

    Join Date
    Dec 2004
    Posts
    8,316
    Rep Power
    7171
    Based on the scenario you give, it sounds like performance is I/O bound by the disk more than anything else, but there may be some configuration parameters you can adjust to gain a bit more performance. What are the exact CPU and RAM specs for the machine and how big is the database?

    (It may have an ultra fast SSD, but the RAM and FSB are still 2-3 orders of magnitude faster in terms of throughput.)
    PHP FAQ

    Originally Posted by Spad
    Ah USB, the only rectangular connector where you have to make 3 attempts before you get it the right way around
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    3

    Specs


    16GB Ram
    Dual Xenon 2.2G's
    DB is about 20MB's

    SSD drive:
    Code:
     /dev/sda:
    
    ATA device, with non-removable media
            Model Number:       INTEL SSDSC2CW240A3
            Serial Number:      CVCV251505UP240CGN
            Firmware Revision:  400i
            Transport:          Serial, ATA8-AST, SATA 1.0a, SATA II Extensions, SATA Rev 2.5, SATA Rev 2.6, SATA Rev 3.0
    Standards:
            Used: unknown (minor revision code 0x0110)
            Supported: 9 8 7 6 5
            Likely used: 9
    Configuration:
            Logical         max     current
            cylinders       16383   16383
            heads           16      16
            sectors/track   63      63
            --
            CHS current addressable sectors:   16514064
            LBA    user addressable sectors:  268435455
            LBA48  user addressable sectors:  468862128
            Logical  Sector size:                   512 bytes
            Physical Sector size:                   512 bytes
            Logical Sector-0 offset:                  0 bytes
            device size with M = 1024*1024:      228936 MBytes
            device size with M = 1000*1000:      240057 MBytes (240 GB)
            cache/buffer size  = unknown
            Nominal Media Rotation Rate: Solid State Device
    Capabilities:
            LBA, IORDY(can be disabled)
            Queue depth: 32
            Standby timer values: spec'd by Standard, no device specific minimum
            R/W multiple sector transfer: Max = 16  Current = 16
            Advanced power management level: 254
            DMA: mdma0 mdma1 mdma2 udma0 udma1 udma2 udma3 udma4 udma5 *udma6
                 Cycle time: min=120ns recommended=120ns
            PIO: pio0 pio1 pio2 pio3 pio4
                 Cycle time: no flow control=120ns  IORDY flow control=120ns
    Commands/features:
            Enabled Supported:
               *    SMART feature set
                    Security Mode feature set
               *    Power Management feature set
               *    Write cache
                    Look-ahead
               *    Host Protected Area feature set
               *    WRITE_BUFFER command
               *    READ_BUFFER command
               *    NOP cmd
               *    DOWNLOAD_MICROCODE
               *    Advanced Power Management feature set
                    Power-Up In Standby feature set
               *    SET_FEATURES required to spinup after power up
               *    48-bit Address feature set
               *    Mandatory FLUSH_CACHE
               *    FLUSH_CACHE_EXT
               *    SMART error logging
               *    SMART self-test
               *    General Purpose Logging feature set
               *    WRITE_{DMA|MULTIPLE}_FUA_EXT
               *    64-bit World wide name
               *    IDLE_IMMEDIATE with UNLOAD
               *    WRITE_UNCORRECTABLE_EXT command
               *    {READ,WRITE}_DMA_EXT_GPL commands
               *    Segmented DOWNLOAD_MICROCODE
               *    Gen1 signaling speed (1.5Gb/s)
               *    Gen2 signaling speed (3.0Gb/s)
               *    Gen3 signaling speed (6.0Gb/s)
               *    Native Command Queueing (NCQ)
               *    Host-initiated interface power management
               *    Phy event counters
               *    unknown 76[14]
               *    DMA Setup Auto-Activate optimization
                    Device-initiated interface power management
               *    Software settings preservation
               *    SMART Command Transport (SCT) feature set
               *    SCT Data Tables (AC5)
               *    Data Set Management TRIM supported (limit 1 block)
               *    Deterministic read data after TRIM
    Security:
            Master password revision code = 65534
                    supported
            not     enabled
            not     locked
                    frozen
            not     expired: security count
                    supported: enhanced erase
            4min for SECURITY ERASE UNIT. 2min for ENHANCED SECURITY ERASE UNIT.
    Logical Unit WWN Device Identifier: 5001517803d4dc13
            NAA             : 5
            IEEE OUI        : 001517
            Unique ID       : 803d4dc13
    Checksum: correct

IMN logo majestic logo threadwatch logo seochat tools logo