| *** clach04 has quit IRC | 00:40 | |
| *** rossand has quit IRC | 01:25 | |
| *** perftuning has quit IRC | 01:53 | |
| *** troal011 has joined #ingres | 02:05 | |
| *** troal01 has quit IRC | 02:22 | |
| *** rossand has joined #ingres | 03:00 | |
| *** ChanServ sets mode: +o rossand | 03:00 | |
| *** rossand has quit IRC | 04:43 | |
| *** Alex|off is now known as Alex| | 06:54 | |
| *** troal011 has quit IRC | 07:36 | |
| *** Alex| is now known as Alex|off | 07:58 | |
| *** Alex|off is now known as Alex| | 07:59 | |
| *** troal01 has joined #ingres | 08:05 | |
| *** grantc_offline is now known as grantc | 08:27 | |
| *** crogr01 has joined #ingres | 08:30 | |
| *** withdefault has joined #ingres | 08:31 | |
| *** mib_5tyfuu has joined #ingres | 08:32 | |
| *** mib_5tyfuu has left #ingres | 08:32 | |
| *** crogr01 has quit IRC | 08:32 | |
| *** ChanServ sets mode: +o withdefault | 08:34 | |
| *** crogr01 has joined #ingres | 09:19 | |
| *** crogr01 has left #ingres | 09:19 | |
| *** crogr01 has joined #ingres | 09:23 | |
| *** crogr01 has left #ingres | 09:23 | |
| *** crogr01 has joined #ingres | 09:26 | |
| *** crogr01 has left #ingres | 09:26 | |
| *** grantc has quit IRC | 09:29 | |
| *** grantc has joined #ingres | 09:30 | |
| *** ChanServ sets mode: +o grantc | 09:30 | |
| *** crogr01 has joined #ingres | 09:48 | |
| *** crogr01 has left #ingres | 09:48 | |
| SlimeyPete | grantc: we're beginning our testing for migration to PHP5, and we've noticed that our memory usage has shot up considerably. I can't be 100% sure yet but I think that retrievals from Ingres are using considerably more memory than they sued to. Is this a known phenomenon? | 10:48 |
|---|---|---|
| SlimeyPete | ... or have we just managed to mis-configure things somehow ... | 10:48 |
| grantc | SlimeyPete, perhaps | 10:49 |
| * SlimeyPete is having some trouble figuring out exactly where the problem lies because our PHP4 setup doesn't have memory_get_usage() compiled in >.< | 10:49 | |
| grantc | the driver allocates memory for a fetch of 100 rows | 10:49 |
| grantc | depending on the row width it may or may not fill that buffer | 10:50 |
| grantc | i can add a config parameter to configure the number of rows it buffers | 10:51 |
| grantc | or tries to buffer | 10:51 |
| SlimeyPete | Hmm. In this case we're fetching more than 100 rows; it's more like 1000+, all in one hit (I think my boss' wrapper function uses the fetch_all function) | 10:51 |
| SlimeyPete | and later on in the script I'm looking at we're fetching perhaps 10-100k rows depending on the user's parameters (it's a web-based report) | 10:52 |
| grantc | sure but what happened before in the driver it would allocate the memory for a single row, fetch that row, free row and repeat for each row | 10:52 |
| grantc | now the driver tries to batch fetch 100 rows at a time to reduce the comms traffic | 10:52 |
| SlimeyPete | Ah. Now it allocates for 100, fetches 100, rinse & repeat? | 10:52 |
| grantc | yup | 10:53 |
| grantc | there is no logic there to adjust for the row width | 10:53 |
| SlimeyPete | thing is, the difference in memory usage is in megabytes | 10:54 |
| grantc | once you have finished with the resultset you can call ingres_free_result() and it will release | 10:54 |
| SlimeyPete | at least, that's my best guess, not having access to memory_get_usage on our PHP4 system | 10:54 |
| grantc | the only place memory allocation has ballooned up, afaicr is from 2.0.0 to 2.0.1 | 10:54 |
| SlimeyPete | Hmmm. | 10:57 |
| SlimeyPete | Once it's fetched its 100 rows, I take it it then frees the buffer? So the maximum overhead at any one time should be 100 rows-worth of memory, if we were fetching the smallest possible rows. | 10:58 |
| grantc | the driver does not leak, at least from what i have seen from valgrind | 10:58 |
| grantc | yes something like that ... | 10:58 |
| SlimeyPete | Thanks for the info. I think I'll dig a bit deeper into my boss' wrapper function and see if I can pinpoint the problem a bit better. | 11:00 |
| grantc | if you want a patch to reduce/increase the number of rows let me know | 11:00 |
| grantc | Alex|, same goes for you | 11:01 |
| grantc | http://ingres.pastebin.com/m6e1329c8 - needs to be applied against CVS HEAD revs | 11:04 |
| SlimeyPete | Hmm. Retrieving 3113 rows into an array using ingres_fetch_array in a while-loop uses 5.79MB of memory, which is 2KB per row. | 11:17 |
| grantc | how big is the row in Ingres? | 11:17 |
| SlimeyPete | you mean the maximum row size we have configured, or the size of the data that the query actually returns? | 11:18 |
| grantc | the latter | 11:18 |
| SlimeyPete | I don't know how to find out, tbh | 11:18 |
| SlimeyPete | though I can have a pretty decent guess, actually, cos I know the column widths... hang on | 11:19 |
| grantc | also how are you referencing the data in the array, by number or col name? | 11:20 |
| SlimeyPete | I think we use both, in the same array (there's some ancient code in some of the pages which still relies on column numbers) | 11:21 |
| SlimeyPete | right, my matchbook calculation suggests an upper limit of 40 bytes worth of actual data per row (we're only retrieving a few columns at this point and they're a mixture of c[2-8] and i4) | 11:22 |
| grantc | hmm | 11:22 |
| grantc | how many queries are you issuing in a request? | 11:23 |
| *** withdefault has quit IRC | 11:23 | |
| SlimeyPete | One. The connection is opened, a single query (which may use a UNION, as in this case) is fired off, then ingres_commit() is called and the connection is closed. | 11:24 |
| grantc | hmm | 11:24 |
| grantc | I guess i need to have a closer look at this | 11:25 |
| grantc | can you file a bug | 11:25 |
| SlimeyPete | Okay. Could you remind me how to find out what version of the driver we're using please? | 11:25 |
| grantc | http://pecl.php.net/bugs/report.php?package=ingres | 11:25 |
| SlimeyPete | is it in phpinfo? (I can't see it) | 11:25 |
| grantc | echo INGRES_EXT_VERSION | 11:25 |
| SlimeyPete | ah, cheers | 11:26 |
| grantc | it should be in phpinfo as well | 11:26 |
| grantc | next to "Ingres Extension Version" | 11:26 |
| SlimeyPete | hmm, looks like we're running v2.0.0 | 11:29 |
| SlimeyPete | might it be worth upgrading first? | 11:29 |
| grantc | for sure - things will be quicker under 2.0.2 or current cvs head | 11:30 |
| SlimeyPete | also, a thought occurs: our page size on the DBMS is 2KB. Is it possible that Ingres itself is sending the rows padded to a full page? | 11:32 |
| * SlimeyPete is not DBA-trained so is on shaky ground here ;) | 11:33 | |
| SlimeyPete | it is a flippin' ancient version of Ingres | 11:33 |
| grantc | without debugging the code on your ingres installation/version i could not say but i would say it is unlikely | 11:34 |
| SlimeyPete | right... well I'll ask our SAs to upgrade the driver, and then if things are still broken I'll file a bug | 11:36 |
| grantc | with php 4 did you use the 2.0 driver or the built-in one? | 11:37 |
| SlimeyPete | the built-in one, I think | 11:38 |
| grantc | hmm | 11:38 |
| SlimeyPete | yes, I remember that it's a 1.x version | 11:38 |
| grantc | not built-in but based on the same code (more or less) | 11:38 |
| grantc | 2.0 is was a massive change... | 11:38 |
| grantc | in fact for your use case - connect/select/fetch/close there should not be that much difference. | 11:39 |
| grantc | except the driver fetches via a cursor now (for selects) | 11:40 |
| grantc | i have on my list, as mentioned before ingres_unbufferred_query() which will be have as before | 11:40 |
| grantc | behave even.. | 11:40 |
| SlimeyPete | is there any easy way to intercept & log the exact data that Ingres is sending across the network when the openapi call is made? | 12:01 |
| * SlimeyPete guesses probably not | 12:02 | |
| grantc | define easy | 12:02 |
| grantc | define II_API_TRACE=5 II_GCA_TRACE=5 II_API_LOG=/tmp/some.log | 12:03 |
| SlimeyPete | "could be achieved within an hour or so" | 12:03 |
| grantc | setting those variables and running the php via the shell will be easier, via the web server you need to jump through some additional hoops | 12:03 |
| SlimeyPete | hmm... I could have a crack at that after lunch | 12:04 |
| SlimeyPete | I'm just curious as to what Ingres is actually doing when I run this stuff | 12:04 |
| *** MagnusG| has joined #ingres | 12:33 | |
| pboro | Hi MagnusG| | 12:34 |
| MagnusG| | Hi pboro | 12:34 |
| pboro | hmmh | 12:53 |
| pboro | Caused by: com.ingres.gcf.util.SqlEx: Cannot INSERT into table '"opsi_opetusohjelma_otsikot"' because the values do not match those in table '"opsi_opetusohjelmat"' (violation of REFERENTIAL constraint '"$opsi__r000005f600000000"'). | 12:53 |
| pboro | the funny thing is, that iiconstraints table does contain "$opsi__r000005f600000000", but it for different table than the one mentioned in the error message | 12:53 |
| pboro | ahh... figured it out | 12:55 |
| *** rossand has joined #ingres | 13:24 | |
| *** ChanServ sets mode: +o rossand | 13:24 | |
| *** DarylM has joined #ingres | 14:49 | |
| * SlimeyPete notes that the OpenAPI log is making his eyes spin :D | 15:07 | |
| grantc | SlimeyPete, ha-ha :) | 15:07 |
| SlimeyPete | as far as I can see, Ingres is sending through the data in a perfectly reasonable form, so I guess I can rule out a problem with the DBMS. | 15:08 |
| SlimeyPete | right, time to poke our SAs and get 'em to upgrade the driver :) | 15:09 |
| grantc | it could be a difference in PHP | 15:09 |
| grantc | it's now recommended that memory_limit is set to 128 or 256 | 15:10 |
| SlimeyPete | our PHP version's actually newer than your bug report form will accept :D | 15:10 |
| grantc | NOTB then :) | 15:10 |
| SlimeyPete | yeah, the last resort is to just up the limit | 15:10 |
| SlimeyPete | but I'd still be concerned that our script is taking up so much more memory | 15:11 |
| grantc | once you submit the bug report I can submit a review for my change to configure the number of rows to buffer | 15:11 |
| *** Alex| is now known as Alex|off | 15:20 | |
| *** Alex|off is now known as Alex| | 15:20 | |
| SlimeyPete | bah. I was going to just submit the bug-report anyway, but it won't let me unless I pretend we're running 2.0.2 | 15:29 |
| grantc | i won't tell anyone | 15:29 |
| SlimeyPete | :) | 15:31 |
| SlimeyPete | k, submitted | 15:31 |
| SlimeyPete | if you need more detail then just poke me and I'll see what I can do | 15:31 |
| SlimeyPete | I suppose I could've included the code from our wrapper script but it's basically just the same as the examples on the wiki | 15:32 |
| SlimeyPete | except with an extra ingres_commit() | 15:32 |
| *** troal01 has left #ingres | 15:32 | |
| grantc | what was your memory limit under php 4? | 15:34 |
| SlimeyPete | 8MB. | 15:35 |
| pboro | pretty low | 15:35 |
| grantc | erm yeah | 15:35 |
| SlimeyPete | so it seems, but it worked | 15:35 |
| SlimeyPete | and now it doesn't even get through the little preliminary queries on this particular page; there's a *way* bigger one further on in the code which the PHP4 system is perfectly happy with. | 15:36 |
| SlimeyPete | PHP5 hits the limit before it even gets to the big one | 15:36 |
| SlimeyPete | I suspect I'll wind up recommending that we raise the limit quite significantly, but my boss won't be happy about doing that instead of figuring out why our script is suddenly consuming so much more RAM | 15:37 |
| grantc | SlimeyPete, can you put "memory_get_usage()" in between each Ingres call and paste the results into pastebin? | 15:38 |
| SlimeyPete | Sure. Give me a few minutes. | 15:39 |
| grantc | ok | 15:39 |
| *** Alex| is now known as Alex|off | 15:42 | |
| SlimeyPete | any particular pastebin preferred? | 15:43 |
| grantc | ingres.pastebin.org | 15:44 |
| grantc | sorry .com.. | 15:44 |
| SlimeyPete | http://ingres.pastebin.com/m6d700a9a | 15:45 |
| SlimeyPete | that's in before, after & inbetween ingres_query and the ingres_fetch_array calls | 15:45 |
| grantc | ok sounds good - what if you add ingres_free_result($result) after the fetch and get another memory count? | 15:45 |
| grantc | also if you do ingres_fetch_array($result,INGRES_ASSOC) | 15:47 |
| pboro | E_PS03A0 Subselects are not supported inside join qualifications. | 15:47 |
| pboro | damnit :) | 15:47 |
| SlimeyPete | grantc: I've been called away so gimme another few minutes but it seems that ingres_free_result did something good | 15:49 |
| *** Alex|off is now known as Alex| | 15:50 | |
| grantc | SlimeyPete, no problem | 15:50 |
| *** Alex| is now known as Alex|off | 15:52 | |
| *** Alex|off is now known as Alex| | 15:52 | |
| SlimeyPete | grantc: Hmm. The addition of free_result has massively reduced the memory usage. It's now peaking at ~800k instead of 8MB | 15:53 |
| grantc | the older driver would only allow for a single result set to be open which would limit any impact | 15:54 |
| grantc | the new one the restriction is dependant on the number of cursors ingres lets you open | 15:54 |
| SlimeyPete | I guess this is the solution then, though I shall speak to my boss about increasing our memory limit anyway | 15:54 |
| grantc | ok - looking at the php.ini-recommended that comes with 5.2.8, memory limit is now 128M | 15:55 |
| SlimeyPete | Hmm... though hang on | 15:56 |
| SlimeyPete | I may have put it in a slightly dumb place in the code | 15:56 |
| SlimeyPete | does free_result just clear the buffer or does it completely wipe the result-set including rows that haven't been retrieved using fetch_array yet? | 15:57 |
| grantc | the latter | 15:57 |
| SlimeyPete | Ah, doh | 15:57 |
| SlimeyPete | So putting it in the loop which is fetching the results row by row was a silly thing to do then :D | 15:58 |
| SlimeyPete | if I put it just after the array, we're still talking megabytes of memory usage and it does hit the limit | 15:58 |
| grantc | a go faster stripe :) | 15:58 |
| SlimeyPete | just after the loop, I mean | 15:58 |
| grantc | if you add INGRES_ASSOC to the call to ingres_fetch_array | 15:59 |
| grantc | ? | 16:00 |
| SlimeyPete | that doesn't seem to make much difference, if any | 16:00 |
| SlimeyPete | 1140080 - 7224400 | 16:01 |
| SlimeyPete | ^^ bytes, before & after results are fetched | 16:01 |
| grantc | ok | 16:01 |
| *** troal01 has joined #ingres | 17:59 | |
| *** DerMeister has joined #ingres | 18:12 | |
| *** Alex| is now known as Alex|off | 18:53 | |
| *** clach04 has joined #ingres | 19:03 | |
| clach04 | in03 | 19:03 |
| clach04 | ingbot help | 19:03 |
| *** troal01 has left #ingres | 20:12 | |
| *** grantc is now known as grantc_offline | 20:30 | |
| *** rossand has quit IRC | 20:48 | |
| *** rossand has joined #ingres | 20:54 | |
| *** ChanServ sets mode: +o rossand | 20:54 | |
| *** DerMeister has quit IRC | 21:53 | |
| *** DarylM has quit IRC | 23:13 | |
Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!