Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm managing a site that does streaming video, the site is running off:

Intel(R) Xeon(R) CPU E31220 @ 3.10GHz

16 GB RAM

2 x 2TB RAID1

1gbit unmetered bandwidth is not an issue as the usage is only 150-250mbit etc.

On this machine, I run the following:

  • apache to host the [url]www.domain.com[/url]
  • mysql server (apt-get install mysql-server)

Everything has been going fine except now there are 200 people streaming videos at any given time.. so now I'm noticing that the site loads slower.. I believe this is because mysql is using 100% cpu. Below I've pasted my top, and also my mysql settings.. Could someone help me with the mysql settings to slow this down?

I do realize that at some point I need to move the mysql server to another machine on its own, but I still think with the current settings im about to post, it would still use 100% cpu even on the standalone machine.. so i think the settings need to be changed? or can someone guide me. Also, I cannot lower the wait_timeout because when I do that, it causes errors on the video conversion script which fetches the videos and converts them and sometimes it can take awhile so i don't know if thats a problem or what

my.cnf:
# * Fine Tuning
#
max_allowed_packet      = 16M
thread_stack            = 1M
thread_cache_size       = 50
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
max_connections        = 1000
wait_timeout            = 20000
tmp_table_size          = 500M
max_heap_table_size     = 1000M
table_cache            = 1000
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 4M
query_cache_size        = 64M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#log_slow_queries       = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
#server-id              = 1
#log_bin                        = /var/log/mysql/mysql-bin.log
expire_logs_days        = 10
max_binlog_size         = 100M
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem



[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer_size                = 64M

----------------------------------------

     >>  MySQLTuner 1.1.1 - Major Hayden <major@mhtx.net>
     >>  Bug reports, feature requests, and downloads at [url]http://mysqltuner.com/[/url]
     >>  Run with '--help' for additional options and output filtering
    [!!] Successfully authenticated with no password - SECURITY RISK!

-------- General Statistics --------------------------------------------------

    [--] Skipped version check for MySQLTuner script
    [OK] Currently running supported MySQL version 5.5.37-0ubuntu0.13.10.1
    [OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------

    [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster 
    [--] Data in MyISAM tables: 1G (Tables: 41)
    [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
    [!!] InnoDB is enabled but isn't being used
    [!!] Total fragmented tables: 4

-------- Security Recommendations  -------------------------------------------

    [OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 22h 23m 46s (5M q [69.809 qps], 1M conn, TX: 8B, RX: 4B)
[--] Reads / Writes: 29% / 71%
[--] Total buffers: 716.0M global + 3.5M per thread (1000 max threads)
[OK] Maximum possible memory usage: 4.1G (26% of installed RAM)
[OK] Slow queries: 0% (2/5M)
[OK] Highest usage of available connections: 77% (775/1000)
[OK] Key buffer size / total MyISAM indexes: 8.0M/119.0M
[OK] Key buffer hit rate: 99.4% (33M cached / 208K reads)
[OK] Query cache efficiency: 67.6% (1M cached / 1M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 921 sorts)
[!!] Temporary tables created on disk: 28% (244 on disk / 849 total)
[OK] Thread cache hit rate: 98% (17K created / 1M connections)
[OK] Table cache hit rate: 62% (428 open / 681 opened)
[OK] Open file limit used: 9% (476/5K)
[!!] Table locks acquired immediately: 55%

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    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
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Optimize queries and/or use InnoDB to reduce lock wait
-------------------------------------------------------

It says run optimize table, but how do i do that? On the web I've tried to search and found to run the mysql check command but i get

# mysqlcheck -u root -p --auto-repair --check --optimize --all-databases
Error:  mysqlcheck doesn't support multiple contradicting commands.

Further, is it safe to just optimize the database? It wont hurt it or anything? Of course ill back up first

I've enabled slow query log now, but so far nothing yet..

At one point, Mysqltuner was saying i had reached 996/1000 connections.. but when i went to go raise the max_connections to 2000 and then i restarted the mysql server, the site became even slower than before.. Perhaps i should not be restarting the mysql server and just adjusting globally ?

top - 23:27:42 up 11 days,  5:28,  3 users,  load average: 2.41, 4.40, 5.97
Tasks: 269 total,   3 running, 265 sleeping,   0 stopped,   1 zombie
%Cpu(s): 24.3 us,  5.6 sy,  0.0 ni, 69.3 id,  0.3 wa,  0.0 hi,  0.5 si,  0.0 st
KiB Mem:  16408692 total, 16237624 used,   171068 free,     9552 buffers
KiB Swap: 15624184 total,     8220 used, 15615964 free, 15072644 cached

  PID USER      PR  NI  VIRT  RES  SHR S  %CPU %MEM    TIME+  COMMAND                                                               
29178 mysql     20   0 2662m  73m 7280 S 100.4  0.5   4:49.03 /usr/sbin/mysqld                                                      
29428 daemon    20   0  544m  19m 7048 S   2.7  0.1   0:07.81 /usr/sbin/apache2 -k start                                            
29943 daemon    20   0  543m  17m 5428 S   2.0  0.1   0:02.57 /usr/sbin/apache2 -k start                                            
29945 daemon    20   0  543m  17m 5352 S   2.0  0.1   0:00.76 /usr/sbin/apache2 -k start                                            
29672 daemon    20   0  543m  16m 4696 S   1.7  0.1   0:00.63 /usr/sbin/apache2 -k start                                            
29792 daemon    20   0  544m  17m 5396 S   1.3  0.1   0:00.15 /usr/sbin/apache2 -k start                                            
  183 root      20   0     0    0    0 S   1.0  0.0   8:51.20 [md0_raid1]                                                           
29445 daemon    20   0  543m  16m 4700 S   0.7  0.1   0:01.04 /usr/sbin/apache2 -k start                                            
29744 daemon    20   0  543m  16m 5384 S   0.7  0.1   0:00.23 /usr/sbin/apache2 -k start                                            
 7705 root      35  15     0    0    0 D   0.3  0.0   2:27.65 [md0_resync]                                                          
29435 daemon    20   0  543m  17m 5596 S   0.3  0.1   0:00.37 /usr/sbin/apache2 -k start                                            
29451 daemon    20   0  543m  17m 5356 S   0.3  0.1   0:00.44 /usr/sbin/apache2 -k start                                            
29453 daemon    20   0     0    0    0 Z   0.3  0.0   0:00.07 [apache2] <defunct>                                                   
29501 daemon    20   0  543m  16m 5324 S   0.3  0.1   0:00.10 /usr/sbin/apache2 -k start                                            
29518 daemon    20   0  543m  17m 5948 S   0.3  0.1   0:00.31 /usr/sbin/apache2 -k start                                            
29534 daemon    20   0  543m  17m 5456 S   0.3  0.1   0:00.42 /usr/sbin/apache2 -k start                                            
29539 daemon    20   0  543m  16m 5348 S   0.3  0.1   0:00.24 /usr/sbin/apache2 -k start                                            
29542 daemon    20   0  543m  16m 4680 S   0.3  0.1   0:00.51 /usr/sbin/apache2 -k start                                            
29549 daemon    20   0  543m  17m 5352 S   0.3  0.1   0:00.90 /usr/sbin/apache2 -k start                                            
29656 daemon    20   0  543m  16m 4792 S   0.3  0.1   0:00.25 /usr/sbin/apache2 -k start                                            
29673 daemon    20   0  543m  16m 5392 R   0.3  0.1   0:00.20 /usr/sbin/apache2 -k start                                            
29682 daemon    20   0  543m  16m 4704 S   0.3  0.1   0:00.58 /usr/sbin/apache2 -k start                                            
29791 daemon    20   0  543m  17m 5392 S   0.3  0.1   0:00.81 /usr/sbin/apache2 -k start                                            
29793 daemon    20   0  543m  17m 5712 S   0.3  0.1   0:00.12 /usr/sbin/apache2 -k start                                            
29926 daemon    20   0  543m  16m 4704 S   0.3  0.1   0:00.44 /usr/sbin/apache2 -k start                                            
29956 daemon    20   0  543m  16m 4700 S   0.3  0.1   0:00.33 /usr/sbin/apache2 -k start    

=====================================


# free -tm
             total       used       free     shared    buffers     cached
Mem:         16024      15861        162          0          4      14351
-/+ buffers/cache:       1506      14517
Swap:        15257          8      15249
Total:       31282      15870      15411

Additional question: If I do move the mysql to its own machine, should the hard drive be ssds ? is that better for mysql ? and would 32 gb of ram be sufficient

The biggest thing in the database is the sessions table which can get as big as 1.5 gb so far.. but i clear it and the database shrinks to under 100mb

I've been advised to increase my query cache, but to what? Currently it is

query_cache_limit       = 4M
query_cache_size        = 64M

so when I changed it to

query_cache_limit       = 4M
query_cache_size        = 264M

and then restarting the mysql server the site then became unusable.. It would barely load or take forever. Was I suppose to change both? Is there a way to change these values without restarting the server

Also it says

Temporary table size is already large - reduce result set size

Does anyone know what would be a good number? for 16 gb of ram machine

tmp_table_size          = 500M
share|improve this question

migrated from serverfault.com May 7 at 0:14

This question came from our site for professional system and network administrators.

1  
Have you worked out what query is consuming the most CPU? –  John Mee May 7 at 0:12
    
As John Mee suggested do a SHOW FULL PROCESSLIST when MySQL is using 100% CPU and look for long running queries and start debugging them (with EXPLAIN for starters). –  HTTP500 May 7 at 1:32
    
strace -cp on a MySQL process, wait 1 minute, ^C, it will tell you where it is spending most of its time. –  Gaius May 7 at 8:28
    
You can enable Slow/General log analyze them using Pt_query_digest tool percona.com/doc/percona-toolkit/2.2/pt-query-digest.html also there is a commercial tool called MONyog- webyog.com which does the same in real-time –  Glenn McKay May 12 at 12:57

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.