best way to hack up an rdbms to approximate something like kdb for poor/dumb people

Quote from Trader922:

look into infobright.

Also, there are lots of other suggestions in the different tick database threads out there.

Eric
More...

I tried infobright, but it didn't work. I loaded everything from a CSV, and then did:

select ticker from ticktable where ticker = 'foo';

And I get an empty set. Is it supposed to work that way? It did fetch data quickly, but none of the queries were working.

Tried reading the forums, couldn't find anyone who had the same problem. Used to same exact query that would work in MySQL, and infobright was having none of it.
 
Quote from NetTecture:

You are fucked. I also would love a chatp car (not more than 500 USD) that uses 1 liter of fuel per 800 miles and goes 400 miles per hour. Wont happen, though.

You need a high end server for that (dual opteron, lots of RAM - possibly 32gb up) and / or a TON of fast discs to get the IO filled, because yuor local disc just takes a lot too much time to load the data. Even saturating a SAS link (1gb per second roughly - takes a LOT of discs to do) you would take about 50-80 second to just load the data. Do you are in for a high end RAID controller and distributing the data over - hm - 4-6 links, and even then the reading takes time.

Alternatively you could get a proper server with 96+ gb RAM and a decent high end database server would then cache all the database in that RAM.

See, no chance with a "non server computer that is a couple of years old". I won't get my car either.
More...

I was hopeful I was hoping for a magic bullet, , but figure you are probably right. (Although, to be fair, I made my request for poor and dumb people for a reason.)

KDB usually takes a few seconds to fetch queries, but it has its own data limits. The hardware required was quite a bit. I was hoping to not to have to spend too much money getting new hardware/software.

If I could get the query time down into the range of maybe 10-15 seconds then that might be workable.
 
Ok, here is what I would go for on a budget. Still going to be a lot:

* AMD X6 when it comes in a month
* 16gb on a mini atx board
* SAS controller
* There is a SuperMicro SAS Cage for 8x2.5" discs. Costs around 400 USD ;)
* Velociraptors. 8. Alternatively some SSD.

On a non-budget... well... Supermicro case SAS backplane place for 24 discs. Dual Opteron with 1x6 cores, 32gb ram, doublind as needed. Raid controller, Velociraptors. Many. Or SSD.

If that is not long term you could try renting capacity on a server like that.

10-15 seconds are doable, but it needs hardware. Basically lots of RAM, fast discs. Especially if you want to store, for example, the results in the database.
 
Quote from garchbrooks:

I tried infobright, but it didn't work. I loaded everything from a CSV, and then did:

select ticker from ticktable where ticker = 'foo';

And I get an empty set. Is it supposed to work that way? It did fetch data quickly, but none of the queries were working.

Tried reading the forums, couldn't find anyone who had the same problem. Used to same exact query that would work in MySQL, and infobright was having none of it.
More...

Did you verify that your data loaded properly? (http://www.infobright.org/wiki/Data_Loading/) I dont remember the command, but there is one to show the table statistics to make sure your data loaded properly

Did you make sure your query was written properly? (http://www.infobright.org/wiki/Optimized_SQL_Functions_and_Operators/)

I've never had any problems like that before. I'd suspect the issue is on your data load.
 
Like posted above, sounds like a data load problem. I've played with infobright with a few billion rows of tick data. Its performance profile isn't all it's hyped up to be, but blows the other MySQL engines out of the water for the right use case.
 
Quote from Trader922:

Did you verify that your data loaded properly? (http://www.infobright.org/wiki/Data_Loading/) I dont remember the command, but there is one to show the table statistics to make sure your data loaded properly

Did you make sure your query was written properly? (http://www.infobright.org/wiki/Optimized_SQL_Functions_and_Operators/)

I've never had any problems like that before. I'd suspect the issue is on your data load.
More...

This works:

select ticker from tickdata;

This, however, doesn't:

select ticker from tickdata where ticker='A';

And that's on a table with 10 rows having nothing but rows where ticker = 'A''.

If I shut down and restart the database, the data is there. It just has some kind of issue with the table. Could be a weird .deb package I installed, that's a possibility.
 
either one of these works for me:
USE Futures_Data; SET @bh_dataformat = 'txt_variable';
select Symbol from Futures_Data where Symbol = "AD" limit 10;

or


USE Futures_Data; SET @bh_dataformat = 'txt_variable';
select Symbol from Futures_Data where Symbol = 'AD' limit 10;
 
Give SQlite a shot, it supports in memory database for optimizing performance. Your disk I/O will be a killer for the initial data load. Wrap your writes inside of transactions.

It is a very fast ACID compliant DB in a single file.



Quote from garchbrooks:

This works:

select ticker from tickdata;

This, however, doesn't:

select ticker from tickdata where ticker='A';

And that's on a table with 10 rows having nothing but rows where ticker = 'A''.

If I shut down and restart the database, the data is there. It just has some kind of issue with the table. Could be a weird .deb package I installed, that's a possibility.
More...
 
Quote from Trader922:

either one of these works for me:
USE Futures_Data; SET @bh_dataformat = 'txt_variable';
select Symbol from Futures_Data where Symbol = "AD" limit 10;

or


USE Futures_Data; SET @bh_dataformat = 'txt_variable';
select Symbol from Futures_Data where Symbol = 'AD' limit 10;
More...

I really am a database newbie, so please pardon me if what I'm saying sounds absolutely dumb.

Here's a sample slice of 1-minute data, in a file called 'slice.csv':

Code: 
'2008-12-01', 10:21:00, 'A', 17.820000, 17.830000, 17.780000, 17.800000, 1600, 1400
'2008-12-01', 10:22:00, 'A', 17.800000, 17.830000, 17.790000, 17.830000, 3200, 2100

When I log into infobright, this is the string I get (so I know it's infobright):

Code: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.40-log build number (revision)=IB_3.3.1_r6997_7017(ice)

I then do this (there's a backslash in here that doesn't show up right on ET; it's correct):

Code: 
mysql> load data infile '/tmp/slice.csv' into table tickdata fields terminated by ',' enclosed by '\'';
Query OK, 50910948 rows affected (3 min 10.44 sec)

So the results of this query:

Code: 
mysql> use tickdb;
Database changed
mysql> select ticker from tickdata limit 10;
+--------+
| ticker |
+--------+
|  'A'   |
|  'A'   |
|  'A'   |
|  'A'   |
|  'A'   |
|  'A'   |
|  'A'   |
|  'A'   |
|  'A'   |
|  'A'   |
+--------+
10 rows in set (0.09 sec)

But then I do:

Code: 
mysql> select ticker from tickdata where ticker='A' limit 10;
Empty set (0.02 sec)

... empty set??

The test table layout I'm using for tickdata is this:

Code: 
mysql> describe tickdata;
+------------+----------+------+-----+---------+-------+
| Field      | Type     | Null | Key | Default | Extra |
+------------+----------+------+-----+---------+-------+
| tickdate   | date     | NO   |     | NULL    |       |
| tstamp     | time     | NO   |     | NULL    |       |
| ticker     | char(16) | NO   |     | NULL    |       |
| openprice  | float    | YES  |     | NULL    |       |
| highprice  | float    | YES  |     | NULL    |       |
| lowprice   | float    | YES  |     | NULL    |       |
| closeprice | float    | YES  |     | NULL    |       |
| upvolume   | int(11)  | YES  |     | NULL    |       |
| downvolume | int(11)  | YES  |     | NULL    |       |
+------------+----------+------+-----+---------+-------+
9 rows in set (0.03 sec)

What am I doing wrong here?
 
You must log in or register to reply here.
Back
Top