csv_from_result() row/size limit?

[eluser]Jeff Simmonds[/eluser]
I've spent the day building a reporting tool at work using codeigniter. One of the requirements of this tool is that the reports can be exported as csv files. Easy enough, or so I thought.

The first problem I'm having is that there are no headers in the exported file. There is a blank line where the headers should be. After inspecting the csv_from_result function everything seems to be in order. Any advice on this would be greatly appreciated.

The second and more serious problem is that any reports I run that have more than about 10,000 rows freeze the site. There is no error message and when I look in the log files it appears that the site just stopped loading just before the views. I don't think the issue is a timeout because a report with 9,700 rows runs and exports in about 1 sec but a report with 10,200 rows gives me a blank screen.

I am running CI 1.7.1

Any ideas would be appreciated,

Jeff

[eluser]jedd[/eluser]
The easy bit ...

In your php.ini file, look for the line:

Code:

memory_limit = 128M

It's probably set to 16M or so at the moment, so bump it up a bit. That should either rule out, or resolve, memory as the causal factor of the problem.

[eluser]Jeff Simmonds[/eluser]
[quote author="jedd" date="1237962545"]The easy bit ...

In your php.ini file, look for the line:

Code:

memory_limit = 128M

It's probably set to 16M or so at the moment, so bump it up a bit. That should either rule out, or resolve, memory as the causal factor of the problem.[/quote]

Thanks for the suggestion.

Unfortunately or fortunately depending on your point of view the memory limit on this server is set to "memory_limit = 1024M". Also as a side note, max_execution_time = 30.

I'm open to any other suggestions you might have now that "The easy bit" has been ruled out.

Thanks very much for the help,
Jeff

[eluser]jedd[/eluser]
Doncha hate that?

Does it freeze while it's generating the csv results, or while it's generating the file from same?

I tend to concur about it not sounding like a timeout (or, now, a memory issue). Does the CPU do anything spectacular after the 2s or so that it takes to freeze, or does it just seem to give up and go back to idle? EDIT Actually just for poops and giggles - have you turned on profiling, to see what resources you're using the for the successful calls?

Can you clarify - 9,700 rows works fine, but somewhere around 10,200 rows it fails - and the only difference is the number of rows (no substantive difference in calculations, record lengths, etc)? It's a shame there are no magic numbers in that vicinity.

[eluser]Jeff Simmonds[/eluser]
[quote author="jedd" date="1237968165"]Doncha hate that?

Does it freeze while it's generating the csv results, or while it's generating the file from same?

I tend to concur about it not sounding like a timeout (or, now, a memory issue). Does the CPU do anything spectacular after the 2s or so that it takes to freeze, or does it just seem to give up and go back to idle? EDIT Actually just for poops and giggles - have you turned on profiling, to see what resources you're using the for the successful calls?

Can you clarify - 9,700 rows works fine, but somewhere around 10,200 rows it fails - and the only difference is the number of rows (no substantive difference in calculations, record lengths, etc)? It's a shame there are no magic numbers in that vicinity.[/quote]

Thanks again for the reply.

I'm pretty sure the problem is occurring while the file is being created as I can call csv_from_result() from within the model with no references to the data outside of the model and it still processes fine below the ~10K records threshold but bombs when you cross it.

The cpu doesn't seem to hang any higher than normal after the request but there is a steady load on the server at all times.

As for benchmarks:

Code:

MEMORY USAGE: 2,848,416 bytes
BENCHMARKS:
Loading Time Base Classes      0.0062
Controller Execution Time ( General / Dailysales )      0.2750
Total Execution Time      0.2812

Those numbers are from a successful report with 9748 results.

The type of data from a ~9,700 result query to a ~10,200 result query doesn't change. Here is a sample row from a successful export:

Code:

"885544","7.77","3.99","0.00","11.76","visa","487659","www.domain.com","AUTH_CAPTURE",

All the rows are similar to this with only the values changing.

I'm right there with you on the "magic numbers". I feel like when I figure out what the problem is I'll feel stupid for missing it.

Thanks for your help,
Jeff

[eluser]jedd[/eluser]
Hi Jeff,

No magic bullets from here, I'm afraid. But I can tell you what I'd do.

Confirm/exclude memory relevance - shift the php memory limit up or down by a fair bit - say to 128mb for starters - and see what that does to your break-point (whether it's still around the 10k mark). If it is a memory problem, don't assume it's a linear correlation.

(Alternatively / additionally - try this on a different machine, ideally a different OS, to confirm if it breaks at exactly the same threshold.)

If that suggested anything useful ...

Actual vs assessed memory usage - not sure how PHP calculates memory usage - whether it does it based on size of data, or size to store that data, if you see what I mean. I know objects take up a lot more space than they appear to, and I'm guessing arrays have a bit of bloat around them. Yes, it seems unlikely it could be this much, but I'd want to rule it out, especially given intergoogle murmurings regarding php / csv / memory problems. I might try something like inserting a sleep(5s), say, for every thousand records created in the csv loop - and watch apache's memory usage during a run. Obviously you'd do this on an isolated test box, after an apache restart etc.

But if it didn't, then I'd be stumped. There may be some internal magic number - say row * elements - but it seems unlikely. I'd pump up the php/apache log settings to Insane and tail -f them, but I assume you've done this already. I'd probably look at some truly dodgy workaround in the short term - splitting the report into much smaller lumps and concatenating them - but I'm guessing you've also (unhappily) entertained that idea already.

[eluser]johnwbaxter[/eluser]
Right, firstly, there is no magic 10,000 row limit. There just isn't unless you've specified it in your query!!

Secondly it must be a limitation in your php.ini, i tend to use "ini_set('memory_limit', '300m'); in my script instead of doing it in php.ini. Also there may be a time limit set in php.ini so you wanna have a look through for anything that specifies script or process running time and bump it right up.

Thirdly i use this http://ellislab.com/forums/viewthread/86928/ instead of the CI function as it runs a million million times quicker.

I have an app that exports csv's all the time and it exports way more than 10,000 records quite happily.

[eluser]jedd[/eluser]
[quote author="audiopleb" date="1238007184"]Right, firstly, there is no magic 10,000 row limit. There just isn't unless you've specified it in your query!![/quote]

For sure. The problem manifests around that number, and current thinking is that it's a function of that and some other item - row length, number of elements, etc. And presumably memory related.

Secondly it must be a limitation in your php.ini, i tend to use "ini_set('memory_limit', '300m');

Memory is already set to 1024 mb.

Also there may be a time limit set in php.ini

PHP.ini timeout is already set to 30s. The script failure occurs in under 3s.

I'll leave Jeff to play with the [url="http://ellislab.com/forums/viewthread/86928/"]implode approach[/url] you mentioned.

I have an app that exports csv's all the time and it exports way more than 10,000 records quite happily.

No one is suggesting that other people with similar data volumes have the same problem. Hence the weirdness factor.

[eluser]johnwbaxter[/eluser]
Could be a odd bit of data in the db that's messing it all up.

Still, kudos to me for suggesting that link. I rock.

[eluser]Jeff Simmonds[/eluser]
[quote author="jedd" date="1238004724"]Hi Jeff,

No magic bullets from here, I'm afraid. But I can tell you what I'd do.

Confirm/exclude memory relevance - shift the php memory limit up or down by a fair bit - say to 128mb for starters - and see what that does to your break-point (whether it's still around the 10k mark). If it is a memory problem, don't assume it's a linear correlation.

(Alternatively / additionally - try this on a different machine, ideally a different OS, to confirm if it breaks at exactly the same threshold.)

If that suggested anything useful ...

Actual vs assessed memory usage - not sure how PHP calculates memory usage - whether it does it based on size of data, or size to store that data, if you see what I mean. I know objects take up a lot more space than they appear to, and I'm guessing arrays have a bit of bloat around them. Yes, it seems unlikely it could be this much, but I'd want to rule it out, especially given intergoogle murmurings regarding php / csv / memory problems. I might try something like inserting a sleep(5s), say, for every thousand records created in the csv loop - and watch apache's memory usage during a run. Obviously you'd do this on an isolated test box, after an apache restart etc.

But if it didn't, then I'd be stumped. There may be some internal magic number - say row * elements - but it seems unlikely. I'd pump up the php/apache log settings to Insane and tail -f them, but I assume you've done this already. I'd probably look at some truly dodgy workaround in the short term - splitting the report into much smaller lumps and concatenating them - but I'm guessing you've also (unhappily) entertained that idea already.[/quote]

Thanks for the good advice jedd.

Your thoughts confirmed that I had not lost my mind!

Earlier I had said:

I feel like when I figure out what the problem is I’ll feel stupid for missing it.

Well, I was right.

It turns out that someone had edited the .htaccess file and set:

Code:

php_value memory_limit 16M

So the value I was seeing in php.ini didn't apply to the dir I was working in.

So as you first predicted it was a memory error. I can now run ~60k+ rows with no problem.

Thanks for all your help, I was right on the verge of concatenating batches (not the solution I wanted) when I stumbled on this setting.

Jeff