StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POapache/mysql slow response but cpu/memory and limits not reached
text
Body
copied!<p>I have a very strange problem.</p> <p>I'm running a very busy LAMP web service on Ubuntu (over a million visitors daily) and lately I'm having problems with apache responding slowly. </p> <p>The odd thing is that neither the webserver machine nor the database machine are reaching any sort of limits. CPU usage and memory all look okay (they're basically not even doing 10% of their max). Apache has enough idle processes to handle incoming requests. Currently it's sitting around 350 to 500 requests per second and it's configured for 800.</p> <p>I've played around with the Mysql settings (innodb) without any success. I checked the slow query log, process list, etc. and there's no sign of any slow queries or anything holding back the system. </p> <p>The only thing at the moment I can do to keep the system up to speed is disabling some database calls on a users visit. In other words limit the amount of database calls being made on each user visit. But the fact I have to do this just stuns me as the machines are nowhere near their limits.</p> <p>I checked disk usage with iotop and it's not much either. Both servers are double AMD 16 core machines with Vertex 4 SSD's and 16GB RAM.</p> <p>I'm starting to be a bit lost so I'm wondering: do you guys have any suggestions on what else I can try or look for?</p> <p>Small update: If I run the following query it comes up with 21343. Which could mean my innodb_buffer_pool_size of 12G is too low? Is it possible I'm having problems with this?</p> <pre><code>SELECT CEILING(Total_InnoDB_Bytes/POWER(1024,2)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A; </code></pre> <p>Here are my config setup,top and iftop for both machines.</p> <pre><code>Apache: Timeout 30 KeepAlive On MaxKeepAliveRequests 0 KeepAliveTimeout 2 StartServers 100 MinSpareServers 100 MaxSpareServers 200 MaxClients 1000 MaxRequestsPerChild 0 Mysql: default-character-set = utf8 skip-external-locking skip-name-resolve skip-locking open_files_limit = 102400 wait_timeout = 30 interactive_timeout = 30 key_buffer = 16M max_allowed_packet = 16M thread_stack = 256K thread_cache_size = 50 max_connections = 50000 table_cache = 4096 thread_concurrency = 8 innodb_thread_concurrency = 8 innodb_buffer_pool_size=12G innodb_additional_mem_pool_size=10M innodb_log_file_size=1500M innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit = 0 tmp_table_size = 128M max_heap_table_size = 128M query_cache_limit = 5M query_cache_size = 128M log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 3 Top output apacheserver: Tasks: 842 total, 1 running, 841 sleeping, 0 stopped, 0 zombie Cpu(s): 3.0%us, 0.5%sy, 0.0%ni, 96.3%id, 0.0%wa, 0.0%hi, 0.2%si, 0.0%st Mem: 16446884k total, 11921852k used, 4525032k free, 183140k buffers Swap: 9541624k total, 58596k used, 9483028k free, 7439992k cached Top output mysql server: Tasks: 197 total, 1 running, 196 sleeping, 0 stopped, 0 zombie Cpu(s): 0.2%us, 0.2%sy, 0.0%ni, 99.5%id, 0.0%wa, 0.0%hi, 0.1%si, 0.0%st Mem: 16446544k total, 16197496k used, 249048k free, 147632k buffers Swap: 16755756k total, 277020k used, 16478736k free, 1976760k cached iftop apache server: TX: cumm: 5.68MB peak: 4.84Mb rates: 4.56Mb 3.11Mb 3.25Mb RX: 973KB 647Kb 643Kb 556Kb 556Kb TOTAL: 6.63MB 5.48Mb 5.19Mb 3.65Mb 3.79Mb iftop mysql server TX: cumm: 954B peak: 1.36Kb rates: 0b 541b 347b RX: 4.59KB 4.69Kb 4.69Kb 2.89Kb 1.67Kb TOTAL: 5.52KB 4.69Kb 4.69Kb 3.41Kb 2.01Kb </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload