Primary Key Issue while importing BIN Logs
I'm having a difficult time synchronizing my Master and Slave Servers. I've been doing this using MySQL Binary Logs, where the Master rotates the logs at 1 hour times, and the slave downloads it and imports it. What happens why I try to pipe the binlog into mysql is that it reports and error that there is a duplicate entry. Basically what I want to do, is overwrite the duplicate with the data from the binlog, but I can't find a way? thoughts? MySQL Slave import command that I've been using and having fail for i in dbbin*; do /usr/local/bin/mysqlbinlog $i | /usr/local/bin/mysql -f -u root --password=l4ka5Tdj databasename; done I can see there is a -r function in mysqlimport, but of course the BinLogs are in SQL format and this will not work Any help would be GREATLY appreciated p.s. suggesting that I use mysql's built in replication is not help. Thanks, Michael McConnell - 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
Importing of MySQL BinLogs on Slave
I'm having a difficult time synchronizing my Master and Slave Servers. I've been doing this using MySQL Binary Logs, where the Master rotates the logs at 1 hour times, and the slave downloads it and imports it. What happens why I try to pipe the binlog into mysql is that it reports and error that there is a duplicate entry. Basically what I want to do, is overwrite the duplicate with the data from the binlog, but I can't find a way? thoughts? MySQL Slave import command that I've been using and having fail for i in dbbin*; do /usr/local/bin/mysqlbinlog $i | /usr/local/bin/mysql -f -u root --password=l4ka5Tdj databasename; done I can see there is a -r function in mysqlimport, but of course the BinLogs are in SQL format and this will not work Any help would be GREATLY appreciated p.s. suggesting that I use mysql's built in replication is not help. Thanks, Michael McConnell - 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: MySQL is hogging my box
Run the RPM's Binaries provided by MySQL and all will work correctly. There is something MySQL is doing in the RPM's that there not telling us Source users. Michael McConnell - Original Message - From: "djinn" <[EMAIL PROTECTED]> To: "Rodney Broom" <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Tuesday, September 18, 2001 10:58 AM Subject: Re: MySQL is hogging my box > (to answer a couple of posts at once): > > Our machine was seeing less than 20 connects / sec. The problems > continued after we broke largeish (although only about 350,000 records, > indexed) tables into smaller ones and verified/optimized all code that > makes mysql queries. We have a good mix of selects, inserts, updates > but as I said, not more than 20/sec and often quite a bit less. On a > dual PIII 1Ghz with 1Gb of RAM... > > We use ISAM and MyISAM tables exclusively. The largest table has less > than 400,000 rows, the hardest hit (lots of tiny select statements, 3 or > 4 per sec) has maybe 1000 rows. > > The queried tables make use of indices. > > The exact same configuration with the exact same number of requests, > etc, ran perfectly on a RH6.2 box, 800Mhz (single processor), 256 Mb > RAM, using MySQL 3.23.32. We only very recently upgraded and haven't > changed code that calls the database or number of requests, or any other > external factor except hardware and upgraded to mysql 3.23.41 about a > month later. > > Thanks for all the dialogue on this. > jenn > > Rodney Broom wrote: > > > > From: Stefan Pinkert <[EMAIL PROTECTED]> > > > > > There are some others with a similar problem(including me). > > > > Hi guys. I'd be really interested in knowing things like how many requests, and of what type, are being made of your database(s). If you're making a couple of dozen SELECTs per day, then I'd say that you have a problem, but if you're doing 500,000,000 INSERTs and 1 Bil. SELECTs, then I could see some call for your systems to slow down. > > > > --- > > Rodney Broom > > Programmer: Desert.Net > > > > - > > 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 > - 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: MySQL Spawning Tons of Processes
It certainly does seem that 3.23.40+ take considerably more resources to do the exact same thing, than MySQL 3.22.xx Is this correct? > On Sun, Sep 16, 2001 at 10:21:05PM -0700, Michael McConnell wrote: > > > > So what your saying is MySQL is supposed to fork a new processes for > > every connection? I don't think so... > > No. MySQL will spawn a new thread to handle each incoming > connection. It's just that threads on a Linux bear a striking > similarity to processes. > > 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.41-max: up 11 days, processed 205,098,399 queries (213/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
Re: MySQL Spawning Tons of Processes
So what your saying is MySQL is supposed to fork a new processes for every connection? I don't think so... > > > > I'm running MySQL 3.23.42 on a PIII 866 with 1 Gig of RAM. If I > > > > connect 400 requests and and ask for something all at the same time, > > > > MySQL spawns many child proccess (hundreds and hundreds). As a > > > > result of all these processes the system essentailly hangs (Loadavg > > > > of 588 525 310) > > > > > > > > Does anyone know how I can stop this from happening? > > > Is what you're asking for an "easy" query? > > A very simple select statement > Good. > > > How many processes do you get? Significantly more than 400? (I hope > > > not.) > > > > A few less than 400 > > That's expected, then. You get one thread per connection, so this > makes sense. > > > > Have you run vmstat to see if the system is swapping like mad? > > > > 0 Swapping just a CPU Idle of 0 and almost all my ram eaten up. > > Very good. No swapping. And the CPU ought to be busy. > > How long does the situation persist? Are you using MyISAM tables? > > 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.41-max: up 10 days, processed 201,979,372 queries (214/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 > - 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: MySQL Spawning Tons of Processes
> On Sun, Sep 16, 2001 at 04:44:05PM -0700, Michael McConnell wrote: > > > > I'm running MySQL 3.23.42 on a PIII 866 with 1 Gig of RAM. If I > > connect 400 requests and and ask for something all at the same time, > > MySQL spawns many child proccess (hundreds and hundreds). As a > > result of all these processes the system essentailly hangs (Loadavg > > of 588 525 310) > > > > Does anyone know how I can stop this from happening? > > Is what you're asking for an "easy" query? A very simple select statement > How many processes do you get? Significantly more than 400? (I hope > not.) A few less than 400 > Have you run vmstat to see if the system is swapping like mad? 0 Swapping just a CPU Idle of 0 and almost all my ram eaten up. - 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
MySQL Spawning Tons of Processes
I'm running MySQL 3.23.42 on a PIII 866 with 1 Gig of RAM. If I connect 400 requests and and ask for something all at the same time, MySQL spawns many child proccess (hundreds and hundreds). As a result of all these processes the system essentailly hangs (Loadavg of 588 525 310) Does anyone know how I can stop this from happening? Thanks so much. RedHat 7.1 Kernel 2.4.9 Database Size =~ 2 gigs = my.cnf [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking set-variable= key_buffer=256M set-variable= max_allowed_packet=1M set-variable= table_cache=256 set-variable= sort_buffer=1M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=2 log-bin server-id = 1 # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=64M #set-variable = bdb_max_lock=10 # Uncomment the following if you are using Innobase tables #innodb_data_file_path = ibdata1:1000M #innodb_data_home_dir = /usr/local/var/ #innodb_log_group_home_dir = /usr/local/var/ #innodb_log_arch_dir = /usr/local/var/ #set-variable = innodb_mirrored_log_groups=1 #set-variable = innodb_log_files_in_group=3 #set-variable = innodb_log_file_size=5M #set-variable = innodb_log_buffer_size=8M #innodb_flush_log_at_trx_commit=1 #innodb_log_archive=0 #set-variable = innodb_buffer_pool_size=16M #set-variable = innodb_additional_mem_pool_size=2M #set-variable = innodb_file_io_threads=4 #set-variable = innodb_lock_wait_timeout=50 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout Extra start parameters Max_Connections=2000 Max_Connect_Errors=2000 - 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
