Symphony.

I'm migrating about 200,000 entries into a Symphony build. I'm using my Archiving XML with Symphony method since the data is being pulled from a remote database as XML already. I'm pulling 20 entries at a time, then sending as one post, which seems to be working nicely.

I'm using S2.0 downloaded from the Symphony site.

Only, I'm up to about 70,000 entries and I'm seeing massive performance issues.

When I view my section through the Symphony backend the page (17 entries per page) loads within a second or two. Slightly slower than usual, but nothing unexpected.

A DS attached to a page pulling the latest 20 entries takes between 10 and 20 seconds to execute. The DS is not filtering, not grouping, and has pagination enabled.

Is this normal? It's performing less than 200 queries total. I've had pages doing up to 1000 queries before and still only taking 3-5 seconds.

Any thoughts?

The times don't quite seem to add up. I wonder whether it could be related to this:

That is very odd. 11.44 seconds to execute that DS, but the total database query time taken is 2.6 seconds. How many fields are in that DS?

The times don't quite seem to add up. I wonder whether it could be related to this:

That actually looks like a bug to me. :/ I'll try to fix this up so you get better information.

If you could fix up the Profile output that would be great, so I can see what's going on.

The DS is pulling only 4 fields (text fields). I was wrong, there is one Filter on the DS, checking that a checkbox is "yes".

I tried stripping the DS right down — remove Filters, it now sorted by System ID, selects 20 entries and only includes one field. Load times only decreased by about 1.5s. My section is filling up as I migrate data across (120,000 entries now) and the DS is now 20s+.

I created another DS to select just one entry from this section, filtering by the System ID field. This DS takes on average about 1s. Would you be able to fix up the Slow Queries section please?

I have upgraded to the latest build from Git which hasn't changed performance at all.

Hmm, odd. By dumping out the slow-queries array, your quandry is confirmed, that the queries themselves only take a few seconds to execute.

["slow-queries"]=>
array(3) {
  [0]=>
  array(5) {
    [0]=>
    string(6) "0.6150"
    [1]=>
    string(203) "SELECT `s`.*, count(`e`.`id`) as `entry_count`

                    FROM `sym_sections` AS `s`
                    LEFT JOIN `sym_entries` AS `e` ON `s`.id = `e`.`section_id`

                     WHERE `s`.`id` = '18' 
                    GROUP BY `s`.id"
    [2]=>
    NULL
    [3]=>
    NULL
    [4]=>
    bool(false)
  }
  [1]=>
  array(5) {
    [0]=>
    string(6) "2.3384"
    [1]=>
    string(453) "SELECT  `e`.id, 
                        `e`.section_id, e.`author_id`, 
                        UNIX_TIMESTAMP(e.`creation_date`) AS `creation_date`, 
                        UNIX_TIMESTAMP(e.`creation_date_gmt`) AS `creation_date_gmt`

                FROM `sym_entries` AS `e`

                LEFT JOIN
                    `sym_entries_data_81` AS t81_1
                    ON (e.id = t81_1.entry_id)

                WHERE 1

                AND `e`.`section_id` = '18' 

                AND t81_1.value = 'yes'

                ORDER BY `e`.`creation_date` DESC
                LIMIT 0, 20"
    [2]=>
    NULL
    [3]=>
    NULL
    [4]=>
    bool(false)
  }
  [2]=>
  array(5) {
    [0]=>
    string(6) "1.2254"
    [1]=>
    string(239) "SELECT count(`e`.id) as `count`
                FROM `sym_entries` AS `e`                           

                LEFT JOIN
                    `sym_entries_data_81` AS t81_1
                    ON (e.id = t81_1.entry_id)

                WHERE 1
                AND `e`.`section_id` = '18' 

                AND t81_1.value = 'yes'"
    [2]=>
    NULL
    [3]=>
    NULL
    [4]=>
    bool(false)
  }
}

I wonder then, where this additional time is coming from...

Bump.

nickdunn, i can't help You much with page slowing down problem, but here's fix You can apply to symphony/lib/toolkit/class.profilepage.php to make it render slow-queries table properly.

At the end of file, in "case 'slow-queries':" part (around line 183) there is:

foreach($records as $r){

change it to:

foreach($records['slow-queries'] as $r){

and it will make list of queries much more usable :).

Thanks ahwayakchih. I have pulled in that change.

@Nick: I don't suppose this is a site I could access? The slowness is coming from somewhere in the data source and not really related to queries (although they are a little slow too). To find out what is causing the bottle neck, it would be necessary to probe around the code a little.

Is there any news on this performance issue?

I should have posted my findings here, apologies.

These metrics were running on my local MAMP install on an old Macbook Pro. Moving the build to a remote production server (albeit just on a cheap shared host) improved page loads to about 2 or 3 seconds, which is more than acceptable for such a big dataset.

It's case closed for now. The site is still in development, so I'll revisit this issue when I come to finishing development of that part.

Thank you, Nick, for this feedback.

On one of my websites I noticed that actually building the XML takes a rather long time in Symphony 2. It's around one second for most pages, which is not critical at all. But if I compare it to datasource execution and XSLT times, it is a long time...

So I simply thought that there might be changes in the pipeline pushing Symphony's speed.

Unfortunately the XMLElement class creates a lot of bottle necks. Its recursive structure causes exponential resource usage. The other problem area is the generic code responsible for powering the Data Sources. Since it needs to cater for all possible situations that the DS editor allows, the code is overly complicated in places. Lastly the Database class can become a memory hog due to its over-zealous logging.

Needless to say, I am aware of a number of places that Symphony could be improved. Maybe v2.0.2 will see some of these addressed.

In addition to fixing existing things, there are a couple of new improvements we will be adding for a future version. Specifically pre-rendering of field XML to lighten the runtime DS load. This will work especially well for data that seldom changes.

Was there talk of replacing XMLElement with PHP's own DomDocument classes? If so, is it a mammoth task, or just a case of rewriting the inners of the XMLElement class?

As for logging, does database logging only occur when being viewed by a developer? Perhaps this should be the case (i.e. only do logging when viewing ?debug perhaps?).

Alistair, thank you. Glad to hear that certain improvements might be implemented soon.

I suppose that switching to the XMLDocument class (as proposed in this thread is something for the far future, isn't it?

If so, is it a mammoth task, or just a case of rewriting the inners of the XMLElement class?

Unfortunately it is a pretty hefty task. The short-term solution is to make use of it in isolated areas, like specifically for DS's XML generation, which is most memory intensive. In a similar way to the use of my ASDC Extension, perhaps this could be an advanced tool available to developers whom are writing super optimised DS's from scratch. At some point in the future it would be nice to have abolished XMLElement all together, but we might have S3 by then. ;)

As for logging, does database logging only occur when being viewed by a developer?

It does this all the time, hence the "over-zealous" remark, and this is obviously to no-ones advantage. Ive already posted an Extension for developers, ASDC, that is a back port of the Symphony 3 database driver class that is more memory efficient and does no logging unless you require it, but it isn't a replacement for the existing one. My goal is for a replacement to happen in the near future