|
Notes
|
|
Oracle Support
|
- Sep 24, 2014 4:46 PM (Wednesday)
|
|
Hello Manoj -
I can only think of the below changes on your
server.
query_cache_size = 128M
query_cache_type=1
query_cache_limit=1M
key_buffer_size = 1G
innodb_buffer_pool_size=2G
Thanks
Anand
MySQL Support Engineer.
------------
Attend MySQL Central @ OpenWorld, Sept 28 - Oct 2, San
Francisco.
Learn about the latest developments directly from
Oracle's MySQL engineers,
boost your skills & share experiences with your
peers.
Register Now! www.oracle.com/openworld/mysql
------------
|
|
|
Update from Customer
|
|
MANOJ.TIWARI@INFOTELGROUP.IN
|
- Sep 24, 2014 12:59 PM (6 days ago)
|
|
Hi Anand,
Please let me know global variables after using that i
can improve Master-slave performance.
Thanks
Manoj
|
|
|
Notes
|
|
|
Oracle Support
|
- Sep 24, 2014 12:26 PM (6 days ago)
|
|
Hello Manoj -
No, thread pool may not help in this slave lag
issues., though it might help for your application
connections, but not to the delay in replication.
Below are the uses of thread pool.
- The default thread-handling model in MySQL Server
executes statements using one thread per client
connection.
- As more clients connect to the server and execute
statements, overall performance degrades.
- As of MySQL 5.5.16, commercial distributions of
MySQL include a thread pool plugin that provides an
alternative thread-handling model designed to reduce
overhead and improve performance.
- The plugin implements a thread pool that increases
server performance by efficiently managing statement
execution threads for large numbers of client
connections.
More on Thread Pool could be found here :
http://dev.mysql.com/doc/refman/5.5/en/thread-pool-plugin.html
Thanks
Anand
MySQL Support Engineer.
------------
Attend MySQL Central @ OpenWorld, Sept 28 - Oct 2, San
Francisco.
Learn about the latest developments directly from
Oracle's MySQL engineers,
boost your skills & share experiences with your
peers.
Register Now! www.oracle.com/openworld/mysql
------------
|
|
|
Update from Customer
|
|
MANOJ.TIWARI@INFOTELGROUP.IN
|
- Sep 24, 2014 12:00 PM (6 days ago)
|
|
Hi Anand,
I used thread pooling both server master and slave.
is it solve my data delay problem b/w master and
slave?
plugin-dir=/usr/lib64/mysql/plugin
plugin-load=thread_pool.so
Thanks
Manoj
|
|
|
Notes
|
|
|
Oracle Support
|
- Sep 24, 2014 11:40 AM (6 days ago)
|
|
Hello Manoj -
With the normal mysql replication, this is a known
issue., On master you will have multi threads to
handle writes, where as in case of slave you will have
only 1 thread which is being used and certainly will
cause the lag when your master is busy.
So, I suggest you check the latest feature of using
multi threaded replication from version 5.6 .
The multi-threaded slave splits processing between
worker threads based on schema, allowing updates to be
applied in parallel, rather than sequentially. This
delivers benefits to those workloads that isolate
application data using databases, the database level
is for the sake of keeping transaction consistency
feature. so for each schema there is a slave worker
thread assigned.
more details here:
http://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html
Thanks
Anand
MySQL Support Engineer.
------------
Attend MySQL Central @ OpenWorld, Sept 28 - Oct 2, San
Francisco.
Learn about the latest developments directly from
Oracle's MySQL engineers,
boost your skills & share experiences with your
peers.
Register Now! www.oracle.com/openworld/mysql
------------
|
|
|
Update from Customer
|
|
MANOJ.TIWARI@INFOTELGROUP.IN
|
- Sep 24, 2014 11:13 AM (6 days ago)
|
|
Hi Anand ,
Actually,when load is normal, then that time every
thing is working fine.
Currently i am using Master-slave approach ,during
load in slave response time is be slow.
ex : during load when any new user register then that
time user data show in slave after 4 to 5 minute
delay.
i want to remove such kind problem.
Thanks
Manoj
|
|
|
Notes
|
|
|
Oracle Support
|
- Sep 24, 2014 10:45 AM (6 days ago)
|
|
Hello Manoj -
OK, I understood.
Did you face any performance issue during the peak
time ? This SR was initially created to address the
replication delay and now went to a different issue of
performance tuning.
If you are ok to run with the current settings and
don't face any performance issue, you can keep it.
However the formulas I gave in the earlier notes are
the general calculation we use in mysql to allocate
buffer.
Thanks
Anand
MySQL Support Engineer.
------------
Attend MySQL Central @ OpenWorld, Sept 28 - Oct 2, San
Francisco.
Learn about the latest developments directly from
Oracle's MySQL engineers,
boost your skills & share experiences with your
peers.
Register Now! www.oracle.com/openworld/mysql
------------
|
|
|
Update from Customer
|
|
MANOJ.TIWARI@INFOTELGROUP.IN
|
- Sep 24, 2014 10:34 AM (6 days ago)
|
|
Hi Anand ,
My app name is nexGtv . you can search on Google
store.generally 5k to 6 k users visit this app but
when any important
event is going on like cricket match,football match or
any important news then that time suddenly users
increase up to 25k to 30 k.
Thanks
Manoj
|
|
|
Notes
|
|
|
Oracle Support
|
- Sep 23, 2014 6:20 PM (6 days ago)
|
|
Hello Manoj -
Do you really need 30K concurrent connection setting,
which is pretty high ? Please note it is one of the
factor used to calculate the memory usage of the
server, having so many open connection will hung the
server completely, its very difficult to have 30k
concurrent connections, you really need to come up
with some number.
max_connection variable play a very important role in
mysql.
Normally, in MySQL server environment you can tune lot
of variables and few of them are really very important
for most common workloads and if you manage to set
these parameters correctly then rest changes will
offer incremental performance gains.
http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_max_connections
First we have to consider that each connection uses at
a minimum of 200k of memory and more depending on if
the connection is doing an in memory sort or using
temporary memory tables. Now if all connections are
very basic we can stack up many connections if we have
a large amount of memory.
Calculation depends upon the kind of ativity each
connection is doing.
1) The static memory consumption by the server will be
the sum of below mentioned variables:
(innodb-additional-mem-pool-size +
innodb-buffer-pool-size + innodb-log-buffer-size +
key_buffer_size + query_cache_size)
2) Each Active connection will consume the sum of
following variables, these are all per thread
buffers.
(binlog_cache_size + (net_buffer_length * 2) +
read_buffer_size + read_rnd_buffer_size +
sort_buffer_size + thread_stack)
So the max_connection you set in your server can eat
upto the calculated value above for each of the
connections.
Please let us know how would be like to go about this
?
Thanks
Anand
MySQL Support Engineer.
------------
Attend MySQL Central @ OpenWorld, Sept 28 - Oct 2, San
Francisco.
Learn about the latest developments directly from
Oracle's MySQL engineers,
boost your skills & share experiences with your
peers.
Register Now! www.oracle.com/openworld/mysql
------------
|
|
|
Update from Customer
|
|
MANOJ.TIWARI@INFOTELGROUP.IN
|
- Sep 23, 2014 10:38 AM (7 days ago)
|
|
Hi Anand,
My app is live TV, app name is nexGtv app .sometimes
30K concurrent users axis it .
so i used max_connections is 19999.
and i set all parameters worst case of server load
.
Then after if you suggest i changed parameter then
definitely i can do.
can i changed parameter ???
Thanks
Manoj
|
|
|
Notes
|
|
|
Oracle Support
|
- Sep 22, 2014 6:28 PM (7 days ago)
|
|
Hello Manoj -
I still see you didnt apply my recommended values yet
to the variables.., I was interested to see the latest
status after setting the new values.
Below are some of the things which needs changing.
max_connections = 19999 ----> what is your expected
concurrent connection ? set it accordingly, I see it
went upto 3106, is this normal ? I strongly suggest
you to set the correct number according to your
requirement.
query_cache_size = 1077936128 ---> its still set to
1G, which is huge. reduce it down to 128M.
query_cache_limit = 5255462912 ---> This is to
control the maximum size of individual query results
that can be cached. The default value is 1MB., I
suggest you leave it to default value unless if your
query results are really big.
key_buffer_size = 8589934592 ---> currently set to
8G, whereas your MyISAM data size is just over 300 MB,
so i suggest you reduce it down to 1G, the usage says
~20% shown below.
MyISAM Key Cache Usage = 100% - 100% *
(Key_blocks_unused * key_cache_block_size) /
key_buffer_size
= 100% - 100% * (6847906 * 1024) / 8589934592
= 18.37%
innodb_buffer_pool_size = 1342177280 ---> The
Innodb_buffer_pool_size is the the closest thing to an
equivalent of key_buffer_size. The major difference
here is the innodb buffer pool contains data and index
pages, while the key buffer contains only index pages,
since your innodb data size is just over 1GB, setting
it to 2G would really help.
Thanks
Anand
MySQL Support Engineer.
------------
Attend MySQL Central @ OpenWorld, Sept 28 - Oct 2, San
Francisco.
Learn about the latest developments directly from
Oracle's MySQL engineers,
boost your skills & share experiences with your
peers.
Register Now! www.oracle.com/openworld/mysql
------------
|
|
|
Update from Customer
|
|
MANOJ.TIWARI@INFOTELGROUP.IN
|
- Sep 22, 2014 2:45 PM (8 days ago)
|
|
|
Upload to TDS successful for the file
output_master.txt.
|
|
|
Notes
|
|
|
Oracle Support
|
- Sep 22, 2014 12:43 PM (8 days ago)
|
|
Hello Manoj -
I see the resident memory is 8.3G for this mysql
instance, is this on master or slave ?
you didn't answer my earlier question, Your database
size seems less than 2G, correct me if i am wrong ?
you said you have implemented the buffer changes from
my recommendations earlier, could you collect the
latest status again to see how things have changed
from the before config changes and after config
changes.
Please attach the output of the script below:
TEE output.txt;
SHOW FULL PROCESSLIST;
SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;
SHOW MASTER STATUS;
SHOW SLAVE STATUS\G
SELECT sleep(60);
SHOW FULL PROCESSLIST;
SHOW MASTER STATUS;
SHOW GLOBAL STATUS;
SHOW SLAVE STATUS\G
SHOW ENGINE INNODB STATUS\G
\s
NOTEE;
Thanks
Anand
MySQL Support Engineer.
------------
Attend MySQL Central @ OpenWorld, Sept 28 - Oct 2, San
Francisco.
Learn about the latest developments directly from
Oracle's MySQL engineers,
boost your skills & share experiences with your
peers.
Register Now! www.oracle.com/openworld/mysql
------------
|
|
|
Update from Customer
|
|
MANOJ.TIWARI@INFOTELGROUP.IN
|
- Sep 22, 2014 11:09 AM (8 days ago)
|
|
|
Upload to TDS successful for the file
master-slave-discussion_22sep.txt.
|
|
|
Notes
|
|
|
Oracle Support
|
- Sep 18, 2014 4:17 PM (11 days ago)
|
|
Hello Manoj -
Your database size seems less than 2G, correct me if i
am wrong ?
For this amount of data, the buffer settings are
really huge.
I need to reduce the variables further down, before
that I need to verify your OS details.
could you collect the below details for me ?
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.
------------
Attend MySQL Central @ OpenWorld, Sept 28 - Oct 2, San
Francisco.
Learn about the latest developments directly from
Oracle's MySQL engineers,
boost your skills & share experiences with your
peers.
Register Now! www.oracle.com/openworld/mysql
------------
|
|
|
Update from Customer
|
|
MANOJ.TIWARI@INFOTELGROUP.IN
|
- Sep 18, 2014 11:03 AM (12 days ago)
|
|
Hi,
I am sending results which is asked by you.
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 | 1251.03125000 |
| MEMORY | 0.00000000 |
| MyISAM | 0.00976563 |
| MyISAM | 0.70837784 |
| MyISAM | 257.57062626 |
| PERFORMANCE_SCHEMA | 0.00000000 |
+--------------------+---------------+
8 rows in set (0.01 sec)
mysql> SELECT COUNT(*),ENGINE,TABLE_SCHEMA FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN
('mysql', 'information_schema', 'performance_schema')
GROUP BY ENGINE, TABLE_SCHEMA;
+----------+--------+--------------+
| COUNT(*) | ENGINE | TABLE_SCHEMA |
+----------+--------+--------------+
| 27 | InnoDB | unified |
| 55 | MyISAM | unified |
+----------+--------+--------------+
2 rows in set (0.00 sec)
i am configuring variables in my.cnf which is suggest
by you.
variables are :-
----------------->>>> To conclude, below
are my recommended values for the variables.
query_cache_size = 128M
query_cache_type=1
query_cache_limit=1M
key_buffer_size = 4G
any more variables which is not showing properly
please suggest me .
Thanks
|
|
|
Notes
|
|
|
Oracle Support
|
- Sep 12, 2014 6:22 PM (17 days ago)
|
|
Hello Manoj -
I see the variables are not configured properly. Do
you have more myisam tables than innodb ? could you
give me the ratio of innodb and myisam tables on your
server please ?
Please run the below queries to see the size of your
database and the number of tables in your database.
mysql> SELECT ENGINE,
SUM(DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS DATA_SIZE
FROM INFORMATION_SCHEMA.TABLES GROUP BY ENGINE,
TABLE_SCHEMA;
mysql> SELECT COUNT(*),ENGINE,TABLE_SCHEMA FROM
INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN
('mysql', 'information_schema', 'performance_schema')
GROUP BY ENGINE, TABLE_SCHEMA;
Query Cache
===========
| query_alloc_block_size | 8192 |
| query_cache_limit | 5255462912 ---------> this is
enormously high |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 1077936128 ------> never set
more than 128MB for query cache, its of not much use.
|
| query_cache_type | ON |
| query_cache_wlock_invalidate | OFF |
First Output:
-------------
| Com_select | 2583814 |
| Qcache_free_blocks | 37073 |
| Qcache_free_memory | 926532272 |
| Qcache_hits | 59767535 |
| Qcache_inserts | 1389828 |
| Qcache_lowmem_prunes | 0 |
| Qcache_not_cached | 1193729 |
| Qcache_queries_in_cache | 109566 |
| Qcache_total_blocks | 256530 |
Based on the output, the query cache statistics are
Query Cache Hit Rate = 100% * (Qcache_hits) /
(Qcache_hits + Qcache_inserts)
= 100% * (59767535) / (59767535 + 1389828)
= 97.73%
Query Cache Insert Rate = 100% * Qcache_inserts /
Com_select
= 100% * 1389828 / 2583814
= 53.79%
Query Cache Usage = 100% - 100% * Qcache_free_memory /
query_cache_size
= 100% - 100% * 926532272 / 1077936128
= 14.05% ---------------- the usage is just 14% of
your 1G, so I suggest you to reduce it down to
128M.
MyISAM Key Cache
================
| key_buffer_size | 8589934592 ----------- >
key_buffer_size is set to 8GB, does your server have
more myisam tables ? |
| key_cache_block_size | 1024 |
First Output:
-------------
| Key_blocks_unused | 6852465 |
| Key_blocks_used | 6479 |
| Key_read_requests | 6674112 |
| Key_reads | 3994 |
Based on the output, the MyISAM key cache hit rate
is
MyISAM Key Cache Hit Rate = 100% - 100% * (Key_reads /
Key_read_requests)
= 100% - 100% * (3994 / 6674112)
= 99.94%
and the MyISAM key cache usage (including overhead)
is
MyISAM Key Cache Usage = 100% - 100% *
(Key_blocks_unused * key_cache_block_size) /
key_buffer_size
= 100% - 100% * (6852465 * 1024) / 8589934592
= 18.31% ----------------- > the usage shows juts
over 18%, which is very less., the output of the query
I gave above should be able to help knowing the
size.
And it is always good to leave read_buffer_size,
sort_buffer_size, read_rnd_buffer_size at their
default values. Just comment that out of my.cnf
totally.
To conclude, below are my recommended values for the
variables.
query_cache_size = 128M
query_cache_type=1
query_cache_limit=1M
key_buffer_size = 4G
With other requested details, I can come up with more
suggestions.
Thanks
Anand
MySQL Support Engineer.
------------
Attend MySQL Central @ OpenWorld, Sept 28 - Oct 2, San
Francisco.
Learn about the latest developments directly from
Oracle's MySQL engineers,
boost your skills & share experiences with your
peers.
Register Now! www.oracle.com/openworld/mysql
------------
|
|
|
Update from Customer
|
|
MANOJ.TIWARI@INFOTELGROUP.IN
|
- Sep 11, 2014 6:35 PM (18 days ago)
|
|
|
Upload to TDS successful for the file
master-slave-discussion.rar.
|
|
|
Notes
|
|
|
Oracle Support
|
- Sep 11, 2014 5:40 PM (18 days ago)
|
|
Hello Manoj -
No, community and enterprise are essentially the same
thing with a different label(just this doesn't match,
one uses openssl the other uses yassl. ). There are
some additional plugin's which are available only on
enterprise versions. Apart from that there is no
performance improvement one can expect between these
two versions.
So, most of the time the slowness on slave is mainly
due to the single threaded logic it follows . If you
could send us more details collected during the
lagging time, we would help you identify the real
cause on what this is due to the network issue or the
issue in applying the events on the slave.
the details we are interested are basically.
From Master:
1. Error log
2. my.cnf configuration file
3. output of following SQL statements:
TEE output.txt;
SHOW FULL PROCESSLIST;
SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;
SHOW MASTER STATUS;
SELECT sleep(60);
SHOW FULL PROCESSLIST;
SHOW MASTER STATUS;
SHOW GLOBAL STATUS;
SHOW SLAVE STATUS\G
SHOW ENGINE INNODB STATUS\G
\s
NOTEE;
From Slave:
1. Error log
2. my.cnf configuration file
3. output of following SQL statements:
TEE output2.txt;
SHOW FULL PROCESSLIST;
SHOW GLOBAL VARIABLES;
SHOW GLOBAL STATUS;
SHOW SLAVE STATUS\G
SELECT sleep(60);
SHOW FULL PROCESSLIST;
SHOW GLOBAL STATUS;
SHOW SLAVE STATUS\G
SHOW MASTER STATUS;
SHOW ENGINE INNODB STATUS\G
\s
NOTEE;
Thanks
Anand
MySQL Support Engineer.
------------
Attend MySQL Central @ OpenWorld, Sept 28 - Oct 2, San
Francisco.
Learn about the latest developments directly from
Oracle's MySQL engineers,
boost your skills & share experiences with your
peers.
Register Now!
www.oracle.com/openworld/mysql------------
|
|
|
Update from Customer
|
|
MANOJ.TIWARI@INFOTELGROUP.IN
|
- Sep 10, 2014 5:55 PM (19 days ago)
|
|
Hi,
Ok. if i migrate my db in MySQL enterprise version
then my master - slave syncing problem will be
solved?
my both servers are equivalent ram and
configuration.
Thanks
|
|
|
Notes
|
|
|
Oracle Support
|
- Sep 10, 2014 12:30 PM (20 days ago)
|
|
Hello Manoj -
If its just passing data between the master and slave,
then its latency and you need have a faster Ethernet
card.
Whereas if its something which is related to the slave
lag, then its known thing in mysql asynchronous
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.
As you know the slave server is single threaded it may
not be fast enough to keep up to your master, you
could somehow keep the speed by having the similar
hardware for both master and slave, For example if
your slave has 16GB RAM, . think how will be the
performance of a server's server doing concurrent job
with 48 GB of memory versus the slave doing the same
work in sequentially with 16GB of RAM.
You will obviously see the performance issue, with
this hardware configuration the slave server could not
keep up to the speed of the master server. It is
always recommended to have identical servers for both
master and slave.
The main reason for the slave's slowness would be:
1. Not enough RAM available, that leads to the caches
doesnt work effectively
2. CPU load - all cores are busy due to complex
calculations or extensive mutex locking.
3. I/O system - is not able to write or read fast
enough
So, your priority of having the data modified on
master instantly on slave is not always possible as
the mysql replication runs on asynchronous
replication., especifally on a heavily loaded master,
slave in sync current is not always possible.
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.
Application changes and Traffic increases are some of
the frequent causes of problems, or a batch job being
ran which does a lot of updates can also be the
problem.
Thanks
Anand
MySQL Support Engineer.
------------
Attend MySQL Central @ OpenWorld, Sept 28 - Oct 2, San
Francisco.
Learn about the latest developments directly from
Oracle's MySQL engineers,
boost your skills & share experiences with your
peers.
Register Now! www.oracle.com/openworld/mysql
------------
|
|
|
Customer Problem Description
|
|
MANOJ.TIWARI@INFOTELGROUP.IN
|
- Sep 9, 2014 2:49 PM (21 days ago)
|
|
Customer Problem Description
---------------------------------------------------
Problem Summary
---------------------------------------------------
How i can decrease response time b/w master slave?
Problem Description
---------------------------------------------------
Hi,
How can we decrease response time b/w master slave ?
actually data passing b/w master and slave it is
taking much time
how we can reduce it? during load on MySQL server
response time increase.
my first priority is that when data modify in master
server then instantly data update in slave server .
How i can do???
Error Codes
---------------------------------------------------
Problem Category/Subcategory
---------------------------------------------------
Consultative Support/Performance Tuning
Uploaded Files
---------------------------------------------------
Template Question Responses
---------------------------------------------------
1) Operating System
cent os
2) Architecture
bogomips : 4000.24
clflush size : 64
cache_alignment : 64
address sizes : 46 bits physical, 48 bits virtual
power management:
processor : 17
vendor_id : GenuineIntel
cpu family : 6
model : 45
model name : Intel(R) Xeon(R) CPU E5-2620 0 @
2.00GHz
stepping : 7
cpu MHz : 1200.000
cache size : 15360 KB
physical id : 0
siblings : 12
core id : 5
cpu cores : 6
apicid : 11
initial apicid : 11
fpu : yes
fpu_exception : yes
cpuid level : 13
wp : yes
3) MySQL Server version
mysql 5.12
|
|
|
