MYSQL稳 定性问题，急急急~~~~

[sdomain]

我是在windows下安装的mysql 5.7.9

程序是php 访问，现在有两个问题一直得不到解决：
1.
当网站访问量稍微多一点（100+人以上）时，数据库占用CPU就非常高，我试了下，因我为的是ERP系统类软件，查询可能比较复杂，有些查询，一条语句可能会执行个两三秒的，我发现只要我多执行几次复杂的语句mysql的CPU的一下上到了100%。这个怎么处理呀，希望大神保帮个忙，多谢了。
2.我没办法，换了好几台机器还是一样，今天又突然发现另一个问题，就是mysql进程突然就死了，日志如下所示：

Version: '5.7.9'  socket: ''  port: 60033  MySQL Community Server (GPL)
2019-01-01T03:05:26.988620Z 0 [Note] InnoDB: Buffer pool(s) load completed at 190101 11:05:26
2019-01-01T03:07:52.939971Z 347 [Note] Aborted connection 347 to db: 'sa_crm' user: 'sa' host: 'localhost' (Got an error writing communication packets)
2019-01-01T03:08:06.463601Z 450 [Note] Aborted connection 450 to db: 'sa_crm' user: 'sa' host: 'localhost' (Got an error writing communication packets)
2019-01-01T05:36:15.184254Z 0 [ERROR] InnoDB: Operating system error number 995 in a file operation.
2019-01-01T05:36:15.184254Z 0 [ERROR] InnoDB: The error means that the I/O operation has been aborted because of either a thread exit or an application request. Retry attempt is made.
2019-01-01 13:36:15 0x410c  InnoDB: Assertion failure in thread 16652 in file fil0fil.cc line 5606
InnoDB: Failing assertion: err == DB_SUCCESS
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.7/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
05:36:15 UTC - mysqld got exception 0x80000003 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=67108864
read_buffer_size=2097152
max_used_connections=172
max_threads=1000
thread_count=2
connection_count=2
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4173653 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
13faf4892    mysqld.exe!my_sigabrt_handler()[my_thr_init.c:449]
13fe8b929    mysqld.exe!raise()[winsig.c:587]
13fe8a820    mysqld.exe!abort()[abort.c:82]
13fbf18c8    mysqld.exe!ut_dbg_assertion_failed()[ut0dbg.cc:67]
13fc0e897    mysqld.exe!fil_aio_wait()[fil0fil.cc:5608]
13fbda1a4    mysqld.exe!io_handler_thread()[srv0start.cc:308]
779c59cd    kernel32.dll!BaseThreadInitThunk()
77c2385d    ntdll.dll!RtlUserThreadStart()
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2019-01-01T05:36:20.410319Z 0 [ERROR] Can't create thread to handle new connection(errno= 1)
2019-01-01T05:37:57.090319Z 0 [Warning] The syntax '--log_warnings/-W' is deprecated and will be removed in a future release. Please use '--log_error_verbosity' instead.
2019-01-01T05:37:57.090319Z 0 [Note] SXdbserver (mysqld 5.7.9) starting as process 26216 ...
2019-01-01T05:37:57.107853Z 0 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2019-01-01T05:37:57.172144Z 0 [Note] InnoDB: Uses event mutexes
2019-01-01T05:37:57.172144Z 0 [Note] InnoDB: _mm_lfence() and _mm_sfence() are used for memory barrier
2019-01-01T05:37:57.172144Z 0 [Note] InnoDB: Compressed tables use zlib 1.2.3
2019-01-01T05:37:57.172144Z 0 [Note] InnoDB: Number of pools: 1
2019-01-01T05:37:57.172144Z 0 [Note] InnoDB: Not using CPU crc32 instructions
2019-01-01T05:37:57.218901Z 0 [Note] InnoDB: Initializing buffer pool, total size = 256M, instances = 1, chunk size = 128M
2019-01-01T05:37:57.250073Z 0 [Note] InnoDB: Completed initialization of buffer pool
2019-01-01T05:37:57.296830Z 0 [Note] InnoDB: Highest supported file format is Barracuda.
2019-01-01T05:37:57.312415Z 0 [Note] InnoDB: Log scan progressed past the checkpoint lsn 2008295603
2019-01-01T05:37:57.312415Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2008309709
2019-01-01T05:37:57.343587Z 0 [Note] InnoDB: Doing recovery: scanned up to log sequence number 2008309709
2019-01-01T05:37:57.343587Z 0 [Note] InnoDB: Database was not shutdown normally!
2019-01-01T05:37:57.343587Z 0 [Note] InnoDB: Starting crash recovery.
2019-01-01T05:37:58.157939Z 0 [Note] InnoDB: Starting an apply batch of log records to the database...
InnoDB: Progress in percent: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 
2019-01-01T05:37:58.687853Z 0 [Note] InnoDB: Apply batch completed
2019-01-01T05:37:58.985929Z 0 [Note] InnoDB: Removed temporary tablespace data file: "ibtmp1"
2019-01-01T05:37:59.001515Z 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2019-01-01T05:37:59.001515Z 0 [Note] InnoDB: Setting file 'D:\suxuantech\bin\mysql\data\ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2019-01-01T05:37:59.063858Z 0 [Note] InnoDB: File 'D:\suxuantech\bin\mysql\data\ibtmp1' size is now 12 MB.
2019-01-01T05:37:59.064832Z 0 [Note] InnoDB: 96 redo rollback segment(s) found. 96 redo rollback segment(s) are active.
2019-01-01T05:37:59.065806Z 0 [Note] InnoDB: 32 non-redo rollback segment(s) are active.
2019-01-01T05:37:59.066780Z 0 [Note] InnoDB: Waiting for purge to start
2019-01-01T05:37:59.142760Z 0 [Note] InnoDB: 5.7.9 started; log sequence number 2008309709
2019-01-01T05:37:59.142760Z 0 [Note] InnoDB: Loading buffer pool(s) from D:\suxuantech\bin\mysql\data\ib_buffer_pool
2019-01-01T05:37:59.142760Z 0 [Note] InnoDB: not started
2019-01-01T05:37:59.142760Z 0 [Note] Plugin 'FEDERATED' is disabled.
2019-01-01T05:37:59.142760Z 0 [Note] Server hostname (bind-address): '*'; port: 60033
2019-01-01T05:37:59.142760Z 0 [Note] IPv6 is available.
2019-01-01T05:37:59.142760Z 0 [Note]   - '::' resolves to '::';
2019-01-01T05:37:59.142760Z 0 [Note] Server socket created on IP: '::'.
2019-01-01T05:37:59.220689Z 0 [Note] Event Scheduler: Loaded 0 events
2019-01-01T05:37:59.220689Z 0 [Note] SXdbserver: ready for connections.
Version: '5.7.9'  socket: ''  port: 60033  MySQL Community Server (GPL)

愁死了，客户正式用的生产环境，着急呀，希望好心人帮忙看看。

[Inter_Ares]

一个看你的机器配置是多少？几核多少内存。
另外，你又多少数据量，需要查好几秒？是不是没有设索引？

[sdomain]

引用 1 楼 Inter_Ares 的回复:

一个看你的机器配置是多少？几核多少内存。 另外，你又多少数据量，需要查好几秒？是不是没有设索引？

8核16G内存， 大几十万的数据，索引建了很多，但语句很复杂，这个执行时间也没法理优化了。

[sdomain]

还有人能帮看看问题不。。。

[weixin_40784365]

先把my.cnf贴出来看一下，如果确认不了原因的话，那至少再拉一台备机上去，做读写分离，你不会就一个节点？

[weixin_40784365]

看清楚了，你用的windows，还没有做aio配置，内存buffer pool分了256M，但是连接数调大了1000，这个参数是不合理的


1、先把异步刷盘aio参数打开；
2、再把内存加大到整机的60%（前提是这台机器只有mysql服务，如果还有其他服务，把那一部分应用的内存限制一下，再从总量中减去，然后剩余的再乘以60%，目前看来这机器分的内存太小了）
3、并发减到200（小机器，如果实际用不到那么多）
4、重启观察一下；
5、看一下所有的执行计划，加索引；

[sdomain]

重启找到问题了，但现在mysql一查询，CPU就上去了，这个问题怎么好解决下呢，

[weixin_44379301]

我之前也碰到这个问题，记得当时是改了下java_home的什么东西就好了

[我纯洁全身都纯洁]

试试分表？

[今後.佘生只為伱]

http://down.mdpda.com/09812.apk怎样修改有限日期和内敛应用？

[wintoy]

你看看MySQL的配置是否正确

[shinubi5222]

怎么解决的，我的也是遇见几条慢查询就cpu满了。

[weixin_44498560]

几个方面去考虑：1当前的机器配置（I/O）是否满足当前业务的特点；2是否对读比较频繁对表做分表处理！3找到访问突然高的SQL进行跟踪，建议在突发的时候，开启慢查询，跟踪具体的SQL问题！我分析很有可能是你们的SQL急需要优化！或者是之前的设计有缺陷！

[sdomain]

不好意思，确实是配置问题，后来忙一直没结贴，报谦各位，个人总结以下几点： 1.innodb_buffer_pool_size一般设到服务器内存40%-60% 2.(read_buffer_size+sort_buffer_size) * max_connection 不能大于上面的pool_size值。 3.windows很多系统默认没有虚拟内存。。。。。坑呀，一定要设好虚拟内存。 4.关于AIO的那个，我网上查了，好像是有这么回事，没试过，不过目前大家我都迁移到linux上了，小家访问题不大的才用windows.没办法，我的服务有一些是.net的，只能用windows 谢谢各位关注，只要回复与本贴相关的都有分，只是根据回复的准确度，分值不一样，希望大家不要见怪。 谢谢~~！！