PHPBLOG: seconds behinds Master increase

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Mar 2, 2015 4:19 PM (12 mins ago)

Upload to TDS successful for the file aprochMaster-slave.docx.

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Mar 2, 2015 4:17 PM (14 mins ago)

Hi Anand ,

Sorry for delay. yes in our server 15K user concurrency because we are using Live Tv App (nexg) you can search it Google play store
NexGTv is very famous app in which live event news channel telecast like cricket match ,latest news so generally concurrency level is high on MySql server.

so can u suggest best approach for server configuration?

i am also sending a doc please find in attachment i want know that master slave configuration.

Notes

Oracle Support

- Feb 27, 2015 10:52 AM (Friday)

Hello Manoj -

Please find my answers inline.

>> Thanks for reply. give my.cnf you are suggesting max_connections is 1000 but in my db daily 15k to30 users Travers.
>> so, kindly suggest me what we will do.

Does your application really send 15k to 30k concurrent connection at any point of time ? If yes, then that is really huge numbers, having more connection might slow down the server very easily. So, please check with your application team and come up with some good numbers, which shouldn't harm the server.

>> one more issue we are tracking in mysql log file is:-
>> Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction
>> how we will solve it.

This is due to the binlog-format being the default value STATEMENT. If you have many non-deterministic statements which are not safe, statements flagged as being unsafe generate a warning to this effect as you see in your error log.

To avoid this, change your binlog_format to MIXED.

more details here: http://dev.mysql.com/doc/refman/5.6/en/replication-rbr-safe-unsafe.html

Thanks
Anand
MySQL Support Engineer

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 27, 2015 10:26 AM (Friday)

Hi Anand,
Thanks for reply. give my.cnf you are suggesting max_connections is 1000 but in my db daily 15k to30 users Travers.
so, kindly suggest me what we will do.

one more issue we are tracking in mysql log file is:-

Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT. Statement is unsafe because it accesses a non-transactional table after accessing a transactional table within the same transaction

how we will solve it.

Thanks&Regards:
Manoj

Notes

Oracle Support

- Feb 26, 2015 11:05 AM (Thursday)

Hello Manoj -

OK, this says you have innodb of 9GB and MyISAM tables of approx 4GB.

Below are my recommend configuration for your slave instance.

For changing innodb log file you may want to follow some of the steps explained in the link http://dev.mysql.com/doc/refman/5.6/en/innodb-data-log-reconfiguration.html . Otherwise you will end up in having issues with bringing up your database.

=============================
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
open_files_limit = 20000

[mysqld]
skip-name-resolve
basedir = /usr
datadir = /db/mysql
socket = /var/lib/mysql/mysql.sock
explicit_defaults_for_timestamp = TRUE

port = 3306
log-bin = /var/lib/mysql/mysql-bin

max_connections = 1000 --------------------------> max_connection was 19999, does your application send 19999 concurrent connection to the DB ? I changed it to 1000 for now

plugin-dir=/usr/lib64/mysql/plugin
plugin-load=thread_pool.so
thread_pool_size = 64

query_cache_size = 128M
query_cache_type=1
key_buffer_size = 4G
max_allowed_packet = 16M

thread_cache_size = 2056
thread_concurrency = 48
myisam_sort_buffer_size = 1228M
bulk_insert_buffer_size = 256M
table_open_cache = 8000

innodb_flush_log_at_trx_commit=2
innodb_additional_mem_pool_size = 32M
innodb_buffer_pool_size = 10G
innodb_thread_concurrency = 0
innodb_log_buffer_size = 20M
innodb_log_file_size = 800M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 1
innodb_buffer_pool_instances = 10

#Replication slave server

relay-log = /var/log/mysql/mysql-relay-bin
relay-log-index = /var/log/mysql/mysql-relay-bin.index
master-info-file = /var/log/mysql/mysql-master.info
relay-log-info-file = /var/log/mysql/mysql-relay-log.info
server-id = 58
read-only

[myisamchk]
====================

Thanks
Anand
MySQL Support Engineer

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 25, 2015 6:55 PM (Wednesday)

mysql> SELECT ENGINE, SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS DATA_SIZE FROM INFORMATION_SCHEMA.TABLES GROUP BY ENGINE,TABLE_SCHEMA ;
+--------------------+---------------+
| ENGINE | DATA_SIZE |
+--------------------+---------------+
| CSV | 0.00000000 |
| InnoDB | 0.10937500 |
| InnoDB | 53.14062500 |
| InnoDB | 2780.29687500 |
| InnoDB | 0.15625000 |
| InnoDB | 1878.10937500 |
| InnoDB | 2429.32812500 |
| MEMORY | 0.00000000 |
| MyISAM | 10.75948811 |
| MyISAM | 0.00976563 |
| MyISAM | 390.87131882 |
| MyISAM | 1506.75603867 |
| MyISAM | 0.71120453 |
| MyISAM | 383.32132816 |
| MyISAM | 437.83624744 |
| PERFORMANCE_SCHEMA | 0.00000000 |
+--------------------+---------------+
16 rows in set (0.67 sec)

====================================

Yes i am using thread pooling also.

in attachment which is i already sent to you, related master slave architecture in Microsoft doc file .kindly suggest me ?

Thanks

Thanks.

Notes

Oracle Support

- Feb 24, 2015 6:23 PM (Tuesday)

Hello Manoj -

you sent only the partial output of the query., please send the output of the below query again.

SELECT ENGINE, SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS DATA_SIZE FROM INFORMATION_SCHEMA.TABLES GROUP BY ENGINE,TABLE_SCHEMA ;

>> We are also thread pooling so can we remove ?

you mean to say that you wanted to use thread pooling in your server ?

Thanks
Anand
MySQL Support Engineer

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 24, 2015 5:48 PM (Tuesday)

Hi,

i has been sent given query out put ist it right?

SELECT COUNT(ENGINE),ENGINE,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA') GROUP BY ENGINE,TABLE_SCHEMA;
SELECT ENGINE, SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS DATA_SIZE FROM INFORMATION_SCHEMA.TABLES GROUP BY ENGINE,TABLE_SCHEMA ;

You can see in attachment which is sent you.

Thanks

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 24, 2015 5:45 PM (Tuesday)

Hi,

We are also thread pooling so can we remove ?

innodb_flush_log_at_trx_commit=0
innodb_additional_mem_pool_size = 180M
innodb_buffer_pool_size = 32G
innodb_file_io_threads = 4
innodb_thread_concurrency = 33
innodb_log_buffer_size = 20M
innodb_log_file_size = 800M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 1
innodb_buffer_pool_instances = 48

join_buffer_size = 128M

thread_pool_size = 64

Thanks

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 24, 2015 5:34 PM (Tuesday)

[mysqld]
skip-name-resolve
basedir = /usr
datadir = /db/mysql
socket = /var/lib/mysql/mysql.sock
explicit_defaults_for_timestamp = TRUE

innodb_buffer_pool_size = 10G

port = 3306

log-bin = /var/lib/mysql/mysql-bin

server-id = 58

relay-log = /var/log/mysql/mysql-relay-bin
relay-log-index = /var/log/mysql/mysql-relay-bin.index
master-info-file = /var/log/mysql/mysql-master.info
relay-log-info-file = /var/log/mysql/mysql-relay-log.info

max_connections = 19999
plugin-dir=/usr/lib64/mysql/plugin
plugin-load=thread_pool.so

query_cache_size = 1028M
query_cache_type=1
query_cache_limit=5012M
key_buffer_size = 8G
max_allowed_packet = 16M

thread_cache_size = 2056
thread_concurrency = 48
myisam_sort_buffer_size = 1228M

bulk_insert_buffer_size = 256M

table_open_cache = 8000

key_buffer = 9024M

sort_buffer_size = 20M
read_buffer_size = 512M
read_rnd_buffer_size=1024M

innodb_flush_log_at_trx_commit=0
innodb_additional_mem_pool_size = 180M
innodb_buffer_pool_size = 32G
innodb_file_io_threads = 4
innodb_thread_concurrency = 33
innodb_log_buffer_size = 20M
innodb_log_file_size = 800M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 1
innodb_buffer_pool_instances = 48

join_buffer_size = 128M

thread_pool_size = 64

[myisamchk]

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
plugin-dir=/usr/lib64/mysql/plugin

open_files_limit = 8192

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

Notes

Oracle Support

- Feb 24, 2015 5:19 PM (Tuesday)

Hello Manoj -

Yes, those are the common reasons we have seen with others customers as well.

>> above point, How i will fast binary log??

What do you mean by making binary log's faster ? Do you mean to ask how to speedup the slave ?

Did you implement the changes I suggested on your configuration file ? Do you still see the replication lag even after setting it ?

We need to see the latest status details after fixing the problems in the configuration file.

Thanks
Anand
MySQL Support Engineer

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 24, 2015 4:39 PM (Tuesday)

Hi Anand,

in Abraham mail he suggest,

Replication Lag can be caused for many reasons, i.e:
- Network issues.
- The binary log is in STATEMENT format and a statement which takes a long time was executed on the master
- The binary log is in ROW format and a statement which affects many rows in a table with no primary key was executed on the master.

above point, How i will fast binary log??
please suggest me.

Thanks

Notes

Oracle Support

- Feb 24, 2015 11:07 AM (6 days ago)

Hello Manoj -

Master-Master is not recommended as it has more chances it might fails. you shouldn't use both the servers as active when running with master-master.

And with master-slave also you can't use both servers for write's, chances are there it goes to inconsistent state.

So, it is always advisible to use the master for writes and slave for read only purpose like running any reporting queries, backup,. etc.

Thanks
Anand
MySQL Support Engineer

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 24, 2015 10:33 AM (6 days ago)

Hi,

I am waiting your response .
please suggest which approach will be good.

Master-slave OR Master-Master
i want to use BOTH DB server parallel.

Thanks

Thanks

Notes

Oracle Support

- Feb 24, 2015 12:11 AM (6 days ago)

Hello Manoj,

Please allow me to reply while Anand is unavailable at this moment.

Replication Lag can be caused for many reasons, i.e:
- Network issues.
- The binary log is in STATEMENT format and a statement which takes a long time was executed on the master
- The binary log is in ROW format and a statement which affects many rows in a table with no primary key was executed on the master.

I would check those scenarios and if possible try to improve them.

Kind regards,
Abraham Perez.

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 23, 2015 8:24 PM (6 days ago)

Hi Anand,

How can we reduce time of seconds behind master, in master- slave.

Thanks

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 23, 2015 8:21 PM (6 days ago)

Notes

Oracle Support

- Feb 23, 2015 2:15 PM (7 days ago)

Hello Manoj -

I apologize about the delay in responding, I was in off shift in the weekend.

Coming to your configuration, I see the below settings.

>> innodb_buffer_pool_size = 1280M
you can set this variable to 80% of your available system memory in a dedicated mysql server, since you also have httpd service running I would suggest you to set 4G if your innodb data size is not very big in the server.

>> query_cache_size = 128M
your query_cache usage is very less, never make higher than 128M query_cache_size. This will not be a benefit, and based on your usage I can suggest to set 32M.
Or you could even disable it completely and observer the performance.
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_query_cache_size

>> key_buffer_size = 4G

Do you have more MyISAM tables in your server ? Key buffer is similar to the buffer pool in innodb, but it buffers MyISAM indexes. MySQL uses MyISAM for the system tables. There's still some buffer available and most of the time it's reading index from the buffer. you can reduce it further down and use that buffer for somewhere else.
http://dev.mysql.com/doc/refman/5.6/en/server-system-variables.html#sysvar_key_buffer_size

>> myisam_sort_buffer_size = 1228M

When you run repair on a myisam tables, for each MyISAM repair thread can consume upto the memory allocated to this variable.

>> innodb_flush_log_at_trx_commit=0

Change this variable to the non-default value only if you have battery backup cache enabled in your system, The innodb_flush_log_at_trx_commit with the default value as 1, the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file which is expensive.with value 2 which means do not flush log to the disk but only flush it to OS cache. The log is still flushed to the disk each second so you normally would not loose more than 1-2 sec worth of updates. Value 0 is a bit faster but is a bit less secure as you can lose transactions even in case MySQL Server crashes. Value 2 only cause data loss with full OS crash.
http://dev.mysql.com/doc/refman/5.6/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

I see the following variables set under [myisamchk] section of your configuration file.

key_buffer = 9024M
sort_buffer_size = 20M
read_buffer_size = 512M
read_rnd_buffer_size=1024M
innodb_flush_log_at_trx_commit=0
innodb_additional_mem_pool_size = 180M
innodb_buffer_pool_size = 32G
innodb_file_io_threads = 4
innodb_thread_concurrency = 33
innodb_log_buffer_size = 20M
innodb_log_file_size = 800M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
innodb_file_per_table = 1
innodb_buffer_pool_instances = 48
join_buffer_size = 128M
thread_pool_size = 64

I suggest you remove this entirely and just add the following variables under the [mysqld] section of your config file.

innodb_buffer_pool_size = 4G
query_cache_size=32M
key_buffer_size = 512M
myisam_sort_buffer_size = 64M
innodb_flush_log_at_trx_commit=2

============

I also need the output of the following query.

SELECT COUNT(ENGINE),ENGINE,TABLE_SCHEMA FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN ('INFORMATION_SCHEMA', 'PERFORMANCE_SCHEMA') GROUP BY ENGINE,TABLE_SCHEMA;
SELECT ENGINE, SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS DATA_SIZE FROM INFORMATION_SCHEMA.TABLES GROUP BY ENGINE,TABLE_SCHEMA ;

Thanks
Anand
MySQL Support Engineer

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 23, 2015 12:40 PM (7 days ago)

Hi Anand ,

I am waiting your response.

Thanks&Regards:
Manoj

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 21, 2015 2:52 PM (9 days ago)

Hi,

I am sending query related answer.
please check it.

Thanks

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 21, 2015 2:51 PM (9 days ago)

Upload to TDS successful for the file output2.txt.

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 21, 2015 2:51 PM (9 days ago)

Upload to TDS successful for the file output_1.txt.

Notes

Oracle Support

- Feb 20, 2015 11:19 AM (10 days ago)

Hello Manoj -

Sure, I shall help you in configuring the variables according to your server.

But I need you to collect the details again, since you didn't send us the output in the requested format the last time.

Please collect the below details again and send it across.

Please attach :

* the output of the script below

It will last one minute and produce one file : output_1.txt

TEE output_1.txt;
SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;
SHOW SLAVE STATUS\G
SHOW ENGINE INNODB STATUS\G
SHOW FULL PROCESSLIST;
SELECT SLEEP(60);
SHOW GLOBAL STATUS;
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G
SHOW ENGINE INNODB STATUS\G
SHOW FULL PROCESSLIST;
SELECT SLEEP(60);
SHOW GLOBAL STATUS;
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G
SHOW ENGINE INNODB STATUS\G
SHOW FULL PROCESSLIST;
\s
NOTEE;

Can you also collect this OS level data:

echo "/proc/version" > /tmp/output2.txt
echo "=============" >> /tmp/output2.txt
cat /proc/version >> /tmp/output2.txt
echo "uname -a" >> /tmp/output2.txt
echo "========" >> /tmp/output2.txt
uname -a >> /tmp/output2.txt
echo "meminfo" >> /tmp/output2.txt
echo "========" >> /tmp/output2.txt
cat /proc/meminfo >> /tmp/output2.txt
echo "cpuinfo" >> /tmp/output2.txt
echo "=======" >> /tmp/output2.txt
cat /proc/cpuinfo >> /tmp/output2.txt
echo "ulimit -a" >> /tmp/output2.txt
echo "=========" >> /tmp/output2.txt
ulimit -a >> /tmp/output2.txt
echo "free -m" >> /tmp/output2.txt
echo "=========" >> /tmp/output2.txt
free -m >> /tmp/output2.txt
echo "mount -v" >> /tmp/output2.txt
echo "=========" >> /tmp/output2.txt
mount -v >> /tmp/output2.txt
echo "df -h -m" >> /tmp/output2.txt
echo "=========" >> /tmp/output2.txt
df -h -m >> /tmp/output2.txt
echo "vmstat 3 30" >> /tmp/output2.txt
echo "===========" >> /tmp/output2.txt
vmstat 3 30 >> /tmp/output2.txt
echo "iostat -m -xcd 3 30" >> /tmp/output2.txt
echo "===================" >> /tmp/output2.txt
iostat -m -xcd 3 30 >>/tmp/output2.txt
echo "mpstat -P ALL 1 4" >> /tmp/output2.txt
echo "=================" >> /tmp/output2.txt
mpstat -P ALL 1 4 >> /tmp/output2.txt
echo "top" >> /tmp/output2.txt
echo "=====" >> /tmp/output2.txt
top -b -d60 -n5 >> /tmp/output2.txt
echo "ps auxww" >> /tmp/output2.txt
echo "======" >> /tmp/output2.txt
ps auxww >> /tmp/output2.txt
echo "sar -A" >> /tmp/output2.txt
echo "======" >> /tmp/output2.txt
sar -A >> /tmp/output2.txt
echo "/var/log/messages" >> /tmp/output2.txt
echo "=================" >> /tmp/output2.txt
cat /var/log/messages >> /tmp/output2.txt
echo "dmesg" >> /tmp/output2.txt
echo "=====" >> /tmp/output2.txt
dmesg >> /tmp/output2.txt
echo "===== END" >> /tmp/output2.txt

Thanks
Anand
MySQL Support Engineer

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 20, 2015 10:57 AM (10 days ago)

Hi Anand ,

Actually, i have not much idea about, how we will mange our configuration file .can u help me regarding
configuration(my.cnf) file , i want to accurate this.

you have my all CPU,RAM details as well as my my.cnf file also.please help me for prepare correct configuration file.

Thanks
Manoj

Notes

Oracle Support

- Feb 18, 2015 3:09 PM (12 days ago)

Hello Manoj -

OK, I see a slight lag in the replication. there are different reasons for the lag in replication.

MySQL replication works in a single thread fashion, so there are more chances for lag during the backups. Because it gets 100 queries in the master all those will be applied as a single SQL thread in the slave server. As MySQL Replication goes in single thread it is enough for one statement to get stuck to have all replication stalled.

The seconds_behind_master responsible for telling the lag in replication is calculated by taking the current master's time, as in the relay log. So if the I/O thread is lagging, then seconds_behind_master is incorrect.

I see some mistakes in your configuration file, what is your server configuration ? like RAM, CPU, etc ?

I noticed you have mentioned many innodb variables under the [myisamchk] section in your configuration file, the server will not read those settings and just ignore it. you should always mention the variables in the correct section in order for the mysql server to read it while starting.

please check this: http://dev.mysql.com/doc/refman/5.6/en/option-files.html

Thanks
Anand
MySQL Support Engineer

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 17, 2015 6:26 PM (12 days ago)

Hi,

I am sending all data which is required please find in attachment.

Thanks

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 17, 2015 6:25 PM (12 days ago)

Upload to TDS successful for the file output1.txt.

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 17, 2015 6:24 PM (12 days ago)

Upload to TDS successful for the file mysqld.log.

Update from Customer

MANOJ.TIWARI@INFOTELGROUP.IN

- Feb 17, 2015 6:21 PM (12 days ago)

Upload to TDS successful for the file my.cnf.

Notes

Oracle Support

- Feb 16, 2015 12:33 PM (14 days ago)

Hello Manoj -

Did you run any batch jobs on master ? when you run batch jobs on master, make sure there is a time delay in between batch jobs, So that slave thread can perform the job before being queued

Please send us the following details to check further.

From Slave:

1. Error log
2. my.cnf configuration file
3. output of following SQL statements:

TEE output1.txt;
SHOW FULL PROCESSLIST;
SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;
SHOW SLAVE STATUS\G
SHOW ENGINE INNODB STATUS\G
SELECT sleep(60);
SHOW FULL PROCESSLIST;
SHOW GLOBAL STATUS;
SHOW ENGINE INNODB STATUS\G
SHOW SLAVE STATUS\G
SHOW MASTER STATUS;
\s
NOTEE;

2. Output of shell commands:

uname -a
iostat -xd 1 5
vmstat 1 5
top -b -n 1
ps aux
df -h
mount
free -m

Thanks
Anand
MySQL Support Engineer

PHPBLOG

Sunday, 22 February 2015

seconds behinds Master increase

No comments:

Post a Comment