The load does not go away after restarting mysql, why?

Question

linux

I loaded MySQL with mysqlslap:

mysqlslap --silent --host=localhost --user=myuser --password=qwerty --concurrency=1000 --iterations=1 --create-schema=superb1 --auto-generate-sql --auto-generate-sql-load-type=write --number-char-cols=50 --number-int-cols=300

I get a good load. But then there were sorties like:

mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3,intcol4....intcol299,intcol300,charcol1,charcol2,charcol3,charcol4,charcol5...charcol49,charcol50 FROM t1 ERROR : Lost connection to MySQL server during query
mysqlslap: Error when storing result: 2013 Lost connection to MySQL server during query

One more attempt

mysqlslap: Cannot run query SELECT intcol1,intcol2,intcol3...,intcol299,intcol300,charcol1,charcol2,charcol3...,charcol50 FROM t1 ERROR : MySQL server has gone away

shorter. I look through ps -aux | grep mysql and I see that mysql survives 2.5 cpu and 90 RAM

mysql      22687  2.5 90.7 3129044 915728 pts/1  Sl   19:04   0:04 /usr/sbin/mysqld --basedir=/usr --datadir=/var/lib/mysql --plugin-dir=/usr/lib64/mysql/plugin --user=mysql --log-error=/var/log/mysqld.log --pid-file=/var/lib/mysql/localhost.localdomain.pid --socket=/var/lib/mysql/mysql.sock

I turn off mysql: service mysql stop
through top it is clear that the RAM is freed up compared to what it was ...
I turn on mysql again: service mysql start
now through top it is clear that no more than 10% of RAM is free. (well, that is again mysql survives 90%)
how can this be? Did I reload it? Tried to just kill the process with mysql then run... Even just simply reboot... What should I do? How to kill load? And what gives such an indestructible load? Looked through lsof:

[[email protected] LOAD]# lsof -p 22687
COMMAND   PID  USER   FD   TYPE             DEVICE SIZE/OFF    NODE NAME
mysqld  22687 mysql  cwd    DIR              253,0     4096 1574409 /var/lib/mysql
mysqld  22687 mysql  rtd    DIR              253,0     4096       2 /
mysqld  22687 mysql  txt    REG              253,0 13760152  406200 /usr/sbin/mysqld
mysqld  22687 mysql  mem    REG              253,0    65928  131456 /lib64/libnss_files-2.12.so
mysqld  22687 mysql  mem    REG              253,0    79008  399003 /usr/lib64/liblve.so.0.9.0
mysqld  22687 mysql  mem    REG              253,0   135896  131128 /lib64/libtinfo.so.5.7
mysqld  22687 mysql  mem    REG              253,0   140192  131124 /lib64/libncurses.so.5.7
mysqld  22687 mysql  mem    REG              253,0  1066448  131159 /lib64/libglib-2.0.so.0.2600.1
mysqld  22687 mysql  mem    REG              253,0    32072  406306 /usr/lib64/libgovernor.so.1.0
mysqld  22687 mysql  mem    REG              253,0   469528  131077 /lib64/libfreebl3.so
mysqld  22687 mysql  mem    REG              253,0  1921216  131084 /lib64/libc-2.12.so
mysqld  22687 mysql  mem    REG              253,0    90880  131079 /lib64/libgcc_s-4.4.7-20120601.so.1
mysqld  22687 mysql  mem    REG              253,0   596264  131114 /lib64/libm-2.12.so
mysqld  22687 mysql  mem    REG              253,0   987096  394343 /usr/lib64/libstdc++.so.6.0.13
mysqld  22687 mysql  mem    REG              253,0    19536  131110 /lib64/libdl-2.12.so
mysqld  22687 mysql  mem    REG              253,0    40400  131088 /lib64/libcrypt-2.12.so
mysqld  22687 mysql  mem    REG              253,0    43832  131462 /lib64/librt-2.12.so
mysqld  22687 mysql  mem    REG              253,0     3944  131420 /lib64/libaio.so.1.0.1
mysqld  22687 mysql  mem    REG              253,0   142640  131108 /lib64/libpthread-2.12.so
mysqld  22687 mysql  mem    REG              253,0   154520  131090 /lib64/ld-2.12.so
mysqld  22687 mysql    0r   CHR                1,3      0t0    4158 /dev/null
mysqld  22687 mysql    1w   REG              253,0   141783 1573706 /var/log/mysqld.log
mysqld  22687 mysql    2w   REG              253,0   141783 1573706 /var/log/mysqld.log
mysqld  22687 mysql    3u  unix 0xffff880035ba4e00      0t0   51185 socket
mysqld  22687 mysql    4uW  REG              253,0 79691776 1574413 /var/lib/mysql/ibdata1
mysqld  22687 mysql    5u   REG              253,0        0 1048581  (deleted)/tmp/ibu2yAkR
mysqld  22687 mysql    6u   REG              253,0        0 1048583  (deleted)/tmp/ibcXg0dV
mysqld  22687 mysql    7w  FIFO                0,8      0t0   19750 pipe
mysqld  22687 mysql    8u   REG              253,0        0 1048584  (deleted)/tmp/ibwjnq7Y
mysqld  22687 mysql    9u   REG              253,0        0 1054053  (deleted)/tmp/ibCD9xx7
mysqld  22687 mysql   10uW  REG              253,0 50331648 1574414 /var/lib/mysql/ib_logfile0
mysqld  22687 mysql   11uW  REG              253,0 50331648 1574415 /var/lib/mysql/ib_logfile1
mysqld  22687 mysql   12uW  REG              253,0    98304 1574483 /var/lib/mysql/mysql/innodb_table_stats.ibd
mysqld  22687 mysql   13uW  REG              253,0    98304 1574485 /var/lib/mysql/mysql/innodb_index_stats.ibd
mysqld  22687 mysql   14uW  REG              253,0  9437184 1574626 /var/lib/mysql/superb1/t1.ibd
mysqld  22687 mysql   15u   REG              253,0        0 1054054  (deleted)/tmp/ibpN5Q0e
mysqld  22687 mysql   16u  IPv6              51235      0t0     TCP *:mysql (LISTEN)
mysqld  22687 mysql   17u  unix 0xffff880035ba4ac0      0t0   51236 /var/lib/mysql/mysql.sock
mysqld  22687 mysql   18u   REG              253,0     2048 1574477 /var/lib/mysql/mysql/event.MYI
mysqld  22687 mysql   19u   REG              253,0        0 1574478 /var/lib/mysql/mysql/event.MYD
mysqld  22687 mysql   20u  unix 0xffff88003320ab40      0t0   51660 /var/lib/mysql/mysql.sock

Answer 1

Why are you killing MySQL? It is normal for a database server to consume most of the memory on the machine (MySQL does not use the operating system's file cache, it has its own buffers). By the way, maybe you should start with setting the sizes of these buffers? And also from tuning other parameters of the selected storage engine (I hope you have InnoDB?).

Answer 2

Connect to MySQL
mysql -u USER -pPASS -P PORT -h HOST
and then ask him what he is doing there
show processlist