Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am developing a product that, as a part of it's operation, must track a large number of files/directories. The idea is to store stat information in a database then, on boot, create watches for each file. Files that change will be queued (in the database) for a group sync to a remote database. They will be synced in order of priority, a number between 1-10.

Information about database:

  • < 100,000 entries of stat info
  • Entire database read at boot, only the file path is necessary
  • Queued files will have a priority field (nothing else needs be searched by)
  • Insertions can be slow

I've found a couple databases that I think will work, but I'm not sure which would be the best:

  • Redis- store file-path as key, stat data as value; queue would be a list
  • MongoDB- more query options than Redis, but still fast

I'm thinking a NoSQL database would be the best solution here, as there isn't too much relational logic going on, and the total data size isn't too large (something like < 100 mb, closer to < 30 mb). I did look at SQLite because it seems to be simple enough to embed in an installable application.

Since this is a distributed application for end-users and not a high-load server, the database doesn't have to support many simultaneous users. The main priority here is to find a database whose model makes the most sense.

So the question, which database would be most applicable for this situation?

Also, are there any other databases that would make more sense for an application like this?

share|improve this question

2 Answers 2

up vote 8 down vote accepted

The first thing that comes to mind is a particular RDBMS that's familiar to me. I recognize, however, that it may not be the best for this application.

So, my advice is to go with a database that are familiar to you. If you're familiar with Redis or MongoDB, then go with one of those. If you're more familiar with SQLite, then chose that.

On a database of this size, it's all going to be pretty quick. Even databases that are more disk-heavy will use some sort of caching so that disk speed isn't too much of a concern.

share|improve this answer
    
Yeah, a database of that size will likely be served entirely out of memory. –  Nick Chammas Aug 12 '11 at 15:47
1  
I am familiar with MySQL (but it's been years), CouchDB, and Redis (just started), and I have a similar structure in SQLite that I can reference. I guess with a db this size it doesn't really matter too much. –  tjameson Aug 12 '11 at 15:55

If you are not that concerned with relational logic, want really fast read speed, and you are willing to work with an RDBMS, I would prejudicially venture to say MySQL. Why ???

The MyISAM storage engine has an option that can allow the physical structure of the table to be augmented for better performance. What is that option ? The ALTER TABLE option ROW_FORMAT.

For example, the book MySQL Database Design and Tuning recommends using ROW_FORMAT=FIXED on pages 72,73. This will internally convert all VARCHAR fields to CHAR. It will make the MyISAM table larger, but executed SELECTs against it will be much faster. I can personally attest to this. I once had a table that was 1.9GB. I changed the format with ALTER TABLE tblname ROW_FORMAT=FIXED. The table ended up 3.7GB. The speed of the SELECTs against it was 20-25% faster without improving or changing anything else.

What if you already have a MyISAM table that is populated with data ? You could get metrics for recommended column definitions based on the data present in the MyISAM table. What query presents those metrics ?

SELECT * FROM tblname PROCEDURE ANALYSE();

PROCEDURE ANALYSE() This will not display data. It will read the value of every column and recommend column definitions. Example, if you have a type column whose values are 1-4, it would sugggest using an ENUM of those 4 values. You could then choose to use TINYINT or CHAR(1) since they take the same amount of space (1 byte).

Here is something else to consider: Since you were thinking about using a NoSQL DB, have you ever thought of using MyISAM in a NoSQL manner ? This is quite possible. Page 175 of the same book I mentioned suggests using HANDLER structures to read a table without the relational baggage. In fact, page 175 gives this example:

CREATE TABLE customer_mileage_details
(
    customer_id INT NOT NULL,
    ff_number CHAR(10) NOT NULL,
    transaction_date DATE NOT NULL,
    mileage SMALLINT NOT NULL,
    INSERT(customer_id),
    INSERT (ff_number,transaction_date)
) ENGINE = MYISAM;

This table contains millions of rows. Suppose that you need to create a data analysis apllication that has the following requirements:

  • It needs to retrieve blocks of information as quickly as possible.
  • Based on user input or other factors, it will likely "jump around" in the table.
  • It is not concerned with concurrency or other data integrity issues.
  • Cross-application table locking is not required.

These commands allow quick-and-dirty reads from the table:

HANDLER customer_mileage_details OPEN;
HANDLER customer_mileage_details READ ff_number FIRST WHERE ff_number=('aaetm-4441');
HANDLER customer_mileage_details READ NEXT LIMT 10;
HANDLER customer_mileage_details CLOSE;

I hope this give food for thought. Please look into it.

CAVEAT

What is very ironic about about me writing this particular post is that I wrote an earlier post about HANDLER being used in Percona Server binaries and thinking that using it was out-of-date. Since that older post, I have never thought I would ever write something in support of HANDLER structures. I now stand corrected.

share|improve this answer
1  
Interesting point about using MySQL as a NoSQL database, but what would this buy me over using something like Redis or MongoDB? –  tjameson Aug 12 '11 at 15:58
1  
Quick-and-dirty answer ? Should you ever have to go back to the relational model, even just for reporting purposes, all the bells and whistles are in place to make the transition back. In addition, you could still use relational operations in conjunction with NoSQL-style access of MyISAM. BTW InnoDB also allows HANDLER access to data. –  RolandoMySQLDBA Aug 12 '11 at 16:04
1  
+1 for the ROW_FORMAT=FIXED. That's useful! –  Richard Aug 12 '11 at 17:24

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.