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]
New mtop/mkill version available
mtop (MySQL top) monitors a MySQL server showing the queries which are taking the most amount of time to complete. Features include 'zooming' in on a process to show the complete query, 'explaining' the query optimizer information for a query and 'killing' queries. In addition, server performance statistics, configuration information, and tuning tips are provided. mkill (MySQL kill) monitors a MySQL server for long running queries and kills them after a specified time interval. Queries can be selected based on regexes on the user, host, command, database, state and query. New in this version: mtop has new options to control how many seconds elapse before a slow query is highlighted. Available from: http://mtop.sourceforge.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Managing replication logs
Here's another way to do it which works well with multiple slaves: http://mtop.sourceforge.net/utils/purge_replication_log This script is run on the slave and the master. When run on the slave it updates a table in the master which keeps track of the log position for each slave. When run on the master, it finds the oldest logfile being used and purges up to that one. Danny Haworth wrote: I too would be interested in a good way of clearing the binary log files (preferably non disruptive to the server ;-) The only thing thats saving me so far is that the systems capacity is 320Gb. danny Anirudha Kukreti wrote: hi all i have established a two way replication setup my problem is that my hard disk gets occupied by the log files; i tried purging the files but after some time again my hard disk gets filled with the log files mysql, queries - 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 - 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
Re: how do i savely delete replication bin-logs ?
(This is a repost of an ealier answer): Here's one which takes log rotation a step further than mysql provides for. It is run on the slaves to keep track of their position and then run on the master to purge up to the earliest slave position. This is useful because you can't reliably get all the slaves' latest position by looking at a master. If a slave is down temporarily, it will not show up on the master. http://www.chelsea.net/~mprewitt/mtop/utils/purge_replication_log Andreas wrote: Hi, I'm trying to figure out a viable two site configuration. Currently I got as far as to a ultra-early stage 2 box test system in a LAN which does 2-way replication. One runs LINUX and the other one Win2K. I have MySQL 4.0.4 on both. Now the bin-logs are piling up on both machines. Well, sort of ;) If I eventually get the whole thing going, the boxes will be in two different places and talk via dial-up InterNet but not more often than 1 or 2 times a day. How can I ensure that MySQL's logs won't fill up my HDDs ? AFAIK the relevant commands SHOW SLAVE STATUS and SHOW MASTER LOGS depend on a working connection between the boxes. How can I figure out in a script which bin-log can be purged when the slave that needed it temporalily isn't reachable ? I wouldn't dare to purge on each ip-down. --- And by the way ... what is this replication client privilege, I saw in the user table on MySQL.com's Linux binary 4.0.4 ? And by another way ... the Win-Binary has neither REPLICATION SLAVE nor REPLICATION CLIENT privilege in the user table. So the replicant-user must have REPLICATION SLAVE on Linux but oviously has to get FILE on Win. Otherwise 2-way won't work. - 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 - 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
looking for handler_read value
I'm trying to put together some statistics on our queries and am wondering what percentage of our queries involve a full table scan. The variable which kind of tracks this is Handler_read_rnd_next. However, since a query may cause multiple Handler_read_rnd_next events to occur to get all of it's data, I can't directly compare this to the Questions variable. However, if I knew how many total Handler_read requests are happening, I could compare that to Handler_read_rnd_next to get the kind of information I want. However, there is no Handler_read variable. Question: is Handler_read = Handler_read_first + Handler_read_key + Handler_read_next + Handler_read_prev + Handler_read_rnd_next? Thanks, Marc - 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
slave stop command doesn't work if slave thread is blocked
Description: If a 'slave stop' command is while a slave is blocked waiting for a table, the slave thread must be manually killed in order for the slave stop command to complete. How-To-Repeat: mysql show slave status\G *** 1. row *** Master_Host: fosters Master_User: mysql-repl Master_Port: 3306 Connect_retry: 60 Log_File: fosters-bin.1088 Pos: 266992616 Slave_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 1 row in set (0.00 sec) mysql flush tables with read lock; Query OK, 0 rows affected (0.03 sec) (wait for slave thread to block) mysql show processlist\G *** 1. row *** Id: 1 User: system user Host: none db: wssource_prod Command: Connect Time: 28 State: Waiting for table Info: /* WSSource::BusObj::Commit 896@DowJonesParse::Process 356@main */UPDATE dow_jones SET A_ Id = 1027 mysql slave stop; (switch to another command interpreter) mysql show processlist\G *** 1. row *** Id: 1 User: system user Host: none db: wssource_prod Command: Connect Time: 172 State: Waiting for table Info: /* WSSource::BusObj::Commit 896@DowJonesParse::Process 356@main */UPDATE dow_jones SET A_ Id = 1027 *** 2. row *** Id: 2 User: mysql Host: localhost db: NULL Command: Query Time: 126 State: waiting for slave to die Info: slave stop mysql kill 2; Query OK, 0 rows affected (0.00 sec) mysql show processlist\G *** 1. row *** Id: 1 User: system user Host: none db: wssource_prod Command: Connect Time: 213 State: Waiting for table Info: /* WSSource::BusObj::Commit 896@DowJonesParse::Process 356@main */UPDATE dow_jones SET A_ Id = 1027 *** 2. row *** Id: 2 User: mysql Host: localhost db: NULL Command: Killed Time: 167 State: waiting for slave to die Info: slave stop mysql kill 1; Fix: Submitter-Id: Marc Prewitt Originator:Marc Prewitt [EMAIL PROTECTED] Organization: Wall Street Source MySQL support: extended login support Synopsis: slave stop command doesn't work if slave thread is blocked Severity: serious Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.46 (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.46, for sun-solaris2.8 on sparc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.46-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/tmp/mysql.sock Uptime: 9 min 28 sec Threads: 2 Questions: 5353 Slow queries: 1 Opens: 109 Flush tables: 3 Open tables: 1 Queries per second avg: 9.424 Environment: System: SunOS polar 5.8 Generic_108528-15 sun4u sparc SUNW,Sun-Blade-1000 Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc GCC: Reading specs from /opt/gcc/3.0.4/lib/gcc-lib/sparc-sun-solaris2.8/3.0.4/specs Configured with: ../3.0.4/configure --with-gcc-version-trigger=/usr/local/src/gcc/3.0.4/gcc/version.c --prefix=/opt/gcc/3.0.4 --with-system-zlib Thread model: posix gcc version 3.0.4 Compilation info: CC='/opt/gcc/2.95.2/bin/gcc' CFLAGS='-O3 -mcpu=v8 -Wa,-xarch=v8plusa' CXX='/opt/gcc/2.95.2/bin/g++' CXXFLAGS='-O3 -mcpu=v8 -Wa,-xarch=v8plusa -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 11 Aug 22 2001 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146204 Jul 11 18:02 /lib/libc.so.1 lrwxrwxrwx 1 root root 11 Aug 22 2001 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146204 Jul 11 18:02 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/opt/mysql/3.23.46 --with-berkeley-db --with-innobase --localstatedir=/export/DB/mysqldb --with-unix-socket-path=/var/tmp/mysql.sock - 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
Re: Replication (possible) problems | 'show binlog' command is broken?
Mihail, Are you still experiencing these problems? We've seen the same thing happen our 2 of our slaves. It seems to happen for a day or two and then go away. We've done a snoop while this happens and it looks like the master is closing the socket to the slave. However, the master reports no errors in it's logs. Mysql support suggests that it might be a hardware or os bug. I'd like to rule this out and get them to look further into the problem. Can you describe your hardware/os setup of the master and slave and the network connecting them? Thanks, Marc Prewitt -- Marc Prewitt Wall Street Source www.wssource.com Mihail Manolov wrote: Greetings, We got the following lines in our error logfile on one of our slave servers: 020521 13:19:29 Error reading packet from server: Lost connection to MySQL server during query (read_errno 0,server_errno=2013) 020521 13:19:29 Slave: Failed reading log event, reconnecting to retry, log 'db-bin.024' position 833365852 020521 13:19:29 Slave: reconnected to master '[EMAIL PROTECTED]:3306',replication resumed in log 'db-bin.024' at position 833365852 It isn't the first one and I was wondering what could be cause? A network problem? Large data chunk, or something? I tried to see what was the event that cause that problem and the following commands only returns an error: show binlog events in 'db-bin.024' from 833365852 limit 5; ERROR 1064: You have an error in your SQL syntax near 'binlog events in 'db-bin.024' from 833365852 limit 5' at line 1 Any help is appreciated! Mihail Manolov Government Liquidation, LLC 202 467 6868 x.227 [EMAIL PROTECTED] www.govliquidation.com - 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 - 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
Re: AUTO_INCREMENT with Replication
Eric Frazier wrote:
Hi,
That kind of bothers me. But I can see how it might be better in some way
than using a time function. My idea was to use the perl Time::HiRes to make
a unique key adjusted by timezone. As long as the clocks of the two
machines are fairly in since, it should work :) I guess I could also add in
a unique machine id like time integer key + M for master or S for slave S2
S3 etc. Does that sound insane?
Doesn't sound insane at all. We've been using something like that for the
past 4 years (before auto_increment was available in mysql).
Here's what we're using:
sub _NextId {
my $self = shift;
my $pid = 0 x (5 - length($$)) . $$;
$pid .= 0 x (3 - length($COUNTER)) . $COUNTER;
$COUNTER = 0 if $COUNTER++ 998;
return time() . $pid;
}
You could use the hires time and get rid of the counter probably.
Thanks,
Eric
mysql is good
At 05:16 PM 2002-07-03 -0700, you wrote:
to somewhata simulate auto increments, each slave (and master) could be
periodically
assigned a chunk of keys to use, with the stipulation that only that server
can
use those keys. for instance at time 0 (arbitrarily)
server 1 gets: 0-999
server 2 gets:1000-1999
server 3 gets:2000-2999
etc
you'll have to keep track of this info on each server, whether in a table or
file.
then when that chunk is used up, that client can request another chunk of
keys.
(you could also update those key chunks daily, weekly, etc. instead.)
The size of the chunks would want to be determined by the application,
frequency of
communication between master slave, and volume of records going in.
I have *not* implemented such a system, but this could work, depending on
your app.
sean
- Original Message -
From: Eric Frazier [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, July 03, 2002 2:32 PM
Subject: Re: AUTO_INCREMENT with Replication
Well. Good to know. So I guess the only alternative would be to generate
keys by date/time?
I was hoping to avoid that. I am still worried about the timestamp type
not
having good enough resolution. Seconds are pretty broad.
Thanks,
Eric
You're asking for trouble. :-)
AUTO_INCREMENTS are not safe for use in a mutli-master environment.
The scenario you painted will result in a primary key violation on the
master when it reads the value inserted on the slave.
Jeremy
--
Jeremy D. Zawodny, [EMAIL PROTECTED]
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.51: up 34 days, processed 779,275,123 queries (258/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
Leading Edge Marketing Inc.
250-360-2992
-
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
Leading Edge Marketing Inc.
250-360-2992
-
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
-
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
mtop (MySQL top) v0.6.2 released
mtop (MySQL top) monitors a MySQL database showing the queries which are taking the most amount of time to complete. Features include 'zooming' in on a process to show the complete query, 'explaining' the query optimizer information for a query and 'killing' queries. In addition, server performance statistics, configuration information, and tuning tips are provided. New 0.6.2 features: Comments are now stripped from monitor window. Comments will still appear in the zoom/explain views. Also removed leading spaces from display columns. More information available at: http://mtop.sourceforge.net/ mtop is released under the GPL - 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 not staying connected
Description: Replication has been running fine for the past few months. Last night after we did a couple of table rebuilds and a few hundred thousand sql updates, replication started falling behind due to the slave disconnecting every minute or so. Here's what we see in the error log: 020604 7:08:53 Error reading packet from server: Lost connection to MySQL server during query (read_errno 0,server_errno= 2013) 020604 7:08:53 Slave: Failed reading log event, reconnecting to retry, log 'fosters-bin.906' position 218272266 020604 7:08:53 Slave: reconnected to master 'mysql-repl@fosters:3306',replication resumed in log 'fosters-bin.906' at pos ition 218272266 The slave reconnects, runs for about 1-5 minutes and then get's the same error as above. We have another slave which is encountering the same problem but much less frequently (2.5 hours between the last error and it's replication has managed to stay caught up. Both master and servers are running 3.23.46. What do you think is causing the disconnects and is there any way to debug this? How-To-Repeat: Fix: Submitter-Id: Originator:Marc Prewitt Organization: Wall Street Source MySQL support: extended login support Synopsis: Replication not staying connected Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.46 (Source distribution) Server: /usr/local/bin/mysqladmin Ver 8.23 Distrib 3.23.46, for sun-solaris2.8 on sparc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.46-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/tmp/mysql.sock Uptime: 8 hours 36 min 18 sec Threads: 2 Questions: 3970404 Slow queries: 127 Opens: 461 Flush tables: 1 Open tables: 448 Queries per second avg: 128.169 Environment: System: SunOS polar 5.8 Generic_108528-12 sun4u sparc SUNW,Sun-Blade-1000 Architecture: sun4 Some paths: /usr/local/bin/perl /usr/local/bin/make /usr/local/bin/gcc GCC: Reading specs from /opt/gcc/3.0.4/lib/gcc-lib/sparc-sun-solaris2.8/3.0.4/specs Configured with: ../3.0.4/configure --with-gcc-version-trigger=/usr/local/src/gcc/3.0.4/gcc/version.c --prefix=/opt/gcc/3.0.4 --with-system-zlib Thread model: posix gcc version 3.0.4 Compilation info: CC='/opt/gcc/2.95.2/bin/gcc' CFLAGS='-O3 -mcpu=v8 -Wa,-xarch=v8plusa' CXX='/opt/gcc/2.95.2/bin/g++' CXXFLAGS='-O3 -mcpu=v8 -Wa,-xarch=v8plusa -felide-constructors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: lrwxrwxrwx 1 root root 11 Aug 22 2001 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136748 Nov 27 2001 /lib/libc.so.1 lrwxrwxrwx 1 root root 11 Aug 22 2001 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136748 Nov 27 2001 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/opt/mysql/3.23.46 --with-berkeley-db --with-innobase --localstatedir=/export/DB/mysqldb --with-unix-socket-path=/var/tmp/mysql.sock - 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
Re: binlog and replication stuff
FLUSH LOGS safely rotates the binlog. Davide Giunchi wrote: You are right, but my master generate about 150Mb of binlog x day, i a slave fail after 4 day it will reexec a lot of query and take some time. how can i safely rotate the log in master+slaves without restarting them? The Mysql manual explain how to rotate from a `hostnmae`-bin`.002 (or 001) file, but what about if the binlog is always the same? I could do a FLUSH MASTER in the master and then FLUSH SLAVE in the slaves, it wouldn't loose the sync? Regards When a slave crashes or reboots, it should start replicating from where it left off--at least ours do work that way. The current replication state is saved in master.info on the slave and when it starts up again, it should read that file and resume reading the binlog on the master where it left off. The master.info file contains the binlog#, location, user, password, etc... neccessary to restart replication. In you case, does the slave give you any error messages in it's error log about why it couldn't resume replication? If it doesn't try to start replication, have you tried doing a 'slave start' on the slave after it reboots? -Marc -- Davide Giunchi. Membro del FoLUG (Forlí Linux User Group) - http://folug.linux.it GPG Key available on http://www.keyserver.net Fingerprint: 8075 363A B4FA 0196 FEEC AF9C 19A9 66C7 CDAB D0D5 - 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
Re: binlog and replication stuff
If the master crashes, you can move the ip of you master to the slave machine and it can become the new master. However, to move back to teh master, you will need to copy the stuff back to the master. There's some good information in the manual about how to plan disaster recovery: http://www.mysql.com/doc/R/e/Replication_FAQ.html [EMAIL PROTECTED] wrote: How about if master crashes?...is there anyway to recover?...aside from copying all stuff from slave to master... R.B.Roa PhilCom Corporation Tel. No. 858- Mobile No. (63) (919-xxx) -Original Message- From: Davide Giunchi [SMTP:[EMAIL PROTECTED]] Sent: Wednesday, April 10, 2002 5:37 PM To: Marc Prewitt Cc: [EMAIL PROTECTED] Subject:Re: binlog and replication stuff You are right, but my master generate about 150Mb of binlog x day, i a slave fail after 4 day it will reexec a lot of query and take some time. how can i safely rotate the log in master+slaves without restarting them? The Mysql manual explain how to rotate from a `hostnmae`-bin`.002 (or 001) file, but what about if the binlog is always the same? I could do a FLUSH MASTER in the master and then FLUSH SLAVE in the slaves, it wouldn't loose the sync? Regards When a slave crashes or reboots, it should start replicating from where it left off--at least ours do work that way. The current replication state is saved in master.info on the slave and when it starts up again, it should read that file and resume reading the binlog on the master where it left off. The master.info file contains the binlog#, location, user, password, etc... neccessary to restart replication. In you case, does the slave give you any error messages in it's error log about why it couldn't resume replication? If it doesn't try to start replication, have you tried doing a 'slave start' on the slave after it reboots? -Marc -- - 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
Re: How to automate bin-log clean-up with replicated MySQL servers?
We have a slightly smarter script which checks each slave to find the
furthest one behind and purges up to that number. However, it currently
figures out who the slaves are by checking a process status on the
master. Unfortunately, this doesn't work if one of your slaves is down.
So, I'm going to rewrite it to check the known slaves.
-Marc
Jeremy Zawodny wrote:
On Tue, Apr 09, 2002 at 10:15:12AM +0200, paul wrote:
Hello all,
I have setup a replication between two MySQL v4.01a servers on
FreeBSD, which works really fine. The only problem is that since we
have MANY updates on the master database, the bin-logs grow very
fast. We need to do a manual clean-up at least once every other day,
otherwise the disk space will get exhausted: SHOW SLAVE STATUS on
the slave, write down the 'Log_File' name, and pass that to PURGE
MASTER LOGS TO 'logname' on the master.
Does anybody have an idea how we could automate this procedure, or
should we manage the log-files is another way?
Here's a quick home-grown solution that works well for us. It's not
the smartest approach, but we never let a slave get too far behind
(they're all closely monitored), it works qutie well.
This is purge_master_logs:
---snip---
#!/usr/local/bin/perl -w
#
# $Source: /CVSROOT/yahoo/finance/mysql/bin/purge_master_logs,v $
## On a mysql server, purge the replication logs if there are too
## many sitting around and sucking up disk space.
$|++;
use strict;
use RunMutex '/tmp/.write_heartbeat.lock';
use DBIx::DWIW;
my $MIN_LOGS = 4; ## keep at least three binary logs around
my $db = DBIx::DWIW-Connect(
DB = mysql,
User = root,
Pass = password,
Host = 'localhost',
);
if (not $db)
{
die Couldn't connect to database!;
}
my @logs = $db-FlatArray(SHOW MASTER LOGS);
## see if there are enough to bother
if (@logs $MIN_LOGS)
{
exit;
}
## if so, figure out what the last one we want to kee is
my $last_log = $logs[-$MIN_LOGS];
print last log is $last_log\n unless $ENV{CRON};
## and purge the rest
$db-Execute(PURGE MASTER LOGS TO '$last_log');
exit;
__END__
---snip---
Jeremy
--
Jeremy D. Zawodny, [EMAIL PROTECTED]
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936
MySQL 3.23.47-max: up 61 days, processed 1,654,071,336 queries (311/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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: binlog and replication stuff
Yes. Davide Giunchi wrote: FLUSH LOGS in the master and all is ok? binlog will be rotate and slaves will see it, continue to work and update master.info? Regads Il 15:31, giovedì 11 aprile 2002, hai scritto: FLUSH LOGS safely rotates the binlog. Davide Giunchi wrote: You are right, but my master generate about 150Mb of binlog x day, i a slave fail after 4 day it will reexec a lot of query and take some time. how can i safely rotate the log in master+slaves without restarting them? The Mysql manual explain how to rotate from a `hostnmae`-bin`.002 (or 001) file, but what about if the binlog is always the same? I could do a FLUSH MASTER in the master and then FLUSH SLAVE in the slaves, it wouldn't loose the sync? -- Davide Giunchi. Membro del FoLUG (Forlí Linux User Group) - http://folug.linux.it GPG Key available on http://www.keyserver.net Fingerprint: 8075 363A B4FA 0196 FEEC AF9C 19A9 66C7 CDAB D0D5 - 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
Re: Replication incompleteness problem
A couple of questions below:
Wendell Dingus wrote:
...
cron entries:
-
Server2:
20 4 * * * /etc/rc.d/init.d/mysql stop 1/dev/null 2/dev/null
#
25 4 * * * /bin/rm -f /var/lib/mysql/master.info 1/dev/null 2/dev/null
Why are you deleting master.info? It's my understanding that this is
where the replication position is stored. The slave will loose it's
position if you do this, I believe.
#
30 4 * * * /etc/rc.d/init.d/mysql start 1/dev/null 2/dev/null
Server1:
0 4 * * * /usr/bin/mysql -uroot -px /var/lib/mysql/fl 1/dev/null
2/dev/null
Note: File fl contains flush master;
Are you sure the command you are using is flush master and not PURGE
MASTER LOGS TO {logname}
That is normally how the old master logs are deleted.
-
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
Re: binlog and replication stuff
When a slave crashes or reboots, it should start replicating from where it left off--at least ours do work that way. The current replication state is saved in master.info on the slave and when it starts up again, it should read that file and resume reading the binlog on the master where it left off. The master.info file contains the binlog#, location, user, password, etc... neccessary to restart replication. In you case, does the slave give you any error messages in it's error log about why it couldn't resume replication? If it doesn't try to start replication, have you tried doing a 'slave start' on the slave after it reboots? -Marc Davide Giunchi wrote: Hello. I'm using MySQL-3.23.38 on 5 RedHat 7.1/7.2 servers composed by one master and 4 mysql slave, every write is made on the master and then replicated to the slave. The problem is that if one client crash or must be rebooted i've to: - on the mysql master stop mysql, delete binlogfile, tar all the data in a file and then restart mysql - on ALL the mysql slave stop mysql, delete all data, get the tar file from the master and the restart mysql so i've to restart all mysql slave even if only one is really crash, if i don't do this i loose the syncronization between master and slave (as shown in the mysql.log). As your experience what do you do when a mysql slave crash and loose the sync? I would like to use a method to restart only the crashed client without touch other's slave and without restart master. I could make a backup script that get all the data from the the master (like mysqlhotcopy) without restart nothing, but what about the binlog file? i could FLUSH them in the server but then other slave will loose the sync I appreciate any help or suggestion. Best Regards. -- Davide Giunchi. Membro del FoLUG (Forlí Linux User Group) - http://folug.linux.it GPG Key available on http://www.keyserver.net Fingerprint: 8075 363A B4FA 0196 FEEC AF9C 19A9 66C7 CDAB D0D5 - 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 - 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
Re: Problem with Sun Os
Looks like mysql was compiled to use a shared version of zlib. You'll need to get it from: http://www.gzip.org/zlib/ Andrea Soracchi wrote: Hello, When i try to launch my server mysqld I have the follwing result: ld.so.1: ./bin/my_print_defaults: fatal: libz.so.1: open failed: No such file or directory I have installed the following version mysql-3.23.49 (Official MySQL binary) Environment: System: SunOS lan-ba14ub-04 5.8 Generic_108528-13 sun4u sparc SUNW,UltraSPARC-IIi-cEngine Architecture: sun4 Some paths: /usr/bin/perl /usr/ccs/bin/make /usr/local/bin/gcc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='-O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-O3 -fno-omit-frame-pointer -felide-con structors -fno-exceptions -fno-rtti' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1768120 Feb 16 00:02 /lib/libc.a lrwxrwxrwx 1 root root 11 Feb 26 09:59 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146392 Feb 16 00:02 /lib/libc.so.1 -rw-r--r-- 1 root bin 1768120 Feb 16 00:02 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Feb 26 09:59 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1146392 Feb 16 00:02 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql '--with-comment=Official MySQL binary' --with-extra-charsets=complex --with-server-suffix= --enable-thread-safe-client --enable-local-infile --e nable-assembler --disable-shared Perl: This is perl, version 5.005_03 built for sun4-solaris Thanks in advanced Andrea Soracchi - 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
Re: How can I kill the slow query automatically?
Without setting up replication, you could use 'UPDATE LOW PRIORITY': http://www.mysql.com/doc/U/P/UPDATE.html If you do setup replication, you can set the replica database to use low priority updates too by using the --low-priority-updates option to mysqld: http://www.mysql.com/doc/C/o/Command-line_options.html You should try both of these before switching to innodb as the performance is not as fast as myisam. -Marc Ken Menzel wrote: Hi, There is no way to kill slow queries autmatically unless you were to write a script (maybe in perl or php?) to do this job. I have a similar problem and am thinking of replicating the database just to avoid this problem. I would then direct certain type of queries to the replicated server. The issue I have is I need selects not to lock the table for update! On FreeBSD this seems also to cause the CPU usage to jump very high. I was thinking of writing some test scripts, but I think the real answer for both of us may be to change to the InnoDB table type. Have you tried this? use ALTER TABLE type=innodb, after you have intalled mysql-max and created the tablespace. I hope this helps, Ken - Original Message - From: °í¼ø¹Î [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, April 03, 2002 5:28 AM Subject: How can I kill the slow query automatically? My DB has many update queries and a few select queries. Sometimes select queries take very long time so update can't proceed. I can kill the select query manually. But I can't check the status all the time. Are there any ways to kill slow query automatically? If you know, please let me know. Any information will be appreciated. - 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
Re: Keeping MySQL Databases Heathly
We run a CHECK TABLE nightly on every table (see: http://www.mysql.com/doc/C/H/CHECK_TABLE.html) and I would HIGHLY recommend doing so. If the check table fails, we run a REPAIR TABLE. We also used to do a REPAIR TABLE EXTENDED on weekends to optimize indexes until our db got too big for that to finish in a reasonable amount of time. Alex Pilson wrote: Do MySQL server administrators recommend running a cron script daily to isamchk? Is there any other things that should be run daily to keep MySQL running top-notch? If so, does anyone have any pointers to a script that has already been developed? Thanks. -- - Alex Pilson FlagShip Interactive, Inc. [EMAIL PROTECTED] 404.728.4417 404.642.8225 CELL // Web Design // Lasso Application Development // Filemaker Pro / SQL Development // Sonic Solutions Creator Authoring // Apple DVD Studio Pro Authoring // Macromedia Director/Flash Authoring - - 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 - 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
Re: Keeping MySQL Databases Heathly
And here's a script that will do the check/repair for you. It has some
custom logging libs in it that you'll have to remove to make it work
(Admutil and Getpassword), but you'll get the general idea.
Alex Pilson wrote:
Do MySQL server administrators recommend running a cron script daily
to isamchk?
Is there any other things that should be run daily to keep MySQL
running top-notch? If so, does anyone have any pointers to a script
that has already been developed?
Thanks.
--
#!/usr/local/bin/perl
#
# $Id: 60mysql,v 1.30 2002-04-07 09:55:24-04 mprewitt Exp $
# $Source: /usr/local/src/daily/0.1/RCS/60mysql,v $
# $Locker: $
# Copyright 2002 (c) Chelsea Networks, All rights reserved
use strict;
use Admutil;
use File::Basename;
use GetPassword (); # import no exports
use Getopt::Long;
my $opt_extended_check;
GetOptions(
'extended-check!' = \$opt_extended_check,
) || print STDERR Usage: 60mysql [--extended-check]\n;
my $script = new Admutil;
my $script_name = basename( $0 );
$script-StartLog( -logdir=/var/adm/daily/$script_name, -private=1,
-days=14 );
$script-ErrMailTo(mail-oncall);
$script-StartSyslog(-facility=local7);
#
# End standard Admutil header here. Begin regular script.
#
# Look at comments in /usr/local/httpd/conf/wusage.conf.template for how
to
# configure things.
#
use DBI();
use Sys::Hostname;
my $hostname = hostname;
my $dir = /export/DB/mysqldb;
unless ( -d $dir ) {
print $script_name does't run on $hostname because it is not a mysql
server\n;
exit;
}
my $password = GetPassword::GetPassword( -context='mysql', -user='mysql'
) or
$script-Error( -level=CRIT,
-text=Unable to get password for mysql );
my $username = mysql;
#
# Database checks first.
#
my $day = (localtime)[6];
my $check_type = FAST;
# extended check takes too long. run manually.
# if ($day == 0){
# #EXTENDED Do a full key lookup for all keys for each row.
# print running extended check\n;
# $check_type = EXTENDED;
# } else {
# #FAST Only check tables which haven't been closed properly.
# $check_type = FAST;
# }
if ($opt_extended_check){
#EXTENDED Do a full key lookup for all keys for each row.
print running extended check\n;
$check_type = EXTENDED;
}
#
# First, connect to the dataserver.
#
my $dbh = DBI-connect( DBI:mysql:mysql, $username, $password,
{'RaiseError' = 0 } )
||
$script-Error( -level=CRIT,
-text=Unable to connect to mysql [$DBI::errstr] );
my $sth = $dbh-prepare( SHOW DATABASES );
$sth-execute ||
$script-Error( -level=CRIT,
-text=Unable to obtain list of databases
[$DBI::errstr] );
while( my $db = ( $sth-fetchrow_array )[0] ) {
next if ( $db eq binlogs || $db eq RCS || $db eq lost+found
|| $db eq hash || $db eq OUTPUT );
print Checking database $db\n;
my $sth = $dbh-do( use $db ) ||
$script-Error( -level=WARN,
-text=Unable to access $db for checking
[$DBI::errstr] );
my $sth = $dbh-prepare('show tables')
|| $script-Error( -level=WARN,
-text= show tables prepare failed
[$DBI::errstr] );
$sth-execute
|| $script-Error( -level=WARN,
-text= show tables execute failed
[$DBI::errstr] );
my @tables = map $_-[0], @{ $sth-fetchall_arrayref };
if ( @tables ) {
foreach my $table ( @tables ) {
#
# if CHECK fails REPAIR TABLE should be run(look in sub
statement below)
#
my $sql_check = CHECK TABLE $table $check_type;
my $sql_analyze = ANALYZE TABLE $table;
my $sql_repair = REPAIR TABLE $table;
print table $table\n;
if( statement($sql_check) ) {
#
# Analyze it.
#
# statement($sql_analyze);
} else {
#
# Uh-oh...better repair it.
#
statement($sql_repair);
}
}
} else {
if ( $sth-errstr ) {
$script-Error(-level=WARN,
-text= Unable to enumerate tables from
database $db);
}
}
}
#
# Did the slave die?
#
sub check_slave {
if ( my $sth = $dbh-prepare('show slave status')) {
if ( $sth-execute ) {
return ( $sth-fetchrow_array )[0,4,5,6,10];
} else {
return $script-Error(-level=WARN,
-text= Unable to execute show slave
status
[$DBI::errstr] );
}
} else {
return $script-Error(-level=WARN,
-text= Unable to prepare show slave status
[$DBI::errstr] );
}
}
my( $host, $file, $pos1, $status, $error ) = check_slave;
if ( $host $file $status eq No ) {
$dbh-do( SLAVE START ) ||
$script-Error(-level=WARN,
Re: Mysql binary logs
You might want to use the mysqlbinlog utility to look at the binlog. The binlog format has changed at least once in past versions and could change again. However, the output from mysqlbinlog just a straight text format of updates/inserts/deletes which happen to a database. -Marc Konstantin Yotov wrote: Hello! :) I want to ask about binary log - what kind of info it writes and how I can use it. Is this info is connected with optimizing mysql work. Thanks. Kosyo __ Do You Yahoo!? Yahoo! Greetings - send holiday greetings for Easter, Passover http://greetings.yahoo.com/ - 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 - 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
Re: Adding an index to a table
James Riordon wrote: At 15:07 +0800 03/28/2002, Sammy Lau was thought to have said : ?http://www.mysql.com/doc/C/R/CREATE_INDEX.html Granted, I should have looked before I spoke for the first question. 2 - Do I have to do anything special like stop requests to the server while I add the index. 3 - How safe is this to do? I do not see the answer for #2 and #3 in the recommended reading. Can anyone comment on those questions, or should I just backup and dive on in? You do not need to stop requests while building the index. However, the table will be locked while the index is being created so you may not want to do it during a high traffic period. The time it takes to build the index depends upon how many rows are in the table and whether or not the index is unique (unique indexes will take longer to build). It's safe to do. The worst thing that could happen is that you kill your mysql server mid-index create and then you will need to do a recover table when you bring it up but no data will be lost due to the in-process index create. - 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
Re: =?iso-8859-1?Q?Capacité_Mysql?=
If I understand you correctly, you are interested in backing up a database without using the mysqldump command, correct? You have a couple of options: 1) Issue a 'flush tables with read lock', backup the binary files of the database, issue a 'unlock tables'. However, no one will be able to access the database during the period from the flush tables to the unlock tables. (see: http://www.mysql.com/doc/F/L/FLUSH.html) 2) Run replication and backup the slave with the above method. Access to the main database will not be affected. Tony Jarriault wrote: Bonjour, J'utilise Mysql 3.23... sous un environnement Solaris 7. La taille de la base de donnée est de 1Go 5. Quelles sont les limites pour le bon fonctionnment de mysql ? Existe il un moyen plus rapide de faire des copies de bases que le dump ? Merci Cordialement, Tony Jarriault --- Service webmaster : mailto:[EMAIL PROTECTED] Tel : 01-34-49-06-69 MGN : http://www.mgn.fr --- Tony Jarriault mailto:[EMAIL PROTECTED] Tel : 01-34-49-06-43 MATRA GLOBAL NETSERVICES Societé du groupe PROSODIE 8, rue Grange Dame Rose 78140 Vélizy - 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 - 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
