>
> 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
