Percona Server 5.6 write performance slow GTID on

122 views
Skip to first unread message

Zak

unread,
Jul 8, 2014, 9:51:10 AM7/8/14
to percona-d...@googlegroups.com
We were running our application of a windows mysql 5.6 server with standard default settings.  
 The application is performing as expected with circular replication on.

On the exact same machine (spec wise) I am running Vmware sphere 5.5 and loaded up centos6.5 with percona server
with 3 cores and 8 gig of ram.

The application worked well on multiple clients on our network however when we circular enabled replication using GTID to our branch
we noticed the application lagging. After persona restart and turning off the replication everything was fine.  
When we turn on the replication, the app slows down, however I can reproduce the same outcome  (this could be a coincidence ).     

After talking to the developers, we put in database execution times to see were the problem could be.
On the most part all reads are fine, the writes are the problem. It is taking several seconds to write to the db.

We do random testing, at times everything is working full speed, but most of the time there is a lag
due to the writes from the application logs

We do not have this problem at all on a windows mysql 5.6 server, however we are not using GTID on that.

Any help to solve this problem will be appreciated
thanks


now for stats:

TOP shows percona only using 0.3% CPU and 18% of memory
free shows 

free shows
             total       used       free     shared    buffers     cached
Mem:       8061500    2127884    5933616          0     117600     559844
-/+ buffers/cache:    1450440    6611060
Swap:      2097144          0    2097144

Using MyTop
  
MySQL on localhost (5.6.17-65.0-56-log)
Queries: 16.6k  qps:    0 Slow:    13.0         Se/In/Up/De(%):    44/03/03
             qps now:    5 Slow qps: 0.0  Threads:    3 (   2/   0) 41/04/00
Cache Hits: 3.8k  Hits/s:  0.0 Hits now:   1.0  Ratio: 52.3% Ratio now: 45.
Key Efficiency: 96.9%  Bps in/out:  31.0/ 1.3k   Now in/out:  1.8k/11.3k

      Id      User         Host/IP         DB      Time    Cmd Query or Stat
       --      ----         -------         --      ----    --- ----------
     7002     admin       localhost pos_001_00         0  Query show full pr
     7143 unauthent connecting host                    0 Connec login
     2611      root        10.2.0.2                 5650 Binlog Master has s
        1 system us                               261674 Connec Waiting for



SHOW INNODB STATUS

| InnoDB |      |
=====================================
2014-07-08 14:17:41 7fdc583d1700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 41 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 2294 srv_active, 0 srv_shutdown, 260644 srv_idle
srv_master_thread log flush and writes: 262938
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 656
OS WAIT ARRAY INFO: signal count 643
Mutex spin waits 8188, rounds 68767, OS waits 298
RW-shared spins 295, rounds 8739, OS waits 287
RW-excl spins 0, rounds 121, OS waits 4
Spin rounds per wait: 8.40 mutex, 29.62 RW-shared, 121.00 RW-excl
------------
TRANSACTIONS
------------
Trx id counter 7736510
Purge done for trx's n:o < 7736485 undo n:o < 0 state: running but idle
History list length 2519
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 7281, OS thread handle 0x7fdc583d1700, query id 17762 localhost admin init
SHOW ENGINE INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
 ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
22820 OS file reads, 9274 OS file writes, 2579 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 9, seg size 11, 34 merges
merged operations:
 insert 34, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 16164704746
Log flushed up to   16164704746
Pages flushed up to 16164704746
Last checkpoint at  16164704746
Max checkpoint age    2172672369
Checkpoint age target 2104776358
Modified age          0
Checkpoint age        0
0 pending log writes, 0 pending chkp writes
1259 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 6000001024; in additional pool allocated 0
Total memory allocated by read views 200
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 140107312       (92818888 + 47288424)
    Page hash           5802184 (buffer pool 0 only)
    Dictionary cache    25115007        (23206256 + 1908751)
    File system         919432  (812272 + 107160)
    Lock system         14504032        (14503672 + 360)
    Recovery system     0       (0 + 0)
Dictionary memory allocated 1908751
Buffer pool size        357823
Buffer pool size, bytes 5862572032
Free buffers            332548
Database pages          22389
Old database pages      8284
Modified db pages       0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 1, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 22373, created 16, written 7745
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 22389, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
0 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
0 out of 1000 descriptors used
Main thread process no. 1997, id 140577082447616, state: sleeping
Number of rows inserted 493, updated 336, deleted 79, read 139994198
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
------------
TRANSACTIONS
------------
Trx id counter 7736510
Purge done for trx's n:o < 7736485 undo n:o < 0 state: running but idle
History list length 2519
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0, not started
MySQL thread id 7281, OS thread handle 0x7fdc583d1700, query id 17762 localhost admin init
SHOW ENGINE INNODB STATUS
----------------------------
END OF INNODB MONITOR OUTPUT
============================






percona my.cnf file


[MYSQLD]
#sql-mode = ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
sql-mode = STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
user=mysql
basedir=/usr/
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
pid-file=mysqld.pid
port=3306
log-error=/var/log/mysqld.log
log-warnings=2
#log-output=FILE
symbolic-links=1
lower-case-table-names=1


## THREAD POOLS OPTIONS
thread_handling=pool-of-threads
thread_pool_size=2


### INNODB OPTIONS SETTINGS FOR 8GIG RAM and 2 CORE CPU  #####
innodb-buffer-pool-size=5591M
innodb-flush-log-at-trx-commit=1
innodb-file-per-table=1
innodb_data_file_path = ibdata1:10M:autoextend
innodb-log-file-size=1280M

## You may want to tune the below depending on number of cores and disk sub
innodb-read-io-threads=4
innodb-write-io-threads=4
innodb-doublewrite=1
innodb-log-buffer-size=8M
innodb-flush-method = O_DIRECT
innodb-buffer-pool-instances=1    # default 8
innodb-log-files-in-group=2
innodb-thread-concurrency=0      #docs say leave it at 0, windows machine got it at 16
innodb-file-format=barracuda
#### Above settings verified ###

### STILL TO VERIFY ########
innodb-locks-unsafe-for-binlog=1
innodb-autoinc-lock-mode=2       # using default 1
## avoid statistics update when doing e.g show tables
#innodb-stats-on-metadata=0
default-storage-engine=innodb

# REPLICATION SPECIFIC - GENERAL
#server-id must be unique across all mysql servers participating in replication.
server-id=1

# REPLICATION SPECIFIC
#binlog_format=ROW
#log-bin=binlog
#relay-log=relay-bin
##expire_logs_days=7
#log-slave-updates=1
#gtid_mode=ON
#enforce-gtid-consistency=1
#master-info-repository=TABLE
#relay-log-info-repository=TABLE

auto_increment_increment=10
auto_increment_offset=1

# OTHER THINGS, BUFFERS ETC
max-connections=800

thread_cache_size=10
key_buffer_size = 64M
table_open_cache=500
sort_buffer_size=4M
read_buffer_size=1M
read-rnd-buffer-size = 4M
max-allowed-packet = 512M
table-open_cache=1024
open_files_limit=3072                 # open file limit must be 2X or 3X table open cache

query-cache-type = 1
query-cache-size = 1M           #from docs
#query-cache-size = 350M         #from win ini

tmp_table_size = 125M
max_heap_table_size = 64M
##myisam-sort-buffer_size = 8M
#skip-name-resolve
#memlock=0
#sysdate-is-now=1
## 5.6 backwards compatibility
#explicit_defaults_for_timestamp=1

[client]
user=admin
password="pass"

[mysqldump]
max-allowed-packet = 512M
#default-character-set=utf8

[MYSQLD_SAFE]
pid-file=mysqld.pid
log-error=/var/log/mysqld.log
basedir=/usr/
datadir=/var/lib/mysql





                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    
Reply all
Reply to author
Forward
0 new messages