Re: Replication performance questions
Out of curiosity, how many queries/sec are you able to push from each slave? If I may ask.. :) Atle On Mon, 5 Mar 2007, Ross Vandegrift wrote: On Fri, Mar 02, 2007 at 12:57:25AM -0800, Gary W. Smith wrote: Pdns? Close! bind-dlz Anyway, did you enable the slow query logging? That still might give you an idea if something is running slow. But I also forgot to ask earlier, what is running slow, the inserts or the selects during the inserts? I've been trying to determine if there's a way to enable slow query logging at runtime. I've made the changes to the config file, but restarting the database for this stuff is a little dicey and with the stakes being DNS, I'd rather be conservative! set long_query_time=1; doesnt' seem to have created the slow query file, so I'm guessing this means a restart? -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication performance questions
On Fri, Mar 02, 2007 at 12:57:25AM -0800, Gary W. Smith wrote: Pdns? Close! bind-dlz Anyway, did you enable the slow query logging? That still might give you an idea if something is running slow. But I also forgot to ask earlier, what is running slow, the inserts or the selects during the inserts? I've been trying to determine if there's a way to enable slow query logging at runtime. I've made the changes to the config file, but restarting the database for this stuff is a little dicey and with the stakes being DNS, I'd rather be conservative! set long_query_time=1; doesnt' seem to have created the slow query file, so I'm guessing this means a restart? -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication performance questions
Inserts are of the form (updates are analogous):
insert into dns_records (zone, host, data, ... )
values ('domain.com', 'www', '1.2.3.4', ... );
Queries are of the form:
select ttl, type, mx_priority, case
when lower(type)='txt' then
concat('\', data, '\')
when lower(type) = 'soa' then
concat_ws(' ', data, resp_person, serial, refresh,
retry,
expire, minimum)
else data end from dns_records where
zone = 'domain.com' and host = 'www';
We've fixed a few data formatting issues that made the select queries
slow under certain circumstances, but we're still running into
occasional performance problems running the inserts/updates.
There are no joins, subqueries, transactions, or any of the usual muck
that complicates a performance issue.
Pdns?
Anyway, did you enable the slow query logging? That still might give
you an idea if something is running slow. But I also forgot to ask
earlier, what is running slow, the inserts or the selects during the
inserts?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication performance questions
I could be wrong BUT... 1) Does increasing the number of replication slaves increase query latency on the master? We're considering tiering the replication if it might help - replicate the master to two slaves, each of which replicates to ten clients. The slaves should only be pulling from the log file, not querying the master data directly. But yes, I guess I could cause an additional load on the server if there are many many slaves. But with 10,000 updates a day (that is 8 per minute, this shouldn't be much of a load at all. 2) Is there a chance that the insert latency is coming from the fact that the table is growing so long? At a certain point, even with indexes, I imagine that the engine is going to have to do some linear searching. Well, back to answer 1. Replication is about log's, not querying the data. You mentioned updates, but what about querying the data. Do you run a lot of queries against the data on the master server? We have a database with 50M rows in it and we have a complicated replication strategy for the reader just so we can take 99% of the load off the master. We have a slave'd database just to run reports from (actually we have a load balanced cluster of them). The master received inserts about 20 records/s Also, what type of database are you using? INNODB? MyISAM? If you are running MyISAM then things can get slow on updates. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication performance questions
Also, what type of database are you using? INNODB? MyISAM? If you are running MyISAM then things can get slow on updates. Sorry, I missed where you said you were using MyISAM. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication performance questions
On Thu, Mar 01, 2007 at 03:46:56PM -0800, Gary W. Smith wrote: 1) Does increasing the number of replication slaves increase query latency on the master? We're considering tiering the replication if it might help - replicate the master to two slaves, each of which replicates to ten clients. The slaves should only be pulling from the log file, not querying the master data directly. But yes, I guess I could cause an additional load on the server if there are many many slaves. But with 10,000 updates a day (that is 8 per minute, this shouldn't be much of a load at all. This makes a lot of sense to me, and I kind of suspected the additional slaves wouldn't really add much load. The updates are typically small so it not like there's much data to wait for. 2) Is there a chance that the insert latency is coming from the fact that the table is growing so long? At a certain point, even with indexes, I imagine that the engine is going to have to do some linear searching. You mentioned updates, but what about querying the data. Do you run a lot of queries against the data on the master server? We have a database with 50M rows in it and we have a complicated replication strategy for the reader just so we can take 99% of the load off the master. We have a slave'd database just to run reports from (actually we have a load balanced cluster of them). The master received inserts about 20 records/s We have a similar setup with our database that's 1/50 the size ::-) The master is there only to service updates from the application and to push them to the slaves. All reads happen locally on each slave node. Slaves never perform updates. Also, what type of database are you using? INNODB? MyISAM? If you are running MyISAM then things can get slow on updates. The table is MyISAM. I searched on google a bit for info on slow updates with MyISAM and didn't really hit it on the nose. Can I ask you to elaborate? -- Ross Vandegrift [EMAIL PROTECTED] The good Christian should beware of mathematicians, and all those who make empty prophecies. The danger already exists that the mathematicians have made a covenant with the devil to darken the spirit and to confine man in the bonds of Hell. --St. Augustine, De Genesi ad Litteram, Book II, xviii, 37 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication performance questions
The table is MyISAM. I searched on google a bit for info on slow updates with MyISAM and didn't really hit it on the nose. Can I ask you to elaborate? In /etc/my.cnf try adding: long_query_time = 1 log-slow-queries=/var/lib/mysql/mysql-slow.log Restart and then watch the file. If a anything pop's up, do an EXPLAIN on the SQL statement and see what might be slowing it down. This helped me when I was debugging a join between two tables with a where clause between the two on a non-indexed field (with 100k rows in one, 30k rows in another). It wasn't too slow but I was able to turn the 5+ second response to around .2 seconds on one of our test servers. My dev environment that I'm testing my 50M row database on is a P4 HT workstation running under xen DomU (with 1gb of ram in the DomU). At first I thought all my sluggish responses were because of hardware but low and behold, it was just in need of some good indexes. With that said, don't go index crazy. If that fails and nothing else seems to help, add lots of ram and cpu's :). Hope that helps. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication performance questions
On Thu, Mar 01, 2007 at 08:29:09PM -0800, Gary W. Smith wrote:
The table is MyISAM. I searched on google a bit for info on slow
updates with MyISAM and didn't really hit it on the nose. Can I ask
you to elaborate?
In /etc/my.cnf try adding:
long_query_time = 1
log-slow-queries=/var/lib/mysql/mysql-slow.log
Let me make the problem a step more frustrating ::-). All of the
queries and updates to this database are extremely simple and very
predictable. The database is a backend for DNS.
Inserts are of the form (updates are analogous):
insert into dns_records (zone, host, data, ... )
values ('domain.com', 'www', '1.2.3.4', ... );
Queries are of the form:
select ttl, type, mx_priority, case
when lower(type)='txt' then
concat('\', data, '\')
when lower(type) = 'soa' then
concat_ws(' ', data, resp_person, serial, refresh, retry,
expire, minimum)
else data end from dns_records where
zone = 'domain.com' and host = 'www';
We've fixed a few data formatting issues that made the select queries
slow under certain circumstances, but we're still running into
occasional performance problems running the inserts/updates.
There are no joins, subqueries, transactions, or any of the usual muck
that complicates a performance issue.
--
Ross Vandegrift
[EMAIL PROTECTED]
The good Christian should beware of mathematicians, and all those who
make empty prophecies. The danger already exists that the mathematicians
have made a covenant with the devil to darken the spirit and to confine
man in the bonds of Hell.
--St. Augustine, De Genesi ad Litteram, Book II, xviii, 37
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: Replication Performance
Dear ALL: Some time the slave would crushed by I/O error.It happen at the server with the lower CPU.( per 45 days ) All of the slave database has block the binary log,it reduce the CPU loading. The database would replicate text,int,varchar but no blob. Network connection hasn't drop frequently,it seems hard to avoid that. Do any way make the slave query more time but not just idle and wait for the data? Since we can't bear the slave need 10 minutes to synchronize the data with master. Here the master status Uptime: 2592393 Threads: 24 Questions: 214644229 Slow queries: 197 Opens: 580 Flush tables: 1 Open tables: 254 Queries per second avg: 82.798 At 2004-07-01, 22:06:24 you wrote: What does the network setup look like? What type of data is being replicated varchar, text, blob? Is the network connection being dropped or are there any errors being logged anywhere? -Original Message- From: MaFai To: [EMAIL PROTECTED] Sent: 6/30/04 10:34 PM Subject: Replication Performance Dear, [EMAIL PROTECTED], We have set up 1 master and 4 slave as replication. Sometime,the slave need 4~10 minutes to synchronize the data with master database. Do any way to tune the performance? Or any other way to reduce the time to replicate? Best regards. MaFai [EMAIL PROTECTED] 2004-07-01 = = = = = = = = = = = = = = = = = = = = = = Best regards. MaFai [EMAIL PROTECTED] 2004-07-02
Re: Replication Performance
MaFai wrote: Dear ALL: Some time the slave would crushed by I/O error.It happen at the server with the lower CPU.( per 45 days ) All of the slave database has block the binary log,it reduce the CPU loading. The database would replicate text,int,varchar but no blob. Network connection hasn't drop frequently,it seems hard to avoid that. What does this mean? It does or does not drop frequently? If you have a poor network connection, you will never get this working. Do any way make the slave query more time but not just idle and wait for the data? Since we can't bear the slave need 10 minutes to synchronize the data with master. Here the master status Uptime: 2592393 Threads: 24 Questions: 214644229 Slow queries: 197 Opens: 580 Flush tables: 1 Open tables: 254 Queries per second avg: 82.798 At 2004-07-01, 22:06:24 you wrote: What does the network setup look like? What type of data is being replicated varchar, text, blob? Is the network connection being dropped or are there any errors being logged anywhere? -Original Message- From: MaFai To: [EMAIL PROTECTED] Sent: 6/30/04 10:34 PM Subject: Replication Performance Dear, [EMAIL PROTECTED], We have set up 1 master and 4 slave as replication. Sometime,the slave need 4~10 minutes to synchronize the data with master database. Do any way to tune the performance? Or any other way to reduce the time to replicate? Best regards. MaFai [EMAIL PROTECTED] 2004-07-01 = = = = = = = = = = = = = = = = = = = = = = Best regards. MaFai [EMAIL PROTECTED] 2004-07-02 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication Performance
What does the network setup look like? What type of data is being replicated varchar, text, blob? Is the network connection being dropped or are there any errors being logged anywhere? -Original Message- From: MaFai To: [EMAIL PROTECTED] Sent: 6/30/04 10:34 PM Subject: Replication Performance Dear, [EMAIL PROTECTED], We have set up 1 master and 4 slave as replication. Sometime,the slave need 4~10 minutes to synchronize the data with master database. Do any way to tune the performance? Or any other way to reduce the time to replicate? Best regards. MaFai [EMAIL PROTECTED] 2004-07-01 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Performance
On Thu, Jul 01, 2004 at 11:34:29AM +0800, MaFai wrote: Dear, [EMAIL PROTECTED], We have set up 1 master and 4 slave as replication. Sometime,the slave need 4~10 minutes to synchronize the data with master database. Do any way to tune the performance? Or any other way to reduce the time to replicate? You need to identify the bottleneck. Is the slave's IO thread taking too long to pull binary log entries from the master? Or is the SQL thread hopelessly behind because of a slow hard disk, CPU, or memory shortage? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Performance
Todd Burke wrote: Hello 1/ I have a search engine which crawls auction sites and returns information which is inserted into a mysql database. This activity represents on average about 3 inserts per second with a combined payload of 450 bytes per second added to the database. This updating occurs continuously throughout the day. Since this activity puts a burden on the machine on which it occurs (not only the mysqld inserts but also the scripts and http client activity), I am planning to dedicate one box to the crawling/inserting and replicate the data to one (or possibly more) other servers. These slave servers will handle all client requests (almost exclusively reads). The mysql documentation states: You should set up one server as the master, and direct all writes to it, and configure as many slaves as you have the money and rackspace for, distributing the reads among the master and the slaves. My questions are: Since the updating occurs throughout the day and the same amount of data has to be eventually inserted into the slaves I assume the updating will require the same amount of resources (disk, cpu usage) on the slaves as on the master - the same number of writes will occur on the slaves. Correct. So the the benefit of this configuration (in terms of performance) is that it is the extra processing required to do the crawling will be offloaded to the one master server, not the resources required for the mysql writes. Your benefit will be to spread the load from the client requests across multiple servers. If all you want to do is to limit the affect of the crawler, you could batch those up and add them during off hours. I assume inserts done thru replication are not more efficient than regular inserts. Correct, replication inserts affect the db just as the original writes do. However, one nice thing that you can do to the slaves to limit the affect of inserts/updates is to turn on low priority updates. This prevents updates from locking out selects and makes your clients happier. (See the replication FAQ for more details on this: http://www.mysql.com/doc/en/Replication_FAQ.html) Also, as an alternative to batching the updates to the master as mentioned above, you could turn off replication during heavy times and turn it on until things catch up. This could easily be automated in a script. What is the performance hit of replication and is there a way to limit the effect of the writes on the slave servers, thru configuration parameters, for example, or would it even make sense to take the slave offline at regular intervals while replication is taking place? Performance hit for replication will just be extra load caused by the inserts from the master. The slave process has little or no extra overhead. As mentioned in the FAQ: You can also start the slaves with --skip-bdb, --low-priority-updates and --delay-key-write=ALL to get speed improvements for the slave. In this case the slave will use non-transactional MyISAM tables instead of BDB tables to get more speed. 2/ Is there any documentation on handling and configuring large tables? Hope this is not too confusing... Many thanks Todd Burke phbnyc.com Marc Prewitt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication Performance
On Tue, Jun 24, 2003 at 03:34:48PM -0500, Todd Burke wrote: Hello 1/ I have a search engine which crawls auction sites and returns information which is inserted into a mysql database. This activity represents on average about 3 inserts per second with a combined payload of 450 bytes per second added to the database. This updating occurs continuously throughout the day. Since this activity puts a burden on the machine on which it occurs (not only the mysqld inserts but also the scripts and http client activity), I am planning to dedicate one box to the crawling/inserting and replicate the data to one (or possibly more) other servers. These slave servers will handle all client requests (almost exclusively reads). [snip] My questions are: Since the updating occurs throughout the day and the same amount of data has to be eventually inserted into the slaves I assume the updating will require the same amount of resources (disk, cpu usage) on the slaves as on the master - the same number of writes will occur on the slaves. Yes. So the the benefit of this configuration (in terms of performance) is that it is the extra processing required to do the crawling will be offloaded to the one master server, not the resources required for the mysql writes. The real benefit is that clients reading the data don't block writes on the master AND you can have many, many, many more clients reading the data this way--more than a single machine could ever handle. I assume inserts done thru replication are not more efficient than regular inserts. Right. What is the performance hit of replication and is there a way to limit the effect of the writes on the slave servers, thru configuration parameters, for example, or would it even make sense to take the slave offline at regular intervals while replication is taking place? The performance hit of replication on the master is trivial. I've had 40 slaves (or was it 30?) replicating from a master with no side-effects. I can think of no reason to pause replication on the slave(s). 2/ Is there any documentation on handling and configuring large tables? Yes, here's something I wrote to explain it a bit more than the manual does (or used to): http://jeremy.zawodny.com/blog/archives/000796.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 21 days, processed 675,697,506 queries (361/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
