• Welcome to the Chevereto user community!

    Here users from all over the world gather around to learn the latest about Chevereto and contribute with ideas to improve the software.

    Please keep in mind:

    • 😌 This community is user driven. Be polite with other users.
    • 👉 Is required to purchase a Chevereto license to participate in this community (doesn't apply to Pre-sales).
    • 💸 Purchase a Pro Subscription to get access to active software support and faster ticket response times.
  • Chevereto Support CLST

    Support response

    Support checklist

MySQL high cpu usage (200%)

twm

Chevereto Member
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.

5GHXe6.png


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

5GH0fG.jpg

5GHYaK.jpg



My Site
5GHTOB.png
 
Last edited:
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.
 
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?
 
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.
 
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?
 
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.
 
already fixed
thank you.
AnYW9J.jpg



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.

AnJMLV.png
 
Last edited:
Back
Top