System slow after update 12.2 -> 13.0

Deleted member 67440 · Apr 23, 2021

Unfortunately I haven't used mysql for years (only mariadb).
I advise you to check with top and then m what exactly happens.
It is normal for mysqld to do many nice (and slow) things while starting the innodb tablespace.
But then it should stop using a large chunk of resources.
From your log it takes about 5 seconds

A really brutal method is to stop mysql and check with vmstat 1 100 if the disk is up.
Then you run mysql and stop apache, and check the same thing.

If the disk is active with mysql turned on and apache turned off you can have an indication on where to deepen.

Leo L. · Apr 23, 2021

Thanks fcorbelli for the heads up.

I tried another brutal method : temporarily disable mysql AND apache at the same time.
Did vmstat 1 100 and saw :

Code:

procs    memory    page                      disks     faults       cpu
r  b  w  avm  fre  flt  re  pi  po   fr   sr ad0 pa0   in   sy   cs us sy id
0  0  0 3.8G 1.4G 1.4K 113   0   0 1.9K 1.6K   0   0 1813 5.4K 8.0K  2  0 97
0  0  0 3.8G 1.4G    0   0   0   0    0  100   3   0 1094   88 2.2K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0  100   0   0 1105   68 2.2K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0  110   1   0 1114  107 2.3K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0  100   2   0 1111   68 2.2K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   89   0   0 1052   88 2.1K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0  100   0   0 1115   69 2.3K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0  100   0   0 1110   91 2.3K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0  105   0   0 1104   69 2.2K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   90   1   0 1092   88 2.2K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   90   0   0 1036   68 2.1K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0  168   99  61   0 1110   88 2.3K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0  100   0   0 1049   68 2.1K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   90   0   0 1047   89 2.1K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0  100   0   0 1040   68 2.1K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   90   0   0 1038   88 2.1K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0  100   1   0 1041   69 2.1K  0  0 100
0  0  0 3.8G 1.4G    2   0   0   0    0   90   0   0 1032  125 2.1K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   99   0   0 1094   73 2.2K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   90   0   0 1055   88 2.2K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   99   0   0 1101   68 2.2K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   99   0   0 1102   88 2.2K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   90   1   0 1081   68 2.2K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   90   0   0 1059   85 2.2K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   99   0   0 1072   68 2.2K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   90   0   0 1041   88 2.1K  0  0 100
0  0  0 3.8G 1.4G    0   0   0   0    0   90   0   0 1041   69 2.1K  0  0 100

I'm not sure how to check if these are better results or not with mysql and apache off.

[edit : is this the ad0 column we're using for measuring activity ? If yes, the vmstat here is OK because lots of "0" in that column]

--
Léo.

Deleted member 67440 · Apr 23, 2021

Now turn on mysql and see what append after say 30 seconds.
Then turn on apache

Leo L. · Apr 24, 2021

fcorbelli said:
Now turn on mysql and see what append after say 30 seconds.
Then turn on apache

For the record, that's with MySQL OFF and Apache OFF :

Code:

root@vendome # vmstat 1 100
 procs    memory    page                      disks     faults       cpu
 r  b  w  avm  fre  flt  re  pi  po   fr   sr ad0 pa0   in   sy   cs us sy id
 0  0  0 2.7G 1.5G 1.4K 114   0   0 1.9K 1.6K   0   0 1821 5.5K 8.1K  2  0 98
 0  0  0 2.7G 1.5G  544   0   0   0   88   90   0   0 1089  749 2.3K  0  0 100
 0  0  0 2.7G 1.5G  173   0   0   0  698   99   0   0 1091  395 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1036   69 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1096   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1053   73 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1106   88 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1108   68 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1100   89 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1101   69 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1096   91 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1037   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1081   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1096   92 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1040   68 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1101  260 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1091   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1089   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1084   68 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1081   91 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1107   69 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0   16   90  12   0 1113   89 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   1   0 1095   73 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1053   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1071   69 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1052   89 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1061   72 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1096   88 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1029   68 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1044   90 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1067   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   1   0 1090   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1093   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1043   88 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1095   70 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1085   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1085   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1098   91 2.3K  0  0 100
 0  0  0 2.7G 1.5G    2   0   0   0  471   90   0   0 1091   81 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1053   90 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1094   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1094  102 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1086   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1026   89 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   1   0 1087   69 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1045   88 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1092   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1072  128 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1099   69 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1102   89 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1102   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1100   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90  14   0 1064   72 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1099   89 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1099   69 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1083   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1098   68 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1048   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1090   72 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1044   88 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1069   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1094   72 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1097   89 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1081   69 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1078   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1041   68 2.1K  0  0 100
 1  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1035   69 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   81   0   0 1042   88 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1049  102 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   82   0   0 1038   73 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   92   0   0 1037   91 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1112   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1079   91 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1108   69 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1101   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1100   68 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1097   88 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1039   69 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1102   89 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1104   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1089   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1095   69 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1097   88 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1098   72 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1095   88 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1093   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1061   89 2.2K  0  0 100
 0  0  0 2.7G 1.5G  546   0   0   0   88   99   0   0 1056  752 2.2K  0  0 100
 0  0  0 2.7G 1.5G  164   0   0   0  691   90   0   0 1094  378 2.3K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1064   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1079   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1055   69 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1099   89 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1061   68 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1086   88 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1031   68 2.1K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1095   91 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1057   69 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   99   0   0 1089  105 2.2K  0  0 100
 0  0  0 2.7G 1.5G    0   0   0   0    0   90   0   0 1037   68 2.1K  0  0 100

MySQL ON and Apache OFF :

Code:

root@vendome # vmstat 1 100
 procs    memory    page                      disks     faults       cpu
 r  b  w  avm  fre  flt  re  pi  po   fr   sr ad0 pa0   in   sy   cs us sy id
 0  0  0 4.9G 816M 1.4K 114   0   0 1.9K 1.6K   0   0 1821 5.5K 8.1K  2  0 98
 0  0  0 4.9G 816M    0   0   0   0    0  280   0   0 1059  138 2.3K  0  0 100
 1  0  0 5.0G 800M 7.7K   0   0   0 1.5K  294   5   0 1094 5.7K 2.8K  1  1 98
 0  0  0 5.0G 800M 2.5K   0   0   0   56  278  32   0 1257 1.7K 2.7K  4  0 95
 0  0  0 5.0G 800M   69   0   0   0    0  309   0   0 1106  239 2.4K  0  0 100
 0  0  0 5.1G 792M 8.7K   0   0   0 3.7K  279  31   0 1157 6.5K 2.7K  2  1 97
 0  0  0 5.1G 792M    0   0   0   0    0  322   0   0 1083  159 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  291   0   0 1143  181 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  323   0   0 1150  161 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  308   0   0 1149  185 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  308   1   0 1101  159 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  310   0   0 1136  142 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  309   0   0 1122  168 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  310   0   0 1119  144 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  310   0   0 1118  173 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  339   0   0 1119  151 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  309   0   0 1113  166 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  309   0   0 1113  164 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  310   0   0 1122  143 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  309   0   0 1069  162 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  310   0   0 1087  143 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  292   0   0 1069  162 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  320   0   0 1103  140 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  291   0   0 1117  162 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  290   0   0 1061  142 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  301   0   0 1049  159 2.2K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  310   0   0 1054  142 2.2K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  310   0   0 1082  163 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  310   0   0 1106  141 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  309   0   0 1110  165 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  308   0   0 1075  148 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0   32  278  84   0 1139  170 2.5K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  310   5   0 1060  140 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  278   0   0 1058  162 2.2K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  310   2   0 1091  145 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  309   3   0 1116  164 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  310   0   0 1143  148 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  341   0   0 1153  164 2.4K  0  0 100
 0  0  0 5.1G 792M    7   0   0   0    0  279   0   0 1118  159 2.3K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  309   0   0 1134  186 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  341   0   0 1126  167 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  308   0   0 1126  146 2.4K  0  0 100
 0  0  0 5.1G 792M   30   0   0  18   18  308   0   0 1127  325 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  309   0   0 1077  141 2.3K  0  0 100
 0  0  0 5.1G 792M    4   0   0   0    0  310   0   0 1140  205 2.4K  0  0 100
 0  0  0 5.1G 792M    0   0   0   0    0  294   0   0 1096  147 2.4K  0  0 100
 0  0  0 5.1G 791M   16   0   0   0   80  319  29   0 1139  287 2.6K  0  0 100
 0  0  0 5.1G 791M    0   0   0   0    0  290   2   0 1085  132 2.3K  0  0 100
 0  0  0 5.1G 791M    0   0   0   0    0  290   0   0 1103  144 2.3K  0  0 100
 0  0  0 5.1G 791M    0   0   0   0    0  319   0   0 1060  127 2.3K  0  0 100
 0  0  0 5.1G 791M    0   0   0   0    0  290   0   0 1059  148 2.3K  0  0 100
 0  0  0 5.1G 791M    0   0   0   0    0  290   0   0 1083  124 2.3K  0  0 100
 0  0  0 5.1G 791M    0   0   0   0    0  319   0   0 1115  155 2.4K  0  0 100
 0  0  0 5.1G 789M  725   0   0   0  793  302   0   0 1154 1.1K 2.5K  0  0 100
 0  0  0 5.1G 789M    0   0   0   0    0  306   0   0 1132  146 2.4K  0  0 100
 0  0  0 5.1G 789M    0   0   0   0    0  319   0   0 1100  130 2.3K  0  0 100
 0  0  0 5.1G 789M    0   0   0   0    0  319   0   0 1069  145 2.3K  0  0 100
 0  0  0 5.1G 789M    0   0   0   0    0  290   0   0 1108  124 2.3K  0  0 100
 0  0  0 5.1G 789M    0   0   0   0    0  290   0   0 1075  148 2.3K  0  0 100
 1  0  0 5.1G 789M  525   0   0   0   88  321   0   0 1078  780 2.3K  0  0 100
 0  0  0 5.1G 789M  203   0   0   0   16  290  13   0 1157  409 2.5K  0  0 100
 0  0  0 5.1G 789M    3   0   0   0  711  319   0   0 1142  198 2.4K  0  0 100
 0  0  0 5.1G 789M    0   0   0   0    0  290   4   0 1105  124 2.3K  0  0 100
 0  0  0 5.1G 789M    0   0   0   0    0  319   0   0 1097  149 2.3K  0  0 100
 0  0  0 5.1G 789M    0   0   0   0    0  290   3   0 1114  124 2.3K  0  0 100
 0  0  0 5.1G 789M    0   0   0   0    0  290   0   0 1089  143 2.3K  0  0 100
 0  0  0 5.1G 789M    0   0   0   0    0  319   3   0 1076  122 2.2K  0  0 100
 0  0  0 5.1G 789M    0   0   0   0    0  290   0   0 1105  146 2.3K  0  0 100
 0  0  0 5.1G 789M    0   0   0   0    0  290   0   0 1092  136 2.3K  0  0 100
 0  0  0 5.0G 789M    6   0   0   0  778  319   1   0 1073  176 2.3K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290   0   0 1116  127 2.3K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290   0   0 1056  143 2.2K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  319   2   0 1124  125 2.4K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  319   0   0 1127  148 2.4K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290   0   0 1137  130 2.4K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  319   0   0 1126  149 2.4K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  319   0   0 1115  124 2.4K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0   24  290   3   0 1110  147 2.4K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  319   0   0 1108  128 2.3K  0  0 100
 0  0  0 5.0G 789M    1   0   0   0    0  319   0   0 1090  147 2.3K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290   0   0 1121  146 2.4K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290   0   0 1089  122 2.3K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  319   0   0 1086  147 2.3K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290   1   0 1069  123 2.2K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290   0   0 1082  144 2.2K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  319   0   0 1137  129 2.3K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  319   0   0 1137  144 2.3K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  319   0   0 1132  127 2.4K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290   0   0 1112  148 2.4K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290  17   0 1093  123 2.3K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290   0   0 1059  144 2.3K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  319   0   0 1115  129 2.4K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  319   0   0 1131  146 2.4K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290   0   0 1070  128 2.3K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290   0   0 1058  142 2.2K  0  0 100
 0  0  0 5.0G 789M  892   0   0   0 1.0K  319   5   0 1114  830 2.4K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  290   0   0 1079  146 2.2K  0  0 100
 0  0  0 5.0G 789M    0   0   0   0    0  319   3   0 1126  128 2.3K  0  0 100
 0  0  0 5.0G 789M    3   0   0   0  653  290   0   0 1127  166 2.3K  0  0 100
 0  0  0 5.0G 792M    2   0   0   0 1.1K  319   0   0 1100  150 2.3K  0  0 100

MySQL ON and Apache ON :

Code:

root@vendome # vmstat 1 100
 procs    memory    page                      disks     faults       cpu
 r  b  w  avm  fre  flt  re  pi  po   fr   sr ad0 pa0   in   sy   cs us sy id
 0  0  0 6.2G 374M 1.4K 114   0   0 1.9K 1.6K   0   0 1821 5.5K 8.1K  2  0 98
 2  0  0 6.2G 372M   41   0   0   0  272  416 1202   0 2899  12K  17K  4  1 96
 0  0  0 6.2G 368M   39   0   0   0  232  459 1547   0 3257  15K  20K  5  1 94
 1  0  0 6.2G 363M   33   0   0   0  232  376 1425   0 3183  14K  19K  5  1 94
 3  0  0 6.3G 341M  18K   0   0   0  14K  12K 1661   0 3465  18K  21K 10  2 88
 4  0  0 6.3G 349M 4.4K   0   0   0 7.4K 7.4K 1163   0 2841  14K  17K  7  1 93
 0  0  0 6.3G 349M   42   0   0   0  440  427 944   0 2690  10K  14K  3  1 96
 1  0  0 6.4G 350M  19K   0   0   0  21K  20K 1030   0 2829  13K  15K  7  1 91
 1  0  0 6.4G 355M 4.5K   0   0   0 5.2K 5.2K 1115   0 2867  14K  18K  9  1 90
 0  0  0 6.4G 352M   28   0   0   0  504  391 1286   0 3013  14K  18K  5  0 94
 5  0  0 6.4G 349M   28   0   0   0  512  472 913   0 2566 9.6K  14K  4  0 96
 0  0  0 6.4G 346M   20   0   0   0  544  427 1030   0 2750  11K  15K  3  1 97
 1  0  0 6.4G 342M   20   0   0   0  488  431 1170   0 2923  12K  17K  5  0 95
 0  0  0 6.4G 340M   24   0   0   0  544  427 767   0 2446 8.5K  13K  4  0 95
 2  0  0 6.4G 340M 2.1K   0   0   0 4.5K 1.8K 1373   0 3203  26K  20K  9  1 90
 2  0  0 6.4G 340M 1.3K   0   1   0 1.3K  460 1392   0 3169  22K  20K  6  2 92
 0  0  0 6.4G 343M   82   0   0   0 3.8K  424 977   0 4372  22K  29K  3  1 96
 0  0  0 6.4G 343M   10   0   0   0 1.8K  419 890   0 2463 5.6K  11K  1  0 98
 1  0  0 6.4G 409M  49K 1235   0   0  67K 101K 1020   0 2624 7.9K  15K 20  2 77
 0  0  0 6.4G 406M 2.1K   0   0   0  679  429 1158   0 2884  18K  20K 24  1 75
 1  0  0 6.4G 403M   15   0   0   0  264  428 1210   0 3043  13K  17K  3  1 96
 4  0  0 6.4G 400M   11   0   0   0  648  435 910   0 2718 8.9K  14K  3  1 96
 0  0  0 6.4G 399M   21   0   0   0  264  435 574   0 2280 6.4K  10K  3  1 96
 0  0  0 6.4G 398M   11   0   0   0 1.0K  436 898   0 2413 8.3K  13K  3  0 97
 0  0  0 6.4G 397M   19   0   0   0  432  432 569   0 2289 6.4K  10K  2  0 97
 0  0  0 6.4G 396M  165   0   6   0  568  439 582   0 2116 5.8K 9.2K  4  0 96
 1  0  0 6.4G 409M 3.6K   0   2   0 7.4K  423 786   0 2986  15K  14K  6  0 93
 0  0  0 6.4G 409M   15   0   1   0  416  420 940   0 2690 8.6K  13K  3  0 97
 0  0  0 6.4G 417M 1.4K   0   0   0 5.2K  416 1375   0 3315  16K  19K  5  1 93
 0  0  0 6.5G 343M  20K   0   0   0 1.5K  431 910   0 2692  14K  15K 10  2 89
 0  0  0 6.5G 339M  200   0   0   0  128  379 1355   0 3146  15K  19K  5  1 94
 0  0  0 6.5G 339M    9  40   0   0 1.3K 1.7K 1335   0 3105  13K  17K  5  1 94
 2  0  0 6.5G 341M   14   0   0   0 2.9K 3.3K 1280   0 3091  12K  17K  5  1 94
 0  0  0 6.5G 341M   71   0   0   0  520  420 1334   0 3060  12K  17K  4  1 95
 0  0  0 6.5G 339M 1.0K   0   0   0  291  514 1031   0 2802  13K  15K  4  1 96
 0  0  0 6.5G 339M  268   0   0   0 3.5K 1.7K 863   0 2548 8.1K  12K  3  0 97
 1  0  0 6.3G 358M  269   0   0   0 6.1K  851 1099   0 2824  11K  15K  3  0 96
 1  0  0 6.3G 365M  306   0   0   0 2.4K  451 840   0 2623 7.8K  12K 13  0 87
 0  0  0 6.3G 372M  168   0   0   0 3.3K  403 943   0 2697 7.6K  12K 12  0 88
 0  0  0 6.3G 372M   22   0   0   0  240  405 1456   0 3385  11K  17K  3  1 96
 0  0  0 6.3G 375M  212   0   1   0 1.7K  407 321   0 1959 3.8K 6.9K  4  0 96
 0  0  0 6.3G 381M 4.6K   0   0   0 3.7K  399  73   0 1237 5.4K 3.6K  3  0 97
 0  0  0 6.3G 381M  374   0   0   0  827  392  62   0 1206  559 2.9K  0  0 99
 0  0  0 6.2G 386M  164   0   0   0 4.3K  432 365   0 1526 1.0K 4.0K  0  0 99
 0  0  0 6.2G 386M  318   0   0   0 1.7K  390 296   0 1376  750 3.5K  0  0 99
 0  0  0 6.2G 386M    0   0   0   0 1.3K  429 323   0 1484  674 3.8K  0  0 100
 0  0  0 6.2G 387M    0   0   0   0 2.0K  390 494   0 1618  747 4.4K  0  0 100
 1  0  0 6.2G 390M 2.7K   0   0   0 4.1K  388 383   0 1466 3.2K 4.0K  1  0 99
 1  0  0 6.2G 344M  19K   0   0   0 8.1K 5.6K 893   0 2511 8.6K  11K  8  1 91
 0  0  0 6.2G 344M    4   0   0   0  152  397 841   0 2728 7.2K  12K  2  1 98
 1  0  0 6.2G 344M   45   0   0   0  352  401 928   0 2671 7.4K  12K  2  1 97
 0  0  0 6.2G 342M    0   0   0   0  112  400 1462   0 3344  11K  17K  3  1 97
 0  0  0 6.2G 341M    0   0   0   0  104  408 822   0 2707 7.3K  12K  1  0 98
 1  0  0 6.2G 339M    0   0   0   0  112  411 909   0 2735 7.3K  12K  2  1 97
 0  0  0 6.2G 339M    0   0   0   0  972 1.4K 616   0 2513 6.0K  11K  1  0 98
 1  0  0 6.2G 341M   66   0   0   0 2.1K 2.5K 474   0 1989 4.5K 7.9K  8  0 92
 0  0  0 6.2G 341M 1.5K   0   0   0  618  403 1073   0 2762  10K  15K  7  1 93
 1  0  0 6.2G 341M    8   0   0   0  104  402 1049   0 2919 8.3K  14K  2  0 97
 0  0  0 6.2G 340M    0   0   0   0   72  392 1188   0 3092 9.7K  15K  3  1 97
 0  0  0 6.2G 339M  406   0   0   0 1.5K  951 1254   0 3113  13K  16K  4  1 96
 1  0  0 6.2G 340M    0   0   0   0 2.0K 2.5K 1256   0 3117 9.5K  15K  3  0 97
 0  0  0 6.2G 347M    4   0   0   0 2.2K  525 870   0 2772 7.5K  13K  2  0 98
 0  0  0 6.2G 347M    0   0   0   0   80  407  36   0 1206  656 2.9K  0  0 100
 0  0  0 6.2G 347M    0   0   0   0   72  408  34   0 1178  331 2.7K  0  0 100
 0  0  0 6.2G 347M    0   0   0   0 1.2K  368 383   0 1479  706 3.9K  0  0 100
 0  0  0 6.2G 347M    0   0   0   0 1.3K  444 358   0 1538  612 3.9K  0  0 100
 0  0  0 6.2G 347M    0   0   0   0 1.3K  390 368   0 1550  660 4.0K  0  0 100
 0  0  0 6.2G 347M    0   0   0   0 1.4K  429 396   0 1498  579 3.9K  0  0 100
 0  0  0 6.2G 347M    0   0   0   0 1.7K  390 441   0 1611  657 4.3K  0  0 100
 0  0  0 6.2G 347M    0   0   0   0 1.4K  390 366   0 1444  587 3.8K  0  0 99
 0  0  0 6.2G 347M    0   0   0   0 1.1K  429 302   0 1402  557 3.6K  0  0 100
 0  0  0 6.2G 347M    0   0   0   0 1.1K  390 256   0 1353  454 3.4K  0  0 100
 0  0  0 6.2G 347M    0   0   0   0  712  429 166   0 1311  388 3.1K  0  0 100
 0  0  0 6.2G 345M 2.8K   0   0   0 1.1K  390 149   0 1374  17K 4.4K  5  1 95
 0  0  0 6.2G 345M    0   0   0   0  304  429  88   0 1222  291 2.8K  0  0 100
 0  0  0 6.2G 345M    0   0   0   0  256  429  65   0 1185  222 2.6K  0  0 100
 0  0  0 6.2G 345M    0   0   0   0  200  390  48   0 1123  215 2.5K  0  0 100
 0  0  0 6.2G 345M    0   0   0   0  168  390  54   0 1141  202 2.5K  0  0 100
 0  0  0 6.2G 345M    0   0   0   0   32  429  11   0 1125  193 2.4K  0  0 100
 0  0  0 6.2G 345M   33   0   0   0    0  390   2   0 1107 2.9K 2.5K  1  0 99
 0  0  0 6.2G 345M    0   0   0   0    0  390   0   0 1091  255 2.3K  0  0 99
 0  0  0 6.2G 345M    0   0   0   0  224  429  10   0 1127  174 2.4K  0  0 100
 0  0  0 6.2G 345M    0   0   0   0    0  390   0   0 1104  146 2.4K  0  0 100
 0  0  0 6.2G 345M    0   0   0   0    0  429   0   0 1107  167 2.4K  0  0 100
 0  0  0 6.2G 345M    0   0   0   0    0  429   0   0 1106  152 2.4K  0  0 100
 0  0  0 6.2G 345M    0   0   0   0    0  390   1   0 1086  168 2.3K  0  0 100
 0  0  0 6.2G 345M    0   0   0   0    0  390   0   0 1090  148 2.3K  0  0 100
 0  0  0 6.2G 345M  529   0   0   0   88  429   0   0 1131  829 2.4K  0  0 100
 0  0  0 6.2G 346M  212   0   0   0 1.2K  429   0   0 1159 2.4K 2.7K  1  0 99
 0  0  0 6.2G 346M    0   0   0   0    0  390   0   0 1063  168 2.3K  0  0 100
 0  0  0 6.2G 346M    0   0   0   0    0  429   2   0 1115  147 2.3K  0  0 100
 0  0  0 6.2G 346M    0   0   0   0    0  390   0   0 1129  167 2.4K  0  0 100
 0  0  0 6.2G 346M    0   0   0   0    0  429   1   0 1126  151 2.4K  0  0 100
 0  0  0 6.2G 346M    0   0   0   0    0  390   0   0 1070  170 2.3K  0  0 100
 0  0  0 6.2G 346M    0   0   0   0    0  429   0   0 1129  150 2.4K  0  0 100
 0  0  0 6.2G 346M    0   0   0   0    0  390   0   0 1115  164 2.4K  0  0 100
 0  0  0 6.2G 346M    0   0   0   0    0  429   0   0 1111  146 2.4K  0  0 100
 0  0  0 6.2G 346M    0   0   0   0   64  429  88   0 1230  165 2.6K  0  0 100
 0  0  0 6.2G 346M    0   0   0   0    0  390   0   0 1144  154 2.4K  0  0 100
 0  0  0 6.2G 346M    0   0   0   0    0  429   0   0 1142  176 2.4K  0  0 100

Hope this helps.

VladiBG · Apr 24, 2021

Do you see the high disk activity when the apache and MySQL are both on? You can check what inodes are accessed by each process using truss(1) . Start both apache and MySQL then find they process ID number and trace the system calls one by one using truss -p PID_NUMBER where PID_NUMBER is the a PID of the apache (use ps or top to see what is the PID number of the process) to see what is causing this disk activity (to interrupt use ctrl+c) then look for stat,open,unlink calls to the file system. It may be some php script that is running on the disk like a backup, or some indexing.

Note:
You can try only with Apache - ON ; MySQL - OFF to see if it's the same.

covacat · Apr 24, 2021

do something like this

mysqladmin -uroot -p status
Enter password:
Uptime: 6398049 Threads: 26 Questions: 4142755259 Slow queries: 16466 Opens: 96805760 Flush tables: 1 Open tables: 64 Queries per second avg: 647.502

Leo L. · Apr 24, 2021

VladiBG said:
Do you see the high disk activity when the apache and MySQL are both on?

I'm not sure : what information should I look to ?
The "ad0" column ?

VladiBG said:
You can check what inodes are accessed by each process using truss(1) . Start both apache and MySQL then find they process ID number and trace the system calls one by one using truss -p PID_NUMBER where PID_NUMBER is the a PID of the apache (use ps or top to see what is the PID number of the process) to see what is causing this disk activity (to interrupt use ctrl+c) then look for stat,open,unlink calls to the file system.

Thanks, here are the results :

Code:

root@vendome # /usr/local/etc/rc.d/apache24 status
apache24 is running as pid 38750.
root@vendome # /usr/local/etc/rc.d/mysql-server status
mysql is running as pid 24506.

For Apache :

Code:

root@vendome:/ # truss -p 38750
wait4(-1,{ EXITED,val=0 },WNOHANG|WUNTRACED,0x0) = 0 (0x0)
getrusage(RUSAGE_SELF,{ u=0.174990,s=0.160181,in=248,out=0 }) = 0 (0x0)
getrusage(RUSAGE_CHILDREN,{ u=161.608894,s=8.420853,in=3688,out=0 }) = 0 (0x0)
select(0,0x0,0x0,0x0,{ 1.000000 })         = 0 (0x0)
wait4(-1,{ EXITED,val=0 },WNOHANG|WUNTRACED,0x0) = 0 (0x0)
getrusage(RUSAGE_SELF,{ u=0.175009,s=0.160181,in=248,out=0 }) = 0 (0x0)
getrusage(RUSAGE_CHILDREN,{ u=161.608894,s=8.420853,in=3688,out=0 }) = 0 (0x0)
select(0,0x0,0x0,0x0,{ 1.000000 })         = 0 (0x0)
wait4(-1,{ EXITED,val=0 },WNOHANG|WUNTRACED,0x0) = 0 (0x0)
getrusage(RUSAGE_SELF,{ u=0.175027,s=0.160181,in=248,out=0 }) = 0 (0x0)
getrusage(RUSAGE_CHILDREN,{ u=161.608894,s=8.420853,in=3688,out=0 }) = 0 (0x0)
select(0,0x0,0x0,0x0,{ 1.000000 })         = 0 (0x0)
wait4(-1,{ EXITED,val=0 },WNOHANG|WUNTRACED,0x0) = 0 (0x0)
getrusage(RUSAGE_SELF,{ u=0.175044,s=0.160181,in=248,out=0 }) = 0 (0x0)
getrusage(RUSAGE_CHILDREN,{ u=161.608894,s=8.420853,in=3688,out=0 }) = 0 (0x0)
select(0,0x0,0x0,0x0,{ 1.000000 })         = 0 (0x0)
wait4(-1,{ EXITED,val=0 },WNOHANG|WUNTRACED,0x0) = 0 (0x0)
getrusage(RUSAGE_SELF,{ u=0.175062,s=0.160181,in=248,out=0 }) = 0 (0x0)
getrusage(RUSAGE_CHILDREN,{ u=161.608894,s=8.420853,in=3688,out=0 }) = 0 (0x0)
select(0,0x0,0x0,0x0,{ 1.000000 })         = 0 (0x0)
wait4(-1,{ EXITED,val=0 },WNOHANG|WUNTRACED,0x0) = 0 (0x0)
getrusage(RUSAGE_SELF,{ u=0.175078,s=0.160181,in=248,out=0 }) = 0 (0x0)
getrusage(RUSAGE_CHILDREN,{ u=161.608894,s=8.420853,in=3688,out=0 }) = 0 (0x0)
select(0,0x0,0x0,0x0,{ 1.000000 })         = 0 (0x0)
wait4(-1,{ EXITED,val=0 },WNOHANG|WUNTRACED,0x0) = 0 (0x0)
getrusage(RUSAGE_SELF,{ u=0.175096,s=0.160181,in=248,out=0 }) = 0 (0x0)
getrusage(RUSAGE_CHILDREN,{ u=161.608894,s=8.420853,in=3688,out=0 }) = 0 (0x0)
select(0,0x0,0x0,0x0,{ 1.000000 })         = 0 (0x0)
wait4(-1,{ EXITED,val=0 },WNOHANG|WUNTRACED,0x0) = 0 (0x0)
getrusage(RUSAGE_SELF,{ u=0.175111,s=0.160181,in=248,out=0 }) = 0 (0x0)
getrusage(RUSAGE_CHILDREN,{ u=161.608894,s=8.420853,in=3688,out=0 }) = 0 (0x0)
select(0,0x0,0x0,0x0,{ 1.000000 })         = 0 (0x0)
wait4(-1,{ EXITED,val=0 },WNOHANG|WUNTRACED,0x0) = 0 (0x0)
getrusage(RUSAGE_SELF,{ u=0.175126,s=0.160181,in=248,out=0 }) = 0 (0x0)
getrusage(RUSAGE_CHILDREN,{ u=161.608894,s=8.420853,in=3688,out=0 }) = 0 (0x0)

For MySQL (random crop of original output) :

Code:

write(45,"f9fdcd36cddd6e22e2e48d577c87e12f"...,2920) = 2920 (0xb68)
_umtx_op(0x8037b02c0,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc5e0c48) ERR#60 'Operation timed out'
_umtx_op(0x8037b0278,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdcbe3c58) ERR#60 'Operation timed out'
_umtx_op(0x8037b02a8,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc7e1c58) ERR#60 'Operation timed out'
fdatasync(45)                     = 0 (0x0)
sendto(112,"0\0\0\^A\0\^A\0\^B\0\0\0(Rows ma"...,52,0,NULL,0) = 52 (0x34)
recvfrom(112,0x883f08b60,4,0,0x0,0x0)         ERR#35 'Resource temporarily unavailable'
kevent(113,{ 112,EVFILT_READ,EV_ADD|EV_ENABLE|EV_CLEAR|EV_DISPATCH,0,0,0x0 0xfffffffffacefeed,EVFILT_USER,EV_ADD|EV_ENABLE|EV_CLEAR|EV_DISPATCH,0,0,0x0 },2,{ 112,EVFILT_READ,EV_CLEAR|EV_DISPATCH,0,0x198,0x0 },2,{ 28800.000000000 }) = 1 (0x1)
_umtx_op(0x816a34bc0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
_umtx_op(0x8037b02c0,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc5e0c48) = 0 (0x0)
pwrite(5,"\M-(@l-\0K\0K\0&\M-.\M-u\0\0\0\0"...,8192,0x1008000) = 8192 (0x2000)
fsync(5)                     = 0 (0x0)
_umtx_op(0x8037b0278,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdcbe3c58) ERR#60 'Operation timed out'
_umtx_op(0x83ce63dc0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
fsync(5)                     = 0 (0x0)
_umtx_op(0x8037b04b8,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffda7dc918) = 0 (0x0)
_umtx_op(0x83ce63dc0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
lseek(114,0x0,SEEK_SET)                 = 0 (0x0)
write(45,"\M-s\M-Z\M^C`!\^A\0\0\0O\0\0\0"...,5272) = 5272 (0x1498)
read(114,""2021-04-23 18:52:54.592412","[r"...,4096) = 4096 (0x1000)
write(45,"dbB4MlcEeY/Aa0bDHFfbF2Mm1HD5Ej9W"...,4096) = 4096 (0x1000)
write(117,""2021-04-24 10:46:43.765944","ne"...,486) = 486 (0x1e6)
write(45,"\nNPCqzsotdgb7tS8VGM1E96Poh2y8mX"...,268) = 268 (0x10c)
fdatasync(45)                     = 0 (0x0)
sendto(104,"0\0\0\^A\0\^A\0\^B\0\0\0(Rows ma"...,52,0,NULL,0) = 52 (0x34)
recvfrom(104,0x88218ada0,4,0,0x0,0x0)         ERR#35 'Resource temporarily unavailable'
_umtx_op(0x816a35ac0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
_umtx_op(0x8037b02c0,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc5e0c48) = 0 (0x0)
kevent(105,{ 104,EVFILT_READ,EV_ADD|EV_ENABLE|EV_CLEAR|EV_DISPATCH,0,0,0x0 0xfffffffffacefeed,EVFILT_USER,EV_ADD|EV_ENABLE|EV_CLEAR|EV_DISPATCH,0,0,0x0 },2,{ 104,EVFILT_READ,EV_CLEAR|EV_DISPATCH,0,0x198,0x0 },2,{ 28800.000000000 }) = 1 (0x1)
_umtx_op(0x8037b0278,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdcbe3c58) ERR#60 'Operation timed out'
fsync(5)                     = 0 (0x0)
pwrite(5,"\M-(@l5\^A\M-q\^A?\0&\M-.\M-u1dy"...,512,0x1009000) = 512 (0x200)
_umtx_op(0x8037b0278,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdcbe3c58) = 0 (0x0)
_umtx_op(0x83ce63dc0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
fsync(5)                     = 0 (0x0)
_umtx_op(0x83ce63dc0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
_umtx_op(0x8037b0500,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffda704918) = 0 (0x0)
lseek(107,0x0,SEEK_SET)                 = 0 (0x0)
read(107,""2021-04-23 18:52:54.592412","[r"...,4096) = 4096 (0x1000)
write(45,"\M-s\M-Z\M^C`!\^A\0\0\0O\0\0\0["...,7924) = 7924 (0x1ef4)
write(117,""2021-04-24 10:46:43.769223","ne"...,485) = 485 (0x1e5)
write(45,"536bd3457a662767e8515be78dd41480"...,1712) = 1712 (0x6b0)
fdatasync(45)                     = 0 (0x0)
sendto(112,"0\0\0\^A\0\^A\0\^B\0\0\0(Rows ma"...,52,0,NULL,0) = 52 (0x34)
recvfrom(112,0x883f08b60,4,0,0x0,0x0)         ERR#35 'Resource temporarily unavailable'
kevent(113,{ 112,EVFILT_READ,EV_ADD|EV_ENABLE|EV_CLEAR|EV_DISPATCH,0,0,0x0 0xfffffffffacefeed,EVFILT_USER,EV_ADD|EV_ENABLE|EV_CLEAR|EV_DISPATCH,0,0,0x0 },2,{ 112,EVFILT_READ,EV_CLEAR|EV_DISPATCH,0,0x198,0x0 },2,{ 28800.000000000 }) = 1 (0x1)
_umtx_op(0x8037b02a8,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc7e1c58) ERR#60 'Operation timed out'
_umtx_op(0x8037b02c0,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc5e0c48) ERR#60 'Operation timed out'
recvfrom(112,"\M^T\^A\0\0",4,0,NULL,0x0)     = 4 (0x4)
_umtx_op(0x8037b0278,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdcbe3c58) ERR#60 'Operation timed out'
recvfrom(112,"\^CUPDATE `oc_authtoken` SET `pa"...,404,0,NULL,0x0) = 404 (0x194)
pwrite(5,"\M-(@l5\^B\0\^A?\0&\M-.\M-u1dyEb"...,1024,0x1009000) = 1024 (0x400)
_umtx_op(0x88ad5b7c0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
_umtx_op(0x8037b0290,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc9e2c68) = 0 (0x0)
_umtx_op(0x8037b02a8,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc7e1c58) = 0 (0x0)
_umtx_op(0x88ad5b7c0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
fsync(5)                     = 0 (0x0)
_umtx_op(0x83ce63dc0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
_umtx_op(0x8037b0278,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdcbe3c58) = 0 (0x0)
pwrite(5,"\M-(@l7\0\^?\0\^?\0&\M-.\M-u\0\a"...,512,0x1009400) = 512 (0x200)
lseek(114,0x0,SEEK_SET)                 = 0 (0x0)
_umtx_op(0x8037b0290,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc9e2c68) = 0 (0x0)
_umtx_op(0x88ad5b7c0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
read(114,""2021-04-23 18:52:54.592412","[r"...,4096) = 4096 (0x1000)
fsync(5)                     = 0 (0x0)
write(117,""2021-04-24 10:46:43.771509","ne"...,486) = 486 (0x1e6)
fsync(5)                     = 0 (0x0)
_umtx_op(0x8037b03e0,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdb302c88) ERR#60 'Operation timed out'
_umtx_op(0x83ce63dc0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
_umtx_op(0x8037b03f8,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x0,0x0) = 0 (0x0)
_umtx_op(0x8037b0278,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdcbe3c58) ERR#60 'Operation timed out'
_umtx_op(0x81603a5c0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
pwrite(5,"\M-(@l=\^B\0\0\0\0&\M-.\M-u\M^S"...,8192,0x100a000) = 8192 (0x2000)
_umtx_op(0x8037b03f8,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x0,0x0) = 0 (0x0)
_umtx_op(0x81603a5c0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
_umtx_op(0x8037b02a8,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc7e1c58) = 0 (0x0)
_umtx_op(0x81603a5c0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
_umtx_op(0x8037b03f8,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x0,0x0) = 0 (0x0)
fsync(5)                     = 0 (0x0)
_umtx_op(0x88ad5b7c0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
_umtx_op(0x8037b04b8,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffda7dc918) ERR#60 'Operation timed out'
_umtx_op(0x8037b0278,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdcbe3c58) = 0 (0x0)
_umtx_op(0x83ce63dc0,UMTX_OP_NWAKE_PRIVATE,0x1,0x0,0x0) = 0 (0x0)
write(45,"\M-s\M-Z\M^C`!\^A\0\0\0O\0\0\0"...,6480) = 6480 (0x1950)
write(45,"2bd0e88f96a3d4b03f41447a50d30a8f"...,3156) = 3156 (0xc54)
fdatasync(45)                     = 0 (0x0)
sendto(104,"0\0\0\^A\0\^A\0\^B\0\0\0(Rows ma"...,52,0,NULL,0) = 52 (0x34)
recvfrom(104,0x88218ada0,4,0,0x0,0x0)         ERR#35 'Resource temporarily unavailable'
kevent(105,{ 104,EVFILT_READ,EV_ADD|EV_ENABLE|EV_CLEAR|EV_DISPATCH,0,0,0x0 0xfffffffffacefeed,EVFILT_USER,EV_ADD|EV_ENABLE|EV_CLEAR|EV_DISPATCH,0,0,0x0 },2,{ 104,EVFILT_READ,EV_CLEAR|EV_DISPATCH,0,0x198,0x0 },2,{ 28800.000000000 }) = 1 (0x1)
_umtx_op(0x8037b02a8,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc7e1c58) ERR#60 'Operation timed out'
_umtx_op(0x8037b0278,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdcbe3c58) ERR#60 'Operation timed out'
read(114,""2021-04-23 18:52:54.592412","[r"...,4096) = 4096 (0x1000)
write(117,""2021-04-24 10:46:43.810128","ne"...,486) = 486 (0x1e6)
write(45,"7aa2c7453308af8fe2c431ccaa76e14d"...,2420) = 2420 (0x974)
fdatasync(45)                     = 0 (0x0)
sendto(104,"0\0\0\^A\0\^A\0\^B\0\0\0(Rows ma"...,52,0,NULL,0) = 52 (0x34)
recvfrom(104,0x88218ada0,4,0,0x0,0x0)         ERR#35 'Resource temporarily unavailable'
kevent(105,{ 104,EVFILT_READ,EV_ADD|EV_ENABLE|EV_CLEAR|EV_DISPATCH,0,0,0x0 0xfffffffffacefeed,EVFILT_USER,EV_ADD|EV_ENABLE|EV_CLEAR|EV_DISPATCH,0,0,0x0 },2,{ 104,EVFILT_READ,EV_CLEAR|EV_DISPATCH,0,0x198,0x0 },2,{ 28800.000000000 }) = 1 (0x1)
recvfrom(104,"\M^T\^A\0\0",4,0,NULL,0x0)     = 4 (0x4)
_umtx_op(0x8037b02c0,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc5e0c48) ERR#60 'Operation timed out'
_umtx_op(0x8037b0278,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdcbe3c58) ERR#60 'Operation timed out'
_umtx_op(0x8037b02a8,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc7e1c58) ERR#60 'Operation timed out'
_umtx_op(0x8037b0290,UMTX_OP_WAIT_UINT_PRIVATE,0x0,0x18,0x7fffdc9e2c68) ERR#60 'Operation timed out'

Strangely (or not ?), after each truss command on MySQL only, it changes PID after each interruption.
Is it normal that a process restarts after truss ?

VladiBG said:
It may be some php script that is running on the disk like a backup, or some indexing.

Is there a way to track down what's heavily ask for such resources from MySQL ?
I'm sorry to ask you so much questions here, I'm not a *BSD newbie but I'm lost when it comes to detailed system monitoring/debugging.

VladiBG said:
Note:
You can try only with Apache - ON ; MySQL - OFF to see if it's the same.

You're absolutely right, I totally forgot about it.

Code:

root@vendome:/ # vmstat 1 100
 procs    memory    page                      disks     faults       cpu
 r  b  w  avm  fre  flt  re  pi  po   fr   sr ad0 pa0   in   sy   cs us sy id
 0  0  0 4.4G 981M 1.4K 113   0   0 1.9K 1.6K   0   0 1820 5.5K 8.1K  2  0 98
 0  0  0 4.4G 985M    2   0   0   0 1.1K  198   0   0 1046  109 2.1K  0  0 100
 0  0  0 4.4G 985M    0   0   0   0    0  190   0   0 1043   92 2.1K  0  0 100
 0  0  0 4.4G 985M    0   0   0   0    0  190   1   0 1030   73 2.1K  0  0 100
 0  0  0 4.4G 985M    0   0   0   0    0  190   2   0 1038   93 2.1K  0  0 100
 0  0  0 4.4G 991M    9   0   0   0 1.5K  189   0   0 1052  604 2.2K  0  0 100
 0  0  0 4.4G 991M    0   0   0   0    0  188   3   0 1034   94 2.1K  0  0 100
 0  0  0 4.4G 997M    1   0   0   0 1.4K  186   0   0 1034  107 2.1K  0  0 100
 0  0  0 4.4G 997M    0   0   0   0    8  190   1   0 1040   92 2.1K  0  0 100
 0  0  0 4.4G 997M    0   0   0   0    0  191   1   0 1053   72 2.1K  0  0 100
 0  0  0 4.4G 997M    0   0   0   0    0  171   0   0 1052   94 2.1K  0  0 100
 0  0  0 4.4G 997M    0   0   0   0    0  190   0   0 1053   73 2.1K  0  0 100
 0  0  0 4.4G 997M    0   0   0   0    0  171   1   0 1052   92 2.1K  0  0 100
 0  0  0 4.4G 997M    0   0   0   0    0  180   0   0 1041   72 2.1K  0  0 100
 1  0  0 4.4G 997M    0   0   0   0    0  180   0   0 1044   76 2.1K  0  0 100
 0  0  0 4.4G 997M    0   0   0   0    0  180   0   0 1039   90 2.1K  0  0 100
 0  0  0 4.4G 997M    1   0   0   0    0  189   0   0 1040   74 2.1K  0  0 100
 0  0  0 4.4G 999M    1   0   0   0  512  184   0   0 1052  976 2.2K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  180   1   0 1026   92 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  180   0   0 1025   72 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0  208  180  41   0 1066   94 2.2K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  180   0   0 1023   76 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  180   2   0 1022   92 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  180   0   0 1030   72 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  180   0   0 1036   93 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  189   1   0 1048   72 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  189   0   0 1052   94 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  171   0   0 1053   73 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  190   0   0 1050   92 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  170   0   0 1053   72 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  190   0   0 1058   94 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  171   0   0 1039   73 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  190   1   0 1034   92 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  171   0   0 1035   68 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  190   1   0 1035   95 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  172   1   0 1041   75 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  190   0   0 1037   94 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  171   0   0 1046   73 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  190   3   0 1054   92 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  171   0   0 1055   72 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  192   0   0 1054   94 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  189   0   0 1048   73 2.1K  0  0 100
 0  0  0 4.4G 999M    0   0   0   0    0  171   0   0 1046   92 2.1K  0  0 100
 0  0  0 4.4G 994M 8.3K   0   0   0 1.2K  194   5   0 1147 5.1K 2.8K  1  1 98
 0  0  0 4.4G 994M   32   0   0   0    8  180   1   0 1069  176 2.2K  0  0 100
 0  0  0 4.5G 985M 9.6K   0   0   0 3.8K  194  93   0 1222 8.0K 3.0K  3  1 96
 0  0  0 4.5G 997M    3   0   0   0 5.0K  203   2   0 1170 1.4K 2.5K  1  0 99
 0  0  0 4.5G 997M    0   0   0   0    0  180   0   0 1106  180 2.3K  0  0 100
 0  0  0 4.5G 997M    0   0   0   0    0  198   0   0 1104  119 2.3K  0  0 100
 2  0  0 4.5G 994M 1.5K   0   0   0  162  180   0   0 1055 2.0K 2.1K  0  0 99
 0  0  0 4.5G 994M  193   0   0   0   80  200  69   0 1174  219 2.4K  0  0 100
 0  0  0 4.5G 996M   14   0   0   0 1.5K  200   0   0 1112  712 2.3K  0  0 100
 0  0  0 4.5G 996M    0   0   0   0    0  180   0   0 1100  191 2.2K  0  0 100
 0  0  0 4.5G 996M   36   0   0   4    4  198   0   0 1063  137 2.2K  0  0 100
 0  0  0 4.5G 996M   71   0   0   0   24  180  30   0 1170  521 2.5K  0  0 100
 0  0  0 4.5G 994M 1.5K   0   0   0   86  201   3   0 1141 1.3K 2.4K  1  0 99
 1  0  0 4.5G 994M  658   0   0   0   52  182   2   0 1103  751 2.3K  1  0 99
 0  0  0 4.5G 994M  529   0   0   0   42  182   2   0 1098  562 2.3K  1  0 99
 0  0  0 4.5G 994M  940   0   0   0   56  202   2   0 1127  733 2.3K  1  0 99
 0  0  0 4.5G 994M    0   0   0   0    8  199   1   0 1094   76 2.3K  0  0 100
 0  0  0 4.5G 994M    0   0   0   0    0  181   0   0 1064   94 2.2K  0  0 100
 0  0  0 4.5G 994M    0   0   0   0    0  201   0   0 1092   94 2.3K  0  0 100
 0  0  0 4.5G 994M    0   0   0   0    0  181   0   0 1035   73 2.1K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0 2.5K  198   0   0 1105 1.0K 2.3K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  180   0   0 1086   72 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  198   0   0 1033   93 2.1K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  180   0   0 1091   72 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  198   0   0 1088   93 2.3K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  198   0   0 1098   87 2.3K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  180   4   0 1111   97 2.3K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  198   0   0 1068   76 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  180   0   0 1046   92 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  198   0   0 1097   80 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  189   0   0 1103   98 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  189   5   0 1109   72 2.2K  0  0 100
 0  0  0 4.5G 1.0G    2   0   0   0    0  190   0   0 1120  502 2.3K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  190   2   0 1046  171 2.1K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  190   1   0 1097   96 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  190   0   0 1094   77 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0   56  190  51   0 1154   94 2.3K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  190   0   0 1097   72 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  190   1   0 1105   93 2.3K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  209   0   0 1106   73 2.3K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  171   0   0 1091   95 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  190   2   0 1077   74 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  190   0   0 1111   92 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  208   0   0 1105   73 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  183   1   0 1113   96 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  180   0   0 1065   75 2.2K  0  0 100
 0  0  0 4.5G 1.0G 5.4K   0   0   0  892  203   7   0 1180 2.6K 2.8K  2  0 98
 0  0  0 4.5G 1.0G 6.9K   0   0   0 3.4K  189  72   0 1220 4.2K 2.8K  2  0 98
 0  0  0 4.5G 1.0G    1   0   0   0   10  190  16   0 1134  435 2.4K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0   20  180   2   0 1096  194 2.3K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  198   0   0 1091   93 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  198   0   0 1095   85 2.3K  0  0 100
 0  0  0 4.5G 1.0G    3   0   0   0  511  180   0   0 1097  126 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  198   0   0 1106   93 2.3K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  198   0   0 1107   94 2.3K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  180   0   0 1063   93 2.2K  0  0 100
 0  0  0 4.5G 1.0G    0   0   0   0    0  198   3   0 1102   72 2.3K  0  0 100

--
Léo.

Leo L. · Apr 24, 2021

covacat said:
do something like this

mysqladmin -uroot -p status
Enter password:
Uptime: 6398049 Threads: 26 Questions: 4142755259 Slow queries: 16466 Opens: 96805760 Flush tables: 1 Open tables: 64 Queries per second avg: 647.502

Here is what I get :

Code:

root@vendome # mysqladmin -uroot -p status
Uptime: 11  Threads: 3  Questions: 112  Slow queries: 1  Opens: 145  Flush tables: 3  Open tables: 64  Queries per second avg: 10.181

covacat · Apr 24, 2021

Leo L. said:

Here is what I get :

Code:

root@vendome # mysqladmin -uroot -p status
Uptime: 11  Threads: 3  Questions: 112  Slow queries: 1  Opens: 145  Flush tables: 3  Open tables: 64  Queries per second avg: 10.181

leave it on for some more time
11 seconds is not quite relevant

Deleted member 67440 · Apr 24, 2021

So it seems to be actually Apache to use the hard disk frequently.
It should be clarified if this happens only in the first phase ( "warming up" of the mysql cache and also of the filesystem) or if it persists.

Essentially, when the services are stopped, no data is read from disk; when they are active they are provided (and therefore in the first phase it is completely normal to have activity on the hard disk).

Then, after a certain period, if the site is not very loaded, the .JPG etc files provided by Wordpress are read by the ARC, while mysql - in turn - no longer reads from disk but from its internal cache for the execution of the query (=by PHP's script).

Obviously this is the physiological situation, not the pathological one.

Is there a way to track down what's heavily ask for such resources from MySQL ?

So I would start with mysqltuner to check, specifically, the size of the innodb cache, and then switch to check Apache.

In short, there is no disk activity (or very little) if and when a cache is used (ARC, mysql, Wordpress, whatever).

In some cases (but it depends on the site itself) I move the Wordpress cache (normally activated) to a RAMDISK, if there is ample RAM but slow disks (this often happens for OVH)

In fact, another element to take into consideration, after profiling mysql, is the possibility that some Wordpress plugin (there are many) for the cache (written on the hard disk) does more good than harm.

Bottlenecks can be numerous.
Slow drive.
Mysql which often reads-writes to disk
Misconfigured Apache
Badly configured PHP
Wordpress misconfigured
Wordpress cache plugins that do damages
Simply a used site
The filesystem (or rather its cache) which competes with other application caches.
Demonic possession

VladiBG · Apr 24, 2021

What are you running on your apache server?

Leo L. · Apr 25, 2021

covacat said:
leave it on for some more time
11 seconds is not quite relevant

Indeed

Now after one day :

Code:

Uptime: 77515  Threads: 2  Questions: 5916417  Slow queries: 163  Opens: 2324  Flush tables: 3  Open tables: 2243  Queries per second avg: 76.326

Leo L. · Apr 25, 2021

VladiBG said:
What are you running on your apache server?

Essentially, it's my main server for personal things, like :
- imapd server accessible with roundcube webmail (through apache) ;
- personal cloud with nextcloud (through apache) ;
- webdav sync system to sync my contacts and calendars regularly (through apache) ;
- 2 personal websites under Wordpress (through... yes apache).

VladiBG · Apr 25, 2021

The output of the truss indicate a writing of some sort of logging with timestamps like a general log (query log). Is your general log enabled in MySQL?

Can you provide the output of the following SQL query:
SHOW VARIABLES LIKE 'general%';

also
SHOW VARIABLES LIKE 'log_output%';

Leo L. · Apr 25, 2021

VladiBG First of all thanks for your continuous support

Yeah I think I enabled logging yesterday, after SirDice question about MySQL error logs earlier in this thread.

Here are the informations you asked for :

Code:

root@localhost [(none)]> SHOW VARIABLES LIKE 'general%';
+------------------+---------------------------+
| Variable_name    | Value                     |
+------------------+---------------------------+
| general_log      | ON                        |
| general_log_file | /var/log/mysql/mysqld.log |
+------------------+---------------------------+

root@localhost [(none)]> SHOW VARIABLES LIKE 'log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+

VladiBG · Apr 25, 2021

This log every query to the server and generate huge amount of writes it should be used only when you debugging something. So if you are done logging turn it off with
set global general_log = 'off';
And also in the my.cnf file if it's enabled there.

To see the log in the table:
select event_time, user_host, command_type, CONVERT(argument USING latin1) AS query from mysql.general_log;

Leo L. · Apr 25, 2021

Thanks VladiBG !
What do I have to look for inside this table ?
Because it seems to be... litterally a TON of information

Code:

11954249 rows in set (4 min 38.32 sec)

VladiBG · Apr 25, 2021

You can see the log of all queries to the MySQL it's useful when you need to debug some long running query (to view the actual query) or resolve some issue while you developing some app. When you stop that logging you can test again and see if your issue with the load on the disk is gone.

Also you can truncate that table, you don't need that log.

Mayhem30 · Apr 25, 2021

What version of MySQL are you using?

Once I updated to version 5.7.33, MySQL kept eating my all my ram and then starting using swap. In order to fix this issue, I had to install devel/google-perftools and added the following to the top of my /usr/local/etc/mysql/my.cnf file :

Code:

[mysqld_safe]
malloc-lib=/usr/local/lib/libtcmalloc_minimal.so

Then restart.

MySQL used to eat up 6GB + swap, now it's back to max 1.1 GB usage like it was before upgrading to version 5.7.33.

More info here : https://forums.freebsd.org/threads/...lines-exhaust-ram-and-swap.72733/#post-489301

richardtoohey2 · Apr 25, 2021

Leo L. said:

Love the "plot" thickening idea

For now :

root@vendome # tail -f slow-query.log

Code:

/usr/local/libexec/mysqld, Version: 8.0.23 (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument
/usr/local/libexec/mysqld, Version: 8.0.23 (Source distribution). started with:
Tcp port: 3306  Unix socket: /tmp/mysql.sock
Time                 Id Command    Argument

Nothing since 5 minutes now...

root@vendome # tail -f /var/log/mysql/mysqld.log

Code:

2021-04-23T16:37:32.514999Z 0 [Warning] [MY-011068] [Server] The syntax 'expire-logs-days' is deprecated and will be removed in a future release. Please use binlog_expire_logs_seconds instead.
2021-04-23T16:37:32.516963Z 0 [System] [MY-010116] [Server] /usr/local/libexec/mysqld (mysqld 8.0.23) starting as process 70872
2021-04-23T16:37:32.519753Z 0 [Warning] [MY-010156] [Server] Although a path was specified for the --slow-query-log-file option, log tables are used. To enable logging to files use the --log-output=file option.
2021-04-23T16:37:32.555301Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2021-04-23T16:37:37.543098Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2021-04-23T16:37:38.548875Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /tmp/mysqlx.sock
2021-04-23T16:37:38.977140Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed.
2021-04-23T16:37:38.977454Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel.
2021-04-23T16:37:39.147514Z 0 [System] [MY-010931] [Server] /usr/local/libexec/mysqld: ready for connections. Version: '8.0.23'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution.

Nothing since 5 minutes also...

Note - this looks like the base MySQL log - the output above is NOT the slow query log.

You need something like this in your /usr/local/etc/mysql/my.cnf and you will need to restart MySQL to pick it up:

Code:

long_query_time = 0.1
slow_query_log = 1
slow_query_log_file = /var/log/mysql-slow.log
log_queries_not_using_indexes = 1

You should create the file first (/var/log/mysql-slow.log) and chown it to the mysql user (make sure the mysql user has write permissions).

Slow query log entries look like this:

Code:

# Time: 2021-04-25T20:00:00.102744Z
# User@Host: some_user[some_user] @ localhost []  Id:  5032
# Query_time: 0.000254  Lock_time: 0.000128 Rows_sent: 0  Rows_examined: 11
SET timestamp=1619380800;
SELECT         some_fields
        FROM         some_table 
        WHERE        some_condition
        ORDER BY    some_field;

Then you can see by the query time or the lock time or the number of rows sent/examined if you've got an issue with slow queries or unindexed tables.

System slow after update 12.2 -> 13.0

Deleted member 67440

Guest

Leo L.

Deleted member 67440

Guest

Leo L.

VladiBG

covacat

Leo L.

Leo L.

covacat

Deleted member 67440

Guest

VladiBG

Leo L.

Leo L.

VladiBG

Leo L.

VladiBG

Leo L.

VladiBG

Mayhem30

richardtoohey2