Re: Concurrent Inserts with merged table
Is there a way I can restore the concurrent select feature to a working state without having to shut down the server and rebuild the entire data base? Usually when concurrent insert is not permitted, it's because there are holes in the table that cause inserts to go somewhere other than at the end of the table. Thanks for the suggestion, but apparently you missed the preceding sentence in my post, which said, ...I unpacked the table, even ran REPAIR, OPTIMIZE, and FLUSH TABLES. ... Had to dump the data base, drop it, and then recreate it in order to get concurrent selects working again. That is to say, I tried that already. Didn't work. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Concurrent Inserts with merged table
On Sat, Mar 7, 2009 at 12:10 PM, buf...@biffco.net wrote: Is there a way I can restore the concurrent select feature to a working state without having to shut down the server and rebuild the entire data base? Usually when concurrent insert is not permitted, it's because there are holes in the table that cause inserts to go somewhere other than at the end of the table. Thanks for the suggestion, but apparently you missed the preceding sentence in my post, which said, ...I unpacked the table, even ran REPAIR, OPTIMIZE, and FLUSH TABLES. ... Had to dump the data base, drop it, and then recreate it in order to get concurrent selects working again. No, I saw it. I was just commenting that this is the usual reason why it doesn't work. I should have been clearer about that. Another way to find out whether this is the problem (yes, I know, you already answered this question ;-) is to set concurrent_insert=2 (see http://dev.mysql.com/doc/refman/5.0/en/concurrent-inserts.html ). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Finding replicated database
Sure. Set binlog-do-db to foo and set up a slave, and then try this: create database foo; create database bar; use bar; create table foo.table1(a int); use foo; insert into table1(a) values(1); Now go to the slave and check replication. It's broken: Last_Error: Error 'Table 'foo.table1' doesn't exist' on query. Default database: 'foo'. Query: 'insert into table1(a) values(1)' Why? Because binlog-do-db doesn't do what you think it does. Check the docs again :) Read this: http://dev.mysql.com/doc/refman/5.0/en/replication-rules.html and then this: http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#option_mysqld_binlog-do-db I won't duplicate the documents here. The docs are clear and comprehensive. But pay attention to default database which is what changes when you say use foo or use bar. Notice what the default database is in the error above! On Fri, Mar 6, 2009 at 12:08 PM, Atle Veka at...@flyingcroc.net wrote: Hi, care to elaborate on the pit-falls of binlog-do-db? Thanks, Atle On Wed, 4 Mar 2009, Baron Schwartz wrote: No. But I would recommend removing binlog-do-db and using replicate-wild-do-table=mydb.% on the slave instead. The binlog-do-db approach has many pitfalls anyway, and is likely to burn you at some point, e.g. get the slave out of sync with the master. The bonus of my suggestion is that you'll be able to see from SHOW SLAVE STATUS what's being replicated. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Log and Data directories
Check the archives for the last couple of weeks, I posted some benchmarks from a client's RAID10 4-disk array. Baron On Fri, Mar 6, 2009 at 8:21 PM, dbrb2002-...@yahoo.com wrote: Thanks Baron... Also, curious question.. as you might have used what is called GOOD hw configurarion with RAID 5/10 .. so whats the typical IO (rnd rw) that you archive/expect on high trafficked sites ? --- On Wed, 3/4/09, Baron Schwartz ba...@xaprb.com wrote: From: Baron Schwartz ba...@xaprb.com Subject: Re: MySQL Log and Data directories To: dbrb2002-...@yahoo.com Cc: mysql@lists.mysql.com Date: Wednesday, March 4, 2009, 11:40 AM On Wed, Mar 4, 2009 at 1:22 PM, dbrb2002-...@yahoo.com wrote: On a high read/write load.. is it good to split log (binlogs, innodb txn logs) and data (all tables, innodb tablespace) in different partitions ? Anybody had any experience ? For example; out of 25 disks array with 142GB 1rpm... I would like to keep few disks to logs and rest to data .. is it advised or better to keep everything in spool so that all spindles can be efficiently managed... Thanks in advance There are exceptions to everything I'm about to write, but: Under high read loads, there is no benefit. Under high write loads, there might be. With this many disks, yes. With fewer disks, the relatively trivial sequential log writes will not actually degrade performance much, and the non-trivial performance impact of stealing disks away and dedicating them to the logging workload will make a lot of difference. The real answer is always -- run a benchmark and see. Does the improvement offset things like any kind of penalty the OS imposes on you (e.g. LVM can't take a snapshot across multiple volumes)? -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Confused About Rows and Skipping On Import with MySQL Migration Toolkit 1.1
It's not skipping any rows. When you select records from a database, it gets them in the order that is quickest to retrieve them, not the order they were entered. The natural order is how they are stored on disk. As your database is updated over time, this order may change. If you have an auto increment column, order it by that value. That field will have the order the records were imported in. Brent Baisley On Mar 6, 2009, at 9:10 PM, revDAVE wrote: Hi folks, I'm trying to use MySQL Migration Toolkit 1.1 with MS SQL server 2005 http://dev.mysql.com/downloads/gui-tools/5.0.html - basically all is working great - some tables import no problem - except... I'm trying to import an address table and in the summary it says that there's a few problems like: incorrect string value for column 'street' at row 655 0 rows transferred The problem is when I try to import just 650 or so, then I go to phpmyadim to look - well the rows are not in the original order - they start with addressID 1-2-3-4 etc but randomly skips some so there's no way I can match the imported row 655 to the addressID (655) (they were in order - no deletions) - to find the bad field to fix it... Q: why is it importing rows and seemingly skipping many? Q: how can I figure out which is the REAL row 655 (without counting by hand) to fix the field text string error? Thanks in advance - dave -- Thanks - RevDave Cool @ hosting4days . com [db-lists 09] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=brentt...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Concurrent Inserts with merged table
On Sat, Mar 7, 2009 at 12:10 PM, buf...@biffco.net wrote: Is there a way I can restore the concurrent select feature to a working state without having to shut down the server and rebuild the entire data base? Usually when concurrent insert is not permitted, it's because there are holes in the table that cause inserts to go somewhere other than at the end of the table. ...ran REPAIR, OPTIMIZE, and FLUSH TABLES. ... Had to dump the data base, drop it, and then recreate it in order to get concurrent selects working again. Another way to find out whether this is the problem (yes, I know, you already answered this question ;-) is to set concurrent_insert=2 (see http://dev.mysql.com/doc/refman/5.0/en/concurrent-inserts.html ). Hey thanks for pointing out that. After reading the =2, I recall having skimmed over that section a while ago and forgotten about it. Sounds promising. I'll try it and report back. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Finding replicated database
BTW, same problems occur on the slave side with replicate-do and replicate-ignore. They seem to go away with row-based replications - that's our big hope, anyway. It appears to work so far in test. On Sat, Mar 7, 2009 at 12:41 PM, Baron Schwartz ba...@xaprb.com wrote: Sure. Set binlog-do-db to foo and set up a slave, and then try this: create database foo; create database bar; use bar; create table foo.table1(a int); use foo; insert into table1(a) values(1); Now go to the slave and check replication. It's broken: Last_Error: Error 'Table 'foo.table1' doesn't exist' on query. Default database: 'foo'. Query: 'insert into table1(a) values(1)' Why? Because binlog-do-db doesn't do what you think it does. Check the docs again :) Read this: http://dev.mysql.com/doc/refman/5.0/en/replication-rules.html and then this: http://dev.mysql.com/doc/refman/5.0/en/replication-options-binary-log.html#option_mysqld_binlog-do-db I won't duplicate the documents here. The docs are clear and comprehensive. But pay attention to default database which is what changes when you say use foo or use bar. Notice what the default database is in the error above! On Fri, Mar 6, 2009 at 12:08 PM, Atle Veka at...@flyingcroc.net wrote: Hi, care to elaborate on the pit-falls of binlog-do-db? Thanks, Atle On Wed, 4 Mar 2009, Baron Schwartz wrote: No. But I would recommend removing binlog-do-db and using replicate-wild-do-table=mydb.% on the slave instead. The binlog-do-db approach has many pitfalls anyway, and is likely to burn you at some point, e.g. get the slave out of sync with the master. The bonus of my suggestion is that you'll be able to see from SHOW SLAVE STATUS what's being replicated. -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- Baron Schwartz, Director of Consulting, Percona Inc. Our Blog: http://www.mysqlperformanceblog.com/ Our Services: http://www.percona.com/services.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
