homepage Welcome to WebmasterWorld Guest from 54.147.248.118
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Hardware and OS Related Technologies / Website Technology Issues
Forum Library, Charter, Moderators: phranque

Website Technology Issues Forum

    
Dedicated server - MySQL using a lot of CPU resources
hanyaz

5+ Year Member



 
Msg#: 3945488 posted 10:07 am on Jul 3, 2009 (gmt 0)

Hello,
I have a dedicated server where i am hosting about 12 sites. Most of them are joomla sites.
The database in some of them is huge...when i launched two or three of them everything was ok, running and loading normally but today i have noticed an important slowdown for some of them without any reason.
I have looked at my logs, nothing special...when i taped "htop" in putty to see the various users running, i have found that mysql is using a lot of CPU ressources, so i am bit confused. Dunno where to strart to solve this
can anybody advice me on how to do in such a situation?
Thanks

 

g1smd

WebmasterWorld Senior Member g1smd us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 3945488 posted 10:26 am on Jul 3, 2009 (gmt 0)

*** without any reason .... mysql is using a lot of CPU resources ***

There's your reason right there. Maybe time to upgrade your hosting, or move some sites to a different server.

Before that, do make sure that all of your code is as optimised as possible. Poor PHP and/or mySQL code may be creating more work than is necessary.

Frank_Rizzo

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3945488 posted 2:01 pm on Jul 3, 2009 (gmt 0)

What are the hardware specs? As g1smd says it may be time to upgrade.

Run mysqltuner.pl and see the recommendations. Could be that your memory needs tuning.

Can some of the database be archived? When did you last optimise / analyse / repair the tables?

hanyaz

5+ Year Member



 
Msg#: 3945488 posted 2:34 pm on Jul 3, 2009 (gmt 0)

My server specifications :
Server information
Responsive True
Server banner Apache/2.0.59 (Unix) mod_ssl/2.0.59 OpenSSL/0.9.8g
Server OS Unix
Server technologies PHP,mod_ssl,OpenSSL

Further details :

Hardware
Processor Intel Core2Duo
2x 2.33+ GHz
3 Mo L2 - FSB 1066 MHz
Architecture64 bits
Memory 2 Go DDR2
hard disc 2x 500 Go
type SATA2 - RAID 0/1

i have run the script you adviced here is the output :

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

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 534M (Tables: 2169)
[!] Total fragmented tables: 101

-------- Performance Metrics -------------------------------------------------
[--] Up for: 23d 5h 30m 8s (2B q [1K qps], 1M conn, TX: 892B, RX: 370B)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 58.0M global + 1.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 219.7M (11% of installed RAM)
[OK] Slow queries: 0% (42K/2B)
[!] Highest connection usage: 100% (101/100)
[OK] Key buffer size / total MyISAM indexes: 16.0M/92.8M
[OK] Key buffer hit rate: 99.7% (15B cached / 50M reads)
[!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (879K temp sorts / 1B sorts)
[!] Joins performed without indexes: 169473
[OK] Temporary tables created on disk: 0% (554K on disk / 991M total)
[!] Thread cache is disabled
[!] Table cache hit rate: 0% (64 open / 5M opened)
[OK] Open file limit used: 11% (117/1K)
[OK] Table locks acquired immediately: 99% (3B immediate / 3B locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Enable the slow query log to troubleshoot bad queries
Reduce or eliminate persistent connections to reduce connection usage
Adjust your join queries to always utilize indexes
Set thread_cache_size to 4 as a starting value
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
max_connections (> 100)
wait_timeout (< 28800)
interactive_timeout (< 28800)
query_cache_size (>= 8M)
join_buffer_size (> 128.0K, or always use indexes with joins)
thread_cache_size (start at 4)
table_cache (> 64)

thanks

hanyaz

5+ Year Member



 
Msg#: 3945488 posted 2:35 pm on Jul 3, 2009 (gmt 0)

Can some of the database be archived?
all of them need to run

When did you last optimise / analyse / repair the tables?
just now and all the messages were ok

Frank_Rizzo

WebmasterWorld Senior Member 10+ Year Member



 
Msg#: 3945488 posted 10:34 am on Jul 4, 2009 (gmt 0)

The lack of thread cache could be the main problem.

Make a copy of your my.cnf file and make some changes. Either edit or add the following:

[mysqld]
thread_cache_size = 4
key_buffer = 100M
table_cache = 640
query_cache_size = 32M

Restart mysql and run the test again after 5 mins to check the following:

[--] Total buffers: 58.0M global + 1.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 219.7M (11% of installed RAM)

What we want to do is to give mysql more of the available RAM but not too much such that other services on the server suffer.

Run the test again a day later and check the thread cache, connections, timeouts etc.

I would look at increasing further the thread_cache_size and the query_cache_size.

hanyaz

5+ Year Member



 
Msg#: 3945488 posted 4:52 pm on Jul 4, 2009 (gmt 0)

Hello,
i have looked in the forum of my hosting company and found somebody advicing to insert the following settings in the my.cnf file :

table_cache = 4096
thread_cache_size = 64
key_buffer = 64M
query_cache_size = 64M
join_buffer_size = 256K

I made the changes you adviced me, restarted mysql and run again the mysqltuner.pl file here is the output :

>> MySQLTuner 1.0.0 - Major Hayden <major@mhtx.net>
>> Bug reports, feature requests, and downloads at [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.44
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: -Archive -BDB -Federated -InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 579M (Tables: 2179)
[!] Total fragmented tables: 112

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2h 54m 17s (27M q [2K qps], 11K conn, TX: 13B, RX: 4B)
[--] Reads / Writes: 91% / 9%
[--] Total buffers: 174.0M global + 1.6M per thread (100 max threads)
[OK] Maximum possible memory usage: 335.7M (16% of installed RAM)
[OK] Slow queries: 0% (1/27M)
[OK] Highest usage of available connections: 9% (9/100)
[OK] Key buffer size / total MyISAM indexes: 100.0M/103.5M
[OK] Key buffer hit rate: 100.0% (97M cached / 27K reads)
[OK] Query cache efficiency: 52.8% (13M cached / 26M selects)
[!] Query cache prunes per day: 126299
[OK] Sorts requiring temporary tables: 0% (6K temp sorts / 12M sorts)
[!] Joins performed without indexes: 1937
[OK] Temporary tables created on disk: 0% (4K on disk / 12M total)
[OK] Thread cache hit rate: 98% (150 created / 11K connections)
[OK] Table cache hit rate: 98% (469 open / 475 opened)
[OK] Open file limit used: 60% (837/1K)
[OK] Table locks acquired immediately: 99% (25M immediate / 25M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Enable the slow query log to troubleshoot bad queries
Adjust your join queries to always utilize indexes
Variables to adjust:
query_cache_size (> 32M)
join_buffer_size (> 128.0K, or always use indexes with joins)

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Hardware and OS Related Technologies / Website Technology Issues
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved