1

We have run a PHP/MySQL blog site on a shared server for years with no problem (my code not Wordpress). I'm not an system or database guy except for what I need. Our host (BlueHost) moved our site to a new server, and our capacity to serve our audience has dropped significantly. We're experiencing a seasonal spike now, so that makes it worse. So much so that we're also using another identical site to bear the load (GoDaddy). Not ideal. I don't have admin privileges obv, but I can see that we get 1000 max connections on GoDaddy and 150 on BlueHost (not sure what it was before).

I've been tracking the Threads_connected and Threads_running for both sites (again identical sites). GoDaddy never gets above ~25 connected and ~2 running. BlueHost will start to stack up Threads_connected until it hits the 150 limit mark. I'm trying not to break our resource allotment, but even with a lot of (and sometimes all) traffic redirected over to GoDaddy, BlueHost will stay above 120 for an extended period of time. Not sure if it's a caching issue or what.

I've tried doing an 'inline' declaration for timeouts when I open my PHP/PDO connection to MySQL, but I'm somewhat skeptical that it's fixing the problem.

$dbh_ip_check -> exec("SET session wait_timeout=10"); 
$dbh_ip_check -> exec("SET session connect_timeout=10"); 

These are the current global variables:

mysql> show global variables like '%timeout';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 20       |
| delayed_insert_timeout      | 40       |
| have_statement_timeout      | YES      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 10       |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 60       |
| thread_pool_idle_timeout    | 60       |
| wait_timeout                | 3600     |
+-----------------------------+----------+

We don't get a huge amount of traffic, but our spikes are probably seeing >100 people hit the site at a time, and that would continue for a half hour or more.

Any recommendations for fixes on a shared server? Thanks.

Current server variables/status:

Show global status copy/paste: https://paste.debian.net/1182642/

Show global variables: https://paste.debian.net/1182643/

ETA:

  [~]# iostat -xm 5 3
Linux 4.19.150-76.ELK.el7.x86_64 (boxxxxx.bluehost.com)         01/27/21       _x86_64_ (12 CPU)

avg-cpu:  %user   %nice %system %iowait  %steal   %idle
          51.92    5.08   25.79    2.47    1.35   13.39

Device:         rrqm/s   wrqm/s     r/s     w/s    rMB/s    wMB/s avgrq-sz avgqu-sz   await r_await w_await  svctm  %util
  [~]# ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) 800000
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 178732
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) 800000
open files                      (-n) 100
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 15240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 25
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited
8
  • Edit question to include a link to show global status and show global variables.
    – danblack
    Jan 25 at 0:25
  • Er. Run the sql show global status and show global variables and include its output information in a publicly accessible link e.g. (paste.debian.net).
    – danblack
    Jan 25 at 3:08
  • Updated question to include current status.
    – bill
    Jan 25 at 17:09
  • @bill How much RAM on your shared BLUEHOST server? # cores? URL to your site? Please ASK BlueHost to share results ulimit -a for OS Command Prompt as well as iostat -xm 5 3 for indication of busy and core count. Please post these short results here. Thanks, anlaysis in process. Jan 27 at 11:19
  • Updated to include iostat -xm and ulimit -a.
    – bill
    Jan 27 at 14:45
1

max_connections is probably set to 151. And this is probably because of running a newer version of MySQL.

Assuming BlueHost allows you to change the config file (and they may even have a UI for doing such), change that to, say, 1000.

Having hundreds of 'simultaneous' connections is not, in itself, a "bad" thing. However, it may be indicative of some other problem.

Do the connections hang on longer than necessary? Is there some form of 'connection pooling'? What is the client -- does it have a limit on connections to it? Are you also having CPU or other performance problems? How long does one user stay online and connected?

4
  • I cannot edit config file, but I think I can set the timeout on a per connection basis. It also occurred to me that our connection count may include those for other sites on our shared server. It may not be a bad thing if I see the connection count reach the limit (at least not our fault). We have seasonal spikes, and I'm just trying to stay below our resource allotment. Maybe I should be focused more on metrics such as physical memory to do that?
    – bill
    Jan 26 at 13:30
  • @bill - It is naughty of them not to let you change max_connections. So, I would focus on minimizing the number of connections attempted and (as you suggest with wait_timeout) disconnecting faster. More RAM is not likely to help, but I will check the variables/status to look for clues.
    – Rick James
    Jan 26 at 18:51
  • @RickJames - "It is naughty of them not to let you change max_connections" - as this is a shared server I would not expect an individual user to be able to change such settings at all, as it is system-wide and affects other users. If using a dedicated server or VPS (VM or container) then it would be odd not to be able to tweak that setting. Jan 27 at 14:48
  • @DavidSpillett - Sorry, I did not understand "shared" for your case. It is unusual for a cloud provider not to isolate you in a VM. I suggest you find a different provider.
    – Rick James
    Jan 27 at 18:31
0

Rate Per Second = RPS

Suggestions to consider for your configuration [mysqld] section.

If you can login as root you may be able to dynamically change these values with

SET GLOBAL variable_name=value;

key_buffer_size=16M  # from 8M - unless you are at RAM limit
key_cache_age_threshold=7200  # from 300 seconds to reduce key-reads RPS of ~ 400
key_cache_block_size=16384  # from 1024 to reduce mgmt overhead
key_cache_division_limit=50  # from 100 percent to have HOT/WARM cache
max_connections=512  # from 151 default to reduce 43,543 connections rejected in 17 days

Assistance from Blue Host may be required.

A couple of observations from data provided, The activity level you have would be better served with a Dedicated server. com_rollback_to_savepoint averages one per second since instance start why so many? Unusually high for any system to consistently rollback to savepoint. there were 319 com_stmt_prepare processes completed MORE than com_stmt_close. leaves resources in use. CLOSE() appears to be missed when connection is done, leaving threads_connected of 38 when SHOW GLOBAL STATUS information was retrieved. Please View profile, Network profile for contact information and free downloadable Utility Scripts to assist with performance tuning.

4
  • Thanks for that information. I don't have root access, and I doubt Bluehost would make any changes to the server. Connections failing to close would explain a lot. Also, I assume they are pooling connections for all sites hosted on that server. There are 'Threads_connected' even when I turn off our database interactions.
    – bill
    Jan 27 at 14:51
  • Until they timeout a thread will stay connected - unless your code CLOSE() removes the connection. If you are using pooling, should thread_handling be 'pool-of-threads'? Blue Host may be wiling to assist, if you open a ticket. Jan 27 at 17:33
  • I use PHP/PDO to connect to MySQL. My current code sets the connection to null at the end. Is there a more reliable way for me to close it myself after making my query(s)?
    – bill
    Jan 27 at 18:16
  • If you could post your PHP/PDO code that accomplishes CONNECT, PROCESS, CLOSE - a brief review would be completed for you. Will you be able to implement any of the suggestions above to improve performance? Jan 27 at 20:43

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.