mysqlcheck可以帮助我解决数据库问题，而不损害我的数据库？ [英] Can `mysqlcheck` help me resolve database problems without damaging my database?

问题描述

背景

我有一个Drupal网站，其数据库由phpmyadmin管理。数据库大小超过1500个表。我有速度的巨大问题，并寻找一个很好的解决。

研究

我一直在努力找到一种方法来加快速度，一切都使我回到数据库的纯大小。我碰到了命令

  OPTIMIZE TABLE 
 tbl_name [，tbl_name] ... 
 
 <$> 
 c $ c> mysqlcheck -o< db_schema_name> 
  
我没有一个测试区域来使用这个命令，因为我们只有一个数据库。我知道运行这个命令在数据库上需要很长时间，因为数据库的大小可能需要几天。
 
 
 
 推理 
 
 
 
我想使用这个的原因是因为它似乎MySQL关闭或崩溃大约每天。在phpmyadmin我看到这个
 
 
 
  
 
 
 
它只有9小时，我从这个1个月前开始工作，我没有关闭。 
 
 
 
 状态变量 
 
 
 
是显示值作为警报的状态变量的列表，我希望用mysqlcheck修复这些值。
 
 
 
  
 
 
 
  Conclution  
 
 
 
我想知道如果在数据库上运行mysqlcheck将导致任何问题或损坏我的数据库中的任何数据。 
解决方案
第一部分的答案是好消息...  mysqlcheck -o 不会比运行 OPTIMIZE TABLE  这是一个方便的实用程序，登录到服务器，获取表的列表，并遍历它们，发送一个 OPTIMIZE TABLE 每次向服务器查询一个表，直到完成。
 
 
 
现在，有一些坏消息。如果您的表空间中有潜在损坏，可能会遇到 OPTIMIZE TABLE ，因此您应该确保您已准备好使用备份和恢复计划。这种情况相当遥远，但它是一种可能的结果。
 
 
 
更糟糕的消息：几乎肯定是咆哮着错误的树。 
 
 
 
在同一台计算机上运行Apache和MySQL，流量大或流量变化很大，是违反最佳实践的，并且是一个问题的食谱，因为这两种服务都倾向于以增加其在负载下的内存消耗，并且如果数据库是网站数据的后备存储，则增加的负载倾向于同时在两个服务上发生。
 
 
 
请参阅我对对数据库管理员Stack Exchange 的InnoDB崩溃后归档和为什么Apache在服务器上运行Wild和杀死MySQL故障彻底覆盖的这个相当普遍的问题，其中MySQL是受害者，超过任何东西。 
 
 
 
请注意，无论是否使用InnoDB都没关系。 MySQL错误日志中的数据库恢复条目会有所不同，但是死循环是这样的：前面没有任何可疑，MySQL错误日志说：
  mysqld_safe现在运行的进程数：0 
  
一个经常被误解为MySQL崩溃，但这不是发生了什么...它被杀了。 MySQL甚至可能拒绝重新启动，直到Apache平静下来或重新启动，或服务器重新启动。再次，从错误日志，您可能或可能不会另外看到这样：
  InnoDB：初始化缓冲池， 4.0G 
 InnoDB：mmap（4395630592 bytes）failed; errno 12 
 InnoDB：缓冲池完成初始化
 InnoDB：致命错误：无法为缓冲池分配内存
 [错误]中止
 [注意] / usr / libexec / mysqld ：关闭完成
 mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended 
  
检查 / var / log / syslog 或 / var / log / messages 会显示真正的问题。
  $ sudo egrep'kernel | oom'/ var / log / syslog 
  
 ...或消息...应显示大量条目，如下所示：
  kernel：pcscd invoked oom-killer：gfp_mask = 0xd0，order = 0，oomkilladj = 0 
  
 Apache的内存很匮乏，系统会面临整体不稳定的风险，所以东西被牺牲了。 某事可能是MySQL服务器守护程序， mysqld 。
  kernel：Out of memory：Killed process 3044，UID 27，（mysqld）
  
 
 
尝试自己重新启动，并且对于所有你知道，这可能偶尔也会发生，但是除非Apache的内存需求快速下降，MySQL将不允许从系统请求足够的内存，并将放弃。 
 
 
 
优化表具有有效的应用程序...但是，在这种情况下，如果我已经正确识别您的问题，将是非常相当的重新安排躺椅 它可以节省一些磁盘空间，但是它也会在运行时花费一些空闲磁盘空间，因为一些存储引擎创建一个新的表副本，然后重命名副本并删除旧表。无论如何，它不太可能对内存消耗产生任何有意义的影响。
 
Background

I have a Drupal site with a database that is managed with phpmyadmin. The database is over 1500 tables in size. I'm having huge issues with speed and was looking for a good fix. The database is 2.5-3 years old and has never been maintained (to the best of my knowledge).

Research

I've been trying to find a way to speed things up and everything leads me back to the pure size of the database. I came across the command
OPTIMIZE TABLE
tbl_name [, tbl_name] ...
Witch soon lead me to the more powerful
mysqlcheck -o <db_schema_name>
I don't have a test area to use this command on as we only have one database. I know running this command will take a very long time on my database it could take days because of the sheer size of the database.

Reasoning

The reason I would like to use this is because it seems that MySQL shuts down or crashes every day or so. On phpmyadmin I see this 



It has only been up for 9hrs and I haven't switched it off since I started work on this 1 month ago. It seems to never get upto more than 15hrs.

Status Variables

This is the list of status variables that are showing values as alerts, I'm hoping to fix some of these with the mysqlcheck.



Conclution

I would like to know if running mysqlcheck on the database will cause any problems or damage any data in my database. It would also be handy to get an idea of how much time an operation like this will take.
解决方案
The first part of the answer is the good news... that mysqlcheck -o is no more likely to harm your database than is running OPTIMIZE TABLE on every table, because that's all it does.  It's a convenience utility that logs into the server, fetches a list of the tables, and iterates through them, sending an OPTIMIZE TABLE query to the server for one table at a time, until it's done.

Now, some bad news.  If you have latent corruption in your tablespaces, OPTIMIZE TABLE might run into it, so you should be certain you are prepared for that possibility, with backups and a recovery plan.  The chances of this are fairly remote, but it is one possible outcome.

Worse news: are almost certainly barking up the wrong tree.  

Running Apache and MySQL together on the same machine with significant traffic -- or significant traffic variation -- is against best practices and is a recipe for problems, because both services tend to increase their memory consumption under load, and if the database is the backing store for web site data, then increased load tends to occur on both services at the same time.

See my answer to InnoDB Crash Post Mortem on Database Administrators Stack Exchange and Why is Apache Running Wild and Killing MySQL on Server Fault for thorough coverage of this fairly common problem, where MySQL is the victim, more than anything.  

Note that it doesn't matter whether you are using InnoDB or not.  The database recovery entries in the MySQL error log will be a little different, but the dead giveaway is this: preceded by nothing suspicious at all, the MySQL error log says:
mysqld_safe Number of processes running now: 0
The messages following that one are often misinterpreted as MySQL "crashing," but that's not what's happening... It's been killed.   MySQL may even refuse to restart, until Apache calms down or is restarted, or the server is rebooted.  Again, from the error log, you may or may not additionally see something like this:
InnoDB: Initializing buffer pool, size = 4.0G
InnoDB: mmap(4395630592 bytes) failed; errno 12
InnoDB: Completed initialization of buffer pool
InnoDB: Fatal error: cannot allocate memory for the buffer pool
[ERROR] Aborting
[Note] /usr/libexec/mysqld: Shutdown complete
mysqld_safe mysqld from pid file /var/run/mysqld/mysqld.pid ended
Checking /var/log/syslog or /var/log/messages (depending on which distro you run) will show you the real issue.
$ sudo egrep 'kernel|oom' /var/log/syslog
...or messages... should reveal a number of entries beginning something like this:
kernel: pcscd invoked oom-killer: gfp_mask=0xd0, order=0, oomkilladj=0
Apache gets so memory hungry that the system is at risk of overall instability, so "something" gets sacrificed.  That "something" is likely to be the MySQL Server daemon, mysqld.
kernel: Out of memory: Killed process 3044, UID 27, (mysqld)
MySQL will usually try to restart on its own, and for all you know, this may occasionally be happening, too... but unless Apache's memory demands drop off quickly, MySQL will not be allowed to request sufficient memory from the system, and will give up.

Optimizing the tables has its valid applications... but, in this case, if I have identified your issue correctly, it would be very much comparable to rearranging the deck chairs on the sinking ship Titanic.  It may save you some disk space, but it will also cost you some spare disk space while running since some storage engines make an entirely new copy of the table, then rename the copy and delete the old table.  In any event, it's unlikely to have any meaningful impact on memory consumption.

                        
这篇关于mysqlcheck可以帮助我解决数据库问题，而不损害我的数据库？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！