Just to start I'm on a dedicated forum so it's not cause I'm on a shared server. Basically just recently my site has been getting alot of mysql max connection errors.
When I asked my hosting company they said it was caused by table locks so we converted the threads, posts and users tables into innodb. We also dropped the full-text index on messages in the posts table. This didn't resolve the max connection errors and now the site is slower aswell.
When I look at htop during when it's erroring out and it does show alot of php scripts running. Just don't know what's causing them to pile up.
Background info: My site has around 1mil posts with approx 300 active users at any given time. Also probably alot of pming going on because it's highly driven by trading amongst members.
Please someone help me debug this...
Switching to InnoDB of course does not decrease the number of connection. Why can't you just increase the limit?
Because currently it's already set to max_connections=125 which seems normal. I feel like increasing the max connections is just hiding an underlying issue somewhere else thats causing this pile up.
I say this because normally htop doesn't show me many php scripts running. Something, sometimes causes them to just start piling up though and when it starts it piles up fast. Increasing the limit feels like it would just increase the threshold for mysql to error out.
(2013-11-26, 04:27 PM)StefanT Wrote: [ -> ]Switching to InnoDB of course does not decrease the number of connection. Why can't you just increase the limit?
Switching to InnoDB was suppose to help with table locks which my hosting service was saying the cause of the php script pile ups but apparently there's more to it.
help?
here's my my.cnf incase there's something set incorrectly here:
[mysqld]
skip-external-locking
key_buffer_size = 384M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
# Try number of CPU's*2 for thread_concurrency
thread_concurrency = 8
#innodb_file_per_table=1 # Ensure that each innodb table is it's own binary data block just in case there's corruption.
query_cache_size=64M
max_allowed_packet=16M # don't change unless required for large blobs
table_cache=1024 # max 2048, can be increased if more Opened tables - SHOW STATUS LIKE 'Opened_tables';
wait_timeout=300 # can be increased if using persistent connections
#max_user_connections=25
open_files_limit=16384
#delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts
#delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts
collation_server=utf8_unicode_ci
character_set_server=utf8
#innodb_flush_method=O_DIRECT # Do not enable this without a good reason - it doesn't work well on new dedis
innodb_buffer_pool_size=128M # check mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';" - free vs total
innodb_thread_concurrency=4 # Number of physical + virtual CPU's, be careful of adding more
tmpdir=/dev/shm
tmp_table_size = 384M
max_heap_table_size = 384M
max_connections=125 # Should be between 100-150, increase *slowly* because it causes MySQL to consume more memory!
[/code]