Re: [Perfparse-devel] Using indexes with mysql

[Ben C. <Be...@cl...>]

Hi Viljo,

First off, I am really excited about your plans to do a PostgreSQL 
conversion.  I have kicked my self on more than one occasion for not 
starting with PSQL.  Please let me know if you need any help.

On this subject, it has been suggested that we use Sqlite as an 
abstraction layer.  I think because 'if it's ok for php, it's ok for 
us'. :) Do you know anything about this?

Comment on integer for date/time.  Yes I agree.  Also a design feature 
of PerfParse: 'Keep to ANSI SQL'.  Which is why I write my schema as:

	value INT

Rather than the MySQL syntax:

	'value' INT(11)

Date is another example.  DATETIME is and ANSI SQL standard and 
therefore a good chance it will work with and RDBMS.

Regards Ben.


Viljo Marrandi wrote:

> Hello,
> 
> Actually, the first thing I thought about perfparse data schema, was 
> that why store hostnames in varchar for every value nagios gives us. 
> Therefore I like Flo's idea about putting hosts, metric names and 
> service_descriptions to different tables and simply joining them with 
> _bin table. And also putting raw data to same table would save lots of 
> space and unneeded duplicate values. I guess that would speed up things 
> greatly.
> 
> I'm not sure about storing times in unix timestamp format. I know mysql 
> supports this, don't know about Oracle, but it's not supported by 
> PostgreSQL, for which I indend to write module sometime (if somebody 
> doesn't beat me on this). By 'supported' I mean that postgres doesn't 
> have TO_UNIXTIME and FROM_UNIXTIME functions (or however they were called).
> 
> So searching would be really weird, because first I had to convert unix 
> timestamp to date and then compare dates to find one week interval or 
> whatever. But then again, I might be wrong too :)
> 
> Rgds,
> Viljo
> 
>> What i would do to save storage:
>>
>> create table hosts_names (
>>    host_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
>>    host_name VARCHAR(75) NOT NULL
>> )
>>
>> and replace host_name column in perfdata_bin with the host_id. Do 
>> joins to get the textual host_name in queries. The same could be done 
>> for service_description and metric, since there is a limited number of 
>> different values.
>>
>> Another proposal would be to use a integer to store timestamps. I'd 
>> use unix timestamps (seconds since 1.1.1970) to store dates, since - 
>> in my experience - the handling of dates in different databases 
>> (mysql, oracle, ...) is not very portable.
>>
>> Last i'd propose to store the raw data in the bin table, since i 
>> really don't know why this is stored in a extra table. But probably 
>> there is a reason i didn't realize.
>>
>> Finally i get:
>>
>> TABLE perfdata_bin (
>>    host_id INTEGER NOT NULL,
>>    service_id INTEGER NOT NULL,
>>    metric_id INTEGER NOT NULL,
>>    ctime INTEGER NOT NULL,
>>    PRIMARY KEY (host_id,service_id,metric_id,ctime),
>>    value DOUBLE,
>>    state TINY INT,
>>    warn DOUBLE,
>>    critical DOUBLE,
>>    max DOUBLE,
>>    min DOUBLE,
>>    txt_data VARCHAR(255)
>> )
>>
>> And tables for host_name, service_description and metric.
>>
>> Note that this table has almost only fixed sized small columns.
>>
>> What do you think?
> 
> 
> 
> -------------------------------------------------------
> This Newsletter Sponsored by: Macrovision For reliable Linux application 
> installations, use the industry's leading
> setup authoring tool, InstallShield X. Learn more and evaluate today. 
> http://clk.atdmt.com/MSI/go/ins0030000001msi/direct/01/
> _______________________________________________
> Perfparse-devel mailing list
> Per...@li...
> https://lists.sourceforge.net/lists/listinfo/perfparse-devel
>