High memory use requesting all data

[zero-24]

Steps to reproduce the issue

Navigate to https://developer.joomla.org/stats

Expected result

JSON of the raw data

Actual result

Error 500

[wilsonge]

500 for me. On it

[mbabker]

Fatal error: Allowed memory size of 268435456 bytes exhausted (tried to allocate 6 bytes) in vendor/joomla/database/src/Mysqli/MysqliDriver.php on line 809

Apparently we'll pulling too much data...

[wilsonge]

The server OS page is stupidly slow to load (or at least it was for me on the first attempt). After the cache kicked in got a lot better

[mbabker]

We might have to get Rochen to bump the PHP memory for that server. I went through a round of trying to optimize things a bit and I was able to get the full results set to load again on my local but pushed onto the server it's still running out of memory.

[mbabker]

Also edited the title a bit since the whole API isn't broken, just the endpoint that pulls the entire database.

[mbabker]

It's working for now.

[mbabker]

Memory's exhausting again. On a PHP 5.5 install a 256M memory limit isn't cutting it.

[wilsonge]

I'll ping Eli again to try and see what we can do. I'm also rushing through the community.joomla.org upgrade so that we can get a PHP version upgrade ASAP

[zero-24]

Do we have no .htacces there you can switch the PHP version per folder?

[mbabker]

No. The Joomla servers don't have this option. We aren't configured the same as Rochen's shared hosting accounts.

On Saturday, April 9, 2016, zero-24 notifications@github.com wrote:

Do we have no .htacces there you can switch the PHP version per folder?

— You are receiving this because you commented. Reply to this email directly or view it on GitHub https://github.com/joomla-extensions/jstats-server/issues/26#issuecomment-207794603

[mbabker]

@wilsonge Gotten anything here? The DB is just about to roll over 300,000 rows and we're still having issues serving up the root / endpoint. I'm starting to worry how long the individual metric endpoints will keep working with this much data.

[alikon]

we can let do some work to the db side to reduce memory

SELECT 'p', php_version , count(*) 
FROM jos_jstats 
GROUP BY php_version
union 
SELECT 'd', db_type , count(*) 
FROM jos_jstats
GROUP BY db_type
union 
SELECT 'c', cms_version , COUNT(*) 
FROM jos_jstats
GROUP BY cms_version
union ALL 
SELECT 's', server_os , COUNT(*) 
FROM jos_jstats 
GROUP BY server_os

but i'm afraid i'll took quite a bit to run....

[mbabker]

It might and it might not. Just running that query on the database directly took nearly 18 seconds the first time I ran it, then I added the db_version to the query and the second run (and subsequent since I went through and deleted some rows with obviously spoofed values) took less than 2 seconds.

[alikon]

...let me propose a RFC...

a common solution can be to follow the "Materialized Views" approach (even if mysql lacks),

for example consider to add a new table for "cms", "os", "db", "php", etc

where each one contains the pre-calculated (materialized) result of a query.

something like this

SELECT  server_os , COUNT(*) 
FROM jos_jstats 
GROUP BY server_os

a refresh of the materialized views can be done (once a day, once a hour, etc) leveraging on the modified field of jos_stats table via a cli php script running under a scheduled crontab....

[mbabker]

Eli and George were already discussing something similar to that yesterday. I'll leave them to share more, but I already threw some of my concerns about that at them and had them addressed.

[wilsonge]

https://github.com/joomla-extensions/jstats-server/tree/mysql_improvements branch here with the WIP - still some way to go yet - largely in moving all the views across and creating the server OS table

[mbabker]

I changed the model's getItems() method to use Generators and the main endpoint is at least working again. Still should keep optimizing things, but based on PHP's docs that will help with the memory usage a fair bit.

[mbabker]

The API's still holding up serving 406K rows now with Generators implemented. Considering this issue closed out for now, see #27 for further refinement.

[zero-24]

Thanks!