MySQL high cpu usage (200%)

twm

Network license
License owner
Joined
Aug 27, 2017
Messages
38
Points
58
If you upload a lot of images at the same time,

MySQL's CPU usage is very high. At this time, the website will be locked

Sorry, My English is pretty basic.



My Server
i7-7700/32G/2x512G SSD/ RAID 1

CentOS 7/Nginx 1.15/MariaDB 10.3/php 7.2/


MySQL
my.conf

[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

[mysqld]
binlog_cache_size = 256K
thread_stack = 512K
join_buffer_size = 8192K
query_cache_type = 1
max_heap_table_size = 2048M
port = 3306
socket = /tmp/mysql.sock
user = mysql
datadir = /www/server/data
basedir = /www/server/mysql
log_error = /www/server/data/mariadb.err
#pid-file = /www/server/data/mariadb.pid
default_storage_engine = MyISAM
#skip-external-locking
#loose-skip-innodb
key_buffer_size = 1024M
max_allowed_packet = 100G
table_open_cache = 2048
sort_buffer_size = 4096K
net_buffer_length = 8K
read_buffer_size = 4096K
read_rnd_buffer_size = 2048K
myisam_sort_buffer_size = 128M
thread_cache_size = 256
query_cache_size = 512M
tmp_table_size = 2048M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

#skip-networking
#skip-name-resolve
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/www/server/data/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on

default_storage_engine = InnoDB
innodb_data_home_dir = /www/server/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /www/server/data
innodb_buffer_pool_size = 4096M
innodb_log_file_size = 1024M
innodb_log_buffer_size = 256M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 120
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 8
innodb_write_io_threads = 8

[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 8M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout




My Site
 
Last edited:

Rodolfo

Chevereto Developer
Chevereto Staff
Joined
Oct 7, 2008
Messages
16,679
Points
237
Location
Chevereto HQ
Website
rodolfoberrios.com
First thing that you should mention is the load that your website has. Based on the processes I'm seeing normal php usage but mysql is different story.

I strongly suggest you to check your table indexes, maybe you need to rebuild these.
 

twm

Network license
License owner
Joined
Aug 27, 2017
Messages
38
Points
58
First thing that you should mention is the load that your website has. Based on the processes I'm seeing normal php usage but mysql is different story.

I strongly suggest you to check your table indexes, maybe you need to rebuild these.
Thank you a lot,
How to rebuild table indexes?
 

twm

Network license
License owner
Joined
Aug 27, 2017
Messages
38
Points
58
If you click on this user's album, MySQL's CPU usage will be 100%.



 
Last edited:

bilderupload

Network license
Beta tester
Joined
Mar 15, 2019
Messages
12
Points
53
Location
Munich
Website
bilderupload.net
I would try to reproduce the error on the local machine with this Database, and than i would use MySQLTuner,
read this Article about how to do it sir, i hope i can help you, wish you alot luck.
 

Rodolfo

Chevereto Developer
Chevereto Staff
Joined
Oct 7, 2008
Messages
16,679
Points
237
Location
Chevereto HQ
Website
rodolfoberrios.com
Show the full query causing the issue and the result of the EXPLAIN command for that query.

No one can help you at all only knowing that X user gives you issues. Your DB is unique to your website, we can't replicate the error by just knowing that.
 

twm

Network license
License owner
Joined
Aug 27, 2017
Messages
38
Points
58
Show the full query causing the issue and the result of the EXPLAIN command for that query.

No one can help you at all only knowing that X user gives you issues. Your DB is unique to your website, we can't replicate the error by just knowing that.
Sorry, I won't use the EXPLAIN command,
I am not sure what information is needed, I can provide the test server root password. Could you check it for me?
 

twm

Network license
License owner
Joined
Aug 27, 2017
Messages
38
Points
58
I changed the type of chv_images to InnoDB,
maybe it's normal now.



 
Last edited:

Rodolfo

Chevereto Developer
Chevereto Staff
Joined
Oct 7, 2008
Messages
16,679
Points
237
Location
Chevereto HQ
Website
rodolfoberrios.com
It seems to be that the problem is on the DB indexes. Test query:

SQL:
SELECT SQL_NO_CACHE * FROM chv_images WHERE chv_images.image_album_id=17028 ORDER BY chv_images.image_id ASC LIMIT 0,5
^ Takes 2.3181s (Album 17028 has 18587 images)

The problem is that the system needs a composite index for ASC sorting by image_id when doing WHERE image_album_id=X. I've added a composite index (image_album_id_image_id) and now the query takes only 0.0004s.

Cheers,
Rodolfo.
 

twm

Network license
License owner
Joined
Aug 27, 2017
Messages
38
Points
58
already fixed
thank you.



Index name: image_album_id_image_id
Index choice: INDEX
Column: image_album_id {bigint(320)]
image_id[bigint(32)]

or
Code:
ALTER TABLE `your_database`.`chv_images` ADD INDEX`image_album_id_image_id` (`image_album_id`, `image_id`);





If I need to fix this on other websites, what do I need to do?
thank you.

 
Last edited:
  • Like
Reactions: Rodolfo