List:General Discussion« Previous MessageNext Message »
From:Eric Robinson Date:May 10 2013 6:36pm
Subject:RE: [>Suspected Spam<][Characteristics] RE: Slow Response -- What Does This Sound Like to You?
View as plain text  
> 
> 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@stripped. 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/
Thread
Slow Response -- What Does This Sound Like to You?Eric Robinson9 May
  • RE: Slow Response -- What Does This Sound Like to You?Rick James9 May
    • RE: Slow Response -- What Does This Sound Like to You?Eric Robinson9 May
      • RE: Slow Response -- What Does This Sound Like to You?Wm Mussatto9 May
        • RE: Slow Response -- What Does This Sound Like to You?Eric Robinson9 May
          • Re: Slow Response -- What Does This Sound Like to You?shawn green9 May
      • Re: Slow Response -- What Does This Sound Like to You?Bruce Ferrell10 May
        • RE: Slow Response -- What Does This Sound Like to You?Rick James10 May
          • RE: [>Suspected Spam<][Characteristics] RE: Slow Response -- What Does This Sound Like to You?Eric Robinson10 May
            • RE: Slow Response -- What Does This Sound Like to You?Rick James10 May
  • Re: Slow Response -- What Does This Sound Like to You?Johnny Withers9 May
  • Re: Slow Response -- What Does This Sound Like to You?Denis Jedig9 May
RE: Slow Response -- What Does This Sound Like to You?Vikas Shukla10 May