| *** KermitTheFragger has joined #ingres | 01:02 | |
| *** Mud has joined #ingres | 01:04 | |
| *** PaulM05 has joined #ingres | 01:39 | |
| *** gerhard has joined #ingres | 01:53 | |
| gerhard | Hi all | 01:55 |
|---|---|---|
| PaulM05 | hi | 01:56 |
| pboro | hi | 01:56 |
| gerhard | When creating tables with BLOB fields, Ingres creates iietab_xxx_yyy tables behind the scenes. How can I determine which iietab belongs to which table? | 01:58 |
| pboro | from iifile_info iirc, lemme check | 01:59 |
| pboro | ah, that reveals the name of the file of iietab_*, and the column mapping is in... hm | 01:59 |
| gerhard | What is the proper way to reorganize those iietab tables? usermod or sysmod? | 02:01 |
| PaulM05 | it's in iiextended_relation | 02:03 |
| pboro | ah, yup | 02:03 |
| PaulM05 | etab_base is the table_reltid, etab_attid is the column num (as in iiattribute), and the actual table name is iietab_{hex of etab_base}_{hex of etab_extension} | 02:05 |
| PaulM05 | gerhard - an ugly bit of SQL to find iietab names - http://ingres.pastebin.com/m7e1255b4 | 03:21 |
| *** atrofast has joined #ingres | 03:25 | |
| gerhard | Wow! Thanks, will try | 03:26 |
| PaulM05 | have sent that round the office to see if anyone can come up with something more elegant ;) | 03:30 |
| *** Mud has quit IRC | 03:45 | |
| *** Mud has joined #ingres | 03:50 | |
| *** PaulM05 is now known as PaulM05_lunch | 04:08 | |
| *** cthibert has joined #ingres | 04:12 | |
| *** zxiiro has quit IRC | 05:02 | |
| gerhard | Paul, your SQL http://ingres.pastebin.com/m7e1255b4 to check the iietab works, thanks again. | 05:28 |
| gerhard | In line 3, there was a wrong quote sign around iietab_ | 05:28 |
| gerhard | fixed version: http://ingres.pastebin.com/m2d7fb128 | 05:31 |
| *** grantc has joined #ingres | 05:45 | |
| *** rossand has joined #ingres | 05:46 | |
| *** ChanServ sets mode: +o rossand | 05:46 | |
| *** zxiiro has joined #ingres | 05:46 | |
| *** ChanServ sets mode: +v zxiiro | 05:46 | |
| *** PaulM05_lunch is now known as PaulM05 | 06:00 | |
| PaulM05 | turns out that etab_extension is the reltid of the iietab table - so one of my colleagues came up with this: http://ingres.pastebin.com/m1a4116de - much simpler! | 06:01 |
| gerhard | Nice. | 06:22 |
| gerhard | a variant that shows iietab sizes in MB: http://ingres.pastebin.com/m5194fb06 | 06:24 |
| PaulM05 | nice | 06:29 |
| *** mull has joined #ingres | 06:37 | |
| gerhard | Has that iietab thingy already been included in Ingres 2.x or is it since Ingres 2006? | 07:18 |
| PaulM05 | it's been around since blobs were introduced so definitely 2.x | 07:18 |
| pboro | gerhard, what page size are you using for blobs? | 07:27 |
| pboro | I changed our etab page size to 8k long time ago, but switched back to 2k after I found out, that our BLOBs are 1-20k in size, but >80% under 2k... so a lot of wasted space | 07:28 |
| pboro | later on I forced the texts stored in blobs to be under 2k and switched to compressed table with regular varchars | 07:28 |
| pboro | I like a lot of postgresql's way of appending the new/modified data to the end of data files and vacuuming/reclaiming free space once a day (during night or so on), cause it's so much better with space efficiency... of course fixed size pages are great if the data is fixed size :) | 07:30 |
| pboro | I wish it was possible to make etabs compressed too :) actually, I wonder if it's possible... | 07:31 |
| pboro | and I don't mean any lzma or similar compression but just not padding the field with zeros or similar | 07:32 |
| *** Deyan has joined #ingres | 07:33 | |
| Deyan | hello everybody | 07:34 |
| pboro | hi Deyan | 07:34 |
| Deyan | guys, i am encountering significant performance fall over time on one of productions servers... | 07:34 |
| Deyan | after i restart it everything seems fine | 07:35 |
| Deyan | than, after few hours of work connections get slower | 07:35 |
| pboro | which version? | 07:35 |
| Deyan | and in general it takes more and more time to get result from queries | 07:35 |
| Deyan | 9.3.0 151 | 07:35 |
| Deyan | x86_64 | 07:35 |
| Deyan | Linux | 07:35 |
| pboro | hmh :/ | 07:35 |
| Deyan | (CentOS) | 07:35 |
| Deyan | right now i am reading tons of article in hope to find what causes the problem | 07:40 |
| Deyan | it is not loaded server at all | 07:40 |
| Deyan | max 100 connections at peak time | 07:40 |
| Deyan | load is mostly below 1 1 1 | 07:41 |
| grantc | iostat? | 07:43 |
| Deyan | i also checked network | 07:43 |
| Deyan | and it is not crowded | 07:43 |
| Deyan | http://codepad.org/ZfAHzMKt | 07:44 |
| Deyan | that is current one | 07:44 |
| *** Mud has quit IRC | 07:45 | |
| gerhard | Deyan: if lots of inserts / deletes are run against tables, they might get "fragemented". A "usermod dbname" may be helpful. | 07:46 |
| *** Alex| has quit IRC | 07:46 | |
| gerhard | Pboro: how can I make an existing table "compressed"? | 07:47 |
| Deyan | gerhard: thanks for information - that is helpful | 07:49 |
| Deyan | because we have lots of temporary tables | 07:49 |
| Deyan | that may have lots of insert/deletes | 07:49 |
| Deyan | i noticed ingres does not take lots of RAM... | 07:50 |
| *** Mud has joined #ingres | 07:50 | |
| Deyan | what configuration parameter to tweak to make it use more ram and hopefuly reduce those inserts/deletes? (like... make temporary tables be in memory or something) | 07:51 |
| PaulM05 | bump up your DMF cache | 07:51 |
| Deyan | i think i should learn more about usermod | 08:03 |
| Deyan | because i never run it | 08:03 |
| Deyan | just optimizedb, weekly | 08:04 |
| gerhard | optimizedb over the whole db? | 08:04 |
| Deyan | yes | 08:04 |
| Deyan | gerhard: you suggested running usermod <dbname> | 08:06 |
| Deyan | should i do it while server is running? | 08:06 |
| gerhard | I think it (optimizedb) is more efficient to run specifically for the table.column combinations that are actually used in the where clauses of long running queries... | 08:07 |
| gerhard | ...it depends | 08:08 |
| *** Alex| has joined #ingres | 08:09 | |
| *** ChanServ sets mode: +o Alex| | 08:09 | |
| gerhard | maybe you should first of all run it on a *testing machine* to get a rough estimation about how long usermod for the whole db will take... | 08:10 |
| DarylM | usermod is of questionable use as a routine administrative function. But your milage may vary. | 08:10 |
| Deyan | gerhard: yep, i have a copy of entire DB on another server | 08:11 |
| Deyan | which i use for testing applications... | 08:11 |
| gerhard | DarylM: you're right | 08:25 |
| gerhard | Deyan: from my own experience, there is no single go-fast-button in Ingres (-: | 08:26 |
| Deyan | gerhard: i am aware of that, mate | 08:27 |
| Deyan | :) | 08:27 |
| gerhard | You'll have to find out *what* queries run slow and choose proper indexes | 08:27 |
| DarylM | The biggest administrative concern with table structures are hash tables that have a lot of insert or key update activity which can potentially cause long overflow chains. | 08:27 |
| Deyan | i am alredy very happy with my own, newbish tweaks | 08:27 |
| DarylM | That can be both a performance problem as well as a concurrency (locking) problem. | 08:27 |
| Deyan | locks are not many | 08:28 |
| Deyan | i checked that | 08:28 |
| Deyan | -rw-rw-r-- 1 ingres ingres 165K 2010-02-18 16:15 usermod.sys.txt | 08:30 |
| Deyan | this is usermod's output | 08:30 |
| Deyan | for the database :D | 08:31 |
| Deyan | 165k file :) | 08:31 |
| Deyan | i am examining it atm | 08:31 |
| Deyan | it gave some nice suggestions | 08:31 |
| DarylM | ? usermod only regurgitates the exact same table structures and indexes you initially created. It doesn't actually suggest anything "new" | 08:33 |
| *** gerhard has quit IRC | 08:34 | |
| Deyan | DarylM: ah... thanks | 08:39 |
| Deyan | so it basically gives me a summary of what type, page size etc, each table/index have? | 08:46 |
| Deyan | so I can review it | 08:46 |
| DarylM | yup. Makes for a good starting point for building other types of scripts, migrations, updates, etc. | 08:48 |
| Deyan | DarylM: do you use direct_io in DBMS ? | 09:02 |
| DarylM | I don't change the defaults. There are not very many OSes supported and the default is supposedly correct for your specific OS | 09:04 |
| *** DarylM has quit IRC | 09:09 | |
| *** DarylM has joined #ingres | 09:15 | |
| Deyan | is it possible somehow to see table statistics? how many inserts/deletes each table had so far, etc? | 09:38 |
| PaulM05 | not directly | 09:39 |
| PaulM05 | if the tables are journalled then you can run auditdb and process the output | 09:39 |
| PaulM05 | but that will only tell you about updates/inserts/deletes not selects | 09:40 |
| PaulM05 | good night all | 09:48 |
| *** PaulM05 has quit IRC | 09:48 | |
| *** Deyan has quit IRC | 10:04 | |
| *** cthibert has left #ingres | 10:10 | |
| *** cthibert has joined #ingres | 10:13 | |
| *** KermitTheFragger has quit IRC | 10:14 | |
| *** Alex| has quit IRC | 10:56 | |
| *** Mud has quit IRC | 11:46 | |
| *** Mud has joined #ingres | 11:51 | |
| *** Mud has quit IRC | 11:52 | |
| *** Mud has joined #ingres | 11:54 | |
| *** grantc has quit IRC | 12:17 | |
| *** cthibert has left #ingres | 13:13 | |
| *** Mud has quit IRC | 13:24 | |
| *** zxiiro has quit IRC | 14:25 | |
| *** zxiiro has joined #ingres | 15:03 | |
| *** ChanServ sets mode: +v zxiiro | 15:03 | |
| *** mull has quit IRC | 15:54 | |
| *** atrofast has quit IRC | 16:23 | |
| *** cytrinox has quit IRC | 19:24 | |
| *** cytrinox has joined #ingres | 19:25 | |
| *** rossand has quit IRC | 19:47 | |
| *** zxiiro has quit IRC | 22:18 | |
| *** zxiiro has joined #ingres | 22:19 | |
| *** ChanServ sets mode: +v zxiiro | 22:19 | |
| *** Alex| has joined #ingres | 22:36 | |
| *** ChanServ sets mode: +o Alex| | 22:36 | |
| *** Alex| has quit IRC | 23:04 | |
| *** Alex| has joined #ingres | 23:23 | |
| *** ChanServ sets mode: +o Alex| | 23:23 | |
Generated by irclog2html.py 2.7 by Marius Gedminas - find it at mg.pov.lt!