| |
| |
| |
|
Page: 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
Comments:
<0> groogs: something's definitely wrong there. try indexing :P
<1> pizza_milkshake: its indexed
<0> Two-Bits: yes, that's another option
<0> groogs: how many records?
<1> 20 million maybe?
<1> sec and i'll tell you exactly
<0> total or per sensor per site?
<0> exactyl doesn't matter
<0> 10s of millions
<2> how is the table indexed?
<1> oh, not even that many
<1> 11 million, total
<0> ok
<1> 1.1 gb
<2> can it mantain a seperate index for the values and one for the dates?
<0> and when you say min/max you mean globally or per sensor per site or what?
<1> its indexed on systemid, variableid (sensor id), and date
<3> how much memory is on the system?
<1> pizza_milkshake: per sensor
<1> raizor: 2 gb
<2> then your indexing is doing nothing of value for a min/max
<1> raizor: though, its not dedicated
<0> also, do you calculate min/max often, or require it to be very fast? if so, you might want to keep a running tab... it would slow down each individual insert operation, but make extracting the values you want much faster
<3> Not dedicated?
<3> How much is generally free?
<0> got to go :/
<1> raizor: er i mean, there are other things running on this system
<1> pizza_milkshake: ok, thanks for the help
<2> see ya pm
<1> hm. so i should be indexing the value as well?
<2> i'm not sure how mysql indexes work
<1> i dont think that will be a problem, as its not inserting all that fast (a few a minute)
<1> i sort of ***umed most of my problems were coming from casting.. storing it in a string field, and asking mysql to do min/max
<3> If you're making mysql search/calc/dosomething on any column big time, index it
<2> you're asking it to do min/max on string values?!
<4> I feel so dirty editing this script. There's like, no error checking what so ever.
<3> oof
<1> Two-Bits: well yes :) thats the whole issue here
<1> Two-Bits: how do i store a mixed set of data
<2> that'd give erroneous results, wouldn't it?
<1> no, it works
<1> its even fast for small data sets (ie, on each system)
<2> "5.2" would be placed higher than "13.8" would it not?
<1> nope
<3> heh heh
<3> yep, I've seen that sorting mess
<2> i would very much so love to know how mysql performs that bit of magic
<3> What magic? That's common ascii value sorting
<2> raizor: he's saying it's NOT sorting like that
<3> ohh
<3> I think he's ***uming that
<2> min/max on strings SHOULD place "5" higher than "13"
<2> if it's not, then convenient as it is, it's either broken, or it's not actually doing min/max on strings
<3> yeah, correct
<2> or it's doing some implicit type juggling.. which would also effectively be broken
<1> hm, yeah its not doing that with my test table
<1> i thought i did have a way to do it though
<1> (i'm not doing any min/max right now, btw)
<3> $a = array("5.2", "13.8"); sort($a, SORT_STRING); print_r($a);
<3> see output
<1> i know what you're saying
<3> That's why mysql does what it does
<1> i'm getting that result from it now (ascii sorting)
<3> ascii(1) + ascii(3) <= ascii(5) + ascii(.)
<3> I think that's how they do it
<2> eh?
<3> and they ignore the rest of the string
<2> actually it would only test the first chars
<2> "5" > "1". done
<1> oh yeah, you can do CAST(field, DECIMAL)
<2> but if you could reliably convert the strigns back to decimals, then you wouldn't have a need to cast to strings in the first place
<2> but if the data set has mixed types, that wouldn't work anyway
<2> or are you saying ALL your data is currently STORED as strings?
<3> Pretty sure that's what he's saying
<1> Two-Bits: yes, currently all strings
<2> i see
<3> My opinion is that he needs to bite the bullet and properly type
<1> but basically, the existing data is not a concern
<2> no wonder the database is so huge
<1> :)
<1> so really the consensus is that i should setup three tables.. each with variableid, date, value ... where value is VARCHAR for one table, DOUBLE for one, and INTEGER for one..
<2> one moment
<2> i'm not sure that's the most convenient place to put the date
<1> when i need to extract data, i can query the variable_type table to get the type of the variable, then query the appropriate data table
<1> (in pratice, i could just keep that stuff cached,because it won't change much and it is not a lot of info)
<3> if you know the variable type could you store it in one table?
<3> using multiple tables would only be for further optimization
<1> yeah, for each variable, it will always be the same type of data
<2> raizor: each "record" would only represent one variable value of the "system"
<3> correct
<3> so you'd have some null values
<2> that'd be a waste of space, plus you'd still need to logically choose the correct column anyway
<2> hrm
<1> what i'd really like to be able to do also is do a query like SELECT MIN(value), MAX(value), AVG(value) FROM data_float WHERE VariableID = 213 AND Date > 3/6/06 and DATA < 3/9/06 GROUP BY <every 10 minutes>
<1> i think postgres has a function for that.. but it could also be GROUP BY (Date % 3600)
<1> raizor: well i need null values for unknown i think
<1> ie .. it's logging, but the sensor is unavailabe or malfunctioning
<3> You'd have the type as another column
<1> in the variables table...
<2> i'm just trying to figure out if it would be best to have a table for sensor value updates that takes variableid, valueid, and date
<2> and then from the variableid you can get the variable type
<2> and using the correct table, query the value with the value id
<1> yeah, it won't make sense to store the type with each value.
<2> it would be a lot of subqueries
<2> well, not a LOT
<1> because the type will never change. each variable has a type. thats it
<1> Two-Bits: storing the date elsewhere will mean a join to get it though
<2> i'm trying to think of indexing issues
<2> but my head now thoroughly hurts
<1> welcome to my last couple of days
<3> index values, index the type, index the primary key, etc.
<1> raizor: so.. everything ;)
<2> not the type
<2> index values, date, and variable id
<3> I meant if he had a column called `type` that referred to variableAsVarChar column or variableAsInt column
<2> and system id, wherever that goes. that too doesn't need to be in the updates table
<2> raizor: why would that ever be indexed?
<3> Because in what I'm picturing selects would deal with it a lot
<2> only in a manner in which the record containing that data was obtained through other criteria
<1> raizor: only once, but remember it's ***ociated with the variableid
<3> If a query has to process the column in a where clause, order by, group by, you might want to index it
<1> raizor: each variableid has a fixed type
<2> why would you "order by ints" or "group by strings" ?
<1> yeah, i won't need to do that
<3> timestamp for instance
<3> If he wanted to do that crazy group by date % 3600
<1> people will only want to grab the data for a given variable, they won't care about the type
<1> raizor: that would still be for a given variable
<2> that's not the type column
<1> because remember too, min/max is good for double or integer, but useless for strings
<2> min/max isn't useless for strings if you need to alphabetize something
<1> where as distinct count() queries will be useful for strings, but useless for integers
<1> Two-Bits: well ok yeah, but for my purposes it is :)
<2> it's just useless to try and get numerical ordering of stringified numbers
<1> the only string value i can even think of is for pumps, 'on', 'off', 'overload' 'underload'
<1> and i think even that stores as integers. i probably don't even need strings..
<2> if it's a defined value set, use numbers
<2> i've worked with ipm before.. i dont recall ever using strings
<1> ipm?
<2> a type of controller
<1> you know, i'm actually trying to think now if my remote systems store it as a string, or a double
<1> i set this up like two years ago
<5> http://www.rafb.net/paste/results/T1DmYh77.html - why am I getting the upload error output?
<5> what'
<5> s wrong with my condition?
<1> hm
<1> so if i had just integers and doubles. ... maybe i SHOULD just use one table?
<2> http://www.sixnetio.com/html_files/oems/ipm_what_is_ipm.htm
<1> i'm going to do some experiments on my big data set. need to run and get my car tested now.. but i will let you guys know
<1> oh yeah, sixnet
Return to
#php
or
Go to some related
logs:
#chat-world
#chat-world
UI_set_result:result too small
#php
txmate
LockedsouL
hipac 2.6.17
#chat-world
#chat-world
#kl
|
|