MySQL on METH!

Dev Shed Forums Sponsor:

February 2nd, 2013, 10:10 PM

karnival800

Contributing User

Join Date: Oct 2012

Posts: 35

Time spent in forums: 4 h 15 m 8 sec
Reputation Power: 1

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.

February 3rd, 2013, 02:35 PM

E-Oreo

Lost in code

Dev Shed God 7th Plane (8000 - 8499 posts)

Join Date: Dec 2004

Posts: 8,052

Folding Points: 945 Folding Title: Novice Folder

Time spent in forums: 2 Months 1 Day 5 h 6 m 34 sec
Reputation Power: 7104

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
How to program a basic, secure login system using PHP
Connect with me on LinkedIn

Quote:

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

February 3rd, 2013, 02:47 PM

karnival800

Contributing User

Join Date: Oct 2012

Posts: 35

Time spent in forums: 4 h 15 m 8 sec
Reputation Power: 1

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

Developer Shed Advertisers and Affiliates