High server load, need help with my.cnf & slowquery log

howzit

Member
Feb 20, 2012
22
0
51
Howzit! Free Advertising Classifieds
cPanel Access Level
Website Owner
The new mysqltuner report:
Code:
Last login: Wed Feb 22 16:57:38 2012 from dsl-146-13-70.telkomadsl.co.za
[email protected] [~]# ./mysqltuner.pl

 >>  MySQLTuner 1.0.1 - 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 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 447M (Tables: 425)
[!!] Total fragmented tables: 14

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 1h 7m 35s (13M q [147.214 qps], 275K conn, TX: 4B, RX: 2B)
[--] Reads / Writes: 78% / 22%
[--] Total buffers: 454.0M global + 1.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 1.0G (51% of installed RAM)
[OK] Slow queries: 1% (165K/13M)
[OK] Highest usage of available connections: 12% (63/500)
[OK] Key buffer size / total MyISAM indexes: 220.0M/204.2M
[OK] Key buffer hit rate: 100.0% (7B cached / 283K reads)
[OK] Query cache efficiency: 77.2% (9M cached / 11M selects)
[!!] Query cache prunes per day: 153011
[OK] Sorts requiring temporary tables: 0% (619 temp sorts / 1M sorts)
[!!] Joins performed without indexes: 57312
[!!] Temporary tables created on disk: 48% (1M on disk / 3M total)
[OK] Thread cache hit rate: 99% (337 created / 275K connections)
[OK] Table cache hit rate: 50% (861 open / 1K opened)
[OK] Open file limit used: 50% (1K/2K)
[OK] Table locks acquired immediately: 99% (6M immediate / 6M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    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 (> 128M)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 96M)
    max_heap_table_size (> 96M)

[email protected] [~]#
The total DB I am working with is about 100Mb
 
Last edited:

srpurdy

Well-Known Member
Jun 1, 2011
101
0
66
cPanel Access Level
Root Administrator
Okay Make these changes

Put this back
tmp_table_size = 64M
max_heap_table_size = 64M

[myisamchk]
key_buffer_size = 96M
sort_buffer_size = 96M
read_buffer_size = 16M
write_buffer_size = 16M

What kind of load are you getting now?
 

howzit

Member
Feb 20, 2012
22
0
51
Howzit! Free Advertising Classifieds
cPanel Access Level
Website Owner
Load Averages: 0.79 0.57 0.59
At this stage there is not much traffic so its difficult to say if its a good sign or not.

my.cnf

Code:
[mysqld]
skip-innodb
max_connections = 500
key_buffer_size = 220M
myisam_sort_buffer_size = 64M
join_buffer_size = 256K
read_buffer_size = 256K
sort_buffer_size = 256K
table_cache = 1024
thread_cache_size = 16
wait_timeout = 1800
max_allowed_packet = 16M
query_cache_limit = 1M
query_cache_size = 128M
query_cache_type = 1
log-queries-not-using-indexes
log-slow-queries=/var/lib/mysql/slow.log
tmp_table_size = 64M
max_heap_table_size = 64M

[mysqld_safe]
err-log=/var/log/mysqld.log
open_files_limit = 8192

[mysqldump]
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 96M
sort_buffer_size = 96M
read_buffer_size = 16M
write_buffer_size = 16M

[mysql.server]
user=mysql
 

srpurdy

Well-Known Member
Jun 1, 2011
101
0
66
cPanel Access Level
Root Administrator
Okay those numbers do look better, but if you can check on a higher traffic time see how that is. :)

At this point I'd let mysql run for a few days.
 

howzit

Member
Feb 20, 2012
22
0
51
Howzit! Free Advertising Classifieds
cPanel Access Level
Website Owner
Howzit Shawn.

What do you make of the latest mysqltuner.pl? I must say... My server is much more stable... Thanks

Code:
-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.0.92-community-log
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 442M (Tables: 425)
[!!] Total fragmented tables: 15

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3d 15h 47m 5s (47M q [149.149 qps], 735K conn, TX: 3B, RX: 3B)
[--] Reads / Writes: 71% / 29%
[--] Total buffers: 422.0M global + 1.2M per thread (500 max threads)
[OK] Maximum possible memory usage: 1015.8M (50% of installed RAM)
[OK] Slow queries: 1% (686K/47M)
[OK] Highest usage of available connections: 22% (110/500)
[OK] Key buffer size / total MyISAM indexes: 220.0M/203.0M
[OK] Key buffer hit rate: 100.0% (16B cached / 726K reads)
[OK] Query cache efficiency: 83.1% (34M cached / 42M selects)
[!!] Query cache prunes per day: 198366
[OK] Sorts requiring temporary tables: 0% (1K temp sorts / 4M sorts)
[!!] Joins performed without indexes: 384585
[!!] Temporary tables created on disk: 48% (3M on disk / 7M total)
[OK] Thread cache hit rate: 99% (204 created / 735K connections)
[OK] Table cache hit rate: 38% (1K open / 2K opened)
[OK] Open file limit used: 57% (1K/2K)
[OK] Table locks acquired immediately: 99% (18M immediate / 18M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Adjust your join queries to always utilize indexes
    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 (> 128M)
    join_buffer_size (> 256.0K, or always use indexes with joins)
    tmp_table_size (> 64M)
    max_heap_table_size (> 64M)

[email protected] [~]#
 

srpurdy

Well-Known Member
Jun 1, 2011
101
0
66
cPanel Access Level
Root Administrator
Sorry for the late reply,

Does look better. I'm wondering if the queries getting flagged as a query without a join are maybe bogus. If performance and reliability seem good there's not a lot more you can do right now. increasing the join_buffer_size usually has a performance decrease rather than an increase. Sometimes select(*) queries can show as queries without an index. So you should avoid using those also for memory reasons as well.
 

howzit

Member
Feb 20, 2012
22
0
51
Howzit! Free Advertising Classifieds
cPanel Access Level
Website Owner
Does look better.
Thanks, the server is more stable and I am trying my best to kill all the Slow Queries but have a few Questions...

Why would a query like this appear as a slow query if there is indexes?:
Code:
# Time: 120313 12:49:46
# Query_time: 0  Lock_time: 0  Rows_sent: 11  Rows_examined: 23
SELECT * FROM adcat WHERE id!='8' ORDER by sortorder,id;
And why would it note 23 rows Examined if there is only 12 rows in that table?

I have another example attach as a pic that I don't understand...
Indexes are available but not being used!?!?!?

slow.gif
 
Last edited:

eva2000

Well-Known Member
Aug 14, 2001
346
19
318
Brisbane, Australia
cPanel Access Level
Root Administrator
Twitter
Thanks, the server is more stable and I am trying my best to kill all the Slow Queries but have a few Questions...

Why would a query like this appear as a slow query if there is indexes?:
Code:
# Time: 120313 12:49:46
# Query_time: 0  Lock_time: 0  Rows_sent: 11  Rows_examined: 23
SELECT * FROM adcat WHERE id!='8' ORDER by sortorder,id;
And why would it note 23 rows Examined if there is only 12 rows in that table?
--log-queries-not-using-indexes means queries which do full scan of all rows in a table which is opposite of what an index would do limiting the number of rows returned. So that query appears in slow query log because it's a full index scan or doesn't have indexes, meaning it's a scan using an index but it returned all rows in that table instead of limiting the number of rows returned. It just highlights that you don't always benefit from indexes if number of rows return is small or end up being all rows in the table.

don't know the explain output of the first query, but the second one with screenshot attachment show no indexes used.
 
Last edited: