[dev.icinga.com #1173] int(11) to small for some of ido tables #538
Comments
Updated by berk on 2011-01-31 10:05:58 +00:00
|
Updated by mfriedrich on 2011-01-31 14:24:22 +00:00
mh discussed is the wrong phrase for that - he told me that he found the id running out of int(11) on the customvariablestatus - which is a rather not commonly used table. there must have been quite a few restarts on that table to get the pk that high. i'd like to see a proposal which tables should be affected because a larger number might lack a bit of performance then. |
Updated by berk on 2011-01-31 15:02:21 +00:00 Hi Michi, is the lack of performance large enough to split model definition into int and bigint? Regards Bernd |
Updated by mfriedrich on 2011-01-31 15:04:48 +00:00 no, but i don't like big upgrade scripts, and even more 'check if all rdbms can do that the same way/behavior'. and furthermore, i'd prefer full git patches ;p |
Updated by mfriedrich on 2011-02-01 17:51:14 +00:00
the more reason i'm also asking - those ids are used for table relations. so it's not only the general ids but mainly the relation ids which need to be adapted. thoughts? |
Updated by mfriedrich on 2011-03-14 21:09:11 +00:00
since this was already reported in other stages, and in conclusion with other dbas ("wurscht"), this makes sense to be replaced in 1.4 for the complete schema and upgrade scripts. |
Updated by mfriedrich on 2011-03-16 17:34:26 +00:00 pgsql uses SERIAL for pks, INTEGER for fks. that should be fine, and not targetted. oracle uses number(.) while mysql int(.) and smallint(.*) (smallint would be number(6)). |
Updated by mfriedrich on 2011-03-24 22:06:51 +00:00
yours :) |
Updated by Tommi on 2011-03-27 21:38:44 +00:00
Applied in changeset 3ee455b. |
Updated by mfriedrich on 2011-04-13 20:56:50 +00:00
that's still missing for mysql and pgsql relatives. |
Updated by brianatb on 2011-04-13 22:28:23 +00:00 Regarding #1405 (bigints for mysql), what's the best thing to do as a work around until it's released in v1.4? |
Updated by mfriedrich on 2011-04-13 22:33:42 +00:00
alter the columns for each table and pk from int(11) to bigint. if you happen to do that, please put the sql over here - we can use that as upgrade script then, and it will save much duplicated work :) |
Updated by Tommi on 2011-04-15 19:41:12 +00:00
attached mysql scripts should change the datatype for xxx_id columns to "bigint unsigned not null unique auto_increment"(="serial" in mysql 5.1+). If somebody can confirm its working in this way i will commit this to git. Changing this for other fields may require more code changes when switching from int to long long. |
Updated by mfriedrich on 2011-04-27 17:23:29 +00:00 the sqls themselves work. but the main problem are the relations - everywhere an instance_id or *_object_id is being used, this needs to changed too - e.g.
where it says that the pk is being changed, but the related instance_id is not...
so this needs a little more rework. |
Updated by Tommi on 2011-04-27 19:39:07 +00:00
You are complete right. All _id colums will need the larger fields. There are also other fields which needs to inspect. For this reason i would like to suggest a review of the data model regarding size of datypes and expected values. Afterwards we will have a new datamodel which we have to bring it into the c code. This will cause very large changes and needs to be tested very heavy before releasing it to the people |
Updated by mfriedrich on 2011-04-27 19:45:06 +00:00 i would split that - one for the current fixes (finding all table relations, or simply replacing the too small ints all over in mysql) and then a complete review. i can live with a quickfix on the size, people will do the same - but larger changes need time and testing stages, right. so if you could create another bunch of replacing/alter statements, those still using the old int definitions, it would be great. |
Updated by Tommi on 2011-04-27 20:08:11 +00:00 sql itself is not the problem. But if you are using a 64bit id in dbqueries.c as current with asprintf("%lu",(unsigned long) data[0],.. we will introduce more problems. finding and changing all these code lines will cause the most effort. |
Updated by Tommi on 2011-05-05 15:08:41 +00:00
sql prepared for V1.4. Attached Scripts should be run as part of Version update. Previous posted scripts mysql_alter.sql and mysl_new.sql are obsolete (cant be removed from here) |
Updated by Tommi on 2011-05-05 15:08:47 +00:00
|
Updated by Tommi on 2011-05-05 15:08:52 +00:00
|
Updated by mfriedrich on 2011-05-10 06:19:25 +00:00 http://libdbi.sourceforge.net/docs/programmers-guide/reference-field.html |
Updated by tgelf on 2011-05-11 18:51:47 +00:00 I'm not sure whether it was a good idea to change instance_id from SMALLINT to BIGINT... |
Updated by Tommi on 2011-05-11 19:08:56 +00:00 Did you found problems with this setting? |
Updated by tgelf on 2011-05-12 12:34:35 +00:00 Well... no issue right now. At least nothing but a user having had big trouble with Icinga 1.4 minus two days he run into BIGINTs fetched with get_uint :p This has been fixed by dnsmichi prior to the final 1.4 release. Call me conservative, but I'm usually trying to keep indexed columns as small as possible. And I see absolutely no need for more than 2^16 Icinga instances - never seen an installation even nearly as large as that. And if so: wouldn't 2^32 be large enough? MySQL is a very special case, other RDBMs have just a single numeric data type and that's it. MySQL does far less automagic work to optimize indexes and data structures. I blindly guess that MySQL is also unable to use many indexes applied to the IDO db right now. It can rarely combine multiple indexes, and things are even more tricky once JOINs, GROUP_BYs and ORDERs are involved. Unfortunately (and obviously, what else should they do) Icinga-Web is making intensive use of all this features and doesn't really care about query performance. NDO's database structure has IMO not been designed with performance in mind, and using 4 times the space formerly reserved for instance_id will definitively not help to improve performance. And as most tables are referencing instance_id (IMO not a good design choice either, but that could be discussed elsewhere) there is far more than just a single index growing as of this "small change". My concerns are not storage- but memory-related: larger indexes also mean more wasted buffer space, caches filling up earlier and so on. These are sideeffects that could lead to earlier temp table operations - and therefore cause more I/O. Once again: no concrete issue at all. But why shall we waste ressources with no need for doing so at all? Saying "Hey, we changed one INT to BIGINT, let's change all the others too" is an excuse I personally would never accept. I have been running MySQL clusters with hundreds of millions of rows - and with pretty large keys. That's not an issue at all. At least not unless your queries allow your database to operate on indexed columns only. As soon they don't: sit back and relax... it could take a few minutes to serve your request ;-) Regards, |
Updated by mfriedrich on 2011-05-13 12:28:36 +00:00 tgelf wrote:
as said on irc, people not telling using git, but blaming the release are hard to support. so this is more or less a user issue.
the thing i don't get is, why one demands a change, the other one reflects that change after being released. i thought that you guys had a talk about that before actually demanding the change as important.
well icinga-web should actually care about query performance. joining this, where'ing that, grouping that isn't always the best thing to do, even with a unified api on the queries (doctrine). the database design is ready for fast inserts, but yet keeping active/inactive config in place. most of the actions done affects one single table, only referenced configs will require more action.
and you're not committing a patch either, so this decision is left to those actually doing it. in that special case, thomas did it and i trust his expertise to decide a. is it worth making an exception for just one id if there would have been such a patch in the first place, telling exactly which ids might be affected and are important, things will be a lot easier.
as usual - patches welcome. theoratically it could be slower. show us performance diagrams between 1.3.1 and 1.4.0 and mark those significants disadvantages you're talking about. |
Updated by tgelf on 2011-05-13 13:21:16 +00:00 dnsmichi wrote:
I'm not "you guys". I accidentally stumbled over this change as of the previously mentioned pre-1.4 git issue. And yes, I was so keen to put this change into question. I have not been part of the discussion mentioned in this ticket - but from the information I'm able to gather from the comments available right here, no one ever requested to change instance_id. It hasn't been changed unless a few days before 1.4, as far as I could find out at first only object_id's have been touched. But then (for whatever reason) all INTs have been changed - and that's what I'm unhappy with.
That's true. But don't expect me to be a big fan of a database designed to fit a specific program's internal data structures just to speed up INSERTs. Throwing more INDEXes in will not help frontend developers to achieve better performance. But let's discuss that somewhere else - it's off topic.
A patch? Rolling back only those changes involving instance_id would suffice :p
IMO: yes. Every single change to a db schema should be questioned and evaluated individually.
It would definitively not get better, but chances are good that it could become worse. This strongly depends on how much RAM for MySQL buffers you are going to throw in.
That's probably addressed to those who started the discussion / opened the ticket. IMO not wiping/refilling the DB on every Icinga reload would also have solved their problems. If those raising this issue did not specify which ID they where referring to they could also have been asked for such details. But if so: why has it been done correctly first? It's the deciscion to "change all ints" that I'm unhappy with - and this happened after the first attempt to fix what has been requested.
As soon as Icinga-Web is offering some profiling instruments I'd be happy to do so. Today I shipped a patch reducing Icinga-Web's web traffic to a fraction of what it has been before. That's far more than just my two Eurocent for today ;-) Cheers, |
Updated by mfriedrich on 2011-05-19 09:15:58 +00:00
as said, feel free to provide a suggestion/patch what exactly needs to be changed. and make sure checking the c sources too, as the instance_name=>instance_id select happens in the first place, and by putting uint instead of ulonglong e.g. will result in null values all over there place. so if the instance_id being bigint is really a problem, someone provide a patch. i don't see a magnificant severity to change that myself. |
Updated by tgelf on 2011-05-19 10:39:25 +00:00 Well... Asking someone for a patch to roll back a modification recently done by yourself is brashly impertinent, isn't it? I'm not interested in such pointless discussions, so please feel free to close this issue. I have had really great times at Kindergarten, but that's been a very long time ago. In my opinion this is still a risky modification, it hasn't been intensively tested and right now probably no one really knows if third party tools (Icinga-Web, Nagvis) working with the IDO database are able to handle all those new 64bit Integers correctly. Let me sum it all up, just for the sake of completeness:
What else shall I say? Go for it, live with it. Don't care about hopeless pessimists like me. I really didn't want to waste your time and feel sorry if I did so. Regards, |
Updated by mfriedrich on 2011-05-19 11:07:26 +00:00
for gods sake i did only approve those diffs, but not implement them. prior testing would have been much appreciated in the first place. did it happen? no. everyone awaits the release, then applies the upgrade path, and then the issues get updated. i for myself tested those changes on the rdbms, also tested them with icinga-web from git during the feature freeze test phasis. i'm regularly doing this to keep quality assurance safe before a release. if you don't feel comfortable to support us in that way of testing, but reporting such things afterwards, then please don't use icinga with idoutils. go use ancient ndoutils instead, it might be compatible and still supports the old fashioned mysql schema like it was 2 years ago. this is not what i do expect from viral development. and like you said, it's a waste of time discussing about such a behavior. i will close this issue for now, if there are any complaints about
then open a new issue providing
over and out. |
Updated by postmaster@digimarc.com on 2014-12-08 14:59:32 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX01.corp.digimarc.com (10.129.10.18) by |
Updated by postmaster@digimarc.com on 2014-12-08 14:59:42 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 14:59:54 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:00:08 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX01.corp.digimarc.com (10.129.10.18) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:00:23 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX01.corp.digimarc.com (10.129.10.18) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:00:34 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:00:51 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:04:13 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX01.corp.digimarc.com (10.129.10.18) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:05:41 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX01.corp.digimarc.com (10.129.10.18) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:05:55 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:06:09 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX01.corp.digimarc.com (10.129.10.18) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:06:23 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:06:35 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:06:52 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:11:40 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX01.corp.digimarc.com (10.129.10.18) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:11:52 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:12:03 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:12:13 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:12:24 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:12:35 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:12:45 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:12:56 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:13:08 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:17:47 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX01.corp.digimarc.com (10.129.10.18) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:18:06 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX02.corp.digimarc.com (10.129.10.19) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:18:18 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX01.corp.digimarc.com (10.129.10.18) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:18:30 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX01.corp.digimarc.com (10.129.10.18) by |
Updated by postmaster@digimarc.com on 2014-12-08 15:18:43 +00:00 Delivery has failed to these recipients or groups: brian+icinga@attributor.com<mailto:brian%2Bicinga@attributor.com> Diagnostic information for administrators: Generating server: digimarc.com
Original message headers: Received: from PDX-XP-EX01.corp.digimarc.com (10.129.10.18) by |
This issue has been migrated from Redmine: https://dev.icinga.com/issues/1173
Created by berk on 2011-01-31 10:05:12 +00:00
Assignee: Tommi
Status: Closed (closed on 2011-05-19 11:07:26 +00:00)
Target Version: (none)
Last Update: 2014-12-08 15:18:43 +00:00 (in Redmine)
Hi Michael,
you discussed that stuff with Tobias. We think that int(11) is to small for some cases. In the past there where not a lot of users using the db results for stuff like report and web interface, but in the future using icinga-web and addons like lconf the support for larger id.
I think using bigint would be a good idea.
Regards
Bernd
Attachments
Changesets
2011-03-27 20:16:09 +00:00 by Tommi 3ee455b
2011-05-03 20:36:10 +00:00 by Tommi c46e5e1
2011-05-03 20:59:45 +00:00 by Tommi 874e2ad
2011-05-03 21:08:39 +00:00 by Tommi ed19196
2011-05-03 21:27:58 +00:00 by Tommi c74b02e
2011-05-03 21:48:11 +00:00 by Tommi 2f773b2
2011-05-04 16:52:26 +00:00 by Tommi 2b806b2
2011-05-05 16:05:21 +00:00 by mfriedrich a3a031c
2011-05-09 12:47:46 +00:00 by mfriedrich a2dc45f
Relations:
The text was updated successfully, but these errors were encountered: