Which engine to choose MyISAM vs InnoDB for the bulletin board? The site has just been launched, but approximately we can say that adding is about 100-200 records per day, deletion is about 100-200 records per day, viewing is 2000-3000 records per day. Possible growth in the future. What is better to choose, please tell me.

Closed due to the fact that it is necessary to reformulate the question so that it was possible to give an objectively correct answer by the participants aleksandr barakin , user194374, VenZell , Bald , Streletz July 8, 16 at 16:41 .

The question gives rise to endless debates and discussions based not on knowledge, but on opinions. To get an answer, rephrase your question so that it can be given an unambiguously correct answer, or delete the question altogether. If the question can be reformulated according to the rules set out in the certificate , edit it .

  • one
    if you care about the correctness of the choice of storage, then first of all you need to abandon mysql - etki
  • @Etki argue pliz. - Kromster
  • one
    @Kromster I do not really understand what you need to argue. There is no normal replication, there is no hot-swap, there are a lot of stories with sudden stops, even only in SQL space there are much more well-proven solutions. - etki
  • @Etki thanks, this anti-recommendation is much better! - Kromster

4 answers 4

MyISAM does not support transactions, it is almost always a minus. If you start on MyISAM, and then start doing manual transaction mechanics (I do not advise), you will add an additional level — and surely an error level full of errors — into the code.

Therefore InnoDB. And do not engage in premature optimization, you will be more worried about the performance of your queries, and not the engine.

The advice about buffer_pool_size you, of course, should take into account, but again, keep in mind that this is another premature optimization. First, learn how to use indexes, and then increase buffer_pool_size, otherwise the first time you will just think that you have a fast database :-)

Moreover, for such small loads - purely in performance - the difference is absolutely imperceptible.

PS Regarding postgres advice: for tasks such as a bulletin board - exactly no difference. There are wars between postgres / mysql about performance, but - for your task - you need to increase the load by a couple of orders of magnitude to notice in which case mysql is better, and in which - postgres.

The main differences between mysql / postgres begin on a high-load hub with increased requirements for specific functionality, replications, etc.

If you organize the work with the database with microservices / encapsulation in the code, migration from one database to another (or using many databases at the same time) will not be a problem for you.

If you are not too lazy to learn postgres, please. If you have big money and you can abruptly find specialists with knowledge of a particular database, choose the database under which you can quickly find specialists suitable for you in terms of the price-quality ratio, or the one that your system architect will advise be if you have big grandmothers)

If you are just doing a bulletin board - like your little startup project / pet project - take mysql, the threshold of entry is slightly lower than in postgres.

In any case, as much as possible encapsulate the program code from calls to the database.

    Focus on InnoDB, the fact is that MyISAM is locked at the table level, in contrast to InnoDB in which the lock is at the row level. MyISAM is very fast when you have a small table, due to the fact that you do not spend transaction costs, but as the table grows, and as you have many simultaneous inserts, MyISAM will lose.

    Just adjust the size of innodb_buffer_pool_size so that your database fits there completely. By default, the size of this buffer is 128MB, if you exceed it, the base is placed on the disk and the speed of query execution drops sharply.

      It's simple: if you don't need your data anyway, then use a blackhole . He does not even promise that he will be able to read the recorded data, unlike any myisam . Well, or take the memory .

      If it is important for you to save your data - you need a transactional repository. I’m not talking about the commonplace begin and commit , but about decades of research, rakes and stuffed cones, both academic and practical, in questions "how can we not lose data" and how to start in an agreed form after the accident, and not as unintelligible. And also - how to make the whole kitchen move faster.

      Of the verified transactional repositories in mysql there is only innodb and its branch XtraDB . Therefore, there is nothing to choose from. In the comments, it’s not the worst idea to migrate to something else. In particular, the remarkable transactional DBMS PostgreSQL .

        MyISAM is better (faster) with the vast majority of selects like yours, InnoDb with inserts. The disadvantages are described above in the responses. and 50,000 selektov for MYSQL is not a difficult task, so the choice of the base is for the future, properly configured mysql works well and with large numbers. If you choose, look towards MariaDb, almost complete compatibility, but much faster and more stable, from the same development as the muscle. In addition, phpmyadmin is also sharpened for it. That is, you do not lose anything.

        • mariadb and percona are drop-in replace for mysql. About "the same development as Muskul" - you interpret it a little widely. developer left to work on mariadb. >> `but much faster and more stable` - proof? but I don’t even know about optimizing phpmyadmin for one of the mysql clones. - strangeqargo