RE: Are There Slow Queries that Don't Show in the Slow Query Logs?
-Original Message- From: Vikas Shukla [mailto:myfriendvi...@gmail.com] Sent: Thursday, May 30, 2013 7:19 PM To: Robinson, Eric; mysql@lists.mysql.com Subject: RE: Are There Slow Queries that Don't Show in the Slow Query Logs? Hi, No, it does not represents the time from request to response not does it includes the time that is spent in waiting for the locks to be released. The slow query log consists of SQL statements that took more than long_query_time seconds to EXECUTE. The default value of long_query_time is 10. The time to acquire the initial locks is not counted as execution time. mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might differ from execution order. Lets take an example, if a query is received at 10:00 hrs and it waits till 10:05 hrs , it starts getting executed at 10:05:00 and completed at 10:05:24 (HH:MM:SS). So, here it took 24 seconds to execute. So only 24 seconds is counted. So if long_query_time is equal to 10, which is by default, this would be logged in slow query log as it takes more than 10 seconds to execute. Sent from my Windows Phone From: Robinson, Eric Sent: 31-05-2013 03:48 To: mysql@lists.mysql.com Subject: Are There Slow Queries that Don't Show in the Slow Query Logs? As everyone knows, with MyISAM, queries and inserts can lock tables and force other queries to wait in a queue. When that happens, does the time shown in the slow query logs represent the whole time from when the server received the request to when the response was sent to the client? Or is the time a query spends waiting for a table lock to be released omitted from what is recorded in the slow query logs? -- Eric Robinson Very good answer, Vikas. Thank you for the clarification! --Eric Disclaimer - May 31, 2013 This email and any files transmitted with it are confidential and intended solely for Vikas Shukla,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: [Suspected Spam][Characteristics] Re: NET START MYSQL QUESTION?
why not answer the question another user made hours ago? under which account do you try to start mysqld? Agreed. Chances are good that if he goes into the Windows Services control panel and gets the properties of the mysql service, he will find that it is configured to start under a Windows account other than the System account, and that the Windows account in question either does not have the required privileges or it is locked. I'd probably just change it to start under the System account. --Erio Disclaimer - May 12, 2013 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: [Suspected Spam][Characteristics] RE: Slow Response -- What Does This Sound Like to You?
1. MyISAM locks _tables_. That can cause other connections
to be blocked. Solution: switch to InnoDB. Caution: There
are a few caveats when switching; see
https://kb.askmonty.org/en/converting-tables-from-myisam-to-innodb/
2. As mentioned by Shawn, the Query Cache can be more trouble
than it is worth. However 90 seconds cannot be blamed on the
QC. Still, shrink it or turn it off:
* If frequently writing to tables, turn it off (type=OFF _and_ size=0)
* If less frequently, then decide which queries will benefit,
add SQL_CACHE to them, set type=DEMAND and size=50M (no larger).
3. Meanwhile, try to make that long query more efficient.
Can you show it to us, together with SHOW CREATE TABLE, SHOW
TABLE STATUS, and EXPLAIN ?
Thanks for the feedback, Rick.
There are 1200+ tables in the database, so I don't think you want a SHOW CREATE
TABLE, SHOW TABLE STATUS, EXPLAIN for all of them. :-) The query in question is
always some variation of the following. From looking at this, which table(s)
would you like to see this information for?
# Time: 130507 18:14:26
# User@Host: site150_DbUser[site150_DbUser] @ cognos08.mycharts.md
[192.168.10.85]
# Query_time: 82 Lock_time: 0 Rows_sent: 1 Rows_examined: 914386
select (mod(dayofweek(`Query1`.`Appointment_Date`)+7-1,7)), {fn CONCAT({fn
CONCAT({fn CONCAT(cast(extract(hour from
`Time_Difference_Query`.`Created_Date`) as char(25)), ':')},
cast(extract(minute from `Time_Difference_Query`.`Created_Date`) as
char(25)))}, ':')}, `Time_Difference_Query`.`Created_Date`,
`Query1`.`Appointment_Provider_Name` from (select distinct
`EMR_ENCOUNTER`.`encType` as Encounter_Type , case when
`EMR_ENCOUNTER`.`encLock` = 0 then 'UnLocked' else 'Locked' end as
Chart_Lock_Status , `EMR_ENCOUNTER`.`notesDoneTime` as Notes_Done_Time ,
`EMR_ENCOUNTER`.`dateOut` as Notes_Done_Date , `EMR_ENCOUNTER`.`timeIn` as
Appointments_Checked_In , `EMR_ENCOUNTER`.`timeOut` as
Appointments_Checked_Out , `EMR_ENCOUNTER`.`depTime` as
Appointments_Departure_Time , `EMR_ENCOUNTER`.`arrivedTime` as
Appointments_Arrived_Time , `EMR_ENCOUNTER`.`endTime` as
Appointment_End_Time , `EMR_ENCOUNTER`.`startTime` as
Appointment_Start_Time , `EMR_ENCOUNTER`.`date` as Appointment_Date ,
`EMR_ENCOUNTER`.`encounterID` as Encounter_ID , `EDI_FACILITIES`.`Name` as
Facility_Name , `APPOINTMENT_PROVIDER`.`uid` as Appointment_Provider_ID ,
{fn CONCAT({fn CONCAT({fn CONCAT({fn CONCAT(`APPOINTMENT_PROVIDER`.`ulname`, ',
')}, `APPOINTMENT_PROVIDER`.`ufname`)}, ' ')},
`APPOINTMENT_PROVIDER`.`uminitial`)} as Appointment_Provider_Name from
(`enc` `EMR_ENCOUNTER` LEFT OUTER JOIN `edi_facilities` `EDI_FACILITIES` on
`EMR_ENCOUNTER`.`facilityId` = `EDI_FACILITIES`.`Id`) LEFT OUTER JOIN
(`doctors` `APPOINTMENT_DOCTOR` INNER JOIN `users` `APPOINTMENT_PROVIDER` on
`APPOINTMENT_DOCTOR`.`doctorID` = `APPOINTMENT_PROVIDER`.`uid`) on
`EMR_ENCOUNTER`.`doctorID` = `APPOINTMENT_DOCTOR`.`doctorID` where
`EMR_ENCOUNTER`.`encType` = 2 and `EMR_ENCOUNTER`.`date` between
cast('2011-01-01' as date) and cast('2013-05-07' as date) and
`EMR_ENCOUNTER`.`patientID` 8663 and `EMR_ENCOUNTER`.`VisitType` 'PTDASH'
and `EMR_ENCOUNTER`.`deleteFlag` = 0 and `APPOINTMENT_PROVIDER`.`UserType` = 1
and `APPOINTMENT_PROVIDER`.`delFlag` = 0 and `EDI_FACILITIES`.`DeleteFlag` = 0)
`Query1` LEFT OUTER JOIN (select distinct `Addressed_Query`.`moddate` as
Locked_Date , `Created_Query`.`moddate` as Created_Date ,
`Created_Query`.`encounterid` as encounterid , `Created_Query`.`reason` as
reason , `Created_Query`.`Patient_Name` as Patient_Name from (select
distinct `SQL1`.`moddate` as moddate , `SQL1`.`encounterid` as encounterid
, `SQL1`.`actionflag` as actionflag , `SQL1`.`ufname` as ufname ,
`SQL1`.`ulname` as ulname , `SQL1`.`reason` as reason , {fn CONCAT({fn
CONCAT(`SQL1`.`ulname`, ', ')}, `SQL1`.`ufname`)} as Patient_Name from
(select users.ufname,users.ulname,cast(reason as char(30)) as reason,
telenc_loghist.actionflag,telenc_loghist.encounterid,telenc_loghist.moddate
from telenc_loghist
inner join enc on
enc.encounterid=telenc_loghist.encounterid
inner join users on users.uid=enc.patientid where
actionflag in(0) and enc.date between '2011-01-01' and '2013-05-07') `SQL1`)
`Created_Query` LEFT OUTER JOIN (select distinct `Q2`.`moddate` as moddate ,
`Q2`.`encounterid` as encounterid , `Q2`.`actionflag` as actionflag from
(select
telenc_loghist.actionflag,telenc_loghist.encounterid,telenc_loghist.moddate
from telenc_loghist
inner join enc on
enc.encounterid=telenc_loghist.encounterid where actionflag in(4) and enc.date
between '2011-01-01' and '2013-05-07') `Q2`) `Addressed_Query` on
`Created_Query`.`encounterid` = `Addressed_Query`.`encounterid` where NOT
`Addressed_Query`.`moddate` is null) `Time_Difference_Query` on
`Query1`.`Encounter_ID` = `Time_Difference_Query`.`encounterid` where
RE: Slow Response -- What Does This Sound Like to You?
-Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDB? Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric Disclaimer - May 9, 2013 This email and any files transmitted with it are confidential and intended solely for Rick James,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: Slow Response -- What Does This Sound Like to You?
-Original Message- From: Wm Mussatto [mailto:mussa...@csz.com] Sent: Thursday, May 09, 2013 3:50 PM To: Robinson, Eric Cc: Rick James; mysql@lists.mysql.com Subject: RE: Slow Response -- What Does This Sound Like to You? On Thu, May 9, 2013 15:25, Robinson, Eric wrote: -Original Message- From: Robinson, Eric [mailto:eric.robin...@psmnv.com] Sent: Thursday, May 09, 2013 1:58 PM To: mysql@lists.mysql.com Subject: Slow Response -- What Does This Sound Like to You? We have a situation where users complain that the system periodically freezes for 30-90 seconds. We check the slow query logs and find that one user issued a complex query that did indeed take 30-90 seconds to complete. However, NO slow queries are recorded for the other 50 users, before, during, or after the freeze. Note that the complex query in question always shows: Lock_time: 0. Q: What conditions could cause single query to lock up a database for a while for all users (even though it shows lock time: 0) but no other slow queries would show in the logs for any other users who are hitting the database at the same time? OS: RHEL3 x64 CPU: 8 x 2.9GHz Xeon RAM: 32GB Disk: RAID 5 (6 x 512GB SSD) MySQL: 5.0.95 x64 Engine: MyISAM MyISAM? Or InnoDB? Lock_time perhaps applies only to table locks on MyISAM. SHOW ENGINE InnoDB STATUS; You may find some deadlocks. Is Replication involved? Anyone doing an ALTER? MyISAM, no replication involved, and nobody is altering the database. This happens whenever people run certain reports. --Eric This may be a dumb question, but have you verified that the applications do not issue a Lock TABLES ...? Either the big one or one of the others. I have not verified this, but it should be easy to find out. Hopefully that is not the case as it is a canned application and we don't have access to the code. --Eric Disclaimer - May 9, 2013 This email and any files transmitted with it are confidential and intended solely for Wm Mussatto,Rick James,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Managing ODBC on Linux? What Happened to myodbc3i?
We usually manage ODBC data sources on Linux using the myodbc3i utility that came with mysql-connector-odbc-3.51.30-1.rhel5.i386.rpm. When we upgraded to mysql-connector-odbc-5.1.10-1.rhel5.i386.rpm, it removed the myodbc3i utility. What GUI tool is now recommended to managing ODBC connections on Linux? -- Eric Robinson Disclaimer - April 17, 2012 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
RE: InnoDB and rsync
You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp aces.html Just refreshing this topic a bit. Can anyone confirm that FLUSH TABLES WITH READ LOCK is sufficient to quiesce the InnoBD background threads per Shawn's message above? -- Eric Robinson Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com,Shawn Green (MySQL). If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: InnoDB and rsync
In one extreme instance, having a few terabytes of data across several instances (on distinct hosts), I was required to do a full-refactoring data migration with an absolute limitation on allowable downtime. Among the technique which I used (and I can't take credit for this one) was to use rsync on the live server for innodb files (this phase took a very long time, but did not interfere with operations). The result of this phase was, as you would expect, a set a seriously broken files which were notheless very similar to the correct files. When that phase was complete, I shut the server down and did another rsync. It required perhaps a minute or 2, but the result was 100% clean innodb data files which satisfied my downtime limitations. FLUSH TABLES WITH READ LOCK might suffice if all transactions are completed/rolled-back but I would stil advise that you scan SHOW ENGINE INNODB STATUS but I would carefully experiment with that. You just described almost the exact procedure that I described at the beginning of this thread, except I use MyISAM so my question was whether the same technique could work with InnoDB. It sounds like it very well could if combined with SHOW ENGINE INNODB STATUS. I will definitely test it to be sure. As for maat-kit, don't let the disclaimers discourage you. If you read the disclaimers carefully on any product (at least those released with the benefit(?) of legal advice), you would have a hard time trusting any of it with your enterprise. The maat-kit team (and Baron Schwartz in particular) and quite simply the *best* MySQL engineering team out there, with the possible exception of the vendor. I would not hesitate to trust them with my data. I will definitely look at it again. Thanks. --Eric Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for Michael Dykman,mysql@lists.mysql.com,Shawn Green (MySQL). If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: InnoDB and rsync
And you will build your business on a hotcopy with external tools beause you do not trust replication? laughable! Do what you want, but dont come back and cry if all goes down You were told in which way you can use rsync with minimum downtime or that replication can be used to stop only salves for a short time and you are acting like a child mama i will do what i said the whole time Our current model has been working well since 2006. We will be careful to verify the reliability of any proposed changes. Have a great day! -- Eric Robinson Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: InnoDB and rsync
* flush atbles * rsync while mysqld is running * stop mysqld * second rsync Unless we can verify 100% that there is a safe way to do it without shutting down MySQL, then I'm sure the approach you described above is the one we will end up with. Thanks for your input. -- Eric Robinson Disclaimer - January 28, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: InnoDB and rsync
You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp aces.html Look for the section talking about clean backups. Now we're talkin. I'll check it out. I read that section but it is not at all clear (1) how one quiesces the InnoDB background threads, or (2) if there is a way to keep them quiesced while the backup is in progress. From what I see there: You can make a clean backup .ibd file using the following method: 1. Stop all activity from the mysqld http://dev.mysql.com/doc/refman/5.5/en/mysqld.html server and commit all transactions. 2. Wait until SHOW ENGINE INNODB STATUS http://dev.mysql.com/doc/refman/5.5/en/show-engine.html shows that there are no active transactions in the database, and the main thread status of InnoDB is Waiting for server activity. Then you can make a copy of the .ibd file. I would assume that flush tables with read lock would work for 1. and then you wait for 2. to happen. Probably shouldn't take very long, especially in the quiet moments. Can anyone confirm that FLUSH TABLES WITH READ LOCK is sufficient to quiesce the InnoDB background threads? When Googling this, I'm seeing posts saying that even after a flush lock, InnoDB keeps making changes to certain files (metadata?) and that's why it is never really safe (according to them) to copy the files from a running instance of MySQL. Call me persistent (or just annoyingly thick-headed) but I am not fully satisfied their explanations. --Eric Disclaimer - January 26, 2011 This email and any files transmitted with it are confidential and intended solely for Johan De Meersman,Shawn Green (MySQL),Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/
RE: InnoDB and rsync
your whole solution is crippled because why in the world are you killing your salves and reinit them without any reason daily? There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. For this reason, most people agree that it is not safe to rely on the slave server as the source for your backups. My solution efficiently corrects row drift and makes sure the slaves are 100% binary replicas of the slaves, which can then be trusted as backup sources. The whole thing is very fast and there is no downtime for users, who can continue to work 24x7. I fail to see how this is crippled. why in the world do you not leave the slaves in peace and backup them to another media because you can stop them as long you want for a consistent backup and after starting the last changes from the master are applied See my comment above. (But also we cannot stop them as long as we want because the slaves are used for running reports. Using my approach, each slave is down for about 30 seconds. The masters are not brought down at all.) If you understand how innodb works you will see that this is not possible by design I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? Is stopping the service really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) -- Eric Robinson Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: InnoDB and rsync
Why don't you use a Maatkit solution like mk-checksum to ensure that your slaves have identical data with the master? I looked at Maatkit a year or so ago. It looked pretty interesting, but then I started reading the disclaimers carefully and they scared the bejeepers out of me. Warnings about data corruption and whatnot. I'll check it out again. (I was actually looking for it this morning but could not remember the name, so thanks for the reminder.) -- Eric Robinson Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for st...@internetretailconnection.com,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: InnoDB and rsync
There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! I'm definitely no expert on this. All I know is that we used to frequently experience situations where queries to the slaves would return different recordsets than the same queries to the masters. Yet by all other indications the servers were in sync. All the replication threads were running and the row counts were identical, but the data in the rows was sometimes different. I asked about this in the list and the answers I got back were that the phenomenon was called row drift and was fairly well known and not always easy (or sometimes even possible) to eliminate because of bad programming practices in some off-the-shelf applications. At that time, the consensus in the list was that it was not safe to trust replication slaves for backup purposes. That's when I came up with the idea of doing an rsync every night, which creates a slave that is 100% reliable for using as a backup source and also eliminates problems with row-drift. Since we started using that technique, we don't get calls from users complaining that their reports are showing bogus totals and such. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Mattia Merzi,Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: InnoDB and rsync
nobody cares if they are binary replica as long as the data is consistent and ident Like I said, I'm no expert on this, but my approach seems like the only way to 100% absolutely sure that the data on the slave is in fact consistent and identical to the data on tha master. so start another slave on the machine with his own socket for backups You say that like it doesn't mean a huge amount of additional work, expense, and complexity. We currently have 240+ master MySQL instances and are adding them at a rate of several per week. Based on everything you've said so far, I still prefer my solution. I just need a way to make the same thing work with InnoDB. -- Eric Robinson Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: InnoDB and rsync
240 mysql-servers? why there is no consolidation? I said 240+ mysql *instances*, not servers. It's actually just 3 physical servers (not counting standby cluster nodes). just need a way to make the same thing work with InnoDB. this is simply impossible That is very unfortunate. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: InnoDB and rsync
240 mysql-instances on 3 physical hosts? what crazy setup is this please? Processors average 90% idle, peaks are low, iowait is low, the system is not swapping, response time is good, and our users are happy all around the country. What is crazy about that? The whole world can work with replication-slaves and you are the only one who installing an endless count of mysql-services instead a hand of large instances I don't know how the rest of the world does it, but we have been doing it like this since 2006 and it has worked great and we have never regretted having multiple instances of mysql. In fact, it is really great because we can maintain each customer's service individually, stop and start mysql without affecting other customers, turn query logs on and off for each customer, customize performance parameters, and so on. I can maintain a customer's database right in the middle of a production day and the other customers won't even notice! It has been great being able to do all these things. outside mysql would ever think of backup a running db-server Then you're just not Googling very well. :-) --Eric Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: InnoDB and rsync
On 1/25/2011 10:45, Robinson, Eric wrote: There is a very good reason: it is the phenomenon of row drift. The master and slave can appear to be in good sync, but often it is not actually the case. ... sounds interesting; have you got any document explaining this phenomenon? AFAIK, the things that (silently) break replication are: - non-deterministic functions in statement-based replication - hand-made updates on the slave db is this enough to justify a *daily* resync?! I'm definitely no expert on this. All I know is that we used to frequently experience situations where queries to the slaves would return different recordsets than the same queries to the masters. Yet by all other indications the servers were in sync. All the replication threads were running and the row counts were identical, but the data in the rows was sometimes different. I asked about this in the list and the answers I got back were that the phenomenon was called row drift and was fairly well known and not always easy (or sometimes even possible) to eliminate because of bad programming practices in some off-the-shelf applications. At that time, the consensus in the list was that it was not safe to trust replication slaves for backup purposes. That's when I came up with the idea of doing an rsync every night, which creates a slave that is 100% reliable for using as a backup source and also eliminates problems with row-drift. Since we started using that technique, we don't get calls from users complaining that their reports are showing bogus totals and such. I suspect that your queries were not as deterministic as you thought they were. Do you have a sample of a query that produced different results between the master and the slave? We shouldn't need the results, just the query. Sorry, no. The software is a canned medical application so we cannot easily inspect the queries that could have been causing the problem. Even though we could capture them in various ways (sniffer, proxy, query logs) it would not be easy to isolate the culprits out of the tens of thousands issued every day. And it was a year or more ago. We have not had the problem since we started rsyncing. :-) Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Shawn Green (MySQL),Mattia Merzi,Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: InnoDB and rsync
I'm starting to worry that you may be right. I know FLUSH TABLES WITH READ LOCK does not work as expected with InnoDB, but is there really no way to put InnoDB into a state where all changes have been flushed to disk and it is safe to rsync the directory? Is stopping the service really the only way? (And even if I stop the service, is rsync totally safe with InnoDB?) You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp aces.html Look for the section talking about clean backups. Now we're talkin. I'll check it out. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Shawn Green (MySQL),Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: InnoDB and rsync
You need to quiesce the InnoDb background threads. One technique is mentioned here: http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-tablesp aces.html Look for the section talking about clean backups. Now we're talkin. I'll check it out. I read that section but it is not at all clear (1) how one quiesces the InnoDB background threads, or (2) if there is a way to keep them quiesced while the backup is in progress. Disclaimer - January 25, 2011 This email and any files transmitted with it are confidential and intended solely for Robinson, Eric,Shawn Green (MySQL),Reindl Harald,mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
InnoDB and rsync
Is there a way to safely backup an InnoDB database using rsync? Right now we have a very efficient and reliable way to backup 240+ separate instances of MySQL with MyISAM tables. The databases range in size from .5GB to 16GB. During this time, users can still access the system, so our customers can work 24x7. In the process, we also refresh 240+ slave instances with a perfect byte-for-byte replica of the master databases. The whole thing takes about 30 minutes. Here's how we do it. Late at night when the number of users on the system is low, we do the following for each of the 240+ instances of MySQL... 1. Shut down the slave and remove all replication-related log files. 2. Perform an rsync of the master's data directory to the slave. Users may be making changes to tables during this rsync. 3. Issue a FLUSH TABLES WITH READ LOCK on the master followed by a RESET MASTER. 4. Perform a second rsync of the data directory from the master to the slave to copy any user changes that happened during step 2. This usually completes in a few seconds, often less than 1. If any users were trying to insert records at this exact moment, their application may appear to pause very briefly. 5. Start the slave. When I'm all done, I have 240+ slave servers in perfect sync with their masters, each having a 100% identical binary copy of its master's database. Since these copies are truly identical, they can be used for a second layer of backup to other media. Like I said, the whole process takes about 30 minutes because the rsync algorithm only copies the byte-level changes. IS THERE ANY WAY TO SET UP SOMETHING THIS EASY AND EFFICIENT USING INNODB? I've been reading about InnoDB hot copy and other approaches, but none of them seem to work as well as the approach I have worked out with MyISAM. Unfortunately, my software wants to force us to switch to InnoDB, so I'm really stuck. If we have to switch to InnoDB and we cannot come up with a method for doing fast, rsync-style backups, it will probably mean huge, costly, and unnecessary changes to our infrastructure. Any help will be GREATLY appreciated. -- Eric Robinson Disclaimer - January 24, 2011 This email and any files transmitted with it are confidential and intended solely for mysql@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physicians' Managed Care or Physician Select Management. Warning: Although Physicians' Managed Care or Physician Select Management has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
Hi Baron, I'm the primary author of Maatkit. Awkward... :-) What can I say -- you could go buy a commercial off-the-shelf tool and believe the song and dance they feed you about the tool being perfect. There's not a single commercial software solution in our toolbox. We're big fans of CentOS, LVS, heartbeat, ldirectord, tomcat, MySQL, Xen, pureFTP, and more. We've been happy with the performance and reliability of all of our FOSS tools. I'm definitely not a Kool-aid drinker when it comes to commercial product marketing. At least with Maatkit, you get transparency. We make a concerted effort to update the RISKS section of each tool with each release, so there is full disclosure. Fair enough, but I still found the warnings a little too scary. A more complete explanation of the exact nature of the bugs and the exact circumstances under which I should be concerned about triggering them would have increased my comfort level. I think Maatkit is by far the best solution for live master-slave sync in most real-world situations. We'll give it another look. -- Eric Robinson Disclaimer - December 9, 2009 This email and any files transmitted with it are confidential and intended solely for Baron Schwartz,Gavin Towey,Tom Worster,my...@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of . Warning: Although has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
Let's face it, sometimes the master and slave get out of sync, even when 'show slave status' and 'show master status' indicate that all is well. And sometimes it is not feasible to wait until after production hours to resync them. We've been working on a method to do an emergency hot-resync during production hours with little or no user downtime. What do you guys think of this approach? It's only for Linux, though... 1. Shut down the slave and remove its replication logs (master.info and *relay* files). 2. Do an initial rsync of the master to the slave. Using rsync's bit-differential algorithm, this quickly copies most of the changed data and can be safely be done against a live database. This initial rsync is done before the next step to minimize the time during which the tables will be read-locked. 3. Do a 'flush tables with read lock;reset master' on the master server. At this point, user apps may freeze briefly during inserts or updates. 4. Do a second rsync, which goes very fast because very little data has changed between steps 2 and 3. 5. Unlock the master tables. 6. Restart the slave. When you're done, you have a 100% binary duplicate of the master database on the slave, with no worries that some queries got missed somewhere. The master was never stopped and users were not severely impacted. (Mileage may vary, of course.) We've tried this a few times and it has seemed to work well in most cases. We had once case where the slave SQL thread did not want to restart afterwards and we had to do the whole thing again, only we stopped the master the second time. Not yet sure what that was all about, but I think it may have been a race issue of some kind. We're still exploring it. Anyway, comments would be appreciated. -- Eric Robinson Disclaimer - December 4, 2009 This email and any files transmitted with it are confidential and intended solely for my...@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of . Warning: Although has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
(1) innodb? It's an off-the-shelf application that uses MyISAM tables. It is possible to convert to innodb, but I have not been sold on innodb in terms of its performance characteristics for this particular application. Maybe I've been reading the wrong stuff. Do you have general thoughts on the differences with respect to performance? (2) why delete slave logs when you can restart the slave with --skip-slave and then use CHANGE MASTER TO? Well... I guess mainly because I didn't know about that option! I thought I needed to fake out mysql on this, but it sounds like I can just do 'flush tables with read lock;reset master;' on the master and 'change master to...;' on the slave. So cool. Thanks for the input! -- Eric Robinson Disclaimer - December 4, 2009 This email and any files transmitted with it are confidential and intended solely for Tom Worster,my...@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of . Warning: Although has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
I would say that it's very important to know why data is getting out of sync between your master and slave. Ultimately, I agree. But since it's a canned application, getting to that point might be hard, and once it is resolved, new issues might arise. I would never have any confidence that the replication is solid enough to use the slave server for backup purposes. (Which, by the way, is the real reason I'm doing this. In the middle of the night, when there are few users on the system, I want to backup the slave, but first I want to make sure I have a 100% reliable copy of the data.) There are ways to resync data that don't involve all this as well: Maatkit has some tools I've looked with great interest at Maatkit, but their tools are replete with warnings about dangers, bugs, and crashes. They certainly do not inspire confidence. -- Eric Robinson Disclaimer - December 4, 2009 This email and any files transmitted with it are confidential and intended solely for Gavin Towey,Tom Worster,my...@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of . Warning: Although has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: Here's an Idea for Re-Syncing Master and Slave During Production Hours without Interrupting Users (Much)
I would never have any confidence that the replication is solid enough to use the slave server for backup purposes. I agree completely there. That's the other reason I like filesystem snapshots is that it allows you to take a backup from the master relatively painlessly. I've thought of using snapshots. Offhand, can't remember the reason that I decided they would not work for us. It'll come to me... -- Eric Robinson Disclaimer - December 4, 2009 This email and any files transmitted with it are confidential and intended solely for Gavin Towey,my...@lists.mysql.com. If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of . Warning: Although has taken reasonable precautions to ensure no viruses are present in this email, the company cannot accept responsibility for any loss or damage arising from the use of this email or attachments. This disclaimer was added by Policy Patrol: http://www.policypatrol.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: Rename Database - Why Would This Not Work?
RENAME TABLE olddb.table1 TO newdb.table1, olddb.table2 TO newdb.table2 put the whole list in here, the whole statement will be applied to the system atomically The database has 1200+ tables, so your approach seems like more work to me. As it is, all I'm doing is: service mysql stop mv olddb newdb service mysql start mysqlcheck -o newdb -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Rename Database - Why Would This Not Work?
I used a simple procedure to rename my MySQL 4.1.22 database, which has only My-ISAM tables: 1. Stopped MySQL 2. Renamed the database directory from olddbname to newdbname 3. Started mysql At this point, I grepped for 'olddbname' and found that many of the old .MYI files still had references to 'olddbname'. So I... 4. Did mysqlcheck -o newdbname Then all the references to 'olddbname' were removed from the index files. I then started our application and everything seems to be working fine using the new database name. Yet I'm still worried because when I Google it, I see people talking about lots of different ways to do a database rename, and people are making it sound like a complicated, dangerous procedure. Why would my simple approach not work? Should I be watching for potential problems down the road because I did it this way? -- Eric Robinson Director of Information Technology Physician Select Management, LLC 775.885.2211 x 111
RE: Rename Database - Why Would This Not Work?
DB engines that have their own data dictionary (Innodb, etc) in addition to what is in the .frm could easily be messed up. Like I said, there are only MyISAM tables in the database, so would there be any risks associated with my simple approach? (Also there are no stored procedures because this is MySQL 4.1.22.) --Eric
mysqld-nt 100% CPU Utilization?
Our MySQL-based medical application has been running fine for 3 years. Last week, mysqld-nt started jumping up to 100% CPU and staying there until someone restarts the service. mysqlcheck found no errors in the database. I dumped it to disk and read it back in to create a fresh copy of the database, but it is still happening. Any ideas? -- Eric Robinson Disclaimer - July 19, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server Load Question
I asked this question previously but didn't get much response so I'll try again. Our server will be home to 100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. Each client has 3 databases. Database-1: 500 tables. 13 tables sized 10-100MB. Remaining tables all less that 10MB. (This is the only database that is updated. The others are just for reference.) Main table grows at a rate of a few hundred MB/year. Database-2: 50 tables. 3 tables sized 10-100MB. All other tables less than 10MB. No data growth. Database-3: 179 tables. 10 tables sized 1-15MB. All other tables less than 1MB. No data growth. So... Total databses: 300 Total tables: 72,900 Q: In terms of performance, is it better for each customer to have its own instance of MySQL, each serving 3 databases, or is it better to have one instance of MySQL serving 300 databases? --Eric Disclaimer - June 19, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Server Load Question
Dan, good guess! Yes, it's the same application for all customers and the two databases are reference information. Unfortunately, customers are running slightly different versions of the application and the reference information is slightly different as well. :-/ I think what I've decided to do is create one instance of MySQL per version of the application. All clients running the same version of the application would use the same instance of MySQL. -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Monday, June 19, 2006 12:55 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Server Load Question Eric, I sent you a note about hardware this morning as well, but here's a further thought - Don't know if this is the case, but is this the exact same application served to 100 different customers? And are Database-2 and Database-3 the same for every customer? Some kind of reference info perhaps? If so, split those off into a single reference database and share it across all your customers - you'll have an easier time managing MySQL, and you should gain something in performance due to MySQL being able to effectively cache some of the info. Maybe that's not what you've got but thought I'd mention it in case. Dan Robinson, Eric wrote: I asked this question previously but didn't get much response so I'll try again. Our server will be home to 100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. Each client has 3 databases. Database-1: 500 tables. 13 tables sized 10-100MB. Remaining tables all less that 10MB. (This is the only database that is updated. The others are just for reference.) Main table grows at a rate of a few hundred MB/year. Database-2: 50 tables. 3 tables sized 10-100MB. All other tables less than 10MB. No data growth. Database-3: 179 tables. 10 tables sized 1-15MB. All other tables less than 1MB. No data growth. So... Total databses: 300 Total tables: 72,900 Q: In terms of performance, is it better for each customer to have its own instance of MySQL, each serving 3 databases, or is it better to have one instance of MySQL serving 300 databases? --Eric Disclaimer - June 19, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Performance Question
Dan, thanks for your comments. I think we agree on how things should be configured. I'll keep the number of daemons to a minimum. The sample data I reported earlier is from an existing production system. The new system that we are deploying will be in a 2-node HA Linux configuration. Each node will be: 2 x Dual-core Xeon 2.8GHz (4 processor cores, total). 8GB RAM RAID 10 array (300GB (usable) I prefer RAID 10 to RAID 0+1 because the former can survive the loss of 2 drives and read/write performance is about the same as RAID 0+1. Thanks again for your input. --Eric Robinson -Original Message- From: Dan Buettner [mailto:[EMAIL PROTECTED] Sent: Monday, June 19, 2006 6:35 AM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance Question One reason you might be seeing a higher number of writes than reads is if MySQL is able to answer queries from the data it has cached in RAM, rather than having to read off disk. I would second Atle's opinion that this setup should be entirely possible with a single database daemon, especially the way you have it segmented into so many tables across so many databases - that will help reduce contention for locks among your many clients. A big reason I would vote for one (or as few as possible anyway) database daemon is sheer simplicity. Anytime you can make system design simpler, it will make for improved reliability, easier maintenance, easier upgrades, etc. It seems you've really done the numbers on planning your data size and growth - good show. You don't talk much about your hardware, except to note RAID 1. That will obviously be an important piece of the puzzle for serving so many clients and so much data. You might consider upgrading to a multi-disk RAID 1+0 array to improve speed, for example. With so many potential client connections to MySQL, you might also take a good hard look at multiprocessor machines if you haven't already. MySQL's site has a good section on optimization: http://dev.mysql.com/doc/refman/5.0/en/optimization.html Another great resource is Jeremy Zawodny's High Performance MySQL from O'Reilly. HTH, Dan Robinson, Eric wrote: All customer sites will use the same application, but each will have its own set of 3 databases. In believe the nature of the application confines users to brief, bursty selects and updates except possibly when they run reports. I have not specifically analyzed reporting, but I ran a 2-hour sampling today of a client site with 25 users during a period of typical workload. Here's the overall disk statistics: % Read Time: 5.26 % Write Time: 5.00 Avg Bytes/Read: 2918 Avg Bytes/Write: 6563 Avg Read Queue: .05 Avg Write Queue: .013 Avg Disk Secs/Read: .013 Avg Disk Secs/Write: .004 Avg Read Bytes/Sec: 15151 Avg Write Bytes/Sec: 66904 Avg Disk Reads/Sec: 3 Avg Disk Writes/Sec: 9 The numbers are very interesting. The system writes to disk 3 times more often than it reads, and the writes are more than double the size. Bytes written per second is 4 times higher than bytes read. Yet, on average, reads take longer than writes and they tend to stack up in the queue a little more, which could explain why % Read Time is slightly higher. This is a RAID 1 array. System has plenty of RAM and was not swapping. All in all, the application appears write-heavy, but I don't think anyone can hog all the disk I/O. So, your opinion is that one instance of MySQL with a lot of databases is just as efficient as multiple MySQL instances? (Note: I WILL have to run separate instances in some cases because some customers are using slightly different versions of the application.) --Eric -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Saturday, June 17, 2006 12:14 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance Question So, you're looking at 150-300 databases and ~31-62k tables based on your numbers? MySQL should be able to handle that, as should your OS, but the most important part IMO is how your clients will be using their data(bases). What sort of queries, how many, etc. Will it be possible for one client to hog all the disk IO? Ignoring the latter questions, with a properly designed database and tuned queries this doesn't seem like an impossible setup on a single database daemon. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 16 Jun 2006, Robinson, Eric wrote: Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB
RE: MySQL Performance Question
All customer sites will use the same application, but each will have its own set of 3 databases. In believe the nature of the application confines users to brief, bursty selects and updates except possibly when they run reports. I have not specifically analyzed reporting, but I ran a 2-hour sampling today of a client site with 25 users during a period of typical workload. Here's the overall disk statistics: % Read Time:5.26 % Write Time: 5.00 Avg Bytes/Read: 2918 Avg Bytes/Write:6563 Avg Read Queue: .05 Avg Write Queue:.013 Avg Disk Secs/Read: .013 Avg Disk Secs/Write:.004 Avg Read Bytes/Sec: 15151 Avg Write Bytes/Sec:66904 Avg Disk Reads/Sec: 3 Avg Disk Writes/Sec:9 The numbers are very interesting. The system writes to disk 3 times more often than it reads, and the writes are more than double the size. Bytes written per second is 4 times higher than bytes read. Yet, on average, reads take longer than writes and they tend to stack up in the queue a little more, which could explain why % Read Time is slightly higher. This is a RAID 1 array. System has plenty of RAM and was not swapping. All in all, the application appears write-heavy, but I don't think anyone can hog all the disk I/O. So, your opinion is that one instance of MySQL with a lot of databases is just as efficient as multiple MySQL instances? (Note: I WILL have to run separate instances in some cases because some customers are using slightly different versions of the application.) --Eric -Original Message- From: Atle Veka [mailto:[EMAIL PROTECTED] Sent: Saturday, June 17, 2006 12:14 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: MySQL Performance Question So, you're looking at 150-300 databases and ~31-62k tables based on your numbers? MySQL should be able to handle that, as should your OS, but the most important part IMO is how your clients will be using their data(bases). What sort of queries, how many, etc. Will it be possible for one client to hog all the disk IO? Ignoring the latter questions, with a properly designed database and tuned queries this doesn't seem like an impossible setup on a single database daemon. Atle - Flying Crocodile Inc, Unix Systems Administrator On Fri, 16 Jun 2006, Robinson, Eric wrote: Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB. Remaining tables all less that 10MB. (This is the only database that is updated. The others are just for reference.) Main table grows at a rate of a few hundred MB/year. Database2: 50 tables. 3 tables sized 10-100MB. All other tables less than 10MB. No data growth. Database3: 179 tables. 10 tables sized 1-15MB. All other tables less than 1MB. No data growth. --Eric Disclaimer - June 16, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Performance Question
Our server will be home to 50-100 separate clients. Each client will have their own set of databases that will be accessed by 10-60 users at each client's site. In terms of performance, is it better to have 1 instance of MySQL servicing multiple databases, or multiple instances of MySQL each serving 1 database? Here's some more information to work with: Each client has 3 databases. Database: 500 tables. 13 tables sized 10-100MB. Remaining tables all less that 10MB. (This is the only database that is updated. The others are just for reference.) Main table grows at a rate of a few hundred MB/year. Database2: 50 tables. 3 tables sized 10-100MB. All other tables less than 10MB. No data growth. Database3: 179 tables. 10 tables sized 1-15MB. All other tables less than 1MB. No data growth. --Eric Disclaimer - June 16, 2006 This email and any files transmitted with it are confidential and intended solely for [EMAIL PROTECTED] If you are not the named addressee you should not disseminate, distribute, copy or alter this email. Any views or opinions presented in this email are solely those of the author and might not represent those of Physician Select Management (PSM) or Physician's Managed Care (PMC). Warning: Although the message sender has taken reasonable precautions to ensure no viruses are present in this email, neither PSM nor PMC can accept responsibility for any loss or damage arising from the use of this email or attachments. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql4.0
Does mysqlcheck sometimes repair problems that it does not report? I've seen a few instances where running mysqlcheck -r -f fixed application problems even though it reported all tables OK. --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlcheck
Does mysqlcheck sometimes repair problems that it does not report? I've seen a few instances where running mysqlcheck -r -f fixed application problems even though it reported all tables OK. --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fixing Databases When Replication Is Enabled?
So, just to be clear, when I run: mysqlcheck -r -f database_name Any fixes are recorded to the binlog and replicated to the slave? I want to be sure about this because someone in this forum said the opposite a couple of weeks ago. Thanks! --Eric -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 02, 2006 8:23 AM To: Marciano Cc: Robinson, Eric; mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? My Sincere apologies, I intented to mean mysqlcheck but somehow came out as myisamchk :) Kishore Jalleda On 5/2/06, Marciano [EMAIL PROTECTED] wrote: How myisamchk can write to binlog if the server need to be down? - Mensagem Original De: Kishore Jalleda [EMAIL PROTECTED] Para: Robinson, Eric [EMAIL PROTECTED] Cópia: mysql@lists.mysql.com Assunto: Re: Fixing Databases When Replication Is Enabled? Data: 01/05/06 22:25 Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yes On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Webmail Intercol http://www.intercol.com.br -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fixing Databases When Replication Is Enabled?
I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fixing Databases When Replication Is Enabled?
I ran mysqlcheck, not myisamchk or REPAIR. Is what you said still true? -Original Message- From: Kishore Jalleda [mailto:[EMAIL PROTECTED] Sent: Monday, May 01, 2006 1:56 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: Fixing Databases When Replication Is Enabled? On 5/1/06, Robinson, Eric [EMAIL PROTECTED] wrote: I ran mysqlcheck against a replication master database and it reported a problem with a table, which it corrected. Is the slave now out of sync? If so, how do I correct the problem without copying the whole database over a slow WAN link? --Eric By Default when you run REPAIR or myisamchk --recover , MySQL writes the changes made to the Binlog , and those will be obviously replicated to the slave, so if everything went fine with your myisamck on the master, then your slave is in sync and you don't have to do anyhthing special on the slave... Kishore Jalleda http://kjalleda.googlepages.com/projects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Quick Replication Question
When you have master-slave replication enabled, and something goes wrong with one of the tables on the master, and you have to run mysqlcheck -r to fix it, does the fix get written to the binlog and replicated to the slave? --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Quick Replication Question
Okay, so that is not too encouraging. Is there any way to find out what mysqkcheck did so it can be manually applied to the slave? Or is mysqlcheck not the best way to fix things when you are using replication? -Original Message- From: Jeremiah Gowdy [mailto:[EMAIL PROTECTED] Sent: Friday, April 28, 2006 9:05 AM To: Robinson, Eric; mysql@lists.mysql.com Subject: Re: Quick Replication Question no. - Original Message - From: Robinson, Eric [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Friday, April 28, 2006 8:51 AM Subject: Quick Replication Question When you have master-slave replication enabled, and something goes wrong with one of the tables on the master, and you have to run mysqlcheck -r to fix it, does the fix get written to the binlog and replicated to the slave? --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to Verify Replication Status?
James, thanks for the link. It looks promising. Since you have never used it, you probably won't know the answer to this question, but should I expect the checksums on the master and slave databases to be the same if they are in sync? -Original Message- From: James Harvard [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 19, 2006 5:31 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: How to Verify Replication Status? Never used it, but this might help: http://dev.mysql.com/doc/refman/5.0/en/checksum-table.html James Harvard At 5:00 pm -0700 19/4/06, Robinson, Eric wrote: I have master-slave replication working fine. However, I worry about the possibility of the master and slave accidentally getting out of synchronization. Are there circumstances (other than a direct INSERT to the slave) that could cause the master and slave to be out of sync? Is there a way to periodically do some kind of full check to verify that the slave is an exact duplicate of the master? I thought of just counting the rows in all the tables on both servers, but that only tells part of the story. Is the a more elegant and complete way? Also, the servers are separated by a slow WAN link, so transferring the whole database across the network is not an option. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to Verify Replication Status?
Eric, that is very helpful. Thanks.
Assuming the master and slave are in sync, is there a reason the
checksums would not match? I would rather not dump the database and run
an external checksum unless I have to.
--
Eric Robinson
Director of Information Technology
Physician Select Management, LLC
775.720.2082
-Original Message-
From: Eric Braswell [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 20, 2006 1:49 AM
To: Robinson, Eric
Cc: mysql@lists.mysql.com
Subject: Re: How to Verify Replication Status?
There are only a very limited set of circumstances where slaves could
get out of sync, and if everything is set up right, it basically should
not happen.
See: http://dev.mysql.com/doc/refman/5.0/en/replication-rules.html
And: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
CHECKSUM TABLE is a good option if it's a read-only table or you can
stop writes (or just replication) long enough to do that. It only works
on MyISAM.
You could also do basically the same thing by dumping the data in the
same way on each server, and running a checksum (e.g. md5sum) or diff
tool. One thing I have done is to use:
mysqldump --skip-opt {database} {database}.sql
..on each machine, then diffed the files using diff (note *nix bias
here). Using skip-opt to output inserts on individual lines allows you
to compare the data to see exactly where any differences are. But this
won't help you if you can't transfer all the data to one place -- you
could just do a checksum then on both sides and compare that. Would be
pretty easy to script that and perform periodic checks.
Eric
--
Eric Braswell
Web Manager MySQL AB
Cupertino, USA
Robinson, Eric wrote:
I have master-slave replication working fine. However, I worry about
the
possibility of the master and slave accidentally getting out of
synchronization. Are there circumstances (other than a direct INSERT
to
the slave) that could cause the master and slave to be out of sync? Is
there a way to periodically do some kind of full check to verify that
the slave is an exact duplicate of the master? I thought of just
counting the rows in all the tables on both servers, but that only
tells
part of the story. Is the a more elegant and complete way? Also, the
servers are separated by a slow WAN link, so transferring the whole
database across the network is not an option.
--
Eric Robinson
Director of Information Technology
Physician Select Management, LLC
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to Verify Replication Status?
All excellent information. I have much to ponder.
-Original Message-
From: Eric Braswell [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 20, 2006 11:20 AM
To: Robinson, Eric
Cc: mysql@lists.mysql.com
Subject: Re: How to Verify Replication Status?
You'll need to checksum them at the same point in time, naturally --
which you can do by stopping replication on the slave, then performing
the command on the master, then slave, and compare.
In other words, if you are doing this on a live system, you have to make
sure there are no writes in between the two checksums... but I'm sure
that's clear.
The one problem with using CHECKSUM TABLE is that a row-by-row
comparison takes some time and necessarily forces a table lock, so
performing this on a live system can be problematic if you are expecting
frequent writes. You might consider adding the live checksum to your
tables for that.
See: http://dev.mysql.com/doc/refman/5.0/en/create-table.html
A data dump, on the other hand, can be fairly quick and would allow you
to perform the checksum without interfering with any other operations. I
don't know the details of your situation (or the size of your data), but
I'm fairly sure that that's how I would do it.
Another possibility is to do a checksum directly on the database files
if you can do so while the server is not updating anything, which would
avoid the dump, and is extremely fast. You could have a shell script
lock the tables, do an md5sum on the .MYD and maybe .frm files (for
MyISAM), then unlock. I can't off-hand think of any reason this wouldn't
work well as long as the slave and master are configured identically and
are the same version.
Eric
--
Eric Braswell
Web Manager MySQL AB
Cupertino, USA
Robinson, Eric wrote:
Eric, that is very helpful. Thanks.
Assuming the master and slave are in sync, is there a reason the
checksums would not match? I would rather not dump the database and
run
an external checksum unless I have to.
--
Eric Robinson
Director of Information Technology
Physician Select Management, LLC
775.720.2082
-Original Message-
From: Eric Braswell [mailto:[EMAIL PROTECTED]
Sent: Thursday, April 20, 2006 1:49 AM
To: Robinson, Eric
Cc: mysql@lists.mysql.com
Subject: Re: How to Verify Replication Status?
There are only a very limited set of circumstances where slaves could
get out of sync, and if everything is set up right, it basically
should
not happen.
See: http://dev.mysql.com/doc/refman/5.0/en/replication-rules.html
And: http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
CHECKSUM TABLE is a good option if it's a read-only table or you can
stop writes (or just replication) long enough to do that. It only
works
on MyISAM.
You could also do basically the same thing by dumping the data in the
same way on each server, and running a checksum (e.g. md5sum) or diff
tool. One thing I have done is to use:
mysqldump --skip-opt {database} {database}.sql
..on each machine, then diffed the files using diff (note *nix bias
here). Using skip-opt to output inserts on individual lines allows you
to compare the data to see exactly where any differences are. But this
won't help you if you can't transfer all the data to one place -- you
could just do a checksum then on both sides and compare that. Would be
pretty easy to script that and perform periodic checks.
Eric
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Verify Replication Status?
I have master-slave replication working fine. However, I worry about the possibility of the master and slave accidentally getting out of synchronization. Are there circumstances (other than a direct INSERT to the slave) that could cause the master and slave to be out of sync? Is there a way to periodically do some kind of full check to verify that the slave is an exact duplicate of the master? I thought of just counting the rows in all the tables on both servers, but that only tells part of the story. Is the a more elegant and complete way? Also, the servers are separated by a slow WAN link, so transferring the whole database across the network is not an option. -- Eric Robinson Director of Information Technology Physician Select Management, LLC 775.720.2082
RE: SHOW TABLE STATUS: Update_Time Is Wrong?
Spenser, the bug report was a direct hit in the sense that it spoke about the problem I am having, but it was actually wrong and the suggested FLUSH TABLES workaround did not work. On my servers (4.0.13-nt running on Windows 2000 Pro) FLUSH TABLES had no effect at all on the Update_time. I quit trying to use Update_time to track replication status. Now I do the following: SHOW MASTER STATUS on the master and record the binlog file name and position. SHOW SLAVE STATUS on the slave and record the Master_log_file and Exec_master_log_position. If these match, then I assume replication is up to date. Is this an okay assumption? BTW, I am aware that starting with 4.1.1 there is a Seconds_behind_master field that could be helpful, but our medical application currently only supports up to MySQL 4.0.18. -- Eric Robinson -Original Message- From: Spenser [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 29, 2004 6:23 PM To: Robinson, Eric Cc: Mikael Fridh; mysql@lists.mysql.com Subject: RE: SHOW TABLE STATUS: Update_Time Is Wrong? Eric, I'm glad that last answer worked, but I'm wondering what exactly you did to resolve the problem? I see the bug report and work around. But what specifically did you do, what did you type to fix it? By the way, what operating system are you using for your servers? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SHOW TABLE STATUS: Update_Time Is Wrong?
The time zones are correct. Besides, the difference is not an even hour, and it varies. Sometimes the master server shows an update_time that is only a few minutes different, other times it is 510 seconds or 633 seconds or 6056 seconds different. Eventually it catches up, but it sometimes takes an hour or two. Other times it catches up immediately after an update. I cannot discern a pattern. The only thing I know is that update_time seems to match the .MYD file's timestamp in Windows. -- Eric Robinson -Original Message- From: Mikael Fridh [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 28, 2004 8:15 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: SHOW TABLE STATUS: Update_Time Is Wrong? Robinson, Eric wrote: When I execute SHOW TABLE STATUS on my master replication server, it shows an Update_Time for some tables that is more than 2 hours earlier than the Update_Time for the same tables on the slave server. However the bin log names, positions, and number or records are correct. What's with that? Let us know if your timezones are set correctly. -- ___ |K | Ongame E-Solutions AB - www.ongame.com | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SHOW TABLE STATUS: Update_Time Is Wrong?
That looks like a direct hit. Thanks! -- Eric Robinson -Original Message- From: Mikael Fridh [mailto:[EMAIL PROTECTED] Sent: Wednesday, December 29, 2004 1:06 PM To: Robinson, Eric Cc: mysql@lists.mysql.com Subject: Re: SHOW TABLE STATUS: Update_Time Is Wrong? Robinson, Eric wrote: When I execute SHOW TABLE STATUS on my master replication server, it shows an Update_Time for some tables that is more than 2 hours earlier than the Update_Time for the same tables on the slave server. However the bin log names, positions, and number or records are correct. What's with that? Let us know if your timezones are set correctly. The time zones are correct. Besides, the difference is not an even hour, and it varies. Sometimes the master server shows an update_time that is only a few minutes different, other times it is 510 seconds or 633 seconds or 6056 seconds different. Eventually it catches up, but it sometimes takes an hour or two. Other times it catches up immediately after an update. I cannot discern a pattern. The only thing I know is that update_time seems to match the .MYD file's timestamp in Windows. http://bugs.mysql.com/bug.php?id=4164 -- ___ |K | Ongame E-Solutions AB - www.ongame.com | /\| Mikael Fridh / Technical Operations |_\/| tel: +46 18 606 538 / fax: +46 18 694 411 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbie: Using SELECT to Get Table Status?
I knew about COUNT(*) but thanks. I was mostly looking for a way to get the time stamp of the last update or insert. I did find a way using an ADODB.Command object. I was able to send the command SHOW TABLE STATUS LIKE 'TABLENAME'; The results were returned as a recordset. Have a great day. -- Eric Robinson -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 28, 2004 12:22 AM To: mysql@lists.mysql.com Subject: Re: Newbie: Using SELECT to Get Table Status? Hello. You may use count(*) for counting rows. Robinson, Eric [EMAIL PROTECTED] wrote: The mysqlSHOW TABLE STATUS command returns such things as the number or rows and time of the last update.=20 Is it possible to get the same information using a SELECT statement? FYI, I am writing a script to monitor replication status by comparing this information between the master and slave servers. -- Eric Robinson -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SHOW TABLE STATUS: Update_Time Is Wrong?
When I execute SHOW TABLE STATUS on my master replication server, it shows an Update_Time for some tables that is more than 2 hours earlier than the Update_Time for the same tables on the slave server. However the bin log names, positions, and number or records are correct. What's with that? -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbie: Using SELECT to Get Table Status?
The mysqlSHOW TABLE STATUS command returns such things as the number or rows and time of the last update. Is it possible to get the same information using a SELECT statement? FYI, I am writing a script to monitor replication status by comparing this information between the master and slave servers. -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Auto-Increment Starting Point? (Multimaster Replication Question)
When you set a field to auto-increment, can you tell it where to start? I'm trying to set up multimaster replication, but I'm worried about auto-increment collisions. Q: If server A starts auto-incrementing at 0, and server B starts auto-incrementing at some point higher than the maximum number of records, would that allow replication without auto-increment collisions? Q2: Assuming you can tell it where to start auto-incrementing, what happens after the following sequence: 1. Johnny inserts record 1000 at server A. 2. Server A receives record number 5000 from server B via replication. 3. Mary needs to insert a new record. Does server A number the new record 1001 or 5001? -- Eric Robinson
How to Enable Full Query Logging?
Hi, I included the following statement in my.ini: log=c:\log.txt I then executed some queries. The file log.txt only shows the following: MySql, Version: 4.0.13-nt-log, started with: TCP Port: 3306, Named Pipe: MySQL Time Id CommandArgument 040527 9:31:57 1 Connect [EMAIL PROTECTED] as anonymous on 040527 9:32:07 1 Query show status 040527 9:32:17 1 Query show status 040527 9:32:27 1 Query show status 040527 9:32:37 1 Query show status 040527 9:32:47 1 Query show status 040527 9:32:57 1 Query show status I thought the log file was supposed to show the full text of SELECT, INSERT, and UPDATE statements. Am I incorrect? How do I see this information? --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
1-Way or 2-Way Replication?
Hi, Our healthcare organization has 2 geographical locations which I will call corporate_office and satellite_office. Currently, we run a medical application from a MySQL server at the corporate_office. Users at the satellite_office use the same server. Speed across the WAN is acceptable. We want to set up replication to guard against loss of WAN connectivity. All workstations should continue to use the MySQL server at the corporate_office unless the WAN link goes down. Then users at the satellite_office would switch to their own local server for both SELECT and UPDATE, until such time as the WAN link comes back up and the databases are re-synchronized. Then they would switch back to the corporate_office. Is this possible? Will it work robustly? I have been reading the MySQL manual and there are seemingly many subtleties to setting up replication correctly. I would greatly appreciate some input from the experienced members of this list. Thank you, -- Eric Robinson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1-Way or 2-Way Replication?
there is no mechanism for propagating slave changes from the slave back up to the master... synchronization occurs *only* from master to slave (hence the terminology). Then why do they call it 2-way replication? Is there such a thing as master-to-master? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1-Way or 2-Way Replication?
Circular replication is possible... A-B-C-A Thanks, but that still does not answer the question... why do they call it 2-way replication? That implies two machines, not several. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1-Way or 2-Way Replication?
It is there. Look for two-way :-) I don't think they do... A search of the documentation for 2-way yields nothing. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1-Way or 2-Way Replication?
That is my question exactly. -Original Message- From: Ditto kolankanny [mailto:[EMAIL PROTECTED] Sent: Monday, May 17, 2004 8:40 AM To: Robinson, Eric; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: 1-Way or 2-Way Replication? Hi all, In this A-B-C-A setup A is master and B is slave. in the same time B is master and C is slave. and C is master and A is slave. that means All are master and slave. then why don`t A---BA in this same thinking i tried it and its working. for the time being it in a test platform and its ok. i don`t know in future it will create any problems or not. i send a mail to this group abou this and a persion in mysql asking same thing, i am waiting for the reply. regards Ditto [EMAIL PROTECTED] 5/17/2004 17:15:26 Robinson, Eric [EMAIL PROTECTED] wrote on 17/05/2004 15:48:12: there is no mechanism for propagating slave changes from the slave back up to the master... synchronization occurs *only* from master to slave (hence the terminology). Then why do they call it 2-way replication? Is there such a thing as master-to-master? I don't think they do. On http://dev.mysql.com/doc/mysql/en/Replication_Intro.html it says: MySQL 3.23.15 and up features support for one-way replication. A search of the documantation for 2-way yields nothing. Circular replication is possible. If machines replicate A-B-C-A, each update is tagged wit its originating machine and drops when it replicates rounc. However, this is mainly a load-sharing rather than reliability feature; there are considerable complexities if the same table is updated on more then one machine. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: 1-Way or 2-Way Replication?
Shawn, your answer is excellent, and I now understand why replication is a touchy issue. But now I am worried that I will not be able to accomplish what I had hoped. I want to have a server at my corporate office and a server at my branch office. Most of the time, all users at both locations would use the master server at the corporate office. Is it possible to set up replication in such a way that if the WAN link goes down, the users at the branch office switch over to their local server and continue working normally, including INSERT and UPDATE? If so, what happens when the WAN link comes back up? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Monday, May 17, 2004 11:12 AM To: [EMAIL PROTECTED] Subject: RE: 1-Way or 2-Way Replication? Robinson, Eric: 5/17/04 1048 there is no mechanism for propagating slave changes from the slave back up to the master... synchronization occurs *only* from master to slave (hence the terminology). Then why do they call it 2-way replication? Is there such a thing as master-to-master? /Robinson, Eric Eric, In order to declare a server a master server it must be able to accept INSERTs and UPDATEs without corrupting your data integrity. If you want to keep 2 master servers in sync, you must implement some form of distributed locking. Distributed locking is what keeps both servers from issuing the same ID number at the same time to two different records or permitting simultaneous updates to the same record on two different servers. Once you solve the issue of distributed transaction locking you are now able to keep both sets of data intact. The entire locking/communication cycle between two (or more) servers that ensures BOTH data integrity AND consistency is what is mean by the term 2-way replication. MySQL does not support distributed locking (yet). Until then, your data integrity is ensured ONLYif you allow inserts/updates on only 1 server at a time and copy the changes as they happen to all other servers that need copies of the data. If you have two servers, A and B, and your applications are written so that server A is where all of the data creation and changes occur then you can ensure that all new records will have unique primary key values and that each primary key value identifies the same data (you have achieved data consistency). Those new records could be copied over to B through the built-in replication system in near-real-time. This is the 1-way replication you have read so much about. Each master server (like A) can push data to multiple slaves (like B) but as of right now there is no way for separate MySQL servers to communicate with each other so that each of them can accept new or updated data while preventing loss of data consistency (2-way or n-way replication). I hope I helped you to understand that 2-way replication involves much more than simple data copying from server to server and requires an entire distributed transaction system in order to prevent data collisions and corruptions. MySQL will have it in a future release but it's ready for an initial alpha test, yet. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
