Web Hosting Talk : Web Hosting Main Forums : Hosting Security and Technology : MySQL Optimization, Intel E8300, 8Gb RAM, cPanel

[ovisopa]

Hello.

I'm trying to optimize a server (Intel E8300) with 8Gb of RAM and WHM/cPanel administration panel.

Now the server has only 46 accounts in WHM, and about 10.000 unique visitors / day

As mysql is always on top for CPU ussage I have edited 2 values in my.cnf, but this was done in 10 minutes and not to much research, any more help will be appreciated. On this server as I see it's been using MyISAM for storage engine and maybe just a few tabels with InnoDB

(new config............................................................../..............................................prev config)

PHP Code:

[mysqld]                                               [mysqld]
set-variable = max_connections=500                     set-variable = max_connections=500
safe-show-database                                     safe-show-database
log-slow-queries = /var/log/mysql-slow.log             log-slow-queries = /var/log/mysql-slow.log
long_query_time = 5                                    long_query_time = 5
query_cache_limit=8M                                   query_cache_limit=8M
query_cache_size=512M                                  query_cache_size=512M
query_cache_type=1                                     query_cache_type=1
tmp_table_size=1024M                                   tmp_table_size=1024M
ft_min_word_len = 3                                    ft_min_word_len = 3
sort_buffer_size=16M                                   sort_buffer_size=16M
read_buffer_size=1M                                    read_buffer_size=1M
key_buffer=32M                                         key_buffer=32M
key_buffer_size=1024M                                  key_buffer_size=32M
join_buffer=4M                                         join_buffer=4M
table_cache=1024                                       table_cache=512 


Bellow I will write the lines from MySQL Status page (phpMyAdmin) which ware highlighted with RED

PHP Code:

MySQL is running for 52 days

Slow_queries                2,789
Innodb_buffer_pool_reads    56
Innodb_row_lock_waits        5
Handler_read_rnd            63 M
Handler_read_rnd_next        4,108.81 M
Qcache_lowmem_prunes        1,010 k
Slow_launch_threads        11
Created_tmp_disk_tables        883 k
Select_full_join            4,807
Sort_merge_passes            1
Opened_tables                21 k
Table_locks_waited            369 k 


Bellow is the result of top command, what I don't understand is why here I see such a big CPU usage for mysql process (69%) and the Server status shows just 1.59, 1.40, 1.24

PHP Code:

top - 10:47:30 up 306 days, 11:01,  1 user,  load average: 1.59, 1.40, 1.24
Tasks: 168 total,   1 running, 166 sleeping,   0 stopped,   1 zombie
Cpu(s): 23.2%us, 18.0%sy,  0.0%ni, 55.3%id,  3.2%wa,  0.0%hi,  0.3%si,  0.0%st
Mem:   8301108k total,  7545036k used,   756072k free,   293272k buffers
Swap:  4192956k total,       92k used,  4192864k free,  6160040k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 5417 mysql     15   0  733m 602m 5380 S   69  7.4  21319:40 mysqld
 9603 root      15   0  5580 4036 2416 S    1  0.0   2:06.47 authProg
19089 nobody    15   0 14664 5800 1680 S    0  0.1   0:00.18 httpd
22341 nobody    15   0 14664 5796 1680 S    0  0.1   0:00.04 httpd
22465 mailnull  15   0 11180 3708 2452 S    0  0.0   0:00.09 exim
22666 nobody    15   0 14664 5688 1636 S    0  0.1   0:00.01 httpd
    1 root      15   0  2064  592  512 S    0  0.0   0:22.48 init
    2 root      RT  -5     0    0    0 S    0  0.0   0:25.41 migration/0
....
... 


Thank you in advance

[jphilipson]

I would recommend using mysql-tuner to properly adjust your MySQL. Also check your mysql error log (on cPanel servers will be in /var/lib/mysql/hostname.domain.tld.err) to see if anything jumps out. Also, that behavior is not abnormal at all, I usually see it when MySQL is automatically repairing/optimizing db's etc.

[ovisopa]

I did used mysql-tunner but after I have changed few values in my.cnf and restarted the server. This was the first result of mysql-tunner

PHP Code:

 >>  MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>
 >>  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.77-community-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 273M (Tables: 420)
[--] Data in InnoDB tables: 1M (Tables: 21)
[!!] Total fragmented tables: 67

-------- Performance Metrics -------------------------------------------------
[--] Up for: 10m 7s (26K q [43.911 qps], 1K conn, TX: 33M, RX: 2M)
[--] Reads / Writes: 86% / 14%
[--] Total buffers: 1.5G global + 21.4M per thread (500 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 12.0G (151% of installed RAM)
[OK] Slow queries: 0% (0/26K)
[OK] Highest usage of available connections: 1% (6/500)
[OK] Key buffer size / total MyISAM indexes: 1.0G/122.5M
[OK] Key buffer hit rate: 99.2% (283K cached / 2K reads)
[OK] Query cache efficiency: 83.8% (18K cached / 21K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
[!!] Temporary tables created on disk: 43% (604 on disk / 1K total)
[!!] Thread cache is disabled
[OK] Table cache hit rate: 94% (97 open / 103 opened)
[OK] Open file limit used: 6% (168/2K)
[OK] Table locks acquired immediately: 99% (4K immediate / 4K locks)
[OK] InnoDB data size / buffer pool: 1.6M/8.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
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 1G)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4) 


I have started to defragment the tables that are fragmented using this method :

1. Open phpMyAdmin from WHM panel to have access on all databases. Selected the information_schema database, and than used this query:

PHP Code:

SELECT TABLE_SCHEMA, TABLE_NAME, DATA_FREE
FROM TABLES WHERE DATA_FREE >0
AND ENGINE = 'MyISAM'
AND DATA_FREE > DATA_LENGTH * 0.05 


first I used the DATA_LENGTH * 0.3 but after optimizing the tables shown by that query, mysql-tunner still sows the message that 50 tables are fragmented (initial there ware 67 fragmented tables)

2. Next step was to open every database in phpmyadmin and running this query :

PHP Code:

OPTIMIZE TABLE `table1`, `table2`, `table3` 


Is there a quicker method ?

Also, that behavior is not abnormal at all, I usually see it when MySQL is automatically repairing/optimizing db's etc.

In the 3rd image attached you can see mysql is RED and on top, that's in the stats of everyday, as you can see I have posted the image for 20 mai 2009.

[jphilipson]

If it's optimizing or repairing DB's, or dealing with constant errors, it will remain on the top of the process list. Did you check the mysql error log?

[ovisopa]

I did, the log is just 51k in size and it only contains those kind of messages :

PHP Code:

090727 12:13:26 [Note] /usr/sbin/mysqld: Normal shutdown

090727 12:13:42  InnoDB: Starting shutdown...
090727 12:13:44  InnoDB: Shutdown completed; log sequence number 0 358213533
090727 12:13:44 [Note] /usr/sbin/mysqld: Shutdown complete

090727 12:13:44  mysqld ended

090727 12:13:44  mysqld started
/usr/sbin/mysqld: File '/var/log/mysql-slow.log' not found (Errcode: 13)
090727 12:13:45 [ERROR] Could not use /var/log/mysql-slow.log for logging (error 13). Turning logging off for the whole duration of the MySQL server process. To turn it on again: fix the cause, shutdown the MySQL server and restart it.
090727 12:13:45  InnoDB: Started; log sequence number 0 358213533
090727 12:13:45 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.77-community-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Edition (GPL) 


I monitor my servers using munin, on this server as it has just 46 accounts I never seen anything in the MySQL Slow Query graph, the graph always was empty, only now 2 days ago it had a spike, 5 slow queries/second. Today I did tried to find the log and it wasn't there, I though mysql should have created it. I will create it now.

[ovisopa]

PHP Code:

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
    Set thread_cache_size to 4 as a starting value
Variables to adjust:
  *** MySQL's maximum memory usage is dangerously high ***
  *** Add RAM before increasing MySQL buffer variables ***
    tmp_table_size (> 1G)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4) 


Analizing the recomandations made by mysql-tunner I understand that I have to increase tmp_table_size, make it bigger than 1GB (currently is set to 1024M) but what about the message :

*** MySQL's maximum memory usage is dangerously high ***

????

[lemeneid]

I would change/add these variables in your config. You should also consider installing other programs like xcache/memcached and other app optimisers for the apps you run on your server.

key_buffer=4000M
table_cache=3000
read_buffer=4m
skip-locking
thread_concurrency=4

[ovisopa]

I DON'T UNDERSTAND this:

PHP Code:

[mysqld]
set-variable = max_connections=500
safe-show-database
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 5
query_cache_limit=8M
query_cache_size=512M
query_cache_type=1
tmp_table_size=1536M
ft_min_word_len = 3
sort_buffer_size=16M
read_buffer_size=1M
key_buffer=32M
key_buffer_size=1024M
join_buffer=4M
table_cache=1024
max_heap_table_size=256M
thread_cache_size=4 


How total Mysql memory ussage is calculated and why after setting tmp_table_size to 1536MB and restarted mysql I got this in the error log (and query_cache was disabled):

PHP Code:

090727 17:35:21  mysqld started
090727 17:35:21  InnoDB: Started; log sequence number 0 361029924
/usr/sbin/mysqld: Out of memory (Needed 950109184 bytes)
/usr/sbin/mysqld: Out of memory (Needed 712581120 bytes)
/usr/sbin/mysqld: Out of memory (Needed 534435840 bytes)
090727 17:35:21 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.77-community-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Edition (GPL) 


Now I have lowered
tmp_table_size=1024M
key_buffer_size=512M
max_heap_table_size=128M

PHP Code:

[mysqld]
set-variable = max_connections=500
safe-show-database
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 5
query_cache_limit=8M
query_cache_size=512M
query_cache_type=1
tmp_table_size=1024M
ft_min_word_len = 3
sort_buffer_size=16M
read_buffer_size=1M
key_buffer=32M
key_buffer_size=512M
join_buffer=4M
table_cache=1024
max_heap_table_size=128M
thread_cache_size=4 


but still getting the message out of memory in error log. Any help or advices ?

PHP Code:

090727 18:37:49  mysqld started
090727 18:37:49  InnoDB: Started; log sequence number 0 361073709
/usr/sbin/mysqld: Out of memory (Needed 475054080 bytes)
090727 18:37:49 [Note] /usr/sbin/mysqld: ready for connections. 


[ovisopa]

I have changed my.cnf to the one I had this morning, and the query_cache is still disabled, I don't have any errors in mysql log

PHP Code:

090727 19:19:06  mysqld started
090727 19:19:06  InnoDB: Started; log sequence number 0 361102620
090727 19:19:06 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.77-community-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  MySQL Community Edition (GPL) 


my current settings are

PHP Code:

[mysqld]
set-variable = max_connections=500
safe-show-database
log-slow-queries = /var/log/mysql-slow.log
long_query_time = 5
query_cache_limit=8M
query_cache_size=512M
query_cache_type=1
tmp_table_size=1024M
ft_min_word_len = 3
sort_buffer_size=16M
read_buffer_size=1M
key_buffer=32M
key_buffer_size=32M
join_buffer=4M
table_cache=512 


but tunning-primer.sh and mysqltunner.pl

reports:

QUERY CACHE
Query cache is supported but not enabled
Perhaps you should set the query_cache_size

Any advices ?

[RBBOT]

I would change/add these variables in your config. You should also consider installing other programs like xcache/memcached and other app optimisers for the apps you run on your server.

key_buffer=4000M

I wouldn't.

According to the output of the tuner, the total size of all the key blocks in all the indexes of all the databases on the server is 144Mb. Why would you want to allocate a 4Gb key buffer if only 3.6% of could be used? Unless you are predicting massive growth in your database size, I'd suggest a keybuffer of around 200Mb maximum.

To answer your question about how 69% mysql CPU gives a load average of 1.4, you have 2 cores, so flat-out usage would be 200%. Using 69% of one core means there isn't a backlog of other programs waiting to use the other core, and load average is a measure of how long the queue of processes waiting to run is, not a measure of processor usage %.

I wouldn't set tmp_table_size so high - if you have a SQL query that really needs to create a 1 gigabyte temporary table in an ordinary web application, then you probably have a buggy application - that sort of size temporary table would only be used in data-warehouse reporting applications. Mysql has bugs in the code that predicts what the maximum size of a temporary table could be so sometimes things it is going to need 1gb+ for a table that in actuality only turns out to be a few kb. Therefore it decides it is going to use a disk table no matter what sort of silly size you set tmp_table_size too. Its best to set it to something around the 32-64Mb mark.

Also you can remove "key_buffer" from your configuration - there is no such setting. key_buffer_size controls the size of the key buffer.

[ovisopa]

Originally Posted by RBBOT

key_buffer=4000M

I wouldn't.

According to the output of the tuner, the total size of all the key blocks in all the indexes of all the databases on the server is 144Mb. Why would you want to allocate a 4Gb key buffer if only 3.6% of could be used? Unless you are predicting massive growth in your database size, I'd suggest a keybuffer of around 200Mb maximum.

I just run both scripts again (tuning-primer.sh and mysqltuner.pl), now after 15 hours since the last restart.

PHP Code:

[OK] Key buffer size / total MyISAM indexes: 32.0M/121.3M
[OK] Key buffer hit rate: 99.9% (20M cached / 25K reads) 


and

PHP Code:

KEY BUFFER
Current MyISAM index space = 121 M
Current key_buffer_size = 32 M
Key cache miss rate is 1 : 748
Key buffer free ratio = 16 %
You could increase key_buffer_size
It is safe to raise this up to 1/4 of total system memory;
assuming this is a dedicated database server. 


I will try to increase it now to ~150M

Originally Posted by RBBOT

To answer your question about how 69% mysql CPU gives a load average of 1.4, you have 2 cores, so flat-out usage would be 200%. Using 69% of one core means there isn't a backlog of other programs waiting to use the other core, and load average is a measure of how long the queue of processes waiting to run is, not a measure of processor usage %.

thank you

Originally Posted by RBBOT

I wouldn't set tmp_table_size so high - if you have a SQL query that really needs to create a 1 gigabyte temporary table in an ordinary web application, then you probably have a buggy application - that sort of size temporary table would only be used in data-warehouse reporting applications....

TEMP TABLES
Current max_heap_table_size = 256 M
Current tmp_table_size = 1.00 G
Of 62800 temp tables, 37% were created on disk
Effective in-memory tmp_table_size is limited to max_heap_table_size.
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your
ratio of on disk temp tables.

What I didn't know until I have installed tuning-primer.sh script was that tables with BLOB and TEXT columns are not created into memory. I don't know if i can make the 37% percent lower

Originally Posted by RBBOT

Also you can remove "key_buffer" from your configuration - there is no such setting. key_buffer_size controls the size of the key buffer.

I have removed it now.

But I have another questions, in my previevous message I told you the query_cache was disabled on my server even if it should have been enabled by those lines :

query_cache_type=1
query_cache_size=256M
query_cache_limit=8M

Can you tell me why the query cache was disabled ? (attached an image, the period while query cache was automatically disabled and I was struggling to enable it) I had to lower the number of max connections from 500 to 300 and automatically Configured Max Memory Limit dropped to 6.57 G (Physical Memory : 7.91 G) . When i had 500 max connections the Configured memory limit was above the Physical memory, is this the reason why the Query_Cache was disabled automatically ? If yes, I don't understand why before the other day the query_cache was enabled even with 500 max connection and Configured Max Memory Limit : 11.47 G (out of 7.91 G total)

PHP Code:

CURRENT MEMORY USAGE
Max Memory Ever Allocated : 448 M
Configured Max Per-thread Buffers : 6.28 G
Configured Max Global Buffers : 298 M
Configured Max Memory Limit : 6.57 G
Physical Memory : 7.91 G
Max memory limit seem to be within acceptable norms 


[lvalics]

Usually on every new server, I start from this article
http://www.grafxsoftware.com/faq.php...ed-Server/1/4/

Here you can see a part of
* my.cnf-4.0
* my.cnf-5.0
* my.cnf-4.1
* my.cnf-5.1

This is a starting point for my.cnf and then of course after a while I run mysqltuner.pl

Hope this help.

[Phil_B]

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

see my topic: http://www.webhostingtalk.com/showthread.php?t=877813

It seems that when you add kernal PAE on x32 it increases the physical space but not virtual space so MySQL suffers and can only use a max of 2.5GB I think. Solution is to upgrade to x64 as long as your CPU can support this.

[ovisopa]

your suggested values:
interactive_timeout=10
wait_timeout=20
connect_timeout=20

my default values:
interactive timeout 28,800
wait timeout 28,800
connect_timeout=10

I will change the 28.800 values now, as it seems the default values are way to big for timeouts.

-------

There is another thing I want to ask you about those values, I have another server with just 2Gb RAM, and from time to time (let's say once in 2 weeks, especially Monday or Tuesday) the CPU usage was very very high, sometimes even 80, there ware lots of mysql connections and apache processes, but I think those waren't bunch of visitors coming in, just processes that my server couldn't satisfy because maybe a backup was running or something that used lot's of CPU power.

on that server I had those values for timeouts:
connect timeout 30
delayed insert timeout 300
interactive timeout 60
net read timeout 30
net write timeout 60
table lock wait timeout 50
wait timeout 60

If I lower, at least wait_timeout, connect_timeout and interactive_timeout it will help the server in moments where the server has very HIGH CPU usage ? it will free the resources quicker (even if it won't satisfy the website visitors)

The problem with high load, was always solved by restarting apache and mysql, but in the last ~30 days I never had to do it, I have changed server time, and now the backups are running at a different hour, when there is low traffic on the server.

------

[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

see my topic: http://www.webhostingtalk.com/showthread.php?t=877813

I think reinstalling the OS it will take the websites off line for few hours, and this is not an option now, as the problem is not URGENT, since september 2008 I never had to restart mysql or apache because of CPU usage problems, the server can handle the traffic (and it should because the traffic is not to high) but I wanted to optimize it before having problems, and btw. today it seems it's the first day in wich mySQL is not highlighted in red in WHM's CPU/Memory/MySQL Usage page.

maybe the optimization made yesterday it shows some good results now will see in the next few days.

Anyway, I still wait for some suggestions/answers regarding:

1. Can you tell me why the query cache was automatically disabled ? (even if query_cache_type=1 ; query_cache_size=256M; query_cache_limit=8M)
2. The questions above, regarding timeouts.

Thank you all