cstackbarb Posted February 21, 2008 Share Posted February 21, 2008 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/ Quote Link to comment Share on other sites More sharing options...
cstackbarb Posted February 22, 2008 Author Share Posted February 22, 2008 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. Quote Link to comment Share on other sites More sharing options...
cstackbarb Posted February 27, 2008 Author Share Posted February 27, 2008 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. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.