-2

My Mysql server often crashes, and I need to restart mysql using "service mysqld start" command. I check the mysql error log file.

130807 22:59:47 mysqld_safe Number of processes running now: 0
130807 22:59:47 mysqld_safe mysqld restarted
130807 22:59:47 [Note] Plugin 'FEDERATED' is disabled.
130807 22:59:47 InnoDB: The InnoDB memory heap is disabled
130807 22:59:47 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130807 22:59:47 InnoDB: Compressed tables use zlib 1.2.5
130807 22:59:47 InnoDB: Using Linux native AIO
130807 22:59:47 InnoDB: Initializing buffer pool, size = 128.0M
InnoDB: mmap(137363456 bytes) failed; errno 12
130807 22:59:47 InnoDB: Completed initialization of buffer pool
130807 22:59:47 InnoDB: Fatal error: cannot allocate memory for the buffer pool
130807 22:59:47 [ERROR] Plugin 'InnoDB' init function returned error.
130807 22:59:47 [ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
130807 22:59:47 [ERROR] Unknown/unsupported storage engine: InnoDB
130807 22:59:47 [ERROR] Aborting

130807 22:59:47 [Note] /usr/libexec/mysqld: Shutdown complete

130807 22:59:47 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended

I have about 10,000 users and I have a database which have 10,000 tables. Those tables are used for recording the user status. And, when I create a new user table, I use the following code with PDO.

$statusTable = "status_".$uid;
$qstr = "CREATE TABLE IF NOT EXISTS `status`.`$statusTable` (
    `prim_id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `message` VARCHAR( 600 ) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
    `created_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE = INNODB";
$db->query($qstr);

Does this code cause mysql crash? I use EC2 instance with 615MB ram. Thanks!!

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Howard
  • 35
  • 6
  • Apparently some memory cannot be allocated, so you either misconfigured MySQL (perhaps try [mariadb](http://mariadb.org/) instead) or have not enough RAM. BTW `errno==12` means `ENOMEM` – Basile Starynkevitch Aug 08 '13 at 10:13
  • 3
    10,000 tables????? One for each user?? What?? I don't know if that's your problem or not, but it certainly **isn't** good database design. – Spudley Aug 08 '13 at 10:14
  • 1
    Agreed with Supdley. You should have at most a few dozens of tables (a humanly reasonable number of them). Some of them can be huge (having many millions of rows), if the hardware resources are sufficient for them. – Basile Starynkevitch Aug 08 '13 at 10:17
  • I can't help -1 it. a table per user. Only in PHP world it can happen. – Your Common Sense Aug 08 '13 at 10:36
  • 1
    So... I have better record all message in a big table. When I need to get all message of a user, I select from this big table where uid = ?. Is it right? – Howard Aug 08 '13 at 10:45
  • @Howard, yes, have a single `user` table, primary key `id`, and all your other tables can then have a foreign key `user_id` with a constraint to preserve referential integrity. So, the table you refer to would probably be `status`, would have it's own `id`, and would point to `user_id`. – halfer Sep 05 '13 at 17:14

3 Answers3

2

Aside from a very bad database design, your question/problem seems to have been posted and answered before.

Community
  • 1
  • 1
Brent Baisley
  • 12,641
  • 2
  • 26
  • 39
1

EASY ANSWER: You do not need to create a new table for every user to track their status. You are using their $uid so, you can use a single table with a column uid to track their data. Then when you want to get their data, use $uid such as 

SELECT * FROM user_data
WHERE uid=$uid

Also, you can set a cron scheduler to restart MySQL if it crashes.

* * * * * systemctl is-active --quiet mysqld || systemctl restart mysqld

DETAILED ANSWER:

This is an important question especially for people who are using a very small VPS, say 1GB of RAM or less. If MySQL is dropping out, it may be a problem with your server configuration (Apache | nginx) or MySQL configuration. DOS attacks may cause an increased spike in system resource usages (see image). The end result is that MySQL process gets shutdown by the Kernel. For a long-term solution should look at optimizing your Apache or MySQL configurations.

System resources spike causing RAM spike (just before 6pm) and system resources spike causing only a CPU spike Midnight on Tue 18

There are several other discussions Stack Overflow those topics as well as the MySQL manual and Percona Blog:

MySQL Manual - How MySQL Uses Memory:

https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

Percona - Best Practices for Configuring Optimal MySQL Memory Usage:

https://www.percona.com/blog/2016/05/03/best-practices-for-configuring-optimal-mysql-memory-usage/

How to Optimize MySQL Performance Using MySQLTuner:

https://www.linode.com/docs/databases/mysql/how-to-optimize-mysql-performance-using-mysqltuner/

Apache Memory Usage Configuration:

https://serverfault.com/questions/254436/apache-memory-usage-optimization

Apache Manual on Performance Tuning:

https://httpd.apache.org/docs/2.4/misc/perf-tuning.html

Tuning Apache Server:

https://www.linode.com/docs/web-servers/apache-tips-and-tricks/tuning-your-apache-server/

However, with respect to your original question, yes, you can script a temporary solution that checks if MySQL service is loaded and active and will restart MySQL if it is not loaded and active.

You did not mention what operating system you are using. That would help to give you a specific command. I will give you an example for CentOS linux.
Look at the following output of the command systemctl status mysql. You can see at the top that the service is loaded and active.

[root@centos-mysql-demo ~]# systemctl status mysqld
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2019-06-18 18:28:18 UTC; 924ms ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 3350 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 3273 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 3353 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─3353 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Jun 18 18:28:11 centos-mysql-demo systemd[1]: Starting MySQL Server...
Jun 18 18:28:18 centos-mysql-demo systemd[1]: Started MySQL Server.

If the service is not loaded, then a command such as:

systemctl status mysqld || systemctl restart mysqld

will do the trick of re-starting the process. You could cron that: 

* * * * * systemctl status mysqld || systemctl restart mysqld

However, in the case that mysql is loaded, but the service is not active, your cron will do nothing. So, you should use a more detailed command such as:

* * * * * systemctl is-active --quiet mysqld || systemctl restart mysqld

In this case, if the service is loaded but inactive such as the state that a DOS attack can leave your mysql service, the command will also restart mysql. Using the --quiet flag just specifies the command only to return a status code, not output anything to the screen. If you ommit the --quiet flag you will see a status output of either active or inactive.

You may also create some swap space to add more available RAM resources to your server such as:

sudo dd if=/dev/zero of=/swapfile count=2096 bs=1MiB
chmod 600 /swapfile
mkswap /swapfile
swapon /swapfile
swapon --show
swapon --summary
free -h
raw-bin hood
  • 5,839
  • 6
  • 31
  • 45
-1

I think the problem is you have many parallel connections to your database which is causing this problem. The problem is not with table, problem is with architecture around it.

halfer
  • 19,824
  • 17
  • 99
  • 186
Raghu Chandra
  • 1,102
  • 1
  • 9
  • 14