Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Data Model Questions /

MySQL Out of sort memory; increase server sort buffer size


Go to End
Reply


9 Posts   9501 Views

Avatar
DNA

Community Member, 24 Posts

18 November 2010 at 12:47pm

Now I know that I can probably fix this by changing MySQL's config, however it made me look at the query being run and I'm just wondering why it's so verbose.

SELECT count(*)
FROM
SELECT "SiteTree_Live"."ClassName", "SiteTree_Live"."Created", "SiteTree_Live"."LastEdited", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title", "SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaTitle", "SiteTree_Live"."MetaDescription", "SiteTree_Live"."MetaKeywords", "SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch", "SiteTree_Live"."HomepageForDomain", "SiteTree_Live"."ProvideComments", "SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink", "SiteTree_Live"."Status", "SiteTree_Live"."ReportClass", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType", "SiteTree_Live"."ToDo", "SiteTree_Live"."Version", "SiteTree_Live"."Priority", "SiteTree_Live"."ParentID", "Page_Live"."Intro", "Page_Live"."Content2", "Project_Live"."ExternalURL", "Project_Live"."ClientID", "Project_Live"."Image1ID", "Project_Live"."Image2ID", "Project_Live"."Image3ID", "Project_Live"."Image4ID", "Project_Live"."Image5ID", "Project_Live"."Image6ID", "Project_Live"."Image7ID", "Project_Live"."Image8ID", "Project_Live"."Image9ID", "Project_Live"."Image10ID", "Project_Live"."RelatedProject1ID", "Project_Live"."RelatedProject2ID", "Project_Live"."RelatedProject3ID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END AS "RecordClassName"
FROM "SiteTree_Live"
LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN "Project_Live" ON "Project_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN Expertise_Projects ep ON ep.ProjectID = Project_Live.ID
LEFT JOIN Expertise e ON e.ID = ep.ExpertiseID AND e.DisciplineID = 2
LEFT JOIN Service_Projects sp ON sp.ProjectID = Project_Live.ID
LEFT JOIN Service s ON s.ID = sp.ServiceID AND s.DisciplineID = 2
WHERE ("SiteTree_Live"."ClassName" IN ('Project')) AND ("SiteTree_Live"."ParentID" = 18 AND (e.ID IS NOT NULL OR s.ID IS NOT NULL))
GROUP BY "SiteTree_Live"."ClassName", "SiteTree_Live"."Created", "SiteTree_Live"."LastEdited", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title", "SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaTitle", "SiteTree_Live"."MetaDescription", "SiteTree_Live"."MetaKeywords", "SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch", "SiteTree_Live"."HomepageForDomain", "SiteTree_Live"."ProvideComments", "SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink", "SiteTree_Live"."Status", "SiteTree_Live"."ReportClass", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType", "SiteTree_Live"."ToDo", "SiteTree_Live"."Version", "SiteTree_Live"."Priority", "SiteTree_Live"."ParentID", "Page_Live"."Intro", "Page_Live"."Content2", "Project_Live"."ExternalURL", "Project_Live"."ClientID", "Project_Live"."Image1ID", "Project_Live"."Image2ID", "Project_Live"."Image3ID", "Project_Live"."Image4ID", "Project_Live"."Image5ID", "Project_Live"."Image6ID", "Project_Live"."Image7ID", "Project_Live"."Image8ID", "Project_Live"."Image9ID", "Project_Live"."Image10ID", "Project_Live"."RelatedProject1ID", "Project_Live"."RelatedProject2ID", "Project_Live"."RelatedProject3ID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END) all_distinct

I couldn't get the SQL to run (because of syntax error not sort buffer) in PHPMyAdmin until I took out the count and just ran

SELECT "SiteTree_Live"."ClassName", "SiteTree_Live"."Created", "SiteTree_Live"."LastEdited", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title", "SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaTitle", "SiteTree_Live"."MetaDescription", "SiteTree_Live"."MetaKeywords", "SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch", "SiteTree_Live"."HomepageForDomain", "SiteTree_Live"."ProvideComments", "SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink", "SiteTree_Live"."Status", "SiteTree_Live"."ReportClass", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType", "SiteTree_Live"."ToDo", "SiteTree_Live"."Version", "SiteTree_Live"."Priority", "SiteTree_Live"."ParentID", "Page_Live"."Intro", "Page_Live"."Content2", "Project_Live"."ExternalURL", "Project_Live"."ClientID", "Project_Live"."Image1ID", "Project_Live"."Image2ID", "Project_Live"."Image3ID", "Project_Live"."Image4ID", "Project_Live"."Image5ID", "Project_Live"."Image6ID", "Project_Live"."Image7ID", "Project_Live"."Image8ID", "Project_Live"."Image9ID", "Project_Live"."Image10ID", "Project_Live"."RelatedProject1ID", "Project_Live"."RelatedProject2ID", "Project_Live"."RelatedProject3ID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END AS "RecordClassName"
FROM "SiteTree_Live"
LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN "Project_Live" ON "Project_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN Expertise_Projects ep ON ep.ProjectID = Project_Live.ID
LEFT JOIN Expertise e ON e.ID = ep.ExpertiseID AND e.DisciplineID = 2
LEFT JOIN Service_Projects sp ON sp.ProjectID = Project_Live.ID
LEFT JOIN Service s ON s.ID = sp.ServiceID AND s.DisciplineID = 2
WHERE ("SiteTree_Live"."ClassName" IN ('Project')) AND ("SiteTree_Live"."ParentID" = 18 AND (e.ID IS NOT NULL OR s.ID IS NOT NULL))
GROUP BY "SiteTree_Live"."ClassName", "SiteTree_Live"."Created", "SiteTree_Live"."LastEdited", "SiteTree_Live"."URLSegment", "SiteTree_Live"."Title", "SiteTree_Live"."MenuTitle", "SiteTree_Live"."Content", "SiteTree_Live"."MetaTitle", "SiteTree_Live"."MetaDescription", "SiteTree_Live"."MetaKeywords", "SiteTree_Live"."ExtraMeta", "SiteTree_Live"."ShowInMenus", "SiteTree_Live"."ShowInSearch", "SiteTree_Live"."HomepageForDomain", "SiteTree_Live"."ProvideComments", "SiteTree_Live"."Sort", "SiteTree_Live"."HasBrokenFile", "SiteTree_Live"."HasBrokenLink", "SiteTree_Live"."Status", "SiteTree_Live"."ReportClass", "SiteTree_Live"."CanViewType", "SiteTree_Live"."CanEditType", "SiteTree_Live"."ToDo", "SiteTree_Live"."Version", "SiteTree_Live"."Priority", "SiteTree_Live"."ParentID", "Page_Live"."Intro", "Page_Live"."Content2", "Project_Live"."ExternalURL", "Project_Live"."ClientID", "Project_Live"."Image1ID", "Project_Live"."Image2ID", "Project_Live"."Image3ID", "Project_Live"."Image4ID", "Project_Live"."Image5ID", "Project_Live"."Image6ID", "Project_Live"."Image7ID", "Project_Live"."Image8ID", "Project_Live"."Image9ID", "Project_Live"."Image10ID", "Project_Live"."RelatedProject1ID", "Project_Live"."RelatedProject2ID", "Project_Live"."RelatedProject3ID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END

However even without the count it ran into the sort buffer so I changed it to

SELECT "SiteTree_Live"."*", Page_Live"."Intro", "Page_Live"."Content2", "Project_Live"."ExternalURL", "Project_Live"."ClientID", "Project_Live"."Image1ID", "Project_Live"."Image2ID", "Project_Live"."Image3ID", "Project_Live"."Image4ID", "Project_Live"."Image5ID", "Project_Live"."Image6ID", "Project_Live"."Image7ID", "Project_Live"."Image8ID", "Project_Live"."Image9ID", "Project_Live"."Image10ID", "Project_Live"."RelatedProject1ID", "Project_Live"."RelatedProject2ID", "Project_Live"."RelatedProject3ID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END AS "RecordClassName"
FROM "SiteTree_Live"
LEFT JOIN "Page_Live" ON "Page_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN "Project_Live" ON "Project_Live"."ID" = "SiteTree_Live"."ID"
LEFT JOIN Expertise_Projects ep ON ep.ProjectID = Project_Live.ID
LEFT JOIN Expertise e ON e.ID = ep.ExpertiseID AND e.DisciplineID = 2
LEFT JOIN Service_Projects sp ON sp.ProjectID = Project_Live.ID
LEFT JOIN Service s ON s.ID = sp.ServiceID AND s.DisciplineID = 2
WHERE ("SiteTree_Live"."ClassName" IN ('Project')) AND ("SiteTree_Live"."ParentID" = 18 AND (e.ID IS NOT NULL OR s.ID IS NOT NULL))
GROUP BY "SiteTree_Live"."ID", "Page_Live"."Intro", "Page_Live"."Content2", "Project_Live"."ExternalURL", "Project_Live"."ClientID", "Project_Live"."Image1ID", "Project_Live"."Image2ID", "Project_Live"."Image3ID", "Project_Live"."Image4ID", "Project_Live"."Image5ID", "Project_Live"."Image6ID", "Project_Live"."Image7ID", "Project_Live"."Image8ID", "Project_Live"."Image9ID", "Project_Live"."Image10ID", "Project_Live"."RelatedProject1ID", "Project_Live"."RelatedProject2ID", "Project_Live"."RelatedProject3ID", "SiteTree_Live"."ID", CASE WHEN "SiteTree_Live"."ClassName" IS NOT NULL THEN "SiteTree_Live"."ClassName" ELSE 'SiteTree' END

The SQL above takes out the verbose columns and uses .* whicch runs fine.

So I'm wondering why does SS use the verbose column names? What was the rationale or issues that mean it was needed?

It seems to be the if you changed it to * then you might also get a bit more performance out it too.

Ran some very crude simple test on the below queries

SELECT `SiteTree_Live`.* FROM `SiteTree_Live`

SELECT `SiteTree_Live`.`ClassName`, `SiteTree_Live`.`Created`, `SiteTree_Live`.`LastEdited`, `SiteTree_Live`.`URLSegment`, `SiteTree_Live`.`Title`, `SiteTree_Live`.`MenuTitle`, `SiteTree_Live`.`Content`, `SiteTree_Live`.`MetaTitle`, `SiteTree_Live`.`MetaDescription`, `SiteTree_Live`.`MetaKeywords`, `SiteTree_Live`.`ExtraMeta`, `SiteTree_Live`.`ShowInMenus`, `SiteTree_Live`.`ShowInSearch`, `SiteTree_Live`.`HomepageForDomain`, `SiteTree_Live`.`ProvideComments`, `SiteTree_Live`.`Sort`, `SiteTree_Live`.`HasBrokenFile`, `SiteTree_Live`.`HasBrokenLink`, `SiteTree_Live`.`Status`, `SiteTree_Live`.`ReportClass`, `SiteTree_Live`.`CanViewType`, `SiteTree_Live`.`CanEditType`, `SiteTree_Live`.`ToDo`, `SiteTree_Live`.`Version`, `SiteTree_Live`.`Priority`, `SiteTree_Live`.`ParentID` FROM `SiteTree_Live`

The first ran in .0005 the second in .0006

Once again I'm wondering why does SS use the verbose column names? What was the rationale or issues that mean it was needed?

Avatar
Sequoia

Community Member, 5 Posts

25 November 2010 at 4:38am

Edited: 25/11/2010 5:14am

Had this issue as well. Did you find a solution?

EDIT:
Increasing the sort_buffer_size did it in my case. XAMPP (my dev environment) turns this setting down really low to accommodate low power computers and laptops apparently. Changed it from 64K to 256K.

Avatar
swaiba

Forum Moderator, 1799 Posts

25 November 2010 at 8:45am

In response to...

"I'm wondering why does SS use the verbose column names? What was the rationale or issues that mean it was needed?"

I'd imagine that this is because there is code to generate these and it is the recommend way to do this. As a human if I do something like...

select field from table, othertable where something = somethingelse;

I might get an ambiguous column on one, so then I go...

select t.field from table t, othertable ot where ot.something = t.somethingelse;

But then that is only because I already know there isn't a field, table or otherwise reserved word to "t" and "to". If I didn't' I'd user the entire table name - only way to be sure. Then because this is the result of a DataObject::get (probably, or similar) that will by default get everything as you would expect. It may well also have a default sort specified that would be invoked (I use this on all databoject that appear in modeladmin).

Is that what you were asking?

Avatar
DNA

Community Member, 24 Posts

3 December 2010 at 1:21pm

Firstly I realise that sometimes you don't want all columns of data, but in SS most of the time you need the complete object so you might as well grab everything.

Second yes swaiba, but putting in the full table name isn't too hard.

Third, ignoring the select part, why group by all columns. I think this is what is causing the issue.

I applaud SS for creating such a nice OO system. Love the table structuring and auto table generation. Also love the fact you add indexes into the database from the model. However the way to access this DataObject:get doesn't seem to well thought through and optimised.

Also I've found it isn't necessarily a speed issue, I found the queries below executed at around the same speed. However the second one can do it with 1K of sort memory, the first needs somewhere between 128-256K.

SELECT
   `SiteTree`.`ClassName`, `SiteTree`.`Created`, `SiteTree`.`LastEdited`, `SiteTree`.`URLSegment`, `SiteTree`.`Title`,`SiteTree`.`MenuTitle`, `SiteTree`.`Content`,    `SiteTree`.`MetaTitle`, `SiteTree`.`MetaDescription`, `SiteTree`.`MetaKeywords`, `SiteTree`.`ExtraMeta`, `SiteTree`.`ShowInMenus`, `SiteTree`.`ShowInSearch`, `SiteTree`.`HomepageForDomain`,`SiteTree`.`ProvideComments`, `SiteTree`.`Sort`, `SiteTree`.`HasBrokenFile`, `SiteTree`.`HasBrokenLink`, `SiteTree`.`Status`, `SiteTree`.`ReportClass`, `SiteTree`.`CanViewType`, `SiteTree`.`CanEditType`, `SiteTree`.`ToDo`, `SiteTree`.`Version`, `SiteTree`.`Priority`, `SiteTree`.`ParentID`, `Page`.`Intro`, `Page`.`Content2`, `Project`.`Description`, `Project`.`TeaserLayout`, `Project`.`Layout`, `Project`.`Content3`, `Project`.`Content4`, `Project`.`ExternalURLLabel`, `Project`.`ExternalURL`, `Project`.`HeroImage`, `Project`.`TeaserImage`, `Project`.`ClientID`, `Project`.`Image1ID`, `Project`.`Image2ID`, `Project`.`Image3ID`, `Project`.`Image4ID`, `Project`.`Image5ID`, `Project`.`Image6ID`, `Project`.`Image7ID`, `Project`.`Image8ID`, `Project`.`Image9ID`, `Project`.`Image10ID`, `Project`.`RelatedProject1ID`, `Project`.`RelatedProject2ID`, `Project`.`RelatedProject3ID`, `SiteTree`.`ID`, CASE WHEN `SiteTree`.`ClassName` IS NOT NULL THEN `SiteTree`.`ClassName` ELSE 'SiteTree' END AS `RecordClassName` FROM `SiteTree` LEFT JOIN `Page` ON `Page`.`ID` = `SiteTree`.`ID`
LEFT JOIN `Project` ON `Project`.`ID` = `SiteTree`.`ID`
LEFT JOIN `Expertise_Projects` ON (`Expertise_Projects`.`ProjectID` = `SiteTree`.`ID` AND `Expertise_Projects`.`ExpertiseID` = '5')
WHERE (`SiteTree`.`ClassName` IN ('Project')) AND (`Expertise_Projects`.`ExpertiseID` = '5')
GROUP BY `SiteTree`.`ClassName`, `SiteTree`.`Created`, `SiteTree`.`LastEdited`, `SiteTree`.`URLSegment`, `SiteTree`.`Title`, `SiteTree`.`MenuTitle`, `SiteTree`.`Content`, `SiteTree`.`MetaTitle`, `SiteTree`.`MetaDescription`, `SiteTree`.`MetaKeywords`, `SiteTree`.`ExtraMeta`, `SiteTree`.`ShowInMenus`, `SiteTree`.`ShowInSearch`, `SiteTree`.`HomepageForDomain`, `SiteTree`.`ProvideComments`, `SiteTree`.`Sort`, `SiteTree`.`HasBrokenFile`, `SiteTree`.`HasBrokenLink`, `SiteTree`.`Status`, `SiteTree`.`ReportClass`, `SiteTree`.`CanViewType`, `SiteTree`.`CanEditType`, `SiteTree`.`ToDo`, `SiteTree`.`Version`, `SiteTree`.`Priority`, `SiteTree`.`ParentID`, `Page`.`Intro`, `Page`.`Content2`, `Project`.`Description`, `Project`.`TeaserLayout`, `Project`.`Layout`, `Project`.`Content3`, `Project`.`Content4`, `Project`.`ExternalURLLabel`, `Project`.`ExternalURL`, `Project`.`HeroImage`, `Project`.`TeaserImage`, `Project`.`ClientID`, `Project`.`Image1ID`, `Project`.`Image2ID`, `Project`.`Image3ID`, `Project`.`Image4ID`, `Project`.`Image5ID`, `Project`.`Image6ID`, `Project`.`Image7ID`, `Project`.`Image8ID`, `Project`.`Image9ID`, `Project`.`Image10ID`, `Project`.`RelatedProject1ID`, `Project`.`RelatedProject2ID`, `Project`.`RelatedProject3ID`, `SiteTree`.`ID`, CASE WHEN `SiteTree`.`ClassName` IS NOT NULL THEN `SiteTree`.`ClassName` ELSE 'SiteTree' END

SELECT
   `SiteTree`.*,   
    `Page`.*,   
    `Project`.*,
   `SiteTree`.`ID`,
CASE WHEN `SiteTree`.`ClassName` IS NOT NULL THEN `SiteTree`.`ClassName` ELSE 'SiteTree' END AS `RecordClassName` FROM `SiteTree` LEFT JOIN `Page` ON `Page`.`ID` = `SiteTree`.`ID`
LEFT JOIN `Project` ON `Project`.`ID` = `SiteTree`.`ID`
LEFT JOIN `Expertise_Projects` ON (`Expertise_Projects`.`ProjectID` = `SiteTree`.`ID` AND `Expertise_Projects`.`ExpertiseID` = '5')
WHERE (`SiteTree`.`ClassName` IN ('Project')) AND (`Expertise_Projects`.`ExpertiseID` = '5')
GROUP BY `SiteTree`.`ID`, `Page`.`ID`, `Project`.`ID`, CASE WHEN `SiteTree`.`ClassName` IS NOT NULL THEN `SiteTree`.`ClassName` ELSE 'SiteTree' END

Avatar
DNA

Community Member, 24 Posts

3 December 2010 at 1:35pm

The other thing I notice is that when counting objects SS often uses

SELECT count(*) FROM original query here.

Whereas it would be much more efficient to do a query like this:

SELECT count(*) from (SELECT
   `SiteTree`.`ID` as `SiteTree__ID`,
    `Page`.`ID` as `Page__ID`,   
    `Project`.`ID` as `Project__ID`
FROM `SiteTree` LEFT JOIN `Page` ON `Page`.`ID` = `SiteTree`.`ID`
LEFT JOIN `Project` ON `Project`.`ID` = `SiteTree`.`ID`
LEFT JOIN `Expertise_Projects` ON (`Expertise_Projects`.`ProjectID` = `SiteTree`.`ID` AND `Expertise_Projects`.`ExpertiseID` = '5')
WHERE (`SiteTree`.`ClassName` IN ('Project')) AND (`Expertise_Projects`.`ExpertiseID` = '5')
GROUP BY `SiteTree`.`ID`, `Page`.`ID`, `Project`.`ID`) as countTable

It looks to that the decision has been made around the count rather than the original query.

So that they can do SELECT count(*) FROM orignalQuery they have reducred performance on both queries.

My version of the count executed in ~.0009s their version takes ~.0036s which is approximately 4 times slower.

Avatar
DNA

Community Member, 24 Posts

3 December 2010 at 1:36pm

It may mean that SS needs to write a better database abstraction layer but I'm all for that. In fact I'm thinking about doing it myself.

I don't need it on my current project, as there's many ways to skin a cat with SQL. However I think that there are some better ways to increase performance of the OO DB integration.

Avatar
DNA

Community Member, 24 Posts

3 December 2010 at 1:55pm

This is form the mysql docs: http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html

"On Linux, there are thresholds of 256KB and 2MB where larger values may significantly slow down memory allocation, so you should consider staying below one of those values."

Avatar
swaiba

Forum Moderator, 1799 Posts

3 December 2010 at 10:10pm

Hi,

It sounds like you have really looked into this - much more than me! I agree that there is probably quite a bit that can be done to speed up the database (especially in the admin system). for the live site surely caching will speed things up... http://doc.silverstripe.org/staticpublisher and I did notice a nice post on ssbits about optimizing db lookups... http://www.ssbits.com/snippets/2010/optimizing-complex-models/

If you know the section of code and can change it then I'm sure they'd love to get a patch to bring it in line with your quote there... alternatively you might raise it on open.silverstripe.org - from my experiences they do look at these and fix the real issues.

Go to Top