XFileSharing Pro - mysql at 100% CPU and averaging 300+ locked queries Help :)

Message
Author
e
Posts: 16
Joined: May 24, 2012 6:17 pm

mysql at 100% CPU and averaging 300+ locked queries Help :)

#1 Postby e » Aug 20, 2012 9:17 pm

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

PowerChaos
Posts: 521
Joined: Dec 19, 2009 5:12 pm

#2 Postby PowerChaos » Aug 20, 2012 9:47 pm

my mysql is running on 145% cpu but it still runs fine :D
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 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 )

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
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

e
Posts: 16
Joined: May 24, 2012 6:17 pm

#3 Postby e » Aug 20, 2012 11:19 pm

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

PowerChaos
Posts: 521
Joined: Dec 19, 2009 5:12 pm

#4 Postby PowerChaos » Aug 20, 2012 11:42 pm

well
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)

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

edavrio
Posts: 17
Joined: Aug 17, 2012 9:00 pm

#5 Postby edavrio » Aug 21, 2012 1:14 am

If you don't Cache everything at the SQL level, than any tweaks are useless.

e
Posts: 16
Joined: May 24, 2012 6:17 pm

#6 Postby e » Aug 21, 2012 11:27 am

ok so my.cnf:

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
and mysqltuner says:

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 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 @#$%^&

e
Posts: 16
Joined: May 24, 2012 6:17 pm

#7 Postby e » Aug 21, 2012 10:13 pm

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

filedownload
Posts: 10
Joined: Jul 26, 2012 10:55 pm

#8 Postby filedownload » Aug 22, 2012 2:25 am

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
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 ?

thanks a lot.