-1

I have a mysql database instance with more than 3000 database inside. Each database contains more than 200 tables. I have more than 100 gb of data in all these database at present. I am using windows server 2012R2 operating system with a 4GB of RAM. The RAM memory utilization of the server system was always showing very high. So I tried to restart the system and restart is not working. It is showing restarting for long time and not restarting. When i checked the logs I understood that there is a memory issue. I want to restart my mysql instance and continue. What is the best configuration for the mysql with above architecture? what i need to do to make this work with out failure in future?

[Warning] InnoDB: Difficult to find free blocks in the buffer pool (1486 search iterations)! 1486 failed attempts to flush a page! Consider increasing the buffer pool size. It is also possible that in your Unix version fsync is very slow, or completely frozen inside the OS kernel. Then upgrading to a newer version of your operating system may help. Look at the number of fsyncs in diagnostic info below. Pending flushes (fsync) log: 0; buffer pool: 0. 26099 OS file reads, 1 OS file writes, 1 OS fsyncs. Starting InnoDB Monitor to print further diagnostics to the standard output.
irfan
  • 99
  • 2

1 Answers1

1

4GB RAM -- That is low (these days). Several issues:

  • If you are swapping, then consider splitting the machine into two: MySQL on one; apps on the other.
  • Adjust innodb_buffer_pool_size down to avoid swapping or up to provide more efficiency and less I/O.
  • Both the 100GB of data and the 600K tables would benefit from increasing RAM.

`table_open_cache can't be 600K, but it should be several thousand. Or you should find a way to decrease the number of databases and/or tables per database.

If this is some XaaS service, would it work to move some users to a new machine, thereby altering all the relevant settings and metrics.

We need more information to help further. What app is it? How active is it? Is it swapping? Are apps on the same machine? What version of MySQL?

http://mysql.rjweb.org/doc.php/mysql_analysis

Rick James
  • 2,463
  • 1
  • 6
  • 13
  • Hi..Thank you for the response. The app is a billing application in android. It will be more active during the day time. Every invoice will be updated in the database real time. For each user ,i am having 2 databases in mysql. Apps and database are different machine. MYSQL version is 5.7.22. Please give some suggestions. – irfan Oct 08 '20 at 07:59
  • @irfan - You have 100GB on a smartphone? – Rick James Oct 08 '20 at 14:00
  • No. The application have more than 1000 users. Total data comes around 100 gb of all these users. – irfan Oct 09 '20 at 04:52
  • total data of all the 3000 databases is coming more than 100 gb. – irfan Oct 09 '20 at 05:07
  • which means ,each database have 100/3000 gb of data – irfan Oct 09 '20 at 05:07
  • @irfan - What matters is the "working set". That is how much data is actually being used in a typical hour or day. If most of the 100GB is "old" and "never touched", then that metric is not important. If, on the other hand, the queries look at all 100GB all the time, there will be a _lot_ of I/O. Hence, the tuning needs to be somewhat different. – Rick James Oct 09 '20 at 17:43
  • @irfan - Does one database == one user? (As in some "...-as-a-Service",) Then the question is how many different users show up during a typical hour? – Rick James Oct 09 '20 at 17:45
  • All the users will be active during day time and mostly around 10 tables are frequently used by in each database. – irfan Oct 12 '20 at 18:26