Re: [Replication] - load
On 10/10/07, Ratheesh K J <[EMAIL PROTECTED]> wrote: > So every morning all the queries will be slow for the first time on the DB > server 2 and thereafter will be served by the query cache as they will be > cached and never invalidated until the night. Sorry for the late reply, I'm trying to get caught up on the posts to the list. I *think* your concern is really the first queries of the day having a cache miss and therefore taking longer than you'd like. If you know what the queries are going to be in advance (i.e this is a reporting application and your users typically request the same kind of thing every day), you could setup a cron job to run in the middle of the night to execute those queries. That would seed the query cache and when users begin to use your system in the morning, they wouldn't experience a cache miss slowdown. -- Kevin. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] - load
Hi, Ratheesh K J wrote: @all, Currently we run all our complex reporting queries on a different server. We are not using replication though. What we are doing is to restore the backup of the live data every night onto the reporting server. So we are running reports on data that is not real time. And we are OK with that. We made this setup just to share the load between the two DB servers. SO on the reporting DB server there will be only select queries running. And so all the queries will be served from the Query cache and hence there is a big performance gain. Any updates that happen is only during the night when the backup from DB server 1 is restored onto the DB server 2 (Reporting DB server). So every morning all the queries will be slow for the first time on the DB server 2 and thereafter will be served by the query cache as they will be cached and never invalidated until the night. Now suppose we enable real time replication between DB server 1 and DB server 2 then: 1) Will our aim of load balancing be hit because there will be no overall performance gain as still INSERTS, UPDATES and DELETES will continue to run on DB server 2 due to replication ? i.e the load of the DML statements will still continue to be there. What do you mean, "aim of load balancing be hit?" Yes, the slave server will have to replay the statements from the master server. 2) Is this notion correct that Replication will provide a performance boost considering the context. Isnt the load same as when there was no replication? What load are you talking about? The load on the master will be essentially unchanged by replication. 3) And the query cache will get invalidated on DB server 2 when there is real time replication. So isnt it another perormance hit? Yes, this is true. These questions are just to get an hint of the performance benfit due to replication because conceptually I feel that there is still the same amount of load. On one hand I know that replication is not for load balancing ( am I right? ) and on the other hand I am doubting why Replication? if the load is same. Again I need you to more clearly define what you mean by "the load." Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Replication] - load
@all, Currently we run all our complex reporting queries on a different server. We are not using replication though. What we are doing is to restore the backup of the live data every night onto the reporting server. So we are running reports on data that is not real time. And we are OK with that. We made this setup just to share the load between the two DB servers. SO on the reporting DB server there will be only select queries running. And so all the queries will be served from the Query cache and hence there is a big performance gain. Any updates that happen is only during the night when the backup from DB server 1 is restored onto the DB server 2 (Reporting DB server). So every morning all the queries will be slow for the first time on the DB server 2 and thereafter will be served by the query cache as they will be cached and never invalidated until the night. Now suppose we enable real time replication between DB server 1 and DB server 2 then: 1) Will our aim of load balancing be hit because there will be no overall performance gain as still INSERTS, UPDATES and DELETES will continue to run on DB server 2 due to replication ? i.e the load of the DML statements will still continue to be there. 2) Is this notion correct that Replication will provide a performance boost considering the context. Isnt the load same as when there was no replication? 3) And the query cache will get invalidated on DB server 2 when there is real time replication. So isnt it another perormance hit? These questions are just to get an hint of the performance benfit due to replication because conceptually I feel that there is still the same amount of load. On one hand I know that replication is not for load balancing ( am I right? ) and on the other hand I am doubting why Replication? if the load is same. Pleasecorrect me if I am wrong Thanks & regards, Ratheesh
RE: Replication & LOAD DATA INFILE
During Replication the SLAVE reads data from its MASTER's log. Chances are LOAD DATA gets no logging -- I won't be surprised. -Original Message- From: Jesse [mailto:[EMAIL PROTECTED] Sent: Friday, February 02, 2007 9:44 AM To: MySQL List Subject: Replication & LOAD DATA INFILE I've just performed a LOAD DATA INFILE on the master server, and I've waited a while now, and the data has not shown up in the SLAVE. Does Replication not handle LOAD DATA INFILE? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication & LOAD DATA INFILE
I've just performed a LOAD DATA INFILE on the master server, and I've waited a while now, and the data has not shown up in the SLAVE. Does Replication not handle LOAD DATA INFILE? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication / LOAD DATA FROM MASTER / secondary network interface
Hello List, Has anyone seen any problems using LOAD DATA FROM MASTER on a second slave using a secondary network interface? I have: mysql-master / {172.25.7.20 / eth0, 192.168.7.20 / eth1} / Red Hat EL V.4 / MySQL 5.0.22 mysql-slave1 / 172.25.1.58 / Windows XP / MySQL 5.0.18 mysql-slave2 / 192.168.7.48 / Windows XP / MySQL 5.0.21 My grant was: GRANT REPLICATION SLAVE,SELECT,SUPER,RELOAD ON *.* TO 'replication'@'%' IDENTIFIED BY 'blah' The first two existed harmoniously for many months. Today I wanted to add the second slave. It refused to LOAD DATA FROM MASTER, exclaiming: Error connecting to master: Access denied for user ... I changed the network on mysql-slave2 to use the 172.25.x.x network, and it was able to load the data from the master. I subsequently changed it back to the 192.168.7.x network, and was able to start the slave, which is happily consuming binary logs from the master. My goal was to determine the feasibility of using a second NIC for replication, which apparently I can, although apparently I cannot load data from master. In production, this won't be a problem -- I am starting fresh or I can copy the files noting the master log position(s). Have I discovered a bug? Google and searching the lists.mysql.com don't reveal this exact scenario... Thank you for your time, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Suggestions on db server configuration - Replication & load balancing or Clustering??
Ed Pauley II wrote: I need to come up with a high availability, high performance MySQL server setup. I have two database servers half way across the country from one another being replicated through a VPN. These db servers serve two very busy web sites with multiple applications accessing the db. During busy times we are seeing 1200 to 2000 QPS. For good reason our database servers have high load averages during peek times. I have been looking at MySQL clustering, but due to the fact that our database is rather large the in memory only restriction will make it unfeasible. The other option is load balancing and replication. My problem with this setup is that there will be too many points of failure since there can only be one master for each slave. Not to mention the lag that may be introduced since there would be multiple servers at each location. It is crucial to the operation of the sites that all of the servers stay in sync at all times. Does anyone have any suggestions? - check out http://www.ultramonkey.org/3/ It's not the perfect solution to your problem(s), but it might help. - ian -- +---+ | Ian Sales Database Administrator | | | | "All your database are belong to us" | | ebuyer http://www.ebuyer.com | +---+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Suggestions on db server configuration - Replication & load balancing or Clustering??
I need to come up with a high availability, high performance MySQL server setup. I have two database servers half way across the country from one another being replicated through a VPN. These db servers serve two very busy web sites with multiple applications accessing the db. During busy times we are seeing 1200 to 2000 QPS. For good reason our database servers have high load averages during peek times. I have been looking at MySQL clustering, but due to the fact that our database is rather large the in memory only restriction will make it unfeasible. The other option is load balancing and replication. My problem with this setup is that there will be too many points of failure since there can only be one master for each slave. Not to mention the lag that may be introduced since there would be multiple servers at each location. It is crucial to the operation of the sites that all of the servers stay in sync at all times. Does anyone have any suggestions? -- Ed Pauley II [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication & Load InFile
On Wed, Jul 18, 2001 at 07:38:10PM +0200, Warren van der Merwe wrote: > Hi > > If you use the load infile to insert images into a blob field, and > then the path does not exist when the replication takes place, will > there be an error, or is it clever enough to get the blob data from > the db? It is not that clever... Work is happening in 4.0 to remedy that, however. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 <-- NEW MySQL 3.23.29: up 35 days, processed 269,896,575 queries (89/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication & Load InFile
Hi If you use the load infile to insert images into a blob field, and then the path does not exist when the replication takes place, will there be an error, or is it clever enough to get the blob data from the db? THanks ~ Warren van der Merwe Software Director PRT Trading (Pty) Ltd t/a RedTie Durban, South Africa Cell (+27-83) 262-9163 Office (+27-31) 767-0249 database, mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php