external engagements admin view - slow query

[aturling]

I can't get the Table D - External Engagements admin view to load (admin/epscor/views/external-engagement) on either the live site or the test site. The MySQL query just runs forever, and the page never loads. I edited the query to just get the "total" and "male" fields, and it worked, but as I added in the "female" fields, it slowed and eventually stopped loading. I see that it works on the NM-Dev site, so I'm not sure what the issue on my side is... different version of PHP? not enough memory to run the query? Has anyone run into this before?

[khuffman]

I had a similar issue with the Accomplishment Table a while back, see my email below. After some weeks new versions of MariaDB and php came out and I updated to those versions and this issue got resolved. I still don't know what was causing it, all I know is that I didn't change anything with er-core but updating the php and MariaDB fixed my issue.

Kia ---------- Forwarded message ---------- From: Chris Allen Date: Fri, Feb 21, 2014 at 12:05 PM Subject: Re: MariaDB & er-core module To: Kia L Huffman Cc: Isis Serna

Kia -

Bringing up an old email in reference to a somewhat similar issue we have been experiencing here on our live site with ER Core installed.

We have been experiencing a very slow response time pretty much only when Isis and I are logged in and are running views and/or editing/adding ER core content (Collaborations, Publications, Presentations, etc).

Let me know if you are indeed experiencing performance issues with ERCore.

Thanks, Chris

On Jan 22, 2014, at 9:46 AM, Kia L Huffman <> wrote:

Is anyone using er-core module on a system that has MariaDB as the database server?

FedoraCore20 comes with MariaDB as the default database server. https://mariadb.com/kb/en/mariadb-versus-mysql-compatibility/

On my test-system I have Fedora20, every time I go to the Accomplishment page and click on the link under the Accomplishment table to generate an Excel output, nothing gets produced, it is unresponsive and never generates the Excel file. I have waited about 1 hour and gave up...

While trying to see what is going on, I noticed by looking at "top" that every time I click on that link, the mariadb process takes 100%-99% of the CPU and never lets go below 99% until I kill it manually.

I have no problem getting Excel files for other tables, e.g. Participants, or Calender Events. This strange 99% CPU load behavior shows up only when I click the link under Accomplishment table.

What kind of db query is er-core making when I'm asking for this Excel file?

Please let me know if you have seen this behavior before or have suggestions how to debug this.

Thanks Kia

[aturling]

That's exactly what I'm experiencing, with "top" reporting mysqld taking ~99% of the CPU and more and more memory until I have to manually kill the process and restart it. I ran "show processlist" and it shows the query stuck in the "sending data" state. I tried upgrading to the most recent versions of MySQL and PHP but it's still not working. I'm not experiencing the same issue with the accomplishments excel download, though, just the Table D - external engagements page.

[iserna]

We have not experienced this specific issue in NM, that I can recall. I think our slowness is something else since all of our drupal sites tend to run slow.

The following is the server and db setup for dev-ercore.nmepscor.net., from our sys admin

Ubuntu 14.04.1 LTS mysql Ver 14.14 Distrib 5.5.40, for debian-linux-gnu (x86_64) using readline 6.3

-Isis

[aturling]

Actually I'm not sure if this is related or not, but our site is sometimes slow and gets low on memory after multiple people have logged in and added content. How much RAM do you have on your sites?

For example here's the top two lines from "top" for our site; this is pretty typical usage for mysqld and httpd after they've been running for a few days:

VIRT RES SHR S %CPU %MEM TIME+ COMMAND

1313m 632m 8504 S 0.0 16.5 1:14.35 mysqld

1107m 330m 7144 S 0.0 8.6 0:37.82 httpd

This seems like a lot of memory being used. Is this normal? We're wondering if we need to upgrade our hardware resources.

[aturling]

Ok I've been playing around with this today and I have the page loading now. First I switched from a left join to an inner join in pages/external-engagements.inc (changed the one instance of "leftJoin" to "join"). That made the page load almost instantaneously for two events with external engagement data, and the data looked correct based on what was entered. When I added a third event, though, the query got stuck in the "statistics" state. I tried a few different values for optimizer_search_depth in my.cnf and setting optimizer_search_depth = 1 seemed to work best. On the test site I've added 14 events with external engagement data, and the page loads pretty quickly still. It may still slow down or get stuck again after a certain point; I'll keep an eye on it as people enter data.

[iserna]

Is Template D, when you download it, calculating correctly?

[khuffman]

We run our live site on a VM with 2GB of memory, the VM runs Scientific Linux release 6.6 php-5.3.3-40.el6_6.x86_64 MySQL version: mysql-server-5.1.73-3.el6_5.x86_64

In php.ini I set the memory_limit = 256M post_max_size = 100M

In my.cnf these two lines are the only things I have added skip-networking max_allowed_packet = 100M

I never had the "slowness/never ending query" issue on our live site, I only experienced it on my laptop running Fedora Core 20 (with 4GB memory) at that time I blamed it on MariaDB and MySQL incompatibility, but it seems it is not the case.

On Tue, Mar 24, 2015 at 6:16 PM, Isis Serna notifications@github.com wrote:

Is Template D, when you download it, calculating correctly?

— Reply to this email directly or view it on GitHub https://github.com/EPSCoR/ERCore-3.0/issues/9#issuecomment-85715062.

[aturling]

The Table D excel download matches the format of the one from the NM dev-ercore site - it sums up all of the various column values for the different categories.

[iserna]

@aturling have you had anymore issues with Table D excel download?

[aturling]

I haven't had any more issues since making that code change to pages/external-engagements.inc and adding "optimizer_search_depth = 1" to my.cnf. I also added "performance_schema = 0" which cut down on mysql's memory usage by a lot.

[iserna]

@aturling Great! Glad to hear the issue is now resolved. I am closing this issue.

[tschet]

I'm getting errors on Table D related to MAX_JOIN_SIZE. The query is too complex for the default server setup on Bluehost. I haven't worked out all of the details yet, but I thought I'd let everyone know this can still be an issue. I've attached the full error message if anyone is interested.

max_join error.txt

[iserna]

@tschet is this error on both the ND and NV sites?

[tschet]

I'd prefer not to identify it at the moment. NV and ND are both hosted on dedicated environments. This one is on Bluehost which defaults to a shared hosting environment. Because one site's bad query can slow down other sites, some functionality is capped to prevent this. The MAX_JOIN_SIZE appears to be one of those caps. This query is blocked regardless of the external engagement node count.

[tschet]

I have temporarily bypassed this problem by commenting out the data display portion of the external engagement include. The /reporting/external-engagement page now loads, and the associated export still works fine. It just doesn't display any of the data for comparison. Replacing the page's data display with another query, or even a View could resolve the issue.