MySQL Optimizing

Question

My webserver uses a dedicated host:

Intel(R) Xeon(R) CPU E5620 8core
12Gram
Centos32bit/Driectadmin
DISK SAS 80G
Php-cgi

This host is running one website

Use wordpress 2.92(+plugin cache...)
Database size 600MB
only 100online

My website is running very slowly. Please hep me configure my.cnf.

    [mysqld]
user=mysql
key_buffer=128M
set-variable = max_connections=1000
socket          = /var/lib/mysql/mysql.sock
key_buffer =32M
table_cache = 1024
open_files_limit = 16344
join_buffer_size = 8M
read_buffer_size = 8M
sort_buffer_size = 8M
tmp_table_size=512M
read_rnd_buffer_size=8M
max_heap_table_size=256M
#myisam_sort_buffer_size=256M
thread_cache_size=8
thread_cache=32
query_cache_type=1
query_cache_limit=1024M
query_cache_size=1024M
thread_concurrency = 16
wait_timeout = 10
connect_timeout = 10
interactive_timeout = 10
long_query_time=1
log-slow-queries = /var/log/mysqlslowqueries.log
max_allowed_packet=32M
skip-innodb
[myisamchk]
key_buffer = 64M
sort_buffer = 64M
read_buffer = 16M
write_buffer = 16M

[isamchk]
key_buffer=64M
sort_buffer=64M
read_buffer=16M
write_buffer=16M

And MySQLTuner

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.47-log
[!!] Switch to 64-bit OS - MySQL cannot currently use all of your RAM

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 6h 39m 2s (6M q [57.776 qps], 60K conn, TX: 999M, RX: 2B)
[--] Reads / Writes: 93% / 7%
[--] Total buffers: 1.3G global + 32.2M per thread (1000 max threads)
[!!] Allocating > 2GB RAM on 32-bit systems can cause system instability
[!!] Maximum possible memory usage: 32.7G (275% of installed RAM)
[OK] Slow queries: 0% (10K/6M)
[OK] Highest usage of available connections: 5% (52/1000)
[OK] Key buffer size / total MyISAM indexes: 32.0M/65.5M
[OK] Key buffer hit rate: 100.0% (938M cached / 108K reads)
[OK] Query cache efficiency: 51.4% (3M cached / 5M selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (929 temp sorts / 2M sorts)
[!!] Temporary tables created on disk: 49% (2M on disk / 4M total)
[OK] Thread cache hit rate: 99% (580 created / 60K connections)
[OK] Table cache hit rate: 97% (281 open / 288 opened)
[OK] Open file limit used: 1% (319/16K)
[OK] Table locks acquired immediately: 99% (7M immediate / 7M locks)

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Temporary table size is already large - reduce result set size
    Reduce your SELECT DISTINCT queries without LIMIT clauses

Cannot run mysqld

[root@server ~]#
[root@server ~]# mysqld
100607 18:21:17 [Warning] mysqld: Option '--set-variable' is deprecated. Use --
100607 18:21:17 [Warning] '--log_slow_queries' is deprecated and will be remove
100607 18:21:17 [Note] Plugin 'FEDERATED' is disabled.
100607 18:21:17 [Note] Plugin 'InnoDB' is disabled.
mysqld: File '/var/log/mysqlslowqueries.log' not found (Errcode: 13)
100607 18:21:17 [ERROR] Could not use /var/log/mysqlslowqueries.log for logging                                          urn it on again: fix the cause, shutdown the MySQL server and restart it.
100607 18:21:17 [ERROR] Can't start server: Bind on TCP/IP port: Address alread
100607 18:21:17 [ERROR] Do you already have another mysqld server running on po
100607 18:21:17 [ERROR] Aborting

100607 18:21:17 [Note] mysqld: Shutdown complete

And output from top

[root@server ~]# top

top - 18:45:03 up 3 days,  4:40,  1 user,  load average: 1.80, 1.42, 1.40
Tasks: 266 total,   1 running, 265 sleeping,   0 stopped,   0 zombie
Cpu(s):  4.6%us,  2.5%sy,  0.0%ni, 92.9%id,  0.0%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:  12462096k total,  2741388k used,  9720708k free,   599584k buffers
Swap:  8385920k total,        0k used,  8385920k free,  1612488k cached

  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
 7487 mysql     15   0 1138m 241m 3572 S 98.7  2.0 811:12.72 mysqld
 1508 admin     16   0  166m  43m  21m S  0.3  0.4   0:00.80 php-cgi
 2019 apache    15   0  9264 3268 1444 S  0.3  0.0   0:00.06 httpd
 2089 root      15   0  2336 1132  800 R  0.3  0.0   0:00.15 top
32137 admin     15   0  173m  51m  22m S  0.3  0.4   0:02.11 php-cgi
    1 root      15   0  2072  580  504 S  0.0  0.0   0:02.42 init
    2 root      RT  -5     0    0    0 S  0.0  0.0   0:00.06 migration/0
    3 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/0
    4 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/0
    5 root      RT  -5     0    0    0 S  0.0  0.0   0:00.03 migration/1
    6 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/1
    7 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/1
    8 root      RT  -5     0    0    0 S  0.0  0.0   0:00.18 migration/2
    9 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/2
   10 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/2
   11 root      RT  -5     0    0    0 S  0.0  0.0   0:00.02 migration/3
   12 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/3
   13 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/3
   14 root      RT  -5     0    0    0 S  0.0  0.0   0:00.01 migration/4
   15 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/4
   16 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/4
   17 root      RT  -5     0    0    0 S  0.0  0.0   0:00.01 migration/5
   18 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/5
   19 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/5
   20 root      RT  -5     0    0    0 S  0.0  0.0   0:00.01 migration/6
   21 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/6
   22 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/6
   23 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 migration/7
   24 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/7
   25 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/7
   26 root      RT  -5     0    0    0 S  0.0  0.0   0:00.05 migration/8
   27 root      34  19     0    0    0 S  0.0  0.0   0:00.00 ksoftirqd/8
   28 root      RT  -5     0    0    0 S  0.0  0.0   0:00.00 watchdog/8
   29 root      RT  -5     0    0    0 S  0.0  0.0   0:00.05 migration/9

Well, you need to find out what queries are slowing the site down. Check the slowqueries log `/var/log/mysqlslowqueries.log` — ircmaxell, Jun 07 '10 at 15:07
Then what makes you think it's the database that's slowing it down? What do you see if you run `top`? — ircmaxell, Jun 07 '10 at 15:26

score 5 · Answer 1 · answered Jun 07 '10 at 21:25

If you don't know where to start, then start with MySQLTuner.

You just download and run the perl script.

It asks for the mysql administrator's username and password and then lists some General statistics, then storage statistics and then performance metrics before finally giving you a list of recommendations.

It saves hours on the initial review of your server instance.

Once you have fixed things, I would suggest running the script every hour or so for a week ( maybe less often / maybe for longer, depending how busy your database is ) to see if any settings need changing after being run for real.

Also have a look at resources like this.

Once you know your MySQL instance is sane, you will need to optimise the specific queries in your application code, you can identify the worst offenders by checking out the slow query log (if anything is in there) and using a profiler like XDebug.

+1 rackerhacker's scripts are great; I've used them in the past on a small and large MySQL server installs, and i've always seen better performance from them. — grufftech, Jun 07 '10 at 22:47
+1 Agreed. Major even posted a comment on my one week old blog, so obviously has an interest in what people think of his scripts and is still looking at ways they can be improved. — Richard Holloway, Jun 07 '10 at 22:59

score 3 · Answer 2 · answered Jun 08 '10 at 01:14

The first problem that you have is that you are running on a 32bit OS. You have 12G of Ram (if I read your post right) and you will never see Mysql use that much because your running 32bit. Upgrade Centos to a 64bit OS, and then start using the my.large.cnf file that they provide. That should get you going in a much better direction. If you are still having problems, then things to be evaluated such as is this mostly reads, writes, both. That can tell you that you should be using Innodb or MYISAM. Innodb is going to be good for lots of updates/writes and reads, where if everything is mostly reads then MyISAM is a good choice. We'll need to know this as well to help figure out Key Cache, etc. but the first thing is getting the OS to use all that RAM.

if the `hugemem` kernel is installed, he can access the 'excess' memory — warren, Jun 08 '10 at 01:38
Not all of it, and it will be application dependent. That means then you can access that memory but are having extra io as the OS is trying to swap the memory locations back and forth since it's mathematically above what it can support. This is exactly like enabling PAE on windows. You only get so far and in heavy usage it can become a hindrance instead of something that helps. — RaginBajin, Jun 10 '10 at 02:14

score 1 · Answer 3 · answered Jun 07 '10 at 15:21

1

show status
show variables
show processlist

Are you using php-cgi? fcgid or fastcgi?

answered Jun 07 '10 at 15:21

user6738237482

1,510
12
7

But when i use command :mysqld many error . – Thoman Jun 08 '10 at 01:24
Wrong command. `mysqld` starts a new server daemon. `mysql` starts a client to connect to the server daemon. `mysql` is the one you need here. You can run the client and the daemon on the same machine. The most likely error you will get when you type `mysql` is Access denied. If that happens, try the -u and -p options to add an appropriate username and password. – Ladadadada Jun 08 '10 at 12:39

score 0 · Answer 4 · answered Jun 28 '10 at 12:27

You've not got a lot of users on via mysql yet its consuming 98.7% of your CPU in the screenshot of 'top' is this representative of the way the system is behaving most of the time? If so then something is very wrong (and the high CPU usage is a key symtom of the actucal problem). Even under heavy load with lots of memory, mysqld should be spending a lot of its time in I/O wait.

This bug describes problems with high CPU using 64bit MySQL - although an older version.

What does mysqladmin processlist show?

score 0 · Answer 5 · answered Nov 04 '14 at 14:18

0

Have you taken a look at the running MySQL processes inside mysql? (i.e. via mytop?)

The high CPU load of the MySQLd process gives me the shivers...

answered Nov 04 '14 at 14:18

Gekkie

290
3
18

MySQL Optimizing

5 Answers5