What kind of database should I use because MySQL is not meeting my need?

Question

Currently, I am using MySQL but I feel it is not keeping up with the stream data that is coming. I have memcached in front of MySQL for caching, but most the data is accessed for single time so the cache is not helping much. The other problem is the ibdata file keeps getting bigger and even deleting data won't claim disk space.

The data is short, average 200 characters. I just want store the data and retrieve it using a key. There is no complex data relationship. There are about 1,000,000 insert and get in hour but getting much bigger. I even tried Amazon RDS service, but the latency between EC2 and RDS within the same region is slow I started getting MySQL too many connection error.

I know that I have to use Nosql type of database, but not sure which one. Disk space is important that I need to claim it when I delete data.

Are you using InnoDB or MyISAM as the default storage engine ??? What version of MySQL are you using ??? — RolandoMySQLDBA, Oct 05 '11 at 21:55

score 1 · Answer 1 · edited Oct 05 '11 at 19:20

1

It is only you who can choose. There is a perfect starting point at http://nosql-databases.org/ where you can begin your discovery.

Beside seeing all current databases you can compare their features, options and case studies. This should lead you in the right direction.

edited Oct 05 '11 at 19:20

Joel Coel

12,932
14
62
100

answered Oct 05 '11 at 18:28

mailq

17,023
2
37
69

score 1 · Answer 2 · answered Oct 06 '11 at 00:28

1

Have you considered using a simpler data store, since you aren't using relationships and only need to query by a key?

I'm thinking something along the lines of Berkeley DB or Redis.

answered Oct 06 '11 at 00:28

Martin

490
2
5

score 0 · Answer 3 · answered Oct 05 '11 at 21:57

0

First of all - in SQL world MySQL is fastest engine with very good scalability.

I think, you can|have to redesign some parts in order to get real performance, 1 million operation per hour isn't impossible number.
Maybe you'll want to examine all possible backend-engines and change used at start,fine-tune mysql-settings
PRIMARY KEY
persistent connections
consider running ANALYZE TABLE (or myisamchk --analyze from command line) on a table to help MySQL better optimize queries
Use INSERT DELAYED or INSERT LOW_PRIORITY (for MyISAM) to write to your change log table.
if you delete data from tables, schedule running OPTIMIZE TABLE query regularly

TBC, if you'll want

answered Oct 05 '11 at 21:57

Lazy Badger

3,137
15
13

4

I think that Oracle might have a thing or two to say about your opening statement. – MDMarra Oct 05 '11 at 22:03
2

Microsoft may also have something to say about that. – mrdenny Oct 06 '11 at 01:15
2

If nothing else, a broad and unqualified claim like that cannot be made by ANY SQL server product. Each one shines or fades under different circumstances. – John Gardeniers Oct 06 '11 at 01:26

What kind of database should I use because MySQL is not meeting my need?

3 Answers3