Invision Community 4: SEO, prepare for v5 and dormant account notifications By Matt Monday at 02:04 PM
Dhil_ Posted January 26, 2013 Posted January 26, 2013 My server often uses 100% Cpu performance and i think it might be incorrect mysql optimization for IPB. Therefor i have posed my confiugration here. can anyone come with some good suggestions. # # The MySQL database server configuration file. # # You can copy this to one of: # - "/etc/mysql/my.cnf" to set global options, # - "~/.my.cnf" to set user-specific options. # # One can use all long options that the program supports. # Run program with --help to get a list of available options and with # --print-defaults to see which it would actually understand and use. # # For explanations see # http://dev.mysql.com/doc/mysql/en/server-system-variables.html # This will be passed to all mysql clients # It has been reported that passwords should be enclosed with ticks/quotes # escpecially if they contain "#" chars... # Remember to edit /etc/mysql/debian.cnf when changing the socket location. [client] port = 3306 socket = /var/run/mysqld/mysqld.sock # Here is entries for some specific programs # The following values assume you have at least 32M ram # This was formally known as [safe_mysqld]. Both versions are currently parsed. [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking #skip-innodb # # 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 #bind-address = GLOBALIP # # * Fine Tuning # #concurrent_insert=2 read_buffer_size = 9M key_buffer = 512M max_allowed_packet = 16M thread_stack = 256K thread_cache_size = 8 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP max_connections = 150 table_cache = 8192 tmp_table_size = 300M max_heap_table_size = 300M wait_time=60 wait_timeout = 20 interactive_timeout=20 sort_buffer_size = 16M read_rnd_buffer_size = 16M join_buffer_size = 8M key_buffer_size = 192M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M thread_cache_size = 16 thread_concurrency = 8 # # * Query Cache Configuration # query_cache_limit = 20M query_cache_size = 128M # # * Logging and Replication # # Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. #log = /var/log/mysql/mysql.log # Error logging goes to syslog. This is a Debian improvement :) # # Here you can see queries with especially long duration log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 10 #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 = 2 max_binlog_size = 50M #binlog_do_db = include_database_name #binlog_ignore_db = include_database_name # # * BerkeleyDB # # Using BerkeleyDB is now discouraged as its support will cease in 5.1.12. skip-bdb # # * 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! # You might want to disable InnoDB to shrink the mysqld process by circa 100MB. #skip-innodb # # # * 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 #innodb_additional_mem_pool_size = 16M #innodb_buffer_pool_size = 1024M #innodb_file_io_threads = 4 #innodb_thread_concurrency = 16 #innodb_flush_log_at_trx_commit = 2 #innodb_log_buffer_size = 8M #innodb_log_file_size = 256M #innodb_log_files_in_group = 3 #innodb_max_dirty_pages_pct = 90 #innodb_lock_wait_timeout = 50 #innodb_file_per_table #default-storage-engine=innodb #default-table-type=innodb [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M # # * NDB Cluster # # See /usr/share/doc/mysql-server-*/README.Debian for more information. # # The following configuration is read by the NDB Data Nodes (ndbd processes) # not from the NDB Management Nodes (ndb_mgmd processes). # # [MYSQL_CLUSTER] # ndb-connectstring=127.0.0.1 # # * IMPORTANT: Additional settings that can override those from this file! # The files must end with '.cnf', otherwise they'll be ignored. # !includedir /etc/mysql/conf.d/ log-error=/var/log/mysql/error.log
Luis Manson Posted January 26, 2013 Posted January 26, 2013 Im not experienced on MySQL so i can not give you advice, but you can get started using this scripts:tuning primer: http://www.day32.com/MySQL/mysql tuner: https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl
Rhett Posted January 26, 2013 Posted January 26, 2013 Run mysql tuner as noted above, however your 100% load may not be related to mysql, it would require more info to find the cause. I would have your server admin/host look at the server to determine the issue.
thompsone Posted January 26, 2013 Posted January 26, 2013 I wouldn't assume that tuning MYSQL is the answer to your problem. I might start by looking at what's causing MYSQL to be busy. I would drop that config file and instead use a default my.cnf in it's place and then restart the MYSQL service. Once you've done that you should look at the load on the machine. Without knowing your hardware specs the conf file you posted is hard to tune without some benchmarks to work with and then even with that not having live data is sometimes difficult. I will say that unless you're running a very demanding application that your MYSQL settings are very agressive, then again there's no way to tell what kind of machine you're using or any of the other answers one might look at. Is this a single LAMP machine or is this a clustered machine, do you have 32 processor cores or 2, is this a multithreading machine, do you have 64G of EC memory or 1G of non-EC, are your disks SSD or HDD, is this a RAID0, RAID5, RAID10, no RAID, what kind of read write activity is happening on the drives, dedicated or virtual, and the list goes on. My first suspect is that your disk(s) are thrashing themselves reading and writing because you've caused a condition where MYSQL thinks it has a bigger environment than it does. The R/W bottleneck is slowing down your machine and that has caused you to think MYSQL is the problem. There are a lot of people who can help but it will require a lot of work on your part to actually see it through. Copy pasting a config file is 1/1000th of the information and generally brings up more questions than it does answers. Rewrite your config file to a default my.cnf, restart the service, watch the loads for a bit and report back.
Dhil_ Posted January 27, 2013 Author Posted January 27, 2013 I have a pretty busy debian machine with a single IPB board installation. here is my machine configuration.. vendor_id : GenuineIntel cpu family : 6 model : 30 model name : Intel® Core i5 CPU 750 @ 2.67GHz stepping : 5 cpu MHz : 2673.292 cache size : 8192 KB ram : 6 GB this machine is optimized for apache and mysql (just a single IPB board) and a few cronjobs as (Backup, scheduled task cron) i have between 1000 - 3000 active users in the past 15 mins through out the whole day. my initial problem is, mysql gets too busy and the board stops working.
GreenLinks Posted January 27, 2013 Posted January 27, 2013 In order to supply any suggestions , we need way more data. Keep on mind , server optimisation is not an easy thing and each server , website's requirements are different. If you are not experienced ( which i assume as you creating this thread to receive suggestions clearly shows you are not experienced ) i highly suggest you to hire an experienced System admin to handle your MySQL optimisation. But from what i read , it looks you have Major Table Lock issues on your MySQL server. You should first enable slow query log to see which queries are creating table locks.
thompsone Posted January 27, 2013 Posted January 27, 2013 I have a pretty busy debian machine with a single IPB board installation. ......... i have between 1000 - 3000 active users in the past 15 mins through out the whole day. my initial problem is, mysql gets too busy and the board stops working. Okay that's all good information however for that architecture the usage shouldn't be a problem. What type of access do you have to this machine? SSH, Console, etc. Is this a virtual or dedicated machine? Did you backup your current my.cnf file, replace it with a default tiny config file, and restart MYSQL?
AlexJ Posted January 28, 2013 Posted January 28, 2013 sort_buffer_size = 16M read_rnd_buffer_size = 16M join_buffer_size = 8M tmp_table_size = 300M max_heap_table_size = 300M I believe this values are too high... Run mysqltuner.pl script once and post the output here. How much free memory u got during peak times? Run below and post results: 1. mysqltuner.pl script. 2. top 3. free -m 4. vmstat 10 15
Grumpy Posted January 28, 2013 Posted January 28, 2013 As others said, we need more information. Check this sticky thread:http://community.invisionpower.com/topic/362126-tools-commands-to-diagnose-problems/And post about as much information you can collect.
Weppa333 Posted January 28, 2013 Posted January 28, 2013 it all depends what hardware is behind this, but thread_concurrency 8 is enormous, and in my experience counter productive. Or this system is a 8 CPU Xeon server only doing MySQL, Even a 4 CPU HT system should never have 8 concurrent threads. In most cases, you'd better have a few threads that go fast than 8 threads that slow each other down. Also, You don't tune at all the temp table, and the limit of file descriptors. table_cache=XXXX open_files_limit=XXXXX i'm guessing this server opens a faecesload of tables in parralel (due to blatent over optimisation) and puts them in temp tables on disk.
Dhil_ Posted January 28, 2013 Author Posted January 28, 2013 The values are adjusted according to primer and tuner scripts over the past years maybe its time to run the scripts again to adjust the values again. do we have any similar "scripts" for apache results as requested -------- Performance Metrics ------------------------------------------------- [--] Up for: 2d 0h 16m 45s (9M q [53.600 qps], 1M conn, TX: 144B, RX: 2B) [--] Reads / Writes: 53% / 47% [--] Total buffers: 630.0M global + 49.2M per thread (150 max threads) [!!] Maximum possible memory usage: 7.8G (134% of installed RAM) [OK] Slow queries: 0% (505/9M) [!!] Highest connection usage: 100% (151/150) [OK] Key buffer size / total MyISAM indexes: 192.0M/366.4M [OK] Key buffer hit rate: 100.0% (258M cached / 62K reads) [OK] Query cache efficiency: 67.0% (3M cached / 5M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 0% (29 temp sorts / 127K sorts) [OK] Temporary tables created on disk: 10% (17K on disk / 177K total) [OK] Thread cache hit rate: 98% (14K created / 1M connections) [OK] Table cache hit rate: 94% (3K open / 3K opened) [OK] Open file limit used: 13% (2K/16K) [OK] Table locks acquired immediately: 99% (5M immediate / 5M locks) [!!] InnoDB data size / buffer pool: 13.3M/8.0M -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Reduce your overall MySQL memory footprint for system stability Reduce or eliminate persistent connections to reduce connection usage Variables to adjust: *** MySQL's maximum memory usage is dangerously high *** *** Add RAM before increasing MySQL buffer variables *** max_connections (> 150) wait_timeout (< 20) interactive_timeout (< 20) innodb_buffer_pool_size (>= 13M) Tasks: 152 total, 5 running, 147 sleeping, 0 stopped, 0 zombie Cpu(s): 59.2%us, 0.3%sy, 0.0%ni, 40.4%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st Mem: 6123388k total, 5922044k used, 201344k free, 240492k buffers Swap: 1060248k total, 41316k used, 1018932k free, 4497768k cached total used free shared buffers cached Mem: 5979 5777 202 0 234 4392 procs -----------memory---------- ---swap-- -----io---- -system-- ----cpu---- r b swpd free buff cache si so bi bo in cs us sy id wa 1 0 41316 204016 240524 4497984 0 0 24 37 7 7 50 1 49 0
AlexJ Posted January 28, 2013 Posted January 28, 2013 Take back up of your old MySQL config and replace with this one. Once done restart MySQL and run mysqltuner again. No wonder your system was hanging.. your MYSQL was sucking up all the memory. [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 = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking bind-address = 127.0.0.1 concurrent_insert=2 key_buffer_size = 512M max_allowed_packet = 32M thread_stack = 192K thread_cache_size = 100 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched myisam-recover = BACKUP max_connections = 100 table_cache = 8192 tmp_table_size = 96M max_heap_table_size = 96M wait_time=60 wait_timeout = 20 interactive_timeout=20 connection_timeout = 30 sort_buffer_size = 4M read_rnd_buffer_size = 512K read_buffer_size = 4M join_buffer_size = 4M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 16M query_cache_limit = 4M query_cache_size = 128M log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 5 #log-queries-not-using-indexes expire_logs_days = 2 max_binlog_size = 50M skip-bdb innodb_buffer_pool_size = 128M innodb_log_file_size = 32M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_flush_method = O_DIRECT innodb_thread_concurrency = 0 innodb_additional_mem_pool_size = 8M [mysqldump] quick quote-names max_allowed_packet = 128M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 128M !includedir /etc/mysql/conf.d/ log-error=/var/log/mysql/error.log
Dhil_ Posted January 28, 2013 Author Posted January 28, 2013 Dear AlexJ, Thankyou for ur prompt reply. I replaced my.cnf with this new my.cnf and the DB was unable to start. I reverted back to my old cnf file and its working again.. service mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!
GreenLinks Posted January 28, 2013 Posted January 28, 2013 Dear AlexJ, Thankyou for ur prompt reply. I replaced my.cnf with this new my.cnf and the DB was unable to start. I reverted back to my old cnf file and its working again.. service mysql restart Stopping MySQL database server: mysqld. Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed! Comment out innodb values within the example Alex supplied and restart MySQL daemon
AlexJ Posted January 28, 2013 Posted January 28, 2013 Sorry forgot to mention about removing your old innodb log files. /etc/init.d/mysql stop rm -f /var/lib/mysql/ib_logfile* /etc/init.d/mysql start That should take care of innodb log file issue which stops from restarting your MySQL server.
Dhil_ Posted January 28, 2013 Author Posted January 28, 2013 I have removed the old log files.. and also tried to comment out everything with innodb and yet still unable to start the daemon. my old conf file is working fine :s
AlexJ Posted January 28, 2013 Posted January 28, 2013 I have removed the old log files.. and also tried to comment out everything with innodb and yet still unable to start the daemon. my old conf file is working fine :s I am not sure what you are doing wrong. The only thing that could stop from starting mysql would be logs. What's your MySQL version?
Dhil_ Posted January 28, 2013 Author Posted January 28, 2013 Server version: 5.0.51a-24+lenny5-log (Debian) what i did was replaced my.cnf in /etc/mysql/my.cnf and then restarted mysql without success
thompsone Posted January 28, 2013 Posted January 28, 2013 Dhil_ If it were me this is what I'd do you're welcome to try it. 1.) replace your current my.cnf with this. #config date 01/28/13 [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 = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking myisam-recover = BACKUP bind-address = 127.0.0.1 concurrent_insert = 2 key_buffer = 512M max_allowed_packet = 8M thread_cache_size = 16 max_heap_table_size = 64M tmp_table_size = 64M key_buffer_size = 512M thread_cache_size = 16 query_cache_type = 1 query_cache_size = 128M log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 10 expire_logs_days = 2 max_binlog_size = 50M skip-bdb [mysqldump] quick quote-names max_allowed_packet = 16M [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/ log-error=/var/log/mysql/error.log 2.) Restart mysqld. 3.) Determine if you have any Random queries being returned for your board and disable them. (Examples: Show random pictures, Show random files, Show random posts, etc) 4.) Monitor the load on your machine and check in with mysqltuner to make sure you are happy with your settings.
GreenLinks Posted January 28, 2013 Posted January 28, 2013 Try following and if it doesn't restart , make sure to supply the exact error detail within logs [client] port = 3306 socket = /var/run/mysqld/mysqld.sock [mysqld_safe] socket = /var/run/mysqld/mysqld.sock nice = 0 [mysqld] # # * Basic Settings # user = mysql pid-file = /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock port = 3306 basedir = /usr datadir = /var/lib/mysql tmpdir = /tmp language = /usr/share/mysql/english skip-external-locking bind-address = 127.0.0.1 read_buffer_size = 9M key_buffer = 512M max_allowed_packet = 16M thread_stack = 256K thread_cache_size = 32 myisam-recover = BACKUP max_connections = 150 table_cache = 8192 tmp_table_size = 300M max_heap_table_size = 300M wait_time=60 wait_timeout = 20 interactive_timeout=20 connection_timeout = 30 sort_buffer_size = 4M read_rnd_buffer_size = 512K read_buffer_size = 4M join_buffer_size = 4M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 16M query_cache_limit = 20M query_cache_size = 128M log_slow_queries = /var/log/mysql/mysql-slow.log long_query_time = 10 expire_logs_days = 2 max_binlog_size = 50M [mysqldump] quick quote-names max_allowed_packet = 16M [mysql] #no-auto-rehash # faster start of mysql but no tab completition [isamchk] key_buffer = 16M !includedir /etc/mysql/conf.d/ log-error=/var/log/mysql/error.log
AlexJ Posted January 28, 2013 Posted January 28, 2013 Server version: 5.0.51a-24+lenny5-log (Debian) what i did was replaced my.cnf in /etc/mysql/my.cnf and then restarted mysql without success Did you followed this step ? http://community.invisionpower.com/topic/378021-suggestion-for-mysql-optimization/?p=2362849
thompsone Posted January 28, 2013 Posted January 28, 2013 Why do the log files need to be dumped for a restart? I've never had to do this and I'm curious as to the reasoning. The only reason I've ever come across that the service won't restart is that I've mistakenly given it a configuration command it doesn't understand so it halts.
AlexJ Posted January 28, 2013 Posted January 28, 2013 Why do the log files need to be dumped for a restart? I've never had to do this and I'm curious as to the reasoning. The only reason I've ever come across that the service won't restart is that I've mistakenly given it a configuration command it doesn't understand so it halts. I don't know exact reason but I am assuming InnoDB is picky... when log file changes it thinks something is wrong cos it compares value in my.cnf with current log file size and it's bound to mismatch.. so best is to delete old log files which are of no use once you shutdown the mysql process cleanly. Normally procedure I follow is: 1. shutdown mysql process 2. change config. 3. remove logs. 4. restart mysql. Always worked like charm. Just keep tunning things based on their description till your receive best I/O performance for innodb_log_file_size
thompsone Posted January 28, 2013 Posted January 28, 2013 I don't know exact reason but I am assuming InnoDB is picky... when log file changes it thinks something is wrong cos it compares value in my.cnf with current log file size and it's bound to mismatch.. so best is to delete old log files which are of no use once you shutdown the mysql process cleanly. Normally procedure I follow is: 1. shutdown mysql process 2. change config. 3. remove logs. 4. restart mysql. Always worked like charm. Just keep tunning things based on their description till your receive best I/O performance for innodb_log_file_size I don't follow this, can you explain? "Just keep tuning things based on their description until you receive best I/O performance for innodb_log_file_size" What value in my.cnf do you think the daemon is check summing against the log file that would halt the daemon? I'm learning a lot, thank you.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.