Re: increase with inserts/updates in partitioned table when knowing which partition?
This is also what I want to know. Waiting for somebody's help. On Thu, Jan 17, 2008 at 12:39 AM, Britske <[EMAIL PROTECTED]> wrote: > > Hi, > > I have a partitioned table using hash. For the example lets say I have N > partitions. Selects perform well due to the partitioning. However I > noticed > that inserts and updates slow down considerably when the number of > partitions goes up. (I think because if needs to inspect / open all > partitions of the table) > > Since hashing uses a modulo function I figured that if I inserted rows > which > have the same modulo N in batches each batch of rows is inserted in exactly > one partition (correct?) > I figured this would speed up inserts/ updates since only one partition > needs to be inspected for every batch. > > However, some measurements show me that this doesn't matter at all. > > Could anyone tell me what's incorrect in my thinking, or what I am missing, > > Thanks in advance, > Geert-Jan > > BTW: I already asked this question, but wasn't sure the header asked the > correct question. > -- > View this message in context: > http://www.nabble.com/increase-with-inserts-updates-in-partitioned-table-when-knowing-which-partition--tp14885273p14885273.html > Sent from the MySQL - General mailing list archive at Nabble.com. > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: error while starting replication for the first time
Replication is a complicated process.I can't see what you do and what happened for your computer. From you description,i think you maybe save some old file in your entironment and some configer parmater point to it. So,the best way is sending you actions to me step by step and we will analyse what problem happened. The simple way is re-install your salve and recover mysql data and rebuild Replication environment. - Original Message - From: "Ananda Kumar" <[EMAIL PROTECTED]> To: "Moon's Father" <[EMAIL PROTECTED]> Cc: "Mike" <[EMAIL PROTECTED]>; "Krishna Chandra Prajapati" <[EMAIL PROTECTED]>; "MYSQL General List" Sent: Monday, May 26, 2008 1:03 PM Subject: Re: error while starting replication for the first time > Hi All, > The issue has been solved, we removed relay-log.index files which was > refering to the old relay-log file. After this the CHANGE MASTER worked and > replication is going on fine. > > regards > anandkl > > > On 5/24/08, Moon's Father <[EMAIL PROTECTED]> wrote: >> >> I think you should check the error with perror at first. >> >> On Sat, May 24, 2008 at 12:46 AM, Mike <[EMAIL PROTECTED]> wrote: >> >>> On Fri, May 23, 2008 at 12:08 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: >>> >>> > Hi Mike, >>> > Since i have done a fresh backup from master (with master db down), and >>> > copied over the same to slave. Then why is the slaving looking for old >>> > relay-log. I also did the "RESET SLAVE", bit still getting error. >>> > >>> > I am wondering, why is the slave looking for old relay-logs >>> > >>> >>> >>> Your my.cnf might have relaylog info in it. >>> You might have a master.info or relay log files in your mysql directory. >>> >>> The slave does need relay logs to replicate. So if you keep use RESET >>> MASTER after you backup the mysql then you backup is worthless. >>> >>> Tell me the steps you are using? >>> >>> >>> >>> > >>> > On 5/23/08, Mike <[EMAIL PROTECTED]> wrote: >>> >> >>> >> Ok, since you do not have the bin log you need to start over again with >>> >> the replication and do either a >>> >> mysqldump or get the rawdata. then once you have the data then you can >>> >> start replication again. >>> >> >>> >> >>> >> >>> http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-backups-mysqldump.html >>> >> >>> >> >>> http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-backups-rawdata.html >>> >> >>> >> On Fri, May 23, 2008 at 10:37 AM, Ananda Kumar <[EMAIL PROTECTED]> >>> wrote: >>> >> >>> >>> RESET MASTER...delete all the bin logs. Infact i tried this on the >>> slave >>> >>> as it also a MASTER to itself, but the error kept on coming. >>> >>> >>> >>> regards >>> >>> anandkl >>> >>> >>> >>> >>> >>> On 5/23/08, Mike <[EMAIL PROTECTED]> wrote: >>> >>> >>> >>> On Fri, May 23, 2008 at 8:13 AM, Ananda Kumar <[EMAIL PROTECTED]> >>> wrote: >>> >>> > Files is owned by mysql, but the point is, these relay-log files are >>> > not >>> > present. >>> > Before setting up the slave, i cleaned up all files. >>> > >>> >>> If you want the logs back you could use RESET MASTER maybe. >>> http://dev.mysql.com/doc/refman/5.0/en/reset-master.html >>> >>> Let me know. :-) >>> >>> >>> > >>> > regards >>> > anandkl >>> > >>> > >>> > On 5/23/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> >>> wrote: >>> > > >>> > > Please check the ownership of the files copied on the slave. >>> > > >>> > > Give permissions by chown -R mysql:mysql >>> > > >>> > > On Fri, May 23, 2008 at 2:47 PM, Ananda Kumar <[EMAIL PROTECTED]> >>> > wrote: >>> > > >>> > >> Hi Krishna, >>> > >> As i said, this is a complete copy from master db to slave. >>> > >> I have deleted all old files from slave and setting up from >>> scratch, >>> > by >>> > >> taking a complete backup from master, copying over to slave and >>> > using the >>> > >> change master command with BIN LOG and POSITION taken before >>> taking >>> > a backup >>> > >> copy from master. >>> > >> >>> > >> >>> > >> >>> > >> >>> > >> On 5/23/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> >>> > wrote: >>> > >>> >>> > >>> check permissions >>> > >>> >>> > >>> On Fri, May 23, 2008 at 12:33 PM, Ananda Kumar < >>> [EMAIL PROTECTED]> >>> > >>> wrote: >>> > >>> >>> > Hi All, >>> > I shutdown my master, took a db files backup. Copied it over to >>> > slave >>> > machine. >>> > I executed "change master" command, then when i start slave >>> "slave >>> > start", i >>> > get the below error. I did "reset slave", but still getting >>> same >>> > error, >>> > what >>> > could be the reason, and how to fix it. >>> > >>> > 080522 23:04:05 [ERROR] Failed to open log (file >>> > '/data/mysql-log/relay-log/relay.000791', err
Re: error while starting replication for the first time
Hi All, The issue has been solved, we removed relay-log.index files which was refering to the old relay-log file. After this the CHANGE MASTER worked and replication is going on fine. regards anandkl On 5/24/08, Moon's Father <[EMAIL PROTECTED]> wrote: > > I think you should check the error with perror at first. > > On Sat, May 24, 2008 at 12:46 AM, Mike <[EMAIL PROTECTED]> wrote: > >> On Fri, May 23, 2008 at 12:08 PM, Ananda Kumar <[EMAIL PROTECTED]> wrote: >> >> > Hi Mike, >> > Since i have done a fresh backup from master (with master db down), and >> > copied over the same to slave. Then why is the slaving looking for old >> > relay-log. I also did the "RESET SLAVE", bit still getting error. >> > >> > I am wondering, why is the slave looking for old relay-logs >> > >> >> >> Your my.cnf might have relaylog info in it. >> You might have a master.info or relay log files in your mysql directory. >> >> The slave does need relay logs to replicate. So if you keep use RESET >> MASTER after you backup the mysql then you backup is worthless. >> >> Tell me the steps you are using? >> >> >> >> > >> > On 5/23/08, Mike <[EMAIL PROTECTED]> wrote: >> >> >> >> Ok, since you do not have the bin log you need to start over again with >> >> the replication and do either a >> >> mysqldump or get the rawdata. then once you have the data then you can >> >> start replication again. >> >> >> >> >> >> >> http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-backups-mysqldump.html >> >> >> >> >> http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-backups-rawdata.html >> >> >> >> On Fri, May 23, 2008 at 10:37 AM, Ananda Kumar <[EMAIL PROTECTED]> >> wrote: >> >> >> >>> RESET MASTER...delete all the bin logs. Infact i tried this on the >> slave >> >>> as it also a MASTER to itself, but the error kept on coming. >> >>> >> >>> regards >> >>> anandkl >> >>> >> >>> >> >>> On 5/23/08, Mike <[EMAIL PROTECTED]> wrote: >> >> >> >> On Fri, May 23, 2008 at 8:13 AM, Ananda Kumar <[EMAIL PROTECTED]> >> wrote: >> >> > Files is owned by mysql, but the point is, these relay-log files are >> > not >> > present. >> > Before setting up the slave, i cleaned up all files. >> > >> >> If you want the logs back you could use RESET MASTER maybe. >> http://dev.mysql.com/doc/refman/5.0/en/reset-master.html >> >> Let me know. :-) >> >> >> > >> > regards >> > anandkl >> > >> > >> > On 5/23/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> >> wrote: >> > > >> > > Please check the ownership of the files copied on the slave. >> > > >> > > Give permissions by chown -R mysql:mysql >> > > >> > > On Fri, May 23, 2008 at 2:47 PM, Ananda Kumar <[EMAIL PROTECTED]> >> > wrote: >> > > >> > >> Hi Krishna, >> > >> As i said, this is a complete copy from master db to slave. >> > >> I have deleted all old files from slave and setting up from >> scratch, >> > by >> > >> taking a complete backup from master, copying over to slave and >> > using the >> > >> change master command with BIN LOG and POSITION taken before >> taking >> > a backup >> > >> copy from master. >> > >> >> > >> >> > >> >> > >> >> > >> On 5/23/08, Krishna Chandra Prajapati <[EMAIL PROTECTED]> >> > wrote: >> > >>> >> > >>> check permissions >> > >>> >> > >>> On Fri, May 23, 2008 at 12:33 PM, Ananda Kumar < >> [EMAIL PROTECTED]> >> > >>> wrote: >> > >>> >> > Hi All, >> > I shutdown my master, took a db files backup. Copied it over to >> > slave >> > machine. >> > I executed "change master" command, then when i start slave >> "slave >> > start", i >> > get the below error. I did "reset slave", but still getting >> same >> > error, >> > what >> > could be the reason, and how to fix it. >> > >> > 080522 23:04:05 [ERROR] Failed to open log (file >> > '/data/mysql-log/relay-log/relay.000791', errno 2) >> > 080522 23:04:05 [ERROR] Failed to open the relay log 'FIRST' >> > (relay_log_pos >> > 4) >> > >> > I am using mysql 5.0.41 community version, on debain. >> > >> > regards >> > anandkl >> > >> > >>> >> > >>> >> > >>> >> > >>> -- >> > >>> Krishna Chandra Prajapati >> > >>> MySQL DBA, >> > >>> Ed Ventures e-Learning Pvt.Ltd. >> > >>> 1-8-303/48/15, Sindhi Colony >> > >>> P.G.Road, Secunderabad. >> > >>> Pin Code: 53 >> > >>> Office Number: 040-66489771 >> > >>> Mob: 9912924044 >> > >>> URL: ed-ventures-online.com >> > >>> Email-id: [EMAIL PROTECTED] >> > >> >> > >> >> > >> >> > >> >> > > >> > > >> > > >> > > -- >> > > Krishna Chandra Prajapati >> > > MySQL DBA, >> > > Ed Ventures e
Re: best way to add a new column to a table with 60+ million records
Also, The below method will not add it the default values for date's and also will create the indexes and if any column is set as auto increment, that also need to be take care. So, is there a way to take care of the above, in the create table statement itself. regards anandkl On 5/26/08, Moon's Father <[EMAIL PROTECTED]> wrote: > > Just execute as the following. > 1、create table b like a; > 2、alter table b add ; > 3、insert into b select a. > 4、alter table b rename to a; > > On Wed, Apr 16, 2008 at 11:08 PM, Arun Kumar PG <[EMAIL PROTECTED]> > wrote: > > > given that my table is in myisam, there are some hacky way of doing this > > (referred to this online) like creating table without keys, insert data > > from > > .myd, copy of .frm, .myi files for same table created with keys, and then > > doing a repair table on new table.. > > > > but i was wondering if there is an easy way to do it. > > > > > > On Wed, Apr 16, 2008 at 5:30 PM, Arun Kumar PG <[EMAIL PROTECTED]> > > wrote: > > > > > hi, > > > > > > is there any other best way add a new column to an existing table > having > > > 60+ million records. alter is taking more than 1.5 hours.. what are the > > best > > > practices around this. quick help will be appreciated. > > > > > > -- > > > cheers, > > > > > > - a > > > > > > > > > > -- > > cheers, > > > > - a > > > > > > -- > I'm a mysql DBA in china. > More about me just visit here: > http://yueliangdao0608.cublog.cn >
Re: best way to add a new column to a table with 60+ million records
Just execute as the following. 1、create table b like a; 2、alter table b add ; 3、insert into b select a. 4、alter table b rename to a; On Wed, Apr 16, 2008 at 11:08 PM, Arun Kumar PG <[EMAIL PROTECTED]> wrote: > given that my table is in myisam, there are some hacky way of doing this > (referred to this online) like creating table without keys, insert data > from > .myd, copy of .frm, .myi files for same table created with keys, and then > doing a repair table on new table.. > > but i was wondering if there is an easy way to do it. > > > On Wed, Apr 16, 2008 at 5:30 PM, Arun Kumar PG <[EMAIL PROTECTED]> > wrote: > > > hi, > > > > is there any other best way add a new column to an existing table having > > 60+ million records. alter is taking more than 1.5 hours.. what are the > best > > practices around this. quick help will be appreciated. > > > > -- > > cheers, > > > > - a > > > > > -- > cheers, > > - a > -- I'm a mysql DBA in china. More about me just visit here: http://yueliangdao0608.cublog.cn
Re: Site Attack/Failure Recovery
Is there anyway of doing what is described below with version 5 or will I l have to wait for MySQL 6.0 PlanetMySQL Blog: MySQL 6.0 Feature #2: Online Backup Alexander Nozdrin, Chuck Bell, Lars Thalmann, Peter Gulutzan, Rafal Somla BACKUP DATABASE copies all data and metadata in one or more MySQL databases, into an "image file". RESTORE reads an image file and rewrites all the data and metadata in one or more MySQL databases. So if you lose a database, you can recover all of it as of the time of the last BACKUP DATABASE statement.And then you can re-run the statements in MySQL's binary log to recover "from the time of the last BACKUP DATABASE statement", "to the time that the database loss occurred". In other words ... Careful Use of Online Backup will protect from database loss. Rob Wultsch wrote: On Wed, May 14, 2008 at 10:25 PM, John Comerford <[EMAIL PROTECTED]> wrote: 2) Incremental Backups - say one every half hour, then a script to transfer that to an off site machine that way I can get the DB back to within the last good half hour... http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Being compromised is not inevitable, but hardware failure is. Having trusted (an therefore tested) backups is the only way to operate. Is there some practice in particular you are concerned about? Blanket suggestion: Don't escape things manually, have the db (or emulation) do it for you using prepared statements. It is easier to code this way, and much more secure in the long run. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger problem
On May 15, 2008, at 4:30 AM, rustam ershtukaev wrote: I have been trying to write a trigger in mysql, but can't get it to work. It's really simple,i just need my trigger to add varchar value to a table on insert if postcode = 1000. Where does postcode come from? Your SELECT statement appears to have no relation to the row to be inserted. It also appear that it will always set v_postcode to 1000 if the departement table contains *any* rows with a postcode of 1000. this is how i did it: delimiter | drop trigger testdep| create trigger testdep before insert on departements for each row begin declare v_postcode INTEGER; declare v_place VARCHAR; select departement_postcode into v_postcode from departement where departement_postcode = 1000; IF v_postcode = 1000 then update departementen set departement_place = 'New York' where departement_postcode = 1000; END IF; END| but when i insert a new row i don't get my v_place value set so if there someone who has time to help me with this i would greatly appreciate this :) -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: reorder records in database
On May 15, 2008, at 1:38 PM, afan pasalic wrote: hi, I have a table with tasks. column "status" could be 1 (means "todo") and 0 (meas "done"). also,have column "order_no" to sort tasks by priorities. once in a while order_no is not "in order", e.g 1, 2, 3, 5, 6, 8, 11, 12, 13, 19, 20,... (some numbers are missing). is there built in function to "reset" order_no or I have to create php script for it? Why do you care if there are gaps? Do they really matter? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Query
On Fri, May 23, 2008 at 11:20 PM, Velen <[EMAIL PROTECTED]> wrote: > Hi, > > I wanted to know when doing a select query how is it executed : > > > If there is 1000 records with price<10, 3000 records with flag='Y' and the > table contains 200,000 records. > > Select code, description, price, flag from products where flag='Y' and > price<10 > > Select code, description, price, flag from products where price<10 and > flag='Y' > > Which one of the query will be faster? In query 1, will mysql sort the list > for flag='Y' then from the list find price<'10'? > > Regards, > > Velen > There should be no difference in quey execution. If there is an index on either column with good cardinality, then that index will probably be used to eliminate records first. If you are on mysql 5.0+ then multiple index may be used (merge index). After this happens each individual row will need to be examined, which will be expensive depending on the number or rows left after using the index. EXPLAIN and EXPLAIN EXTENDED are your friends for questions like this. At some point I need to dig into the mysql source to gain a better understanding of what is going on... -- Rob Wultsch [EMAIL PROTECTED] wultsch (aim) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can solve this query?
Hi people. This is my first thread, i hope you can help me. I'm a newbie and my english is so bad, but, i hope you can understand me. this is my question: I've a table TURN with this field IDTURN (int) SEASON (string) SORT (string) another table GROUP IDGROUP (int) IDTURN (int) a table MATCH IDMATCH (int) IDGROUP (int) IDHOMETEAM (int) GOLHOMETEAM (int) and i've another table TRAINER_TEAM IDTRAINER (int) IDTEAM (int) IDTURN_FROM (int) IDTURN_TO (int) in this table i've an ID for the trainer, the id of team and the duration of the experience of this trainer with this team. The duration is set up by an IDTURN_FROM and an IDTURN_TO. i'll want to SELECT SUM OF GOLHOMETEAM FROM MATCH TABLE WHERE TRAINER_TEAM.IDTEAM = MATCH.IDHOMETEAM AND MATCH.IDGROUP.IDTURN.SORT >= TRAINER_TEAM.IDTURN_FROM.SORT AND MATCH.IDGROUP.IDTURN.SORT <= TRAINER_TEAM.IDTURN_TO.SORT AND TRAINER_TEAM.IDTRAINER=5 i hope is understandable. please help, is important!! thank u! -- View this message in context: http://www.nabble.com/How-can-solve-this-query--tp17455534p17455534.html Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
