2

I've been working on MySQL database in simple web applications for a long time and i now have a project which should maintain about 1,000 up to 10,000 query per minute (it is some logging database system) and a lot of theme are Insert statement.

First thing worried me is i don't think MySQL could handle that much query per second,I know i should buy stronger Server but i don't know if MySQL can be good solution for that.Is there any way to calculate that ability before buying servers?

Second important thing is that the database will grow too fast and it will be use all server resources like hard disk or CPU usage sooner or later and i should have an expanding solution,my question in this part from experienced database admins is :

"Is MySQL suitable for distributing or cluster databases ?"

or

"Is there any easy to use database which support clustering and distributing systems?" (I know MySQL have a cluster version but i don't know if it is appropriate in my project).

Edit:

The project is about logging users(by their RF-ID tags) as they pass throw the antenna range or rf-id readers or Electronic gates and read their permission to enter or leave the area which specified in database for that tag ID, and record there activity in database.The operator should access all these data and be able to manipulate theme.

P.S:

I know there are very professional servers with large capacity of hard disk like SAN or NAS(Network-attached storage),but as it is not national or very big company project i need easy to distribute database to distribute it over normal servers.

1

"Is MySQL suitable for distributing or cluster databases ?"

While you certainly can do manually sharding, the truth is that the most common MySQL cluster technologies are focused in high availability. NDB Cluster is based mainly on a memory database with lots of throughput, but not necessarily low-latency and large datasets. Galera does not support autosharding (only does read scaling).

"Is there any easy to use database which support clustering and distributing systems?" (I know MySQL have a cluster version but i don't know if it is appropriate in my project).

Other people may give you better advice about other databases, but beware:

  • Many vendors that criticize the relational and transactional database model sometimes forget to tell you ideas like "eventually consistent" or the possibility of data loss in exchange for better performance (I look at you, NoSQL).
  • Can you use a non-distributed database? There are datawarehousing/highly compressed solutions for MySQL (InfiniDB, TokuDB). Even InnoDB has a memcached interface (allowing you even 1M Querys Per Second), which may help in reducing latency for the SQL (although not memory requirements and disk space).

If you tell us more about how you are going to use your logs (statistics, historic data?), we may be able to tell you if a relational database is better for you, or you have to go for other paradigm like column-based databases, or map-reduce, etc. Even then, you should try any product for yourself.

  • Thank you for your answer, i update the question and explained the project in it.i appreciate your help. – dodol Jul 10 '14 at 6:51
  • This is the key: "The operator should access all these data and be able to manipulate theme." Do you need basic CRUD options? Analytics and statistics? Agregation of data? Searches in real time? – jynus Jul 10 '14 at 6:54
  • Yes sir the CRUD and search is too important and they must be in real time if it took long (more than a second) we should disable the the gates and disaster will happen.the Aggregation is too important but i need some transparency if we need to distribute the DB,i mean if we have to add a server to back that up it look like one server. – dodol Jul 10 '14 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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