I would like to know your opinion on which DBMS (probably SQL) can perform fairly well and not stress the server too much from the CPU / RAM point of view. I'm used to MySQL but I found (maybe it was a bug of MySQL at the time - about 4 years ago) that a database of 1GB (or was it 10GB ? oh well ...) causes system to eat a VERY LOT of ram (something like 2GB of RAM) when only one simple query was made on one of the many tables (a SELECT * FROM ... my_table WHERE='' query and the WHERE condition represented about the 0.1* of the entries of the table).

I don't need a highly performant DBMS which can handle 1TB of data and perform well with more than 100'000 queries per second without eating more than 10GB of RAM. I'm planning to use this as a "statistics collector" database with PHP (stats such as temps, loads, network activity, ... in my LAN). Queries will be made with PDO (or other extensions presents in a framework you may reccomend). I would still like to have a BIG database with low impact on the system, and queries will be made not very frequently (say 1 query each 10 seconds as an order of frequency).

Since these are stats it might as well be done in sqlite, but I'm a little bit reluctant to this approach since I'm not really sure it performs quite as good as MySQL without eating as many resources. Would PostGreSQL be a worthy alternative ? RAM usage should be as low as possible (anyway <4GB MAX). CPU usage as well (the lower,the better) but with >= 4 cores @ >2.8GHz I don't really see problems.

Do you have any special reccomendation ? This will be coded from scratch and has nothing to do with the DB I speak before eating too many resources. Any help will be appreciated.

  • 1
    Shopping/product recon questions aren't on topic here. Please see the FAQ for what is and isn't on topic. – Jacob Feb 29 '12 at 20:22
  • I think you have a bit of a misunderstanding about the relationship between an RDBMS, performance and memory. E.g. A 1Tb database with 100,000 queries per second would need more like. 128gb of ram to even be close to performing well... – Mark Henderson Feb 29 '12 at 20:27
  • I think I'm in the range of 1GB-10GB database MAX. My point is that I have a BIG database but which is not really accessed frequently (few queries / sec). Could that run in low memory environnement (such as 16GB) ? – user76949 Feb 29 '12 at 20:33
  • Why downvotes ? Maybe for those that downvoted it's obvious but for me is not. – user76949 Feb 29 '12 at 20:35
  • @Jacob: so if I edit and explain more clearly will you reopen this question ? – user76949 Feb 29 '12 at 20:41

MySQL should do fine for you given you code the SQL well and have appropriate indexes defined for the queries you are running. You can tune memory but the biggest factor will be your sql and the access plans based upon available indexes.

Before anyone says it, yes PostGreSQL would also do fine as would Oracle, DB2, SQL Server, Sybase, Informix, etc. It sounded like OP has MySQL experience so I went there. The big vendor ones also tend to eat more memory.

  • A good soul showed up finally. I don't understand if the question was posed in a bad way or why they decided to close it, but I'm grateful at least one user decided to give me his advice. – user76949 Feb 29 '12 at 20:37

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