Jump to content

MySQL Server optimization and configuration.


cstackbarb

Recommended Posts

First, I need to say thanks to everyone here. A week ago I would have said I knew next to nothing about MySQL, however, I have found this forum to be very useful in providing good information.

 

I would like to ask for some help as I am still struggling to learn. I needed a program that required MySQL so I setup a Debian box (Testing Lenny) running 64bit on a Xeon 3Ghz with 3GB of ram. Not knowing anything about MySQL I just ran apt-get and let Debian do its thing. This was fine until about a month ago when others saw this program and wanted to use it and a couple other people saw I had a MySQL system and wanted to use it too! So now all these people are using the MySQL box and it became unusably slow. Not knowing a thing about MySQL at the time, I looked at the resources and saw that most of the resources were being used. So in a rushed attempt to fix a software issue with hardware, I bumped the memory up to 7GB. In about a day's time the server was back to being slow. This time, however, it had /tons/ of memory left over.

 

So I hit up Google and found out information about the InnoDB Buffer Pool. Sure enough, it was set to something really low so I bumped it up to 4GB. A couple of weeks went by and the system is slow again! This time, the CPU is spiked (low load averages are about 3-5 with peaks about 10-11) and the memory is being used. Again, this is a 64bit Xeon 3Ghz with 7GB of memory! And its being slammed?

 

OK, time to learn something about MySQL.

 

I stumbled on this forum through a Google search and one of the many things I found was this link ( http://www.phpfreaks.com/forums/index.php/topic,126096.0.html ). I can honestly say that I attempted to read those documents. Understanding what I read is different. I have been reading and trying to Google what I don't know but that is slow as I am searching for quite a bit. I did find two things of great interest.

 

The first gem was the MySQL performance tuning primer script ( http://day32.com/MySQL/ ). I followed what it instructed and it helped, but I still get crazy loads after a couple of days.

 

The next gem was one I found while browsing this forum: MySQLReport ( http://hackmysql.com/mysqlreport ). This was great because their little documentation example report really helped me figure out what I was looking at. I made a couple more tweaks and again performance is /much/ better but the server is still taking a beating.

 

Can someone please look over what I have and help me figure out what I should do? I would really like some help in trying to figure this out. Doing it by myself is going really slow. If it really is as simple as getting more memory, I think the box goes up to 16GB but I have a feeling that will be expensive....I would rather find out if I can make this work more efficient first.

 

Thanks! I appreciate any help people are willing to give!

 

output of tuning-primer.sh

-- MYSQL PERFORMANCE TUNING PRIMER --
     - By: Matthew Montgomery -

MySQL Version 5.0.32-Debian_7etch3-log x86_64

Uptime = 2 days 5 hrs 9 min 28 sec
Avg. qps = 685
Total Questions = 131237089
Threads Connected = 33

Server has been running for over 48hrs.
It should be safe to follow these recommendations

To find out more information on how each of these
runtime variables effects performance visit:
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
Visit http://www.mysql.com/products/enterprise/advisors.html
for info about MySQL's Enterprise Monitoring and Advisory Service

SLOW QUERIES
Current long_query_time = 5 sec.
You have 101 out of 131237898 that take longer than 5 sec. to complete
The slow query log is enabled.
Your long_query_time seems to be fine

WORKER THREADS
Current thread_cache_size = 8
Current threads_cached = 4
Current threads_per_sec = 0
Historic threads_per_sec = 0
Your thread_cache_size is fine

MAX CONNECTIONS
Current max_connections = 100
Current threads_connected = 33
Historic max_used_connections = 38
The number of used connections is 38% of the configured maximum.
Your max_connections variable seems to be fine.

MEMORY USAGE
Max Memory Ever Allocated : 4 G
Configured Max Per-thread Buffers : 349 M
Configured Max Global Buffers : 4 G
Configured Max Memory Limit : 4 G
Physical Memory : 6.75 G
Max memory limit seem to be within acceptable norms

KEY BUFFER
Current MyISAM index space = 69 K
Current key_buffer_size = 16 M
Key cache miss rate is 1 : 1
Key buffer fill ratio = 0 %
Your key_buffer_size seems to be too high.
Perhaps you can use these resources elsewhere

QUERY CACHE
Query cache is enabled
Current query_cache_size = 64 M
Current query_cache_used = 25 M
Current query_cache_limit = 32 M
Current Query cache Memory fill ratio = 39.84 %
Current query_cache_min_res_unit = 4 K
MySQL won't cache query results that are larger than query_cache_limit in size

SORT OPERATIONS
Current sort_buffer_size = 2 M
Current record/read_rnd_buffer_size = 256 K
Sort buffer seems to be fine

JOINS
Current join_buffer_size = 1.00 M
You have had 463 queries where a join could not use an index properly
You should enable "log-queries-not-using-indexes"
Then look for non indexed joins in the slow query log.
If you are unable to optimize your queries you may want to increase your
join_buffer_size to accommodate larger joins in one pass.

Note! This script will still suggest raising the join_buffer_size when
ANY joins not using indexes are found.

OPEN FILES LIMIT
Current open_files_limit = 1024 files
The open_files_limit should typically be set to at least 2x-3x
that of table_cache if you have heavy MyISAM usage.
Your open_files_limit value seems to be fine

TABLE CACHE
Current table_cache value = 256 tables
You have a total of 84 tables
You have 183 open tables.
The table_cache value seems to be fine

TEMP TABLES
Current max_heap_table_size = 32 M
Current tmp_table_size = 32 M
Of 31388245 temp tables, 43% were created on disk
Perhaps you should increase your tmp_table_size and/or max_heap_table_size
to reduce the number of disk-based temporary tables
Note! BLOB and TEXT columns are not allow in memory tables.
If you are using these columns raising these values might not impact your 
ratio of on disk temp tables.

TABLE SCANS
Current read_buffer_size = 128 K
Current table scan ratio = 1 : 1
read_buffer_size seems to be fine

TABLE LOCKING
Current Lock Wait ratio = 0 : 131242045
Your table locking seems to be fine

 

Output of mysqlreport

MySQL 5.0.32-Debian_7et  uptime 2 5:8:58        Thu Feb 21 12:21:30 2008

__ Key _________________________________________________________________
Buffer used    17.00k of  16.00M  %Used:   0.10
  Current       2.92M            %Usage:  18.24
Write hit     100.00%
Read hit        4.48%

__ Questions ___________________________________________________________
Total         131.22M   685.8/s
  DMS          93.36M   487.9/s  %Total:  71.15
  Com_         28.20M   147.4/s           21.49
  QC Hits       9.84M    51.4/s            7.50
  -Unknown    247.31k     1.3/s            0.19
  COM_QUIT     68.60k     0.4/s            0.05
Slow (5)          101     0.0/s            0.00  %DMS:   0.00  Log:  ON
DMS            93.36M   487.9/s           71.15
  SELECT       52.58M   274.8/s           40.07         56.32
  UPDATE       27.21M   142.2/s           20.73         29.14
  INSERT       13.38M    69.9/s           10.19         14.33
  DELETE      200.40k     1.0/s            0.15          0.21
  REPLACE           0       0/s            0.00          0.00
Com_           28.20M   147.4/s           21.49
  begin        13.81M    72.2/s           10.53
  commit       13.81M    72.2/s           10.53
  change_db   322.20k     1.7/s            0.25

__ SELECT and Sort _____________________________________________________
Scan           51.12k     0.3/s %SELECT:   0.10
Range         586.89k     3.1/s            1.12
Full join         463     0.0/s            0.00
Range check         0       0/s            0.00
Full rng join     311     0.0/s            0.00
Sort scan     481.01k     2.5/s
Sort range        393     0.0/s
Sort mrg pass       0       0/s

__ Query Cache _________________________________________________________
Memory usage   25.46M of  64.00M  %Used:  39.77
Block Fragmnt  19.67%
Hits            9.84M    51.4/s
Inserts        36.05M   188.4/s
Insrt:Prune  205.74:1   187.5/s
Hit:Insert     0.27:1

__ Table Locks _________________________________________________________
Waited              0       0/s  %Total:   0.00
Immediate     126.73M   662.4/s

__ Tables ______________________________________________________________
Open              183 of  256    %Cache:  71.48
Opened            301     0.0/s

__ Connections _________________________________________________________
Max used           38 of  100      %Max:  38.00
Total          75.04k     0.4/s

__ Created Temp ________________________________________________________
Disk table     13.75M    71.8/s
Table          31.38M   164.0/s    Size:  32.0M
File                5     0.0/s

__ Threads _____________________________________________________________
Running             1 of   32
Cached              5 of    8      %Hit:  99.92
Created            61     0.0/s
Slow                0       0/s

__ Aborted _____________________________________________________________
Clients        12.88k     0.1/s
Connects            4     0.0/s

__ Bytes _______________________________________________________________
Sent           28.42G  148.5k/s
Received       14.94G   78.1k/s

__ InnoDB Buffer Pool __________________________________________________
Usage           4.50G of   4.50G  %Used:  99.96
Read hit       99.99%
Pages
  Free            125            %Total:   0.04
  Data        250.08k                     84.80 %Drty:   1.08
  Misc          44708                     15.16
  Latched           0                      0.00
Reads           4.95G   25.9k/s
  From file   432.44k     2.3/s            0.01
  Ahead Rnd      5567     0.0/s
  Ahead Sql      1043     0.0/s
Writes        364.56M    1.9k/s
Flushes         6.32M    33.0/s
Wait Free           0       0/s

__ InnoDB Lock _________________________________________________________
Waits           37156     0.2/s
Current             0
Time acquiring
  Total        692913 ms
  Average          18 ms
  Max            2298 ms

__ InnoDB Data, Pages, Rows ____________________________________________
Data
  Reads       511.49k     2.7/s
  Writes        4.07M    21.3/s
  fsync       592.88k     3.1/s
  Pending
    Reads           0
    Writes          0
    fsync           0

Pages
  Created      54.70k     0.3/s
  Read        653.38k     3.4/s
  Written       6.32M    33.0/s

Rows
  Deleted      19.38M   101.3/s
  Inserted     13.38M    69.9/s
  Read          1.38G    7.2k/s
  Updated      26.39M   137.9/s

 

My my.conf

[client]
port		= 3306
socket		= /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket		= /var/run/mysqld/mysqld.sock
nice		= 0
[mysqld]
user		= mysql
pid-file	= /var/run/mysqld/mysqld.pid
socket		= /var/run/mysqld/mysqld.sock
port		= 3306
basedir		= /usr
datadir		= /home/mysqld
tmpdir		= /tmp
language	= /usr/share/mysql/english
skip-external-locking
default-storage-engine          = innodb
bind-address		= 127.0.0.1
key_buffer		= 16M
max_allowed_packet	= 16M
thread_stack		= 128K
thread_cache_size	= 8
table_cache             = 256 
join_buffer_size	= 1M
max_heap_table_size = 32M
tmp_table_size = 32M
query_cache_limit       = 32M
query_cache_size        = 64M
log		= /var/log/mysql/mysql.log
log_slow_queries	= /var/log/mysql/mysql-slow.log
long_query_time = 5
skip-bdb
innodb_buffer_pool_size		= 4608M
innodb_additional_mem_pool_size	= 64M	# 2M
innodb_file_per_table
innodb_flush_log_at_trx_commit	= 0	# 1
innodb_support_xa 		= 0
[mysqldump]
quick
quote-names
max_allowed_packet	= 16M
[mysql]
[isamchk]
key_buffer		= 16M
!includedir /etc/mysql/conf.d/

Link to comment
Share on other sites

Hello again,

I just did a bunch more reading on the mysqlreport and what those numbers mean. I am beginning to understand what goes on without googling something every other minute but would still like some guidance on if I am hitting the target or if I am aiming completely in the wrong direction.

 

When you look at the mysqlreport under the questions, the total amount of questions answered every second is 685.8/s. So the server is indeed /very/ busy. Then looking at the Read/Write to cache hits and the mysql documentation says:

This line indicates the effectiveness of key reads. (Technically, it is the ratio of key reads from hard disk to key reads from RAM.) Key read hit should be no less than 99%. A lower percentage may indicate a problem. A low key hit percentage is usually caused by the key buffer being too small (indicated in the Key Report section above) which prevents MySQL from loading more indexes into RAM. When this happens, MySQL must revert to reading indexes from the hard disk which is terribly slow and completely negates the point of indexes.

 

My read is an abysmal 4.48%! Dah! OK, so my key buffer is too small according to the documentation, but it looks like there is plenty free... ???

Buffer used    17.00k of  16.00M  %Used:  0.10

  Current      2.92M            %Usage:  18.24

That looks like plenty free to me! It still has 12MB not even close to being used! So what gives? I don't know, so I will just keep reading and maybe I will see something else I can fix.

 

On Line 79 of the mysqlreport example documentation, it talks about how the InnoDB Buffer Pool is similar to the Key Buffer used! OK so I found my culprit (I think)! I have completely maxed out my buffer pool!

 

Well I can't just expand the memory as I don't really have any left! I have had a monitoring process running on that box for the past couple of days gathering performance data and at this very moment I am using 5.5GB with fluctuation up to 6.4GB in the last day! That doesn't count cached memory either, otherwise it reads 6.9GB full!

 

I don't know if this is the proper way of telling how big a database is or not, but doing a `du -ch` in the /home/mysql directory (where my.conf says it is storing the database) tells me that 7.7 GB is being used.

 

Is there a way to better optimize this box? Or should I just buy a couple more GB of memory and bump that InnoDB Buffer Pool up higher? If I do that though, will I have to buy more memory every time the database grows? Surely not...People have bigger databases then what fits into their memory right?

 

I really do not know if I am interpreting this data correctly. I would be very grateful if someone could help out.

 

 

Link to comment
Share on other sites

Hello again,

Wow. Thanks for the stellar responses!

 

Anyway, for others that might stumble across this looking for help, here is what I have done so far. This is not a complete answer but I think I am getting there.

 

I had almost no luck finding anyone who could help, so in a panic mode one day I bought more memory (upped the system to 10GB). That allowed the whole database (all 8GB of it) to be loaded into memory and that helped quite a bit. The load went down and the system was a bit more responsive. I was concerned that if the database grew any more then I would be buying more and more memory so I kept hunting for answers.

 

I then manage to get a response from somewhere else that suggested I try to optimize the tables. After researching the command I gave it a shot:

mysqlcheck -Aoqp

It ran all night, but the next day the system was the most responsive it has been in a long while and the load average bounces between 1-2 on average with spikes every 2 hours (for about 5 minutes) to 5. Much better then 5 avg with spikes to 10!

 

Since then I have let it run for almost 1.5 days and my read hit is  -46.69% ! So the system is still writing to disk way too much. Even though the load on the system is lower and it is more responsive, this can't be good. I keep being told that my Read Hit should get closer to 100% not further away from it but so far no one knows what to do about it.

 

I will post back if I find anything else.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.