I suddenly bump into an error in MariaDB (mysql)
Error Code: 5. Out of memory (Needed 3758098168 bytes)
This is my configuration file:
[mysqld]
max_connection=60
datadir=/data/mysql
innodb_buffer_pool_size=6G
skip-external-locking
query_cache_size= 256M
key_buffer_size=20M
table_open_cache=400
sort_buffer_size=4M
read_buffer_size=20M
join_buffer_size=30M
The query contains loads of Left outer Join... So I assumed it must be the join_buffer_size that had to be increased.. I changed it from 3M to 30M, but it still crashes.
This is the query:
SELECT Periods.Periods, Result.count as `Result`, Studies.count as `Studies`,
MouthStudies.Mouthcount as `MOU`,
LiverStudies.LiverCount as `LIV`,
BreastStudies.BreastCount as `BRE`,
BladderStudies.BladderCount as `BLA`,
CervixStudies.CervixCount as `CER`,
ProstateStudies.ProstateCount as `PRO`,
ColorectalStudies.ColorectalCount + ColonStudies.ColonCount as COL,
LungStudies.LungCount as LUN,
KidneyStudies.KidneyCount as KID,
EndometrianStudies.EndometrianCount as `END`,
GallbladderStudies.GallbladderCount as `GAL`,
NasopharynxStudies.NasopharynxCount as `NAS`,
ObesityStudies.ObesityCount as `OBE`,
OesophagealStudies.OesophagealCount as `OES`,
OvarianStudies.OvarianCount as OVA,
PancreasStudies.PancreasCount as PAN,
SkinStudies.SkinCount as SKI,
StomachStudies. StomachCount as STM,
PolypesStudies.PolypesCount as POL
from
(select DISTINCT(DATE_FORMAT(allDates.`DATETIME`,'%M - %Y')) as Periods
from
(select `DATETIME`
from tblresults
union all
select `DATETIME`
from tblstudydetailsmain) as allDates
order by `DATETIME` DESC LIMIT 3) as Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods, count(ID) as `count` FROM central_log.tblresults
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as Result
ON Periods.periods=Result.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods, count(ID) as `count` FROM central_log.tblstudydetailsmain
where USER LIKE concat('','%') and USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as Studies
ON Periods.periods=Studies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'mou%' then 1 else 0 END) as `Mouthcount`
FROM central_log.tblstudydetailsmain
where USER LIKE concat('','%') and USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as MouthStudies
ON Periods.periods=MouthStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'LIV%' then 1 else 0 END) as `Livercount`
FROM central_log.tblstudydetailsmain
where USER LIKE concat('','%') and USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as LiverStudies
ON Periods.periods=LiverStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'BRE%' then 1 else 0 END) as `Breastcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as BreastStudies
ON Periods.periods=BreastStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'BLA%' then 1 else 0 END) as `Bladdercount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as BladderStudies
ON Periods.periods=BladderStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'CER%' then 1 else 0 END) as `Cervixcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as CervixStudies
ON Periods.periods=CervixStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'PRO%' then 1 else 0 END) as `Prostatecount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as ProstateStudies
ON Periods.periods=ProstateStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'COL%' then 1 else 0 END) as `Coloncount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as ColonStudies
ON Periods.periods=ColonStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'LUN%' then 1 else 0 END) as `Lungcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as LungStudies
ON Periods.periods=LungStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'KID%' then 1 else 0 END) as `Kidneycount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as KidneyStudies
ON Periods.periods=KidneyStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'END%' then 1 else 0 END) as `Endometriancount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as EndometrianStudies
ON Periods.periods=EndometrianStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'CRC%' then 1 else 0 END) as `Colorectalcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as ColorectalStudies
ON Periods.periods=ColorectalStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'GAL%' then 1 else 0 END) as `Gallbladdercount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as GallbladderStudies
ON Periods.periods=GallbladderStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'NAS%' then 1 else 0 END) as `Nasopharynxcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as NasopharynxStudies
ON Periods.periods=NasopharynxStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'OBE%' then 1 else 0 END) as `Obesitycount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as ObesityStudies
ON Periods.periods=ObesityStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'oes%' then 1 else 0 END) as `Oesophagealcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as OesophagealStudies
ON Periods.periods=OesophagealStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'OVA%' then 1 else 0 END) as `Ovariancount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as OvarianStudies
ON Periods.periods=OvarianStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'PAN%' then 1 else 0 END) as `Pancreascount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as PancreasStudies
ON Periods.periods=PancreasStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'SKI%' then 1 else 0 END) as `Skincount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as SkinStudies
ON Periods.periods=SkinStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'STM%' then 1 else 0 END) as `Stomachcount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as StomachStudies
ON Periods.periods= StomachStudies.Periods
LEFT OUTER JOIN
(SELECT DATE_FORMAT(`DATETIME`, '%M - %Y') as periods,
sum(CASE WHEN WCRF_CODE LIKE 'POL%' then 1 else 0 END) as `Polypescount`
FROM central_log.tblstudydetailsmain
where USER NOT LIKE 'rroot%'
and TYPE_ACTION LIKE 'added'
GROUP BY periods
order by `DATETIME` DESC) as PolypesStudies
ON Periods.periods= PolypesStudies.Periods;
Basically, it is a log database of all the added/deleted/updated studies in the main database by type of cancer. It is used here to keep an eye on the production of the team. In total there are only 12,457 rows in the table and until yesterday the query returned no error....
If some MySql-MariaDB performance guru are around, please help me to resolve this!
Christophe