0

I really give up. Mysql server suddenly got working very slowly. I have mysql server 5.7.29-0ubuntu0.18.04.1 - (Ubuntu). The machine is pretty powerful, 50 cores, 300GB RAM. I did not have problems, more over before I had bigger loading to DB. Last time I have decreased and optimized queries.

But really simple query may hang for long time:

# Query_time: 12.173414  Lock_time: 0.000053 Rows_sent: 0  Rows_examined: 1
SET timestamp=1582047698;
UPDATE `account`
                  SET `field` = `field` + 1, `last_day` = `last_day` + 1 
                  WHERE `id` = 61449;

Table account consists 30000 rows.

The mysql config is:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

[mysqld]



#skip-grant-tables

bind-address=0.0.0.0

max_allowed_packet         = 2000MB

log_error = 0
general_log = 0
wait_timeout = 28800
interactive_timeout = 28800
innodb_log_buffer_size = 500M
innodb_log_file_size = 2047M

connect_timeout=360
open_files_limit = 5000
max_connections = 5000

[mysqld_safe]

Multithreaded application is run and threads perform queries to DB, especially to account table. And there are not many queries. Maybe about 30 queries per minute.

And another interesting moment: there is table session, and nothing performs queries to session table, excepts php script, when user update page. And I am only user, and there is only 1 update query to session table, but this also hangs, simple query:

# Query_time: 5.971559  Lock_time: 0.000055 Rows_sent: 0  Rows_examined: 1
SET timestamp=1582047736;
UPDATE `session` SET `data`='__flash|a:0:{}__id|i:10;__expire|i:1582049525;lastReferrer-|s:29:\"/mail/index\";lastReferrer-account|s:14:\"/account/index\";lastReferrer-mail|s:20:\"/mail/index\";', `user_id`=10, `timestamp`=1582047730, `useragent`='Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/564 (KHTML, like Gecko) Chrome/79.0.34567.130 Safari/537.36', `expire`=1582049530 WHERE `id`='fsdadfadsafdsfds';

Table session consists only 50 rows.

Probably my description doesn't full and doesn't explain the situation well. I am just confused why it may happen. As I said it worked well with bigger loading, and it must work perfect on such powerful machine.

Of course, I tried restart mysql, apache2, and whole machine...

Mysqltuner results:

https://pastebin.com/cgbAaVPD

SHOW GLOBAL STATUS; :

https://pastebin.com/cRZy0qg4

HERE IS THE SHOW FULL PROCESSLIST, and what is important, you can see it is empty, and even with empty processlist, I tried simple apache request, and it took about 10 seconds to handlle it. Exactly for mysql query, again simple mysql query.

https://prnt.sc/r4g07e

df output:

Filesystem                  1K-blocks     Used Available Use% Mounted on
udev                        198054208        0 198054208   0% /dev
tmpfs                        39617156     2100  39615056   1% /run
/dev/mapper/ubuntu--vg-root 113838276 24550448  83462088  23% /
tmpfs                       198085776        0 198085776   0% /dev/shm
tmpfs                            5120        0      5120   0% /run/lock
tmpfs                       198085776        0 198085776   0% /sys/fs/cgroup
tmpfs                        39617152        0  39617152   0% /run/user/0

sudo lshw -short -C disk output:

 H/W path             Device     Class          Description
==========================================================
/0/84/0.0.0          /dev/sda   disk           120GB Samsung SSD 850
/0/85/0.0.0          /dev/sdb   disk           2TB Samsung SSD 860
/0/86/0.0.0          /dev/sdc   disk           2TB Samsung SSD 860

ulimit -a output:

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 1547298
max locked memory       (kbytes, -l) 16384
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1617596
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 1547298
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

TOP output

enter image description here

Hibernate I think I don't need. If I understand right, hibernate means server turning off or sleep, but my server must work everytime. Also I noticed that insertions always increase mysql ram (RES in top), from what I understood it is normal behavior, and I need also solve it but it is another question. Regarding Swipe 20% - should I set vm.swappiness = 20 ? Also I have read that vm.swappiness = 0 is good idea to prevent mysql swiping at all. And regarding wa 97.5% on top, I don't know exactly, maybe it is php process with 10000 opened threads, maybe no well configured mysql. From what I know big wa value appears when problems with writing data on disk.. Also 17.565G mysql on top got because a lot of threads make insertions and it is always increasing..

  • Do you ever 'drop' tables of INNODB engine? You can find out with SHOW GLOBAL STATUS LIKE '%drop%'; If you do, the process involves cleaning up data in innodb_buffer_pool_size before it can complete. If your query happen to be UPDATE ing one row, it could be delayed. – Wilson Hauck Feb 18 at 19:46
  • 1
    @WilsonHauck, thank u a lot, your comments directed me on right way. I added to post some information.. Unfortunately I can't provide ssh login. – Mikael Feb 20 at 13:11
  • 1
    Welcome. One and only suspect is php which is taking 70% of memory (you have huge memory still...). You shall - for sure - get the actual reason under php configuration (ini files) or in your php code itself. For additional help, you shall even post the php ini (and / or) your php code pertaining to this issue. – Natarajan N Napoleon Feb 22 at 16:49
  • 1
    Is id the PRIMARY KEY? Is it Engine=InnoDB? What is the value of innodb_buffer_pool_size? Are you ALTERing that table at the same time? – Rick James Feb 23 at 19:26
  • 1
    GLOBAL STATUS is useless without VARIABLES. The status says Com_update = 0, implying that you did not try the update during the less than 2 hours of Uptime. – Rick James Feb 23 at 19:35
1

Since you have a huge amount of RAM, but MySQL is barely using any of it, and you have 500MB of data, suggest raising innodb_buffer_pool_size from 128M to at least 1G. Perhaps 30G.

Sort the top output by CPU. Something is causing a load average of 500, but it does not show in the screenshot you provided.

Most of RAM is chewed up by PHP; check its settings.

| improve this answer | |
  • yes most of RAM is chewed by PHP. There are 10k php threads. But how php settings may affect RAM consumption ? I think it can't. It depends on php code. I also use external php extensions. For now, seems, I solved problem by disabling swapping at all. Set swapping = 0, RAM is always increasing, I have to restart processes, it is anther problem, php language is not the best for my purpose, but at least queries not hanging now – Mikael Feb 23 at 20:29
  • @Mikael - If they are being launched by Apache, then check its settings. Otherwise, check PHP's "memory_limit". – Rick James Feb 23 at 21:39
  • memory_limit is unlimited and I can't change it. Cuz then application will not work because of RAM – Mikael Feb 24 at 5:20
  • @Mikael - Then change the configuration of Apache to limit the number of "children". This will cut back on the number of PHP scripts sitting there with "unlimited memory" but possible idle. – Rick James Feb 24 at 19:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.