1

I've been running a pretty high pop website for a few months new and as its growing we've been having some problems lately.

Its a 2 core Xeon, 8GB, SSD server. We're pulling +- 150k people a day.

Its a lightning fast wordpress stack and up to about 450 concurrent (200 pageviews a minute) it remains lightning fast.

If it goes beyond that, the load goes INSANE, from 1.5 to 20 or 30! I've been trying to fine tune my.cfg but I'm about to give up, there is LOADS of information which is very varying, so I'm starting to get confused.

Here is my MySQLTuner from the last 4 days.

As you will see, it tells me to increase query cache, doing so however absolutely destroys my CPU even more and a lot earlier than when it remains at 8MB...

We also converted all tables to InnoDB and saw a big performance gain that way in the past.

I hope I can get some insight from professionals. I would even pay for some one on one time if needed.

Thank you!

  • 1
    Have you tried disabling the query cache? Its a rather big bottleneck and your only getting 42% efficiency. Definitely follow the table_open_cache recommendation. Your database concurrency is only 22 connections. Suggest showing SHOW GLOBAL STATUS (in the question rather than an external pastebin). – danblack Dec 18 '18 at 19:49
  • 1
    paste.ee/p/eZUFT That's the output of show global status. I'll already go ahead and try to alter the open cache recommendation. Any other help or tips would be greatly appreciated – julliuz Dec 19 '18 at 0:54
  • 1
    @danblack you can find global status above, I forgot to @ last night. – julliuz Dec 19 '18 at 16:04
  • @julliuz Additional information requested post on pastebin.com or here, SHOW GLOBAL VARIABLES; and SHOW FULL PROCESSLIST; And from your OS text results of ulimit -a so we can see OS limits. htop or top or mytop so we can see the apps that are running. Thanks – Wilson Hauck Dec 20 '18 at 1:02
  • 1
    Everything @julliuz said plus SHOW ENGINE INNODB STATUS. Enable slow query log for let’s say queries taking more than 10 sec. reduce it if the log is empty and paste it here. Get rid of this tuner and don’t query cache at all by all means. The problem is that the moment you stop caching you will need to introduce some indexes. Install percona toolkit. Ppt is free and will help you a lot in diagnosing your logs. – ninjabber Dec 20 '18 at 10:12
0

2nd Suggestion to allow formatted .sql script attempt

list-IS-gvout.sql Last Updated 12/21/2018 mysqlservertuning.com

email info@mysqlservertuning.com sharable comments, please

DELETE the OUTFILE name(s) if they exist BEFORE you run this sql script

OUTFILE name must be available to write results to destination of SELECT @@datadir

SELECT * INTO OUTFILE './isgv.txt' FROM information_schema.global_variables ORDER BY global_variables.variable_name;

Maybe your text editor will have capacity to post to pastebin.com with this output file.

  • Hey, I managed to gather ALL files now! Server Info: 2 core Xeon, 8GB, SSD server. SQL Tuner: paste.ee/p/kQ7nE variables: pastebin.com/EjFtCS9V show global status: pastebin.com/XSMVKARm show full processlist: pastebin.com/ySHuitN2 show innodb status: pastebin.com/XkjCGAe7 ulimit -a pastebin.com/NwJThcxN dirty pages pastebin.com/hcvZFVb5 top screenshot: imgur.com/a/Hin1Fpt I didn't change anything yet before showing everything requested. – julliuz Dec 21 '18 at 19:22
  • @julliuz Thank you. When will you be available over the next few days? Will begin your analysis today. – Wilson Hauck Dec 21 '18 at 20:20
  • @julliuz Feel free to apply the 1st Suggestions ASAP. Thanks – Wilson Hauck Dec 21 '18 at 20:34
  • 1
    With 8M query cache type 1 , mysql goes up to max 35-40% cpu. When I disabled query cache like you told me for 1 hour, mysql goes up to 65-70%. I checked dirty pages every 10mins and they kept going up from 400 to 2000+ after 1 hour. Any ideas? – julliuz Dec 22 '18 at 1:13
  • @julliuz You NEED, query_cache_type=0 # from 1 (ON) for NO Query Cache to conserve CPU cycles AND query_cache_size=0 # from 8M to conserve RAM for more useful purposes HAVE YOU IMPLEMENTED - innodb_flushing_avg_loops=5 # from 30 to reduce innodb_buffer_pool_pages_dirty count. ALL the 1st suggestions need to be in place as listed. – Wilson Hauck Dec 22 '18 at 2:04
0

1st Suggestions to consider for your my.cnf [mysqld] section

query_cache_type=0  # from 1 (ON) to OFF for NO qc to conserve CPU cycles
query_cache_size=0  # from 8M to conserve RAM for more useful purposes
innodb_io_capacity=1800  # from default (200) to increase SSD IOPS limit
innodb_flushing_avg_loops=5  # from 30 to reduce innodb_buffer_pool_pages_dirty count

added 20181221 8 PM CT USA

innodb_purge_threads=4  # from 1 to expedite purge activity

this will require stop/start after my.cnf change

You may monitor innodb_buffer_pool_pages_dirty count with SHOW GLOBAL STATUS LIKE '%dirty%'; if you check it every 10 minutes, you should see reduction and CPU busy will reduce as well until you get near 0 pages dirty. e still need additional information requested to allow additional performance improvements.

Please view my profile, Network profile and get in touch when you have a moment.

  • Sorry but after changing ALL of this, CPU usage goes up a lot. Mysql process goes from 5-35% to 50%+ ! – julliuz Dec 22 '18 at 14:12
  • 1
    @julliuz Opening the innodb_io_capacity will allow the CPU to be more busy, A) Is your response time improved? B) How many 'dirty' pages do you have at this time? C) We still need to run the findfragtables.sql script and share the results. We will likely find you have tables with more than 10% FREE_Space that need to have OPTIMIZE used to eliminate fragmentation. Thanks – Wilson Hauck Dec 22 '18 at 16:04
  • @julliuz A) Is your response time improved? B) How many 'dirty' pages do you have at this time? C) We still need to run the findfragtables.sql script and share the results. We will likely find you have tables with more than 10% FREE_Space that need to have OPTIMIZE used to eliminate fragmentation. – Wilson Hauck Dec 29 '18 at 18:29

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.