1

We are running Mysql on CentOS 5.5. And are using the database sharding approach. As it is an erp app, there are about 300 tables for each new Application/company. At the moment we have around 600 databases in our mysql server. (250 GB, 24 GB Ram) But the server crashes rather often. 1. Is there a known limit with mysql and the no of databases it can support reliably. i went through the other threads and blogs , but could not find any clear analysis of no. of databases vs mysql server crashes/availability.

On the other hand, i would also like advice from anyone who has done db admin for a complicated app like ERP. 2. Is it a reliable model? How would you change it to make it more reliable?

Software Mechanic
  • 231
  • 1
  • 9

1 Answers1

1

First of all, I am not sure what you call "often". But in my opinion a DB server should not crash. You should really try to get to the bottom of this problem. Is it the whole server that goes down or just the MySQL process? Logs from the crash? MyISAM or Innodb?

Of course there are ways you can get MySQL to fail. The most obvious one that you are likely to run into is to configure MySQL to use more memory than is available (it will run out of memory and crash randomly).

But to answer your questions:

  1. There are known limitations, most of them coming from the OS. Remember that each DB is stored in a separate file, which can cause you to run out resources like file handles. Depending on your configuration 600 databases can start pushing those limits, which can cause annoying crashes under load.

  2. I wouldn't call most ERP installations very complicated nor the amount of data you are dealing with very large. My primary recommendation is to make sure your HW/SW installation is reliable. Unless you run into serious load problems sharding/master-slaves and similar setups are likely to reduce the reliability of your setup, due to the added complexity.

pehrs
  • 8,789
  • 1
  • 30
  • 46
  • Yep we went through the logs, but couldn't get much information out of it. But thanks . I will next try to configure the memory settings within mysql and see. by often, i meant about once in 2 weeks. It is not the whole server, but Mysql service. I guess, the answer then is to tweak the mysql cache settings – Software Mechanic Dec 21 '10 at 04:53
  • reg: 1. Thanks for that insight. Now i can look into the default ulimit settings for open files and other stuff. 2. I meant complicated in the sense of organisation of data. perhaps, high no. of tables is a better word. True at the moment we don't have a lot of data, but expect to over the next few months. – Software Mechanic Dec 21 '10 at 04:59