jpbazinet

Registered
Dec 18, 2011
3
0
51
cPanel Access Level
Root Administrator
I've had a massive server load for a few days now that I've been trying to troubleshoot.
There are a few sites on the server, but the major one seems to be findus which has a database of over 4 GB.

I've checked and rechecked it for corruption, and optimized.

Any help tuning this, would be appreciated.
(I've also run mysqltuner, but since i've just restarted mysql, it's output is not really relevant right now).

Server:
Code:
Processor #1VendorGenuineIntel [TABLE="class: datatable brick, width: 100%, align: center"]
[TR]
[TD="class: cell, align: left"]NameIntel(R) Core(TM)2 Quad CPU    Q9400  @ 2.66GHz Speed2666.904 MHz Cache3072 KB
[/TD]
[/TR]
[/TABLE]
My process manager:

-removed by infopro-


Top:

Code:
Tasks: 321 total,   1 running, 312 sleeping,   0 stopped,   8 zombie
Cpu(s): 47.2%us, 21.1%sy,  0.0%ni, 31.4%id,  0.1%wa,  0.0%hi,  0.2%si,  0.0%st
Mem:   6096036k total,  5201700k used,   894336k free,   241548k buffers
Swap:  2096472k total,      152k used,  2096320k free,  3144856k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
12686 mysql     15   0 1193m 542m 4188 S 252.3  9.1  48:05.92 mysqld
19834 findus    16   0     0    0    0 Z  1.0  0.0   0:00.05 php <defunct>
13291 root      15   0 13000 1428  820 R  0.7  0.0   0:03.11 top
14242 nobody    15   0 84828 9864 1880 S  0.7  0.2   0:00.19 httpd
19860 findus    18   0     0    0    0 Z  0.7  0.0   0:00.04 php <defunct>
20198 findus    17   0 99388  10m 5596 S  0.7  0.2   0:00.02 php
20199 findus    16   0     0    0    0 Z  0.7  0.0   0:00.02 php <defunct>
20204 findus    17   0 99388  10m 5596 S  0.7  0.2   0:00.02 php
14317 nobody    15   0 80496 6348 2664 S  0.3  0.1   0:00.28 httpd
19528 nobody    15   0 79804 4664 1756 S  0.3  0.1   0:00.01 httpd
19688 nobody    15   0 79800 4712 1852 S  0.3  0.1   0:00.02 httpd
    1 root      15   0 10356  688  576 S  0.0  0.0   0:01.62 init
    2 root      RT  -5     0    0    0 S  0.0  0.0   0:01.93 migration/0
    3 root      34  19     0    0    0 S  0.0  0.0   0:06.47 ksoftirqd/0
    4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
    5 root      RT  -5     0    0    0 S  0.0  0.0   0:02.13 migration/1
    6 root      34  19     0    0    0 S  0.0  0.0   0:02.63 ksoftirqd/1
    7 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1
    8 root      RT  -5     0    0    0 S  0.0  0.0   0:00.93 migration/2
    9 root      34  19     0    0    0 S  0.0  0.0   0:01.79 ksoftirqd/2
   10 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/2
   11 root      RT  -5     0    0    0 S  0.0  0.0   0:03.72 migration/3
   12 root      34  19     0    0    0 S  0.0  0.0   0:01.00 ksoftirqd/3
   13 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/3
my.cnf:

Code:
[mysqld]
wait_timeout = 100
# connection_timeout = 10
interactive_timeout = 100
max_connections = 3000
max_user_connection = 600
tmp_table_size = 64M
max_heap_table_size = 64M
key_buffer_size = 384M
innodb_buffer_pool_size = 76M
join_buffer_size = 6M
thread_cache_size = 128
query_cache_size = 200M
table_cache = 1024
ft_min_word_len = 3
# The following can be used as easy to replay backup logs or for replication.
server-id               = 3
log_bin                 = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
expire_logs_days        = 10
max_binlog_size         = 100M
binlog-ignore-db = mysql,information_schema,business_main,mycontac_main,sharebar_test,findrest_main,findnumb_dandb,findnumb_news
 
Last edited by a moderator:

jpbazinet

Registered
Dec 18, 2011
3
0
51
cPanel Access Level
Root Administrator
If you again notice high server load then run command and optimize the database responsible for high server load.
The load never goes down... it ranges between 14 and 150.

I've already optimized.

Code:
+-------+--------------+-----------+-------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| Id    | User         | Host      | db          | Command | Time | State          | Info                                                                                                 |
+-------+--------------+-----------+-------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
| 15265 | findus_admin | localhost | findus_main | Sleep   | 580  |                |                                                                                                      |
| 16371 | findus_admin | localhost | findus_main | Sleep   | 240  |                |                                                                                                      |
| 16590 | findus_admin | localhost | findus_main | Sleep   | 79   |                |                                                                                                      |
| 16593 | findus_admin | localhost | findus_main | Sleep   | 71   |                |                                                                                                      |
| 16611 | findus_admin | localhost | findus_main | Sleep   | 75   |                |                                                                                                      |
| 16678 | findus_admin | localhost | findus_main | Sleep   | 65   |                |                                                                                                      |
| 16685 | findus_admin | localhost | findus_main | Sleep   | 156  |                |                                                                                                      |
| 16799 | findus_admin | localhost | findus_main | Sleep   | 60   |                |                                                                                                      |
| 16822 | findus_admin | localhost | findus_main | Sleep   | 42   |                |                                                                                                      |
| 16868 | findus_admin | localhost | findus_main | Sleep   | 33   |                |                                                                                                      |
| 16884 | findus_admin | localhost | findus_main | Sleep   | 24   |                |                                                                                                      |
| 17008 | findus_admin | localhost | findus_main | Sleep   | 21   |                |                                                                                                      |
| 17060 | findus_admin | localhost | findus_main | Sleep   | 59   |                |                                                                                                      |
| 17065 | findus_admin | localhost | findus_main | Sleep   | 58   |                |                                                                                                      |
| 17073 | findus_admin | localhost | findus_main | Sleep   | 4    |                |                                                                                                      |
| 17077 | findus_admin | localhost | findus_main | Sleep   | 5    |                |                                                                                                      |
| 17090 | findus_admin | localhost | findus_main | Sleep   | 70   |                |                                                                                                      |
| 17097 | findus_admin | localhost | findus_main | Sleep   | 5    |                |                                                                                                      |
| 17105 | findus_admin | localhost | findus_main | Sleep   | 4    |                |                                                                                                      |
| 17127 | findus_admin | localhost | findus_main | Sleep   | 0    |                |                                                                                                      |
| 17129 | findus_admin | localhost | findus_main | Query   | 78   | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17146 | findus_admin | localhost | findus_main | Sleep   | 4    |                |                                                                                                      |
| 17149 | findus_admin | localhost | findus_main | Query   | 69   | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17178 | findus_admin | localhost | findus_main | Query   | 52   | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17190 | findus_admin | localhost | findus_main | Query   | 52   | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17214 | findus_admin | localhost | findus_main | Query   | 48   | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17218 | findus_admin | localhost | findus_main | Query   | 47   | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17233 | findus_admin | localhost | findus_main | Query   | 45   | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17251 | findus_admin | localhost | findus_main | Query   | 36   | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17254 | findus_admin | localhost | findus_main | Query   | 35   | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17278 | findus_admin | localhost | findus_main | Sleep   | 5    |                |                                                                                                      |
| 17280 | findus_admin | localhost | findus_main | Query   | 20   | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17281 | findus_admin | localhost | findus_main | Query   | 18   | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17286 | findus_admin | localhost | findus_main | Sleep   | 6    |                |                                                                                                      |
| 17295 | findus_admin | localhost | findus_main | Query   | 12   | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17304 | findus_admin | localhost | findus_main | Query   | 8    | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17306 | findus_admin | localhost | findus_main | Query   | 7    | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17307 | findus_admin | localhost | findus_main | Query   | 7    | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17309 | findus_admin | localhost | findus_main | Query   | 5    | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17312 | findus_admin | localhost | findus_main | Query   | 4    | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17315 | findus_admin | localhost | findus_main | Query   | 3    | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17316 | findus_admin | localhost | findus_main | Query   | 3    | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17317 | findus_admin | localhost | findus_main | Sleep   | 0    |                |                                                                                                      |
| 17318 | findus_admin | localhost | findus_main | Query   | 2    | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17319 | findus_admin | localhost | findus_main | Query   | 0    | Sorting result | SELECT
                                                                 bl.id, bl.company, bl.address, bl.city, bl.province, bl.postal_code, bl.phone, bl.w |
| 17320 | root         | localhost |             | Query   | 0    |                | show processlist                                                                                     |
+-------+--------------+-----------+-------------+---------+------+----------------+------------------------------------------------------------------------------------------------------+
 

alphawolf50

Well-Known Member
Apr 28, 2011
186
2
68
cPanel Access Level
Root Administrator
We'll need to see your mysqltuner report before we can give any decent recommendations... but a couple things that stand out right now:

  • The findus_main database seems to be spending a lot of time sorting running the same (or nearly the same) query. Proper indexing or rewriting the query could solve your issue.
  • You said the findus db is over 4GB.. but I noticed you innodb_buffer_pool and key_buffer_pool are miniscule in comparison. Mysqltuner will let me know for sure, but I have a feeling one of those two values needs to be increased.
  • It seems like max_connections and max_user_connection are unusually high... but we'll need to see mysqltuner to confirm.
  • Your join_buffer_size is likely too high and your system is wasting cycles allocating/deallocating more memory than it needs. MySQL is a little "funny" in how it allocates the join buffer -- it determines how much memory it needs for the join, and then allocates the larger of that number or your join_buffer_size. So if it determines it only needs 128K to do the join, it'll allocate 6M anyway because of that setting. Worse yet, it'll allocate 6M per-join, per-thread. In most cases, you're better off setting this to something like 256K and letting MySQL decide when it actually needs more.

Anyway, please post the results of mysqltuner.pl as soon as it's been 24 hours, and we'll see how we can help.
 

jpbazinet

Registered
Dec 18, 2011
3
0
51
cPanel Access Level
Root Administrator
I did a lot more research and basically started over with a blank my.cnf file.
Things have been much better (no more loads of 150) but still need work (varies from 1.00 to 6.00).

Here is my current my.cnf

Code:
[mysqld]
max_connections = 500
thread_cache_size = 4
table_cache = 1024
query_cache_limit = 1M
query_cache_size = 17M
query_cache_type = 1
key_buffer_size = 2G
read_buffer_size = 128K
join_buffer_size = 128K
innodb_buffer_pool_size = 60M
# The following can be used as easy to replay backup logs or for replication.
server-id               = 3
log_bin                 = /var/log/mysql/mysql-bin.log
# WARNING: Using expire_logs_days without bin_log crashes the server! See README.Debian!
expire_logs_days        = 10
max_binlog_size         = 100M
binlog-ignore-db = mysql,information_schema,business_main,mycontac_main,sharebar_test,findrest_main,findnumb_dandb,findnumb_news
log-slow-queries=/var/lib/mysql/slow.log
And here is my mysqltuner for the last 19 hours.

Code:
 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.92-community-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 2G (Tables: 458)
[--] Data in InnoDB tables: 53M (Tables: 203)
[!!] Total fragmented tables: 14

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 16h 16m 44s (4M q [75.351 qps], 154K conn, TX: 12B, RX: 557M)
[--] Reads / Writes: 88% / 12%
[--] Total buffers: 2.1G global + 2.7M per thread (500 max threads)
[OK] Maximum possible memory usage: 3.4G (59% of installed RAM)
[OK] Slow queries: 0% (1K/4M)
[OK] Highest usage of available connections: 6% (30/500)
[OK] Key buffer size / total MyISAM indexes: 2.0G/1.8G
[OK] Key buffer hit rate: 100.0% (1B cached / 170K reads)
[OK] Query cache efficiency: 46.3% (1M cached / 3M selects)
[!!] Query cache prunes per day: 2417908
[OK] Sorts requiring temporary tables: 0% (4K temp sorts / 832K sorts)
[!!] Temporary tables created on disk: 27% (78K on disk / 287K total)
[OK] Thread cache hit rate: 94% (7K created / 154K connections)
[OK] Table cache hit rate: 72% (1K open / 1K opened)
[OK] Open file limit used: 28% (1K/4K)
[OK] Table locks acquired immediately: 99% (2M immediate / 2M locks)
[OK] InnoDB data size / buffer pool: 53.2M/60.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    query_cache_size (> 17M)
    tmp_table_size (> 32M)
    max_heap_table_size (> 16M)
 

alphawolf50

Well-Known Member
Apr 28, 2011
186
2
68
cPanel Access Level
Root Administrator
That looks MUCH better :)

Couple of suggestions:

  • I'd bump thread_cache_size up to 32.
  • max_heap_table_size needs to be increased to at least 32M. The reason is tmp_table_size is actually limited by max_heap_table_size, so even though tmp_table_size is currently 32M, it can only use the 16M defined by max_heap_table_size. This will lower your "Temporary tables created on disk".
  • query_cache_size could stand to be larger... Try 32M. The cache generally doesn't hurt performance until it is well over 100M, so for now it is safe to increase the cache to keep your query cahce prunes at a minimum.
  • Keep an eye on "Table cache hit rate". If that starts dropping, you need to increase table_cache. 1536 or 2048 might be good numbers.
  • Keep an eye on "Highest usage of available connections". If you're never using more than a small fraction of your available connections, it is safe to decrease max_connections accordingly. Currently your max connections were 6% of the available connections -- if that holds, you might consider dropping max_connections to around 100.

One last thing -- you do want to wait until mysqltuner says that mysql has been up for at least 24hr (48 is even better) before posting results. In the example you posted, it had only been up for 16hr. Once MySQL is happy, you'll want to take a look at optimizing Apache as well.