GIT – Often the server admin has little control over the applications which uses and it is hard to find the bottlenecks. This blog post can’t bring the peace in the world, or help NASA to finally land on the Mars.

Instead those tasks, I’ll try to solve something else and present my own experiences with MySQL storage engines (at least for MyISAM and InnoDB as the most popular).

1. High performance tuning scripts

Keep in mind that I’m not an expert in this field. When I have problems with MySQL, I dig with my both hands (and head) to solve them without casualties. Every app has its own queries and you’ll need to track them down via slow query log.

Before you do anything, keep in mind that default MySQL installation usually works fine. Unfortunately maximum performances can’t be achieved without adjustments to increase performance and stability.

Before you can do anything, you need to enable slow query log.

Exec

# touch /var/log/slow-query.log

to create slow-query.log file. Then add

log-slow-queries = /var/log/slow-query.log
long_query_time = 4
log-queries-not-using-indexes

(for MySQL 5.0.x)

to /etc/my.cnf (inside [mysqld] section) and restart mysql. Wait at least 24-48 hours before you proceed to the next step.

(For MySQL 5.1.x check here: LINK)

High performance tuning scripts

Mysqltuner

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

# wget mysqltuner.pl
# chmod +x mysqltuner.pl
# ./mysqltuner.pl

Read output and try to follow the recommendations.

Tuning-primer

Tuning-primer is another script who takes information from “SHOW STATUS LIKE…” and “SHOW VARIABLES LIKE…” to produce readable recommendations for tuning server variables.

# wget http://www.day32.com/MySQL/tuning-primer.sh
# ./tuning-primer.sh

Also, read output and try to see what you can do to fix problems. Keep in mind that you need to restart mysql after you add something to /etc/my.cnf. In case something goes wrong, check mysql log (usually /var/log/mysqld.log

MyTOP

is a console-based (non-gui) tool for monitoring the threads and overall performance of a MySQL

To mytop,

# wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
# tar -xvzf mytop-1.6.tar.gz
# cd mytop-1.6
# perl Makefile.PL
# make
# make install

In case some Perl libraries are missing, you should install them via cpan.

For example, Term::ReadKey is required and you can get it via cpan.

# cpan
(pres Enter several times until you get cpan shell)
cpan> install Term::ReadKey
cpan> quit

Usage: # mytop -d DATABASE -u USERNAME -p

(replace the DATABASE, USERNAME and PASSWORD with your parameters.

MySQLReport

mysqlreport makes a friendly report of important MySQL status values. mysqlreport transforms the values from SHOW STATUS into an easy-to-read report that provides an in-depth understanding of how well MySQL is running. mysqlreport is a better alternative (and practically the only alternative) to manually interpreting SHOW STATUS.

# wget http://hackmysql.com/mysqlreport
# chmod +x mysqlreport
# ./mysqlreport --user  --password

Enter password for root and check output. More info can be found here. LINK

2. Write dependance

Now lets do some tests and lets see the dependance from write cache enabled/disabled option on your disks. Keep in mind that RH based distros will probably write cache (I tried on this server and the same thing happen – write cache was disabled after installation).

The first step is to check the disk with

[root@s1 sysbench-0.4.12]# hdparm -i /dev/sda

/dev/sda:

 Model=GB0250EAFYK                             , FwRev=HPG1    , SerialNo=WCAT1E535427
 Config={ HardSect NotMFM HdSw>15uSec SpinMotCtl Fixed DTR>5Mbs FmtGapReq }
 RawCHS=16383/16/63, TrkSize=0, SectSize=0, ECCbytes=50
 BuffType=unknown, BuffSize=16384kB, MaxMultSect=16, MultSect=?16?
 CurCHS=16383/16/63, CurSects=16514064, LBA=yes, LBAsects=268435455
 IORDY=on/off, tPIO={min:120,w/IORDY:120}, tDMA={min:120,rec:120}
 PIO modes:  pio0 pio3 pio4
 DMA modes:  mdma0 mdma1 mdma2
 UDMA modes: udma0 udma1 udma2
 AdvancedPM=no WriteCache=disabled
 Drive conforms to: unknown:  ATA/ATAPI-1 ATA/ATAPI-2 ATA/ATAPI-3 ATA/ATAPI-4 ATA/ATAPI-5 ATA/ATAPI-6 ATA/ATAPI-7

Note WriteCache=disabled line

I suppose you know how to create a database, assign a user and set password

Read-write test for InnoDB

# sysbench --num-threads=16 --max-requests=10000 --test=oltp --oltp-table-size=500000 --mysql-socket=/var/lib/mysql/mysql.sock --oltp-test-mode=complex --mysql-user=test_database --mysql-password=test_database_password run

gave me the next results

 
No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (42.63 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (809.95 per sec.)
    other operations:                    20000  (85.26 per sec.)

Test execution summary:
    total time:                          234.5821s
    total number of events:              10000
    total time taken by event execution: 3751.1329
    per-request statistics:
         min:                                 17.33ms
         avg:                                375.11ms
         max:                               3850.27ms
         approx.  95 percentile:             766.79ms

Threads fairness:
    events (avg/stddev):           625.0000/1.73
    execution time (avg/stddev):   234.4458/0.08

The total time is more than 234 seconds which is terrible result.

Now, delete sbtest table and recreate it again (please see the sysbench howto).

Enable write cache with

# hdparm -W1 /dev/sda

and start test again

The result is

No DB drivers specified, using mysql
Running the test with following options:
Number of threads: 16

Doing OLTP test.
Running mixed OLTP test
Using Special distribution (12 iterations,  1 pct of values are returned in 75 pct cases)
Using "BEGIN" for starting transactions
Using auto_inc on the id column
Maximum number of requests for OLTP test is limited to 10000
Threads started!
Done.

OLTP test statistics:
    queries performed:
        read:                            140000
        write:                           50000
        other:                           20000
        total:                           210000
    transactions:                        10000  (621.08 per sec.)
    deadlocks:                           0      (0.00 per sec.)
    read/write requests:                 190000 (11800.43 per sec.)
    other operations:                    20000  (1242.15 per sec.)

Test execution summary:
    total time:                          16.1011s
    total number of events:              10000
    total time taken by event execution: 255.8624
    per-request statistics:
         min:                                  2.49ms
         avg:                                 25.59ms
         max:                                689.84ms
         approx.  95 percentile:              41.84ms

Threads fairness:
    events (avg/stddev):           625.0000/4.68
    execution time (avg/stddev):   15.9914/0.03

Now we have a 16.1011 seconds for a complete test. I repeated the test several time with the same results.

We have a 14.62 times better result.

It is obvious that write cache is very important for MySQL but keep in mind that something can be lost in case of power failure and you should think about battery backups.

Tuning Kernel parameters

It is good idea to tune a few kernel parameters too. For this purpose you can install ktune (yum install ktune). (more info https://fedorahosted.org/ktune/)

With “service ktune start” you will set up scheduler on deadline instead of cfq (which can be up to 20% slower)

# service ktune start
Applying ktune  settings:
/etc/.ktune:                                         [  OK  ]
Applying  settings from /etc/.conf:            [  OK  ]
Applying deadline elevator: sda                            [  OK  ]

After Ktune I have a little faster time (around 15 seconds)

More info about disk elevators can be found here http://www.redhat.com/magazine/008jun05/features/schedulers/

Optimizing the EXT3 file system on CentOS

noatime
This mount option tells the system not to update inode access times. This is a good option for web servers, news servers or other uses with high access file systems.

Open /etc/fstab and add noatime like shown below

/dev/VolGroup00/LogVol00 /                       ext3    defaults,noatime        1 1
Print Friendly

Comments

comments

Bài viết liên quan