| *** Dejan has joined #ingres | 02:56 | |
| Dejan | hello everybody | 02:56 |
|---|---|---|
| *** grantc has joined #ingres | 03:05 | |
| Vroomfondle | We have a three-column primary key (let's call the columns a, b and c). If I access the table by filtering on column a *only*, Ingres will still use the primary key index, right? | 03:46 |
| grantc | it's certainly possible | 03:48 |
| grantc | is this "select a from table" where a is in an index against table for columns a,b,c? | 03:48 |
| Vroomfondle | it's "select * from table where a = 'blah'" | 03:50 |
| Vroomfondle | where a is the leftmost part of a multi-column key | 03:50 |
| Vroomfondle | (no separate index) | 03:50 |
| grantc | if the optimizer thinks it's better to use the index to fetch the data then it will | 03:52 |
| grantc | but it depends on stats and the trade off between doing a full table scan or using the index to reduce the work it needs to do | 03:53 |
| Vroomfondle | I've just thought of something, tbh. | 03:54 |
| grantc | "set qep" to see what it estimates | 03:54 |
| Vroomfondle | The stats are probably fecked | 03:54 |
| grantc | "set trace point qe90" to see the actual work done | 03:54 |
| Vroomfondle | This is a nightly copy database and optimizedb hasn't been run since it was refreshed. | 03:54 |
| grantc | then it will assume 10% match on col = value (IIRC) | 03:56 |
| grantc | or is it 1% - it's been a while | 03:56 |
| Dejan | vroomfondle, set QEP on | 03:59 |
| Dejan | and check what it does | 04:00 |
| grantc | "set qep" is what it "thinks" it will do whilst "set trace point qe90" is what it actually did | 04:01 |
| grantc | when the output from both is wildly different you know something is wrong | 04:02 |
| Vroomfondle | I don't really know how to read the qe90 thing. Looks like gibberish to me (lots of "et 123" and "ed 456" and "ad 789") | 04:04 |
| Vroomfondle | but the QEP seems very simple - it's taking two steps | 04:04 |
| Dejan | eX are estimated values, aX are actuals | 04:05 |
| Dejan | if i remember well | 04:05 |
| grantc | yup | 04:05 |
| Dejan | it just extends normal QEP | 04:05 |
| Vroomfondle | at 148 | 04:06 |
| Vroomfondle | et 76 | 04:06 |
| Vroomfondle | (etc. - aX is consistently very different from eX) | 04:07 |
| Dejan | it is normal | 04:07 |
| Dejan | because of: "nightly copy database and optimizedb hasn't been run since it was refreshed" | 04:07 |
| Dejan | (my guess) | 04:07 |
| Vroomfondle | I'll ask for optimizedb to be run this afternoon | 04:07 |
| Vroomfondle | when we get a quiet spot to do it in | 04:07 |
| grantc | how many rows are there in the table? | 04:11 |
| Vroomfondle | oh, bazillions. Well, a few hundred thousand. | 04:13 |
| grantc | so you have some stats then? | 04:18 |
| Dejan | you can perhaps do optimisedb on another machine | 04:19 |
| Dejan | and copy statistics from it to the "original" | 04:20 |
| Dejan | i never did so, but i know it is possible :D | 04:20 |
| Vroomfondle | grantc: dunno. Will stats have been created when the data was refreshed? The copy process modifies all tables to truncated and then dumps data into them from files generated from the live database. | 04:26 |
| Vroomfondle | (so I'm told) | 04:26 |
| grantc | no the stats need to be generated manually | 04:38 |
| grantc | there's not auto stat thread | 04:39 |
| grantc | s/not/no/ | 04:39 |
| *** KermitTheFragger has joined #ingres | 04:44 | |
| Dejan | KermitTheFragger, hello | 04:44 |
| *** grantc has quit IRC | 05:12 | |
| *** grantc has joined #ingres | 05:14 | |
| Vroomfondle | my colleague's running an optimizedb. When the tables get truncated again tonight, will all the histograms get blasted away (and if not, will they still be valid once the data's loaded back in, apart from minor differences due to the live data having been updated a bit)? | 05:42 |
| Vroomfondle | I guess what I'm really saying is "do we need to run optimizedb every time we replace the data overnight"? | 05:43 |
| Dejan | i am not an expert but it sounds to me like it should be optimised again | 05:49 |
| grantc | i guess it depends on how much the data changes | 05:51 |
| *** rossand has joined #ingres | 05:52 | |
| *** ChanServ sets mode: +o rossand | 05:52 | |
| *** gerhard has joined #ingres | 06:03 | |
| gerhard | Hi all | 06:03 |
| grantc | hi gerhard | 06:03 |
| gerhard | is it just me who thinks that the "patch frequency" for Ingres enterprise products has increased a lot in the last time...? | 06:06 |
| gerhard | example: Ingres 9.2.1 for Linux-64 exists since end of May 2010 and there are already 3 patches for it | 06:09 |
| *** mull has joined #ingres | 06:19 | |
| *** DarylM has joined #ingres | 06:21 | |
| *** gerhard has quit IRC | 06:42 | |
| *** atrofast has joined #ingres | 06:45 | |
| *** DarylM has quit IRC | 06:46 | |
| *** Alex| has joined #ingres | 09:05 | |
| *** ChanServ sets mode: +o Alex| | 09:05 | |
| *** Dejan has quit IRC | 09:27 | |
| *** bonsaikitten is now known as DrEeevil | 09:57 | |
| atrofast | If you have a int auto_increment col in Ingres, can you manually increment it by one? | 10:35 |
| atrofast | Without inserting a row | 10:36 |
| Alex| | just call nextval on it | 11:01 |
| Alex| | if it is a new "always as identity" column, there is still a system maintained sequence with name $something | 11:02 |
| Alex| | or alter its start value... | 11:02 |
| *** Alex| has quit IRC | 11:07 | |
| *** KermitTheFragger has quit IRC | 11:28 | |
| atrofast | I got a problem with Ingres JDBC (I think)... It's a program that opens two transactions at the same time, when they're both read only it's fine but one of them tries to write it hangs | 12:13 |
| atrofast | Anybody ever run into this? | 12:13 |
| pboro | atrofast, and it's not a deadlock? | 12:14 |
| atrofast | Nope | 12:14 |
| pboro | what ingres version | 12:14 |
| atrofast | 10.0 (svn main) | 12:14 |
| pboro | is the program available somewhere? | 12:14 |
| atrofast | Well yeah, it's Geotools.. I'm working on a port to run it on Ingres | 12:14 |
| atrofast | So you'd need Ingres Geospatial branch and it | 12:14 |
| pboro | oh, haven't done much jdbc on 10.0 yet, neither on geospatial... :/ | 12:14 |
| atrofast | geospatial doesn't really introduce anything new... I'm just wondering if I should set up my connection differently or what not :) | 12:15 |
| pboro | have you checked where the driver gets stuck? you can use for example jstack to print the stacks | 12:15 |
| pboro | you need debug enabled jdbc driver for jstack output to be useful | 12:15 |
| atrofast | Well the Ingres I have installed I built with IIOPTIM=-g | 12:16 |
| atrofast | Not sure if it actually builds teh JDBC driver in debug mode | 12:16 |
| pboro | that doesn't matter for the jdbc driver... afaik the driver is always provided as binary jar, you need to create your own version... I can do that if you wish | 12:17 |
| pboro | create ie compile | 12:17 |
| pboro | and jstack is a tool that comes with jvm | 12:17 |
| atrofast | I got some jstack -l output for the PID that is hung | 12:17 |
| pboro | cool, can you put it on pastebin or similar? | 12:18 |
| atrofast | http://ingres.pastebin.com/H58MphwK | 12:18 |
| atrofast | Thanks for looking :) | 12:18 |
| pboro | luckily the stack output is pretty short :) | 12:19 |
| pboro | apparently the driver is trying to read the response from the server but there's something fishy with it | 12:20 |
| atrofast | This is what iimonitor, show sessions looks like: http://ingres.pastebin.com/Cs6N8ftT | 12:21 |
| pboro | prolly a bug in DAS... or behind it :/ what does ingres tell about the session? | 12:21 |
| pboro | thanks | 12:21 |
| atrofast | Heh | 12:21 |
| atrofast | I read your mind | 12:21 |
| pboro | cs_state: CS_EVENT_WAIT (LOCK(TABLE,DB=4c222be0,TABLE=[15004,0])) | 12:22 |
| pboro | the dbms is waiting for a lock? :/ | 12:22 |
| pboro | or how should that be read... | 12:22 |
| atrofast | I'm afraid I have no clue | 12:22 |
| pboro | is there any other sessions? apparently something else is holding the lock? | 12:23 |
| pboro | you can check the lock list in ipm | 12:23 |
| atrofast | Well the only thing that is connected is through this JDBC driver/software... I run the test right after an ingstop;ingstart | 12:24 |
| pboro | from http://docs.huihoo.com/ingres/ingres2006r2-guides/Command%20Reference%20Guide/852.htm: | 12:24 |
| pboro | CS_EVENT_WAIT: Indicates the session is waiting for an event. The event type is shown in parentheses, and can be any of these: | 12:24 |
| pboro | (LOCK)?The session is waiting for a lock to be granted. | 12:24 |
| pboro | so, the reason why JDBC hangs, is that DBMS is trying to get a lock on a table and it hangs the query | 12:25 |
| atrofast | http://ingres.pastebin.com/Z6Dt2CMR | 12:25 |
| atrofast | Is what my basic lock list looks like | 12:25 |
| pboro | ok, what about when running the Geotools and it hangs? | 12:25 |
| atrofast | It hangs on this statement: DELETE FROM "road" WHERE ("fid" = '0') | 12:26 |
| atrofast | I think the transaction that reads from the "road" table is still open though | 12:27 |
| pboro | looks like it | 12:27 |
| atrofast | Would that lock the table and then cause teh hang? | 12:27 |
| atrofast | How do I make reads not lock the table? | 12:27 |
| pboro | and it's keeping the table locked and causing the hang :/ | 12:27 |
| pboro | well you could try readlock=nolock, but I'm not sure if it's a proper fix for the problem... | 12:28 |
| atrofast | How do I do that in JDBC? | 12:28 |
| atrofast | Would I have to do a stmt.executeQuery()? | 12:28 |
| pboro | you can set it in the connection parameters, lemme check | 12:28 |
| pboro | as soon as I can come up with proper manual page... :) | 12:29 |
| atrofast | Thanks for checking it up for me | 12:30 |
| atrofast | I'm new with JDBC | 12:30 |
| pboro | hmmh, I may remember incorrectly, you may need to use SQL for it... :/ | 12:30 |
| pboro | yeah, no readlock parameter in http://community.ingres.com/wiki/Ingres_JDBC_Config_Utility_Documentation | 12:33 |
| pboro | so you need to set it either using SQL or for the whole server through CBF | 12:33 |
| atrofast | Okay thanks pboro! | 12:33 |
| pboro | you could also try setting ingres.jdbc.property.cursor_mode=readonly in iijdbc.properties | 12:33 |
| pboro | not sure if it fixes the prob, but just a hint | 12:34 |
| pboro | I would need a dev environment and 10.0 for debugging it more properly :/ | 12:34 |
| atrofast | I think cursor_mode=readonly is the default and that didn't work so well for me | 12:35 |
| pboro | oh, okay :D | 12:35 |
| atrofast | okay well changed system_readlock to nolock in cbf, we'll see how that works | 12:36 |
| atrofast | if it still hangs it's something else | 12:36 |
| pboro | yeah... if it opens a cursor in updates-mode and doesn't close it, then it will prolly still hang... | 12:37 |
| pboro | i wonder why it doesn't release it, or are those two transactions related somehow? | 12:38 |
| pboro | I have run in some situations where the program has been designed for MVCC databases and with Ingres they always require readlock=nolock to work at all ;( | 12:39 |
| atrofast | Interestingly enough it now hangs one test further down | 12:42 |
| pboro | hehe | 12:43 |
| atrofast | Yeah this Geotools already runs on Postgres and Oracle | 12:43 |
| atrofast | And SQLServer and MySQL | 12:43 |
| atrofast | It's a pretty beefy piece of software | 12:44 |
| pboro | yeah, those all are MVCC (MySQL if used with InnoDB, with MyISAM there's not much locks...) | 12:44 |
| atrofast | The test specifically tests concurrency | 12:44 |
| pboro | heehee... maybe it expects MVCC features :D | 12:44 |
| pboro | you could investigate the tests more in detail to find out the actual problem if you wish... it probably boils down to locks and concurrent cursors on the same tables :/ | 12:45 |
| atrofast | Yeah that's what I'm suspecting | 12:45 |
| pboro | MVCC should be included in 10.0, you could try enabling it and see if it makes any difference | 12:46 |
| pboro | http://community.ingres.com/wiki/MVCC_User_Guide | 12:46 |
| pboro | and... http://community.ingres.com/wiki/MVCC_User_Guide#Enabling_It_At_The_System_Level | 12:46 |
| atrofast | Hmm cbf doesn't allow me to set system_lock_level to mvcc... I just edited config.dat and changed it there | 12:50 |
| atrofast | This is my first try with MVCC :) | 12:51 |
| atrofast | We just recently merged the latest changes from main into geospatial | 12:52 |
| atrofast | Oh another thing, geotools assumes auto_commit is on so I set AUTO=MULTI in my connection string | 12:53 |
| pboro | wtf, assumes auto_commit is on?! okay... | 12:55 |
| pboro | I would expect JDBC program to set the autocommit state by itself instead of expecting it to be something | 12:56 |
| atrofast | Hmm wait you might actualyl be right there :) | 12:56 |
| pboro | ie. by calling setAutoCommit(true) or setAutoCommit(false)... not doing so would be pretty weird :) | 12:56 |
| atrofast | Okay MVCC turned on, readlock=shared and it hangs in the same place | 12:58 |
| atrofast | I guess I just gotta do readlock=nolock | 12:58 |
| pboro | yeah | 12:59 |
| pboro | hmm btw what is your isolation level? | 12:59 |
| pboro | iirc the default for Ingres is serializable, which is a bit overkill for most applications and also a probable cause for the hang | 13:00 |
| atrofast | Yeah I didn't change that | 13:07 |
| atrofast | What do you recommend? read_uncommitted? | 13:07 |
| pboro | read_committed is pretty usual default for mvcc databases like oracle and postgresql | 13:07 |
| pboro | i don't know any other dbms than ingres that defaults to serializable anyway... | 13:08 |
| pboro | serializable means pretty low concurrency :/ it could even be the original cause of the problem... | 13:10 |
| atrofast | I'll try that | 13:10 |
| atrofast | That setting is the same right? Either change it in cbf/config.dat or execute a query set isolation level = read_committed or wahtever the syntax is? | 13:10 |
| pboro | yup | 13:12 |
| atrofast | No difference setting it to read_committed :( | 13:16 |
| atrofast | The only thing that seems to make any difference is readlock=nolock | 13:16 |
| pboro | okay, weird | 13:21 |
| atrofast | Thanks for helping me out pboro, much appreciated | 13:41 |
| *** DarylM has joined #ingres | 14:16 | |
| *** atrofast has quit IRC | 14:42 | |
| *** mull has quit IRC | 15:00 | |
| *** Vroomfondle has quit IRC | 15:38 | |
| *** Vroomfondle has joined #ingres | 15:41 | |
| *** grantc has quit IRC | 15:52 | |
| *** mull has joined #ingres | 18:05 | |
| *** cytrinox_ has joined #ingres | 19:20 | |
| *** cytrinox has quit IRC | 19:21 | |
| *** cytrinox_ is now known as cytrinox | 19:21 | |
| *** DarylM has quit IRC | 20:49 | |
| *** rossand has quit IRC | 22:26 | |
| *** DerMeister has joined #ingres | 23:02 | |
| *** Alex| has joined #ingres | 23:18 | |
| *** ChanServ sets mode: +o Alex| | 23:18 | |
Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!