Hey all,
I have a couple of questions, which I think are linked to my site being slow.
1) Why is mysql running at 100% cpu... there is plenty of memory?
2) why do I have 300+ locked queries at any particular time?
The queries are:
INSERT INTO Sessions (session_id,usr_id,last_time)
DELETE FROM Sessions WHERE last_time + INTERVAL 5 DAY < NOW() *a lot of them
The site was running fine up until yesterday... so I am flummoxed.
Have been messing with tunning the database .. etc...
Any ideas?
Thanks
E
XFileSharing Pro - mysql at 100% CPU and averaging 300+ locked queries Help :)
-
- Posts: 521
- Joined: Dec 19, 2009 5:12 pm
my mysql is running on 145% cpu but it still runs fine
the cpu is because of the requests it gets and the time it need to handle
here is a my.conf file you can use to see if it improves
in my case located at /etc/my.conf ( centos + cpanel )
my server got 12GB ram and 8 cpu's (4 psychical and 4 treads @ intel I7 920 )
so hopely this config can help you to or helps you to tweak it
small sidenote , cpu has nothing related to memory , you can have a high memory usage and a low cpu usage or a high cpu usage and no memory usage , it depends what the program use and for what reason
Greetings From PowerChaos
The locks is because the script keeps writing sesions and keeps updating the sesions , so the table get locked to prevent making more open/close connections all the time ( persistent connection )mysql status wrote: cpu : 145 %
mem : 16.8 %
command: /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/server.powerchaos.com.pid --skip-external-locking
the cpu is because of the requests it gets and the time it need to handle
here is a my.conf file you can use to see if it improves
in my case located at /etc/my.conf ( centos + cpanel )
my server got 12GB ram and 8 cpu's (4 psychical and 4 treads @ intel I7 920 )
Code: Select all
#[mysqld]
#set-variable = max_connections=500
#log-slow-queries
#safe-show-database
[mysqld]
#skip-locking
#skip-networking
#safe-show-database
query_cache_limit=4M
query_cache_size=256M ## 32MB for every 1GB of RAM
query_cache_type=1
max_user_connections=350
max_connections=750
interactive_timeout=45
wait_timeout=30
connect_timeout=15
thread_cache_size=256
key_buffer= 320M ## 64MB for every 1GB of RAM
join_buffer=4M
max_connect_errors=10
max_allowed_packet=6M
table_cache=1024
record_buffer=4M
sort_buffer_size=4M ## 1MB for every 1GB of RAM
read_buffer_size=4M ## 1MB for every 1GB of RAM
read_rnd_buffer_size=4M ## 1MB for every 1GB of RAM
thread_concurrency=2 ## Number of CPUs x 2
myisam_sort_buffer_size=4M
server-id=1
long_query_time=45
collation-server=latin1_general_ci
[safe_mysqld]
open_files_limit=1500
[mysqldump]
quick
#max_allowed_packet=4M
[mysql]
#no-auto-rehash
#safe-updates
#[isamchk]
#key_buffer=640M
#sort_buffer=8M
#read_buffer=8M
#write_buffer=8M
[myisamchk]
#key_buffer=640M
#sort_buffer=8M
#read_buffer=8M
#write_buffer=8M
#[mysqlhotcopy]
#interactive-timeout
small sidenote , cpu has nothing related to memory , you can have a high memory usage and a low cpu usage or a high cpu usage and no memory usage , it depends what the program use and for what reason
Greetings From PowerChaos
Thanks for the answer!
I am delving into it now.
I also found a cool tool called mysqltuner.
wget http://mysqltuner.com/mysqltuner.pl
Part of the reason I believed the mysql cpu usage was the reason for my slow system, is because it wasn't using memory.. so I imagine it sitting there spinning it's wheels. But, perhaps not.
Again, thanks a lot!!!
e
I am delving into it now.
I also found a cool tool called mysqltuner.
wget http://mysqltuner.com/mysqltuner.pl
Part of the reason I believed the mysql cpu usage was the reason for my slow system, is because it wasn't using memory.. so I imagine it sitting there spinning it's wheels. But, perhaps not.
Again, thanks a lot!!!
e
-
- Posts: 521
- Joined: Dec 19, 2009 5:12 pm
well
for your information
the config is running fine for me (that config file i provided)
here is the output of the tuner
so maybe i can tweak it a little more
but from past experiance , following that guide let crash my mysql faster then at this point (now it seems to run stable)
so the tool can help a little , but its still the experiance that shows if it helps or not
and the best way is to test it out after you changed things until you feel that it is right
ofcourse the difference here is that it is from my webserver that contains more sites and that is a bit differend then just the file server ( there are also 2 fileservers running on my webserver , atleast the database side and no problems )
hopely it can help you
Greetings From PowerChaos
for your information
the config is running fine for me (that config file i provided)
here is the output of the tuner
Code: Select all
>> 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.95-community
[OK] Operating on 64-bit architecture
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB +Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 4G (Tables: 9190)
[--] Data in InnoDB tables: 26M (Tables: 206)
[--] Data in ARCHIVE tables: 44B (Tables: 2)
[--] Data in MEMORY tables: 12M (Tables: 25)
[!!] Total fragmented tables: 598
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 7d 5h 37m 49s (49M q [79.320 qps], 1M conn, TX: 195B, RX: 4B)
[--] Reads / Writes: 77% / 23%
[--] Total buffers: 602.0M global + 16.2M per thread (750 max threads)
[!!] Maximum possible memory usage: 12.5G (106% of installed RAM)
[OK] Slow queries: 0% (3K/49M)
[OK] Highest usage of available connections: 43% (326/750)
[OK] Key buffer size / total MyISAM indexes: 320.0M/1.9G
[OK] Key buffer hit rate: 99.5% (54B cached / 280M reads)
[OK] Query cache efficiency: 86.5% (37M cached / 43M selects)
[!!] Query cache prunes per day: 70952
[OK] Sorts requiring temporary tables: 3% (18K temp sorts / 585K sorts)
[!!] Joins performed without indexes: 73972
[!!] Temporary tables created on disk: 31% (239K on disk / 752K total)
[OK] Thread cache hit rate: 99% (326 created / 1M connections)
[!!] Table cache hit rate: 0% (1K open / 562K opened)
[OK] Open file limit used: 46% (1K/4K)
[OK] Table locks acquired immediately: 99% (8M immediate / 8M locks)
[!!] InnoDB data size / buffer pool: 26.1M/8.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
Reduce your overall MySQL memory footprint for system stability
Enable the slow query log to troubleshoot bad queries
Increasing the query_cache size over 128M may reduce 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
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
*** MySQL's maximum memory usage is dangerously high ***
*** Add RAM before increasing MySQL buffer variables ***
long_query_time (<= 10)
query_cache_size (> 256M) [see warning above]
join_buffer_size (> 4.0M, or always use indexes with joins)
tmp_table_size (> 32M)
max_heap_table_size (> 16M)
table_cache (> 1024)
innodb_buffer_pool_size (>= 26M)
but from past experiance , following that guide let crash my mysql faster then at this point (now it seems to run stable)
so the tool can help a little , but its still the experiance that shows if it helps or not
and the best way is to test it out after you changed things until you feel that it is right
ofcourse the difference here is that it is from my webserver that contains more sites and that is a bit differend then just the file server ( there are also 2 fileservers running on my webserver , atleast the database side and no problems )
hopely it can help you
Greetings From PowerChaos
ok so my.cnf:
and mysqltuner says:
I know I kind of went overboard... but I have 32gigs... might as well use it.
I still have 300+ queries locked - longest at 27 seconds.
But, I think the only solution to that may be going to innodb ?
-----
BTW... not the cause of my problem:
I think it is an apache issue.... because I wrote a simple html page and it is taking 8 seconds to load.
ping is wicked quick, and so is nslookup and host... so probably apache.
Thanks Guys... now onto apache @#$%^&
Code: Select all
[mysqld]
#skip-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 3G
max_allowed_packet = 16M
thread_stack = 568K
thread_cache_size = 2048
myisam_sort_buffer_size = 32M
myisam-recover = BACKUP
max_connections = 1024
table_cache = 2048
thread_concurrency = 16
tmp_table_size = 511M
innodb_buffer_pool_size = 256M
max_heap_table_size = 128M
#
# * Query Cache Configuration
#
query_cache_limit = 256M
query_cache_size = 1G
query_cache_type = 1
log_slow_queries = /var/log/mysql/mysql-slow.log
#
safe-show-database
Code: Select all
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 1G (Tables: 151)
[--] Data in InnoDB tables: 146M (Tables: 15)
[!!] Total fragmented tables: 32
-------- Security Recommendations -------------------------------------------
[OK] All database users have passwords assigned
-------- Performance Metrics -------------------------------------------------
[--] Up for: 9m 26s (51K q [90.569 qps], 8K conn, TX: 21M, RX: 5M)
[--] Reads / Writes: 60% / 40%
[--] Total buffers: 4.4G global + 3.1M per thread (1024 max threads)
[OK] Maximum possible memory usage: 7.4G (23% of installed RAM)
[OK] Slow queries: 0% (0/51K)
[OK] Highest usage of available connections: 36% (376/1024)
[OK] Key buffer size / total MyISAM indexes: 3.0G/253.6M
[OK] Key buffer hit rate: 95.2% (113K cached / 5K reads)
[!!] Query cache efficiency: 10.6% (1K cached / 17K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 191 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 2 total)
[OK] Thread cache hit rate: 95% (376 created / 8K connections)
[OK] Table cache hit rate: 98% (432 open / 439 opened)
[OK] Open file limit used: 0% (455/999K)
[!!] Table locks acquired immediately: 65%
[OK] InnoDB data size / buffer pool: 146.9M/256.0M
-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Optimize queries and/or use InnoDB to reduce lock wait
Variables to adjust:
query_cache_limit (> 256M, or use smaller result sets)
I still have 300+ queries locked - longest at 27 seconds.
But, I think the only solution to that may be going to innodb ?
-----
BTW... not the cause of my problem:
I think it is an apache issue.... because I wrote a simple html page and it is taking 8 seconds to load.
ping is wicked quick, and so is nslookup and host... so probably apache.
Thanks Guys... now onto apache @#$%^&
-
- Posts: 10
- Joined: Jul 26, 2012 10:55 pm
What sessions table? I m a little new, can you please explain in detail what these session tables are and why you had to switch to innodb ?e wrote:Sessions...
It was the Sessions table that appeared to be gumming everything up.
I had to switch the Sessions table over to innodb.
(and I cleaned a lot of it out).
Now it works goodish.
Thanks for the help
E
thanks a lot.