Bitcoin Databases for Large Websites

dashingriddler

legendary

Activity: 1258

Merit: 1001

Quote from: phantomcircuit on June 11, 2013, 04:37:14 PM

Quote from: dashingriddler on June 11, 2013, 08:46:11 AM

I have used mySQL for the same purpose of analysing and searching for info in block chain.
All are INNODB tables with baraccuda compression with key-block-size being 2kb
I am recording all the data available over the block chain and it is effectively taking twice the amount of data (17gb) it takes for normal bitcoind block chain(8.5gb).

Are you storing things as their hex representation or binary representation?

The nearly exact doubling in size makes me suspect hex.

Yes i am storing it as hex at this point of time.

grau

hero member

Activity: 836

Merit: 1030

bits of proof

Quote from: phantomcircuit on June 11, 2013, 04:37:14 PM

Quote from: dashingriddler on June 11, 2013, 08:46:11 AM

I have used mySQL for the same purpose of analysing and searching for info in block chain.
All are INNODB tables with baraccuda compression with key-block-size being 2kb
I am recording all the data available over the block chain and it is effectively taking twice the amount of data (17gb) it takes for normal bitcoind block chain(8.5gb).

Are you storing things as their hex representation or binary representation?

The nearly exact doubling in size makes me suspect hex.

May I offer an other play: LevelDB is a persistent key-value map. I use transaction and block hashes as keys and the value is protobuf serialization. Blocks are stored as serialized header + list of tx hashes + bloom filter. The persistent bloom filter is populated with all data elements of scripts and outpoints. I read the bloom filter set into memory to query. For blocks where filter is positive I retrieve and parse the transactions.

The result is 11.8 GB disk use (at block 241061) and it takes 3 seconds to scan the entire blockchain for addresses generated from a BIP32 public key and transactions spending them with 10 keys lookahead. (LevelDB is native, rest is Java).

phantomcircuit

sr. member

Activity: 463

Merit: 252

Quote from: dashingriddler on June 11, 2013, 08:46:11 AM

I have used mySQL for the same purpose of analysing and searching for info in block chain.
All are INNODB tables with baraccuda compression with key-block-size being 2kb
I am recording all the data available over the block chain and it is effectively taking twice the amount of data (17gb) it takes for normal bitcoind block chain(8.5gb).

Are you storing things as their hex representation or binary representation?

The nearly exact doubling in size makes me suspect hex.

dashingriddler

legendary

Activity: 1258

Merit: 1001

I have used mySQL for the same purpose of analysing and searching for info in block chain.
All are INNODB tables with baraccuda compression with key-block-size being 2kb
I am recording all the data available over the block chain and it is effectively taking twice the amount of data (17gb) it takes for normal bitcoind block chain(8.5gb).

kwilliams

member

Activity: 70

Merit: 10

I have used Mongo for other projects and it's a bitch to maintain. It makes most sense if
a) You know how to maintain it
b) The R/W ratio is skewed to 90% reads or more
c) You have lots of RAM.
d) You don't care about transactions and can live with less than 100% data integrity

phantomcircuit

sr. member

Activity: 463

Merit: 252

Quote from: jgarzik on June 04, 2013, 01:56:32 PM

Quote from: BTC_Junkie on June 04, 2013, 12:16:40 PM

I mean, if I want to store the whole block chain to do data analysis on a web site, what is the best database to use for that?

People generally wind up creating custom databases, or at least highly custom setups of standard database software.

You are talking about indices containing many millions of records. It isn't as easy as just telling your SQL db to index a column. People often turn to tools like redis for such huge datasets.

PostgreSQL has no problem indexing the entire blockchain.

Example: select * from transaction_outputs where ARRAY['18ese9gmJ5zYePvLQiFoC5bVNzVicgDZWz'::character varying] && addresses;
Total runtime: 0.066 ms

The issue is the time to insert/update columns, in general indexed select queries are fast.

maco

sr. member

Activity: 294

Merit: 250

PHP Sites = MYSQL
I haven't touched Mongo as of yet.

Anything with MYSQL should be fine as a stable database for a bitcoin website.

Quote from: BTC_Junkie on June 04, 2013, 11:00:34 AM

I'm building a bitcoin website, and have been using bitcoind for database requests which has been fairly slow. We'll only really be looking at history, and not managing any wallet based functions. I'm considering switching to either SQL or Mongo for longer-term use, does anyone have experience using either for bitcoin sites? What database structure do some of the larger sites like blockchain.info use?

Thanks for your help.

grau

hero member

Activity: 836

Merit: 1030

bits of proof

Quote from: loudog40 on June 06, 2013, 02:22:49 PM

Maybe take a peek at the Bits of Proof Enterprise Server. Stores the blockchain in the JPA compliant database of your choice.

+1

You may run it with an SQL Configuration to get the block chain into a fully normalized relational database or with LevelDB and use its API to retrieve blocks or transactions.

I build, host and support servers running the configuration of your choice if you like.

loudog40

newbie

Activity: 11

Merit: 0

Maybe take a peek at the Bits of Proof Enterprise Server. Stores the blockchain in the JPA compliant database of your choice.

jgarzik

legendary

Activity: 1596

Merit: 1100

Quote from: BTC_Junkie on June 04, 2013, 02:26:34 PM

Quote from: jgarzik on June 04, 2013, 01:56:32 PM

Quote from: BTC_Junkie on June 04, 2013, 12:16:40 PM

I mean, if I want to store the whole block chain to do data analysis on a web site, what is the best database to use for that?

People generally wind up creating custom databases, or at least highly custom setups of standard database software.

You are talking about indices containing many millions of records. It isn't as easy as just telling your SQL db to index a column. People often turn to tools like redis for such huge datasets.

Thanks Jeff. Why would you recommend redis over mongo?

Mongo works just fine, too.

BTC_Junkie

member

Activity: 97

Merit: 10

Quote from: jgarzik on June 04, 2013, 01:56:32 PM

Quote from: BTC_Junkie on June 04, 2013, 12:16:40 PM

I mean, if I want to store the whole block chain to do data analysis on a web site, what is the best database to use for that?

People generally wind up creating custom databases, or at least highly custom setups of standard database software.

You are talking about indices containing many millions of records. It isn't as easy as just telling your SQL db to index a column. People often turn to tools like redis for such huge datasets.

Thanks Jeff. Why would you recommend redis over mongo?

jgarzik

legendary

Activity: 1596

Merit: 1100

Quote from: BTC_Junkie on June 04, 2013, 12:16:40 PM

I mean, if I want to store the whole block chain to do data analysis on a web site, what is the best database to use for that?

People generally wind up creating custom databases, or at least highly custom setups of standard database software.

You are talking about indices containing many millions of records. It isn't as easy as just telling your SQL db to index a column. People often turn to tools like redis for such huge datasets.

BTC_Junkie

member

Activity: 97

Merit: 10

I mean, if I want to store the whole block chain to do data analysis on a web site, what is the best database to use for that?

Aldur1

member

Activity: 73

Merit: 10

www.bitex.co.uk - A new begining in cryptotech

Wouldnt it make sense to keep all you transaction records in a database? I use MySQL and then can reference TX ID's if I make qt calls.

BTC_Junkie

member

Activity: 97

Merit: 10

I'm building a bitcoin website, and have been using bitcoind for database requests which has been fairly slow. We'll only really be looking at history, and not managing any wallet based functions. I'm considering switching to either SQL or Mongo for longer-term use, does anyone have experience using either for bitcoin sites? What database structure do some of the larger sites like blockchain.info use?

Thanks for your help.

Topic: Bitcoin Databases for Large Websites (Read 2625 times)