> > 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 `Query1`.`Appointment_Provider_ID` in (9118, 9119, 60922, 9116, 47495) and `Query1`.`Facility_Name` in ('Fremont Family Care') and `Query1`.`Appointment_Date` between cast(cast('2011-01-01' as date) as date) and cast(cast('2013-05-07' as date) as date) and `Query1`.`Appointment_Provider_ID` = 60922; Also, the query cache size is currently set to 64MB. Do you really think turning it off is a good idea? The my.cnf file follows... [mysqld_safe] timezone=America/Chicago [mysqld] #-optimization skip-locking skip-innodb skip-bdb query_cache_type = 1 tmp_table_size=1M wait_timeout=2048 interactive_timeout=2048 table_cache=1024 query_cache_limit=1M thread_concurrency = 4 key_buffer = 256M query_cache_size=64M max_allowed_packet = 1M sort_buffer_size = 512K read_buffer_size = 512K myisam_sort_buffer_size = 128M thread_cache_size=40 max_connections=500 max_tmp_tables=32 lower_case_table_names=1 #-turn on query logging #log=/ha01_mysql/site150/mysql/query.log log_slow_queries=/ha01_mysql/site150/mysql/slow_query.log long_query_time=3 #-make this server a replication master #log-bin = /ha01_mysql/site150/mysql/binlogs/ expire_logs_days=5 server-id=99 #replicate-do-db=mobiledoc_150 I'm thinking that our best solution may be to re-enable the slave (currently disabled) and point the nasty query at it. By the way, we have considered switching to innodb, but there the advantages of MyISAM in our environment have usually outweighed. --Eric Disclaimer - May 10, 2013 This email and any files transmitted with it are confidential and intended solely for Rick James,Bruce Ferrell,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