1

This will sound strange, but long story short. I have client running two CS Cart web shops one one dedicated server. Cs Cart is version 3.0.2 and each shop has around 2000 products. Website opens quite slow + when more visitors get on the site ( e.g. 150 people) mysql will use over 300% of CPU.

Here are some statistic for one DB ( another one is quite similar) Client is using MariaDB 10.0.22. Default engine is mysql and tables are mysql. Server has over 120G of memory ( under load is uses most around 8G) and cpu is Intel(R) Xeon(R) CPU D-1540 @ 2.00GHz Centos 6.7 + whm/cpanel

enter image description here

enter image description here

Here are detailed stats from phpmyadmin

> Aborted clientsDocumentation  10.6 k
Aborted connectsDocumentation   184
Access denied errorsDocumentation   245 
Aria pagecache blocks not flushedDocumentation  0   
Aria pagecache blocks unusedDocumentation   15.7 k  
Aria pagecache blocks usedDocumentation 15.2 k  
Aria pagecache read requestsDocumentation   5.6 G   
Aria pagecache readsDocumentation   2.1 M   
Aria pagecache write requestsDocumentation  756.7 M 
Bytes receivedDocumentation 973.8 G 
Bytes sentDocumentation 2.9 T   
Com admin commandsDocumentation 5.5 k       
Com beginDocumentation  0   
Com binlogDocumentation 0   
Com call procedureDocumentation 0   
Com change dbDocumentation  282.7 k 
Com change masterDocumentation  0   
Com checkDocumentation  216 
Com checksumDocumentation   0   
Com commitDocumentation 0   
Com create dbDocumentation  6       
Com create tableDocumentation   21  
Com deleteDocumentation 222.1 k 
Com delete multiDocumentation   0   
Com doDocumentation 0   
Com drop dbDocumentation    3   
Com drop eventDocumentation 0   
Com drop functionDocumentation  0   
Com drop indexDocumentation 0   
Com drop procedureDocumentation 0   
Com drop roleDocumentation  0   
Com drop serverDocumentation    0   
Com drop tableDocumentation 3   
Com drop triggerDocumentation   0   
Com drop userDocumentation  2   
Com drop viewDocumentation  0   
Com empty queryDocumentation    197 
Com flushDocumentation  0   
Com get diagnosticsDocumentation    0   
Com grantDocumentation  20      
Com helpDocumentation   0   
Com insertDocumentation 42.6 k  
Com lock tablesDocumentation    6   
Com optimizeDocumentation   0   
Com preload keysDocumentation   0   
Com purgeDocumentation  0   
Com purge before dateDocumentation  0   
Com release savepointDocumentation  0   
Com rename tableDocumentation   0   
Com rename userDocumentation    0   
Com repairDocumentation 0   
Com replaceDocumentation    1.3 M   
Com replace selectDocumentation 20.7 k  
Com rollback to savepointDocumentation  0   
Com savepointDocumentation  0   
Com selectDocumentation 2.1 G   
Com set optionDocumentation 480.3 k 
Com show authorsDocumentation   0   
Com show binlog eventsDocumentation 0   
Com show binlogsDocumentation   33      
Com show create tableDocumentation  9   
Com show create triggerDocumentation    0   
Com show databasesDocumentation 7       
Com show fieldsDocumentation    350.2 k 
Com show function statusDocumentation   0   
Com show grantsDocumentation    6   
Com show index statisticsDocumentation  0   
Com show keysDocumentation  0   
Com show master statusDocumentation 13  
Com show open tablesDocumentation   0   
Com show pluginsDocumentation   0   
Com show privilegesDocumentation    0   
Com show procedure statusDocumentation  0   
Com show processlistDocumentation   1.1 k   
Com show profileDocumentation   0   
Com show profilesDocumentation  0   
Com show relaylog eventsDocumentation   0   
Com show slave hostsDocumentation   0   
Com show slave statusDocumentation  13  
Com show statusDocumentation    15  
Com show storage enginesDocumentation   0   
Com show table statisticsDocumentation  0   
Com show table statusDocumentation  7   
Com show tablesDocumentation    272 k   
Com show triggersDocumentation  14      
Com truncateDocumentation   6   
Com uninstall pluginDocumentation   0   
Com unlock tablesDocumentation  6   
Com updateDocumentation 28.7 k  
Com update multiDocumentation   426 
Com xa commitDocumentation  0   
Com xa endDocumentation 0   
Com xa prepareDocumentation 0   
Com xa recoverDocumentation 0   
Com xa rollbackDocumentation    0   
Com xa startDocumentation   0   
CompressionDocumentation    OFF     
ConnectionsDocumentation    290.9 k 
Cpu timeDocumentation   0   
Created tmp disk tablesDocumentation    2.2 M   
Created tmp filesDocumentation  6   
Created tmp tablesDocumentation 12.5 M  
Delayed errorsDocumentation 0   
Delayed insert threadsDocumentation 0   
Delayed writesDocumentation 0   
Empty queriesDocumentation  451 M   
Executed eventsDocumentation    0   
Executed triggersDocumentation  0   
Feature delay key writeDocumentation    0   
Feature dynamic columnsDocumentation    0   
Feature fulltextDocumentation   0   
Feature gisDocumentation    0   
Feature localeDocumentation 0   
Feature subqueryDocumentation   1.3 M   
Feature timezoneDocumentation   9   
Feature triggerDocumentation    0   
Feature xmlDocumentation    0   
Flush commandsDocumentation 1   
Handler commitDocumentation 18.6 M  
Handler deleteDocumentation 514 k   
Handler discoverDocumentation   0   
Handler external lockDocumentation  0   
Handler icp attemptsDocumentation   4.8 G   
Handler icp matchDocumentation  4.8 G   
Handler mrr initDocumentation   0   
Handler mrr key refillsDocumentation    0   
Handler mrr rowid refillsDocumentation  0   
Handler prepareDocumentation    0   
Handler read firstDocumentation 2.5 M   
Handler read keyDocumentation   8.7 G   
Handler read lastDocumentation  80  
Handler read nextDocumentation  34.2 G  
Handler read prevDocumentation  18.5 k  
Handler read rndDocumentation   641.8 M 
Handler read rnd deletedDocumentation   267.7 k 
Handler read rnd nextDocumentation  1.6 G   
Handler rollbackDocumentation   2   
Handler savepointDocumentation  0   
Handler savepoint rollbackDocumentation 0   
Handler tmp updateDocumentation 531.6 k 
Handler tmp writeDocumentation  1.7 G   
Handler updateDocumentation 351.1 k 
Handler writeDocumentation  1.4 M   
Innodb available undo logsDocumentation 128 
Innodb background log syncDocumentation 252.7 k 
Innodb buffer pool bytes dataDocumentation  162.5 M 
Innodb buffer pool bytes dirtyDocumentation 0   
Innodb buffer pool dump statusDocumentation not started 
Innodb buffer pool load statusDocumentation not started 
Innodb buffer pool pages dataDocumentation  9.9 k   
Innodb buffer pool pages dirtyDocumentation 0   
Innodb buffer pool pages flushedDocumentation   2.1 M   
Innodb buffer pool pages freeDocumentation  2.1 M   
Innodb buffer pool pages lru flushedDocumentation   0   
Innodb buffer pool pages made not youngDocumentation    45 k    
Innodb buffer pool pages made youngDocumentation    52.6 k  
Innodb buffer pool pages miscDocumentation  706 
Innodb buffer pool pages oldDocumentation   3.5 k   
Innodb buffer pool pages totalDocumentation 2.1 M   
Innodb buffer pool read aheadDocumentation  255 
Innodb buffer pool read ahead evictedDocumentation  0   
Innodb buffer pool read ahead rndDocumentation  0   
Innodb buffer pool read requestsDocumentation   72.8 G  
Innodb buffer pool readsDocumentation   7.9 k   
Innodb buffer pool wait freeDocumentation   0   
Innodb buffer pool write requestsDocumentation  11.5 M  
Innodb checkpoint ageDocumentation  0   
Innodb checkpoint max ageDocumentation  1.7 G   
Innodb data fsyncsDocumentation 709.2 k 
Innodb data pending fsyncsDocumentation 0   
Innodb data pending readsDocumentation  0   
Innodb data pending writesDocumentation 0   
Innodb data readDocumentation   135.8 M
Innodb data readsDocumentation  8.8 k   
Innodb data writesDocumentation 2.6 M   
Innodb data writtenDocumentation    80.1 G  
Innodb dblwr pages writtenDocumentation 2.1 M   
Innodb dblwr writesDocumentation    73.5 k  
Innodb deadlocksDocumentation   0   
Innodb have atomic builtinsDocumentation    ON  
Innodb history list lengthDocumentation 796 
Innodb ibuf discarded delete marksDocumentation 0   
Innodb ibuf discarded deletesDocumentation  0   
Innodb ibuf discarded insertsDocumentation  0   
Innodb ibuf free listDocumentation  0   
Innodb ibuf merged delete marksDocumentation    107 
Innodb ibuf merged deletesDocumentation 7   
Innodb ibuf merged insertsDocumentation 62  
Innodb ibuf mergesDocumentation 138 
Innodb ibuf segment sizeDocumentation   2   
Innodb ibuf sizeDocumentation   1   
Innodb log waitsDocumentation   0   
Innodb log write requestsDocumentation  21.7 M  
Innodb log writesDocumentation  370.5 k 
Innodb lsn currentDocumentation 17.3 G  
Innodb lsn flushedDocumentation 17.3 G  
Innodb lsn last checkpointDocumentation 17.3 G  
Innodb master thread active loopsDocumentation  84 k    
Innodb master thread idle loopsDocumentation    168.6 k 
Innodb max trx idDocumentation  27.7 M  
Innodb mem adaptive hashDocumentation   617.1 M 
Innodb mem dictionaryDocumentation  144.3 M 
Innodb mem totalDocumentation   35.2 G  
Innodb mutex os waitsDocumentation  15.6 k  
Innodb mutex spin roundsDocumentation   89.3 M  
Innodb mutex spin waitsDocumentation    289 M   
Innodb oldest view low limit trx idDocumentation    0   
Innodb os log fsyncsDocumentation   438.2 k 
Innodb os log pending fsyncsDocumentation   0   
Innodb os log pending writesDocumentation   0   
Innodb os log writtenDocumentation  11.1 G  
Innodb os merge buffers mergedDocumentation 0   
Innodb os merge buffers readDocumentation   0   
Innodb os merge buffers writtenDocumentation    0   
Innodb page sizeDocumentation   16.4 k  
Innodb pages createdDocumentation   4.1 k   
Innodb pages readDocumentation  8.3 k   
Innodb pages writtenDocumentation   2.1 M   
Innodb purge trx idDocumentation    27.7 M  
Innodb purge undo noDocumentation   0   
Innodb read views memoryDocumentation   376 
Innodb row lock current waitsDocumentation  0   
Innodb row lock timeDocumentation   45  
Innodb row lock time avgDocumentation   0   
Innodb row lock time maxDocumentation   6   
Innodb row lock waitsDocumentation  69  
Innodb rows deletedDocumentation    127.7 k 
Innodb rows insertedDocumentation   141.1 k 
Innodb rows readDocumentation   26.1 G  
Innodb rows updatedDocumentation    170.4 k 
Innodb s lock os waitsDocumentation 84.7 k  
Innodb s lock spin roundsDocumentation  12.1 M  
Innodb s lock spin waitsDocumentation   9.4 M   
Innodb system rows deletedDocumentation 0   
Innodb system rows insertedDocumentation    0   
Innodb system rows readDocumentation    0   
Innodb system rows updatedDocumentation 0   
Innodb truncated status writesDocumentation 0   
Innodb x lock os waitsDocumentation 29.4 k  
Innodb x lock spin roundsDocumentation  26.4 M  
Innodb x lock spin waitsDocumentation   13 M    
Key blocks not flushedDocumentation 0   
Key blocks unusedDocumentation  74.4 k  
Key blocks usedDocumentation    32.8 k  
Key blocks warmDocumentation    11.7 k  
Key buffer fraction %   43.23 % 
Key read ratio %    <0.01 % 
Key read requestsDocumentation  76.7 G  
Key readsDocumentation  20.4 k  
Key write ratio %   72.56 % 
Key write requestsDocumentation 3.5 M   
Key writesDocumentation 2.5 M   
Last query costDocumentation    0   
Max used connectionsDocumentation   66  
Memory usedDocumentation    607.9 M 
Not flushed delayed rowsDocumentation   0   
Open filesDocumentation 4.1 k   
Open streamsDocumentation   0   
Open table definitionsDocumentation 2.4 k   
Open tablesDocumentation    2.9 k   
Opened filesDocumentation   8.8 M   
Opened plugin librariesDocumentation    0   
Opened table definitionsDocumentation   2.3 k   
Opened tablesDocumentation  2.9 k   
Opened viewsDocumentation   0       
Prepared stmt countDocumentation    0   
Qcache free blocksDocumentation 19.9 k  
Qcache free memoryDocumentation 80.8 M  
Qcache hitsDocumentation    2.1 G   
Qcache insertsDocumentation 40.9 M  
Qcache lowmem prunesDocumentation   36.2 M  
Qcache not cachedDocumentation  344.5 k 
Qcache queries in cacheDocumentation    45.7 k  
Qcache total blocksDocumentation    112.1 k 
QueriesDocumentation    2.1 G   
QuestionsDocumentation  2.1 G   
Rows readDocumentation  40.4 G  
Rows sentDocumentation  112.3 G 
Rows tmp readDocumentation  2 G 
Rpl statusDocumentation AUTH_MASTER 
Select full joinDocumentation   20.2 k  
Select full range joinDocumentation 2 M 
Select rangeDocumentation   12.3 M  
Select range checkDocumentation 0   
Select scanDocumentation    1.6 M   
Slave heartbeat periodDocumentation 0   
Slave open temp tablesDocumentation 0   
Slave received heartbeatsDocumentation  0   
Slave retried transactionsDocumentation 0   
Slave runningDocumentation  OFF 
Slow launch threadsDocumentation    0   
Slow queriesDocumentation   17  
Sort merge passesDocumentation  0   
Sort priority queue sortsDocumentation  1.3 M   
Sort rangeDocumentation 8.1 M   
Sort rowsDocumentation  4.8 G   
Sort scanDocumentation  4.1 M   
SyncsDocumentation  9   
Table locks immediateDocumentation  91.2 M  
Table locks waitedDocumentation 44.9 k  
Tc log max pages usedDocumentation  0   
Tc log page sizeDocumentation   0   
Tc log page waitsDocumentation  0   
Threadpool idle threadsDocumentation    0   
Threadpool threadsDocumentation 0   
Threads cache hitrate % 99.98 % 
Threads cachedDocumentation 58  
Threads connectedDocumentation  8   
Threads createdDocumentation    66  
Threads runningDocumentation    1   
UptimeDocumentation 2 days, 22 hours, 15 minutes and 40 seconds 
Uptime since flush statusDocumentation  2 days, 22 hours, 15 minutes and 40 seconds

Here are settings that I found in my.cnf on the server

[mysqld]
local-infile=0
max_connections = 600
max_user_connections = 1000
max-connect-errors = 10
key_buffer_size = 128M
myisam_sort_buffer_size = 64M
read_buffer_size = 1M
table_open_cache = 8000
table-definition-cache = 8000
thread_cache_size = 384
wait_timeout = 20
connect_timeout = 10
tmp_table_size = 512M
max_heap_table_size = 512M
max_allowed_packet=268435456
net_buffer_length = 16384
concurrent_insert = 2
read_rnd_buffer_size = 786432
bulk_insert_buffer_size = 8M
query_cache_limit = 4M
query_cache_size = 256M
query_cache_type = 1
#query-cache-type = 0
#query-cache-size = 0
query_prealloc_size = 262144
query_alloc_block_size = 65535
transaction_alloc_block_size = 8192
transaction_prealloc_size = 4096
max_write_lock_count = 8
log-error
external-locking=FALSE
open_files_limit=50000
sort_buffer_size = 128M
join_buffer_size = 128M
default-storage-engine=MyISAM
innodb-flush-method = O_DIRECT
innodb-log-files-in-group = 2
innodb-log-file-size = 1024M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 32G
# LOGGING #
#log-queries-not-using-indexes = 1
#slow-query-log = 1
#log_slow_rate_limit = 20
#long_query_time = 1
#slow-query-log-file = /var/lib/mysql/mysql-slow.log

The main problem is that client is unviling to upgrade cs cart to latest version due to a lot of modification that he made on the current site. Although, the latest version had quite optimized mysql queries which would speed up things a lot. So I can either try to tweak my.cnf a bit more or simply move database to some separate server/vps/database hosting service.

Few questions 1. Is there anything that can be done on the current database from my.cnf side, implementation of some database caching, moving tables from mysql to innodb or anything similar, that would help regarding the load and speed? 2. If moving database to separate machine would be better, what service should I use for this? Is there any service that is specialized in hosting databases etc. e.g I noticed that Amazon offers something like that ?

  • Absolute numbers are hard to put into perspective although 30M queries / hour is very excessive given you say 150 concurrent user is average high load. What is the number of daily pageloads? What storage engine do you use? You say you might move from MyISAM to InnoDB but I see you have innodb_buffer_pool_size = 32 GB. How big the database is phisically? When you say over 300% of CPU usage what exactly the CPU is doing (user, iowait)? 300% is not high for a Xeon D-1540. It's basically less than 20% load. – Károly Nagy Dec 2 '15 at 16:08
1

Has been a while since I used MySQL, am I reading your graphic correctly? More than 30 million select statements per hour of uptime? Seems a bit excessive, especially if you are seeing around 150 users concurrently.

If that's correct then I'd be looking at your client's modifications as the probable cause before either trying config tweaks or throwing hardware at the problem.

Do you have a dev box which you can clone and a testing suite that can mimic many sessions? If so I would try removing the mods to diagnose.

I'm not familiar with CS Cart but I've seen SQL server performance killed by recursive views, cursor queries and/or bad joins. Have you any data on which queries are consuming all your processing?

1

The Query cache is rather large, not full, and suffering a lot of prunes. Prunes are costly, especially if the cache is large. Suggest you try running for a while with query_cache_size = 128M.

This is dangerously large: tmp_table_size = 512M, but there is no evidence that you swapped because of it.

Are the tables MyISAM? Or InnoDB? Perhaps some of each?

Please provide (from the commandline mysql tool) SHOW VARIABLES; and SHOW GLOBAL STATUS;

But face it. 8K qps is pushing the limits of what a single server can do. The 300% probably implies that 3 cores are busy. How many do you have? I/O seems to be not a big issue (I'll know more with the SHOWs). It is mostly "just lots to do".

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.