Symphony.

Guys, I have a big problem on a live server that is shared hosting, and I don't know how to rectify the problem.

I'm hitting a 90Mb memory limit in PHP's memory_limit on one of my sections listings.

Yes, I have a lot of entries, but there aren't many fields in the sections, so I'm a little worried.

I've attached screen-shots of the relevant info, but I'll outline it here too.

Section:  Artists in Books
Entries:  21628
Error:  Fatal error: Allowed memory size of 94371840 bytes exhausted (tried to allocate 77 bytes) in /home/jrjg1/resources.openbookpublishers.com/symphony/lib/toolkit/class.mysql.php on line 462

I don't have any control over upping the memory limit here, and I would appreciate no responses like "Move to a dedicated server" as it's a university site, and not funded enough for dedicated hosting.

Can anyone shed any light on how I can solve this problem?

Cheers in advance.

I have a very experimental branch that completely replaces the XMLElement implementation with one that uses DOMDocuments that should in theory reduce the amount of memory being used.

It has a few issues that I should point out:

• Potential PHP segfault risk, for some unknown reason it freaks out in various undefined situations and PHP simply quits without executing.
• The stable mode that avoids the segfaults will use roughly the same memory as the current XMLElement implementation, but may increase page generation times exponentially.
• You may find that some extension, fields in particular, produce invalid XML which will cause Exceptions to be thrown.

With that said however, it might just work. Grab the code from the branch I linked to above, take a backup of your site, then replace your symphony folder.

All pages will then use the safe segfault free implementation unless otherwise specified, which you can do by adding the unstable-xml page type to your page.

Report back and let me know your before and after times!

I'm hitting a 90Mb memory limit in PHP's memory_limit on one of my sections listings.

I don't think changing XMLElement will help here, as this is hitting the limit in the backend, right? Is this in the section editor? I've often found that I need to bump the memory limit up when the section and data source editor bombs. My guess is that it's because the entire Symphony structure needs to be built (all sections + all fields) and put into the HTML for quick JavaScript switching in the interface.

This is definitely a problem, and some refactoring of the Section/FieldManager classes could possibly help.

I don't have any control over upping the memory limit here

Can you try adding to your .htaccess:

php_value memory_limit 128M

Does that work?

Rowan, I've read about your work on this, and have been secretly excited by it, however, I can't do that I'm afraid. It's quite an important production site which I can't allow unstable code on.

It's also showcasing the power of Symphony to my employers, which is why I'm a little upset that it is failing this way, especially when the section only has 5 fields (2 being SBL), it's just being used for a cross reference.

Thanks so much for the suggestion though...

Nick,

It's the Entries view for the section. I have the Publish Filtering extension on it, and the view is set to paginate (Sym config) at 30 rows. It's definitely because of the 21628 entries...

I've just tried the memory limit in htaccess to no avail. It's Dreamhost, so they don't allow the memory limit to be increased. I'm shocked it's up at 90mb! That doesn't sound normal for them...

Disabling Publish Filtering and also reducing the pagination to 15 has no effect.

Hmm, I would say the memory limit might have something to do with an infinite loop, because of the simple section.

If you look at the line indicated in the error you will see it is indeed a while loop. However, the argument to this loop is a mysql function. So another possible way of hitting a near inifite loop is if the query has returned a LOT of entries. Because you are viewing this in the backend, you would expect the entries to be limited.

Can you echo the query before it is being executed? (Insert the echo command around line 426 of class.mysql.php). The last query will most likely return too many results.

If you could post that query, we might be able to work from there.

Is it just the one section that causes the error? The total number of entries shouldn't matter too much since only the page size is being queried (EntryManager::fetchByPage does this). Could you list the fields used in this section?

I'm not sure if it's because of an "infinite" loop. The error is indeed within a while loop, but this just happens to be the point in the code at which the memory limit is met. It could be other code executing before this that uses the first 89MB, and this while loop using the last 1MB before hitting the limit.

What fields are in this section?

I'm not sure if it's because of an "infinite" loop. The error is indeed within a while loop, but this just happens to be the point in the code at which the memory limit is met. It could be other code executing before this that uses the first 89MB, and this while loop using the last 1MB before hitting the limit.

Of course, but the point at which the error occurs is always a nice place to start looking. Which is also why I asked designermonkey to post the last query. If that query is indeed unlimited, you have immediately found the culprit.

Sure, if that query only returns a few (small) entries, the fault must have been somewhere else.

Huib, the query is outputting:

array(1) { ["session_data"]=> string(104) "sym-|a:2:{s:8:"username";s:11:"john-porter";s:4:"pass";s:40:"b064039c12eec87b0034061bc876418a82bca30c";}" }

Although, this is only the first query as I have to die; after it otherwise it logs me out and kills the application, due to it outputting for every query.

I am more inclined to go with what Nick has said. When the script errors, it is only trying to allocate 77 bytes of memory, but the memory is full, so it's definitely a build up. It does it very quick too, so the memory is being eaten in big chunks very quickly!

Two screen-shots attached Nick, One of the fields and one of the sections.

Is there any way to get a list somewhere of every query and message from the system? My log file isn't showing anything at all for this.

Attachments:

Screen shot 2011-06-10 at 11.19.00.png and Screen shot 2011-06-10

How do I find the 'last query' that was run at point of error? I have no stack trace at all...

Edit: I just switched on all errors for PHP, with no difference in the error output. humph

It's those two SBL fields. Even if you're not displaying them in the entries table, for every entry row that is queried the 100+ Artists and 7000+ Books will be queried when building that entry's data. Can you temporarily redice the 99999 limits to something more sensible, say 20 or 100 and see if that makes a difference.

So in memory it will be selecting all 7000 Books ready to build the <select> dropdown. If you're showing 20 entries in the table, that's selecting 7000 books 20 times.

If so, then you may need to consider a different field type, or optimising the SBL field somehow. The option will probably be extending the SBL field to use an AJAX search, so that it doesn't load all values into memory first, but when you come to view the field in the backend it uses an AJAX request to look up in the database for matching values. Or just lazy-loading the entries once the page has loaded.

Publish Filtering will also be loading these massive number of options.

Thinking out load, the lazy loading option would be preferable. You could set the limit of the SBL to 1 (the bare minimum). Then write an extension which adds a JS file to backend pages which:

• binds a JS event handler to the focus event of any SBL field that has one <option>
• performs an AJAX request to a custom page the extension provides which returns all of the actual <option> elements (or to the REST API if it's installed)
• add a little AJAX spinner to show this interaction might take some time

Doesn't solve the Publish Filtering dilema though.

Do you definitely need all of these options in the SBL dropdown?

How do I find the 'last query' that was run at point of error? I have no stack trace at all...

Inside the MySQL class echo the query string that is run. When you load the page you'll see all of the echos in the page before any markup.

It's those two SBL fields.

I can completely get that. However, I don't understand how each entry in the SBLs is being loaded on the index table view? Doesn't each entry have just one ID that is used in a where statement returning 1 result per entry? How does the MySQL work here?

Do you definitely need all of these options in the SBL dropdown?

No. I don't really, the data is kind of static and only set on an import. What would happen if the user needs to select an option that isn't listed? It's always bothered me that we limit the options this way, without a fallback for users who need an option that isn't listed.

Maybe jQuery autocomplete can be used to update/modify the Selectbox Link field... Hmm, a little project there me thinks!

Setting the SBLs to 10 fixed it, thanks Nick!

Maybe jQuery autocomplete can be used to update/modify the Selectbox Link field... Hmm, a little project there me thinks!

Have you seen the Reference Link field?

I can completely get that. However, I don't understand how each entry in the SBLs is being loaded on the index table view? Doesn't each entry have just one ID that is used in a where statement returning 1 result per entry? How does the MySQL work here?

Well for one, 7000 entries will be loaded for the With Selected menu. Publish Filtering (IIRC) will also load 7000 entries into the HTML so it can filter quickly.

Then for each row on the publish index there is four queries for every column shown on your Publish index that is a SBL link (all returning 1 row, that is then cached in a poor man style, but nevertheless the query is over 21000+ rows). I can't quite remember exactly, but then I'm pretty sure each row then is another 2 queries (each) over the SBL entry data tables.

So short answer, yeah, there's a lot of queries going on.

Is it possible to try and remove any SBL from the publish index view?

However, I don't understand how each entry in the SBLs is being loaded on the index table view

My hunch is the "With Selected" drop down. Select Box and SBL fields provide a list of the possible options, which populates an optgroup in this dropdown. So I think that the getToggleStates method is called on each SBL field when the entries table is built, which in turn calls the SBL's internal findOptions method, which does the work of looking at the linked section and returning all possible values.

So if you do need the 7000 options in the dropdown, you could try changing the canToggle method of the SBL field to always return FALSE. This might stop the queries firing when building the entries table.

The fact that this executes on every SBL field for every entry shown feels like a bug. Symphony should be clever enough to cache the result of the first instance of the field, and not bother calling the getToggleStates method for future instances, since the values should be the same for all. So if your table displays 17 entries, the getToggleStates should only be called on the first entry.

If this is fixed, you can go back to increasing the limit to 99999 without the entry overview breaking. However this still yields two problems. Firstly when you view an entry, the 7000 relatedvalues will be loaded, to populate the select box UI. As I've described above this could be partially "fixed" by either providing an autocomplete function to only select these values as you type (not useful if you don't know what to search for), or to lazy load the data with an AJAX request (so you only download the 7000 values when you interact with the field).

The related problem is that the Publish Filtering extension parses the same HTML that is rendered in the entry form. So if 7000 values are in the dropdown, Publish Filtering will load these 7000 items on the entry list table. Again, this could be solved by lazy loading, but is a bit fiddly.

I will open a new bug on the tracker describing the idea to only call getToggleStates once per field per page of results. This alone might let you have such a large limit without hitting the memory limits.

Oh, Brendan was far more succinct with the same diagnosis!

Have you seen the Reference Link field?

From memory this doesn't actually solve anything. Its values are still all queried and dumped into the HTML at once, just the autocomplete UI makes it easier to look through them. Craig has often talked about an autocomplete-style field/extension, which is now more easy with the REST API extension.