hi we have problem on our server database high load problem on our server. some time 100% usage of cpu mysql . we use innodb and myislm both on our database . we have too many queries selected and inserted at a time. our server detail Intel Xeon E3-1230 3.20Ghz 8GB Ram our my.cnf file this local-infile=0 max_connections = 600 max_user_connections=1000 key_buffer_size = 1G myisam_sort_buffer_size = 64M read_buffer_size = 64M table_open_cache = 5000 thread_cache_size = 384 wait_timeout = 20 connect_timeout = 10 tmp_table_size = 256M max_heap_table_size = 128M max_allowed_packet = 64M net_buffer_length = 16384 max_connect_errors = 10 concurrent_insert = 2 read_rnd_buffer_size = 786432 bulk_insert_buffer_size = 8M query_cache_limit = 5M query_cache_size = 128M query_cache_type = 1 query_prealloc_size = 262144 query_alloc_block_size = 65535 transaction_alloc_block_size = 8192 transaction_prealloc_size = 4096 max_write_lock_count = 8 slow_query_log log-error external-locking=FALSE open_files_limit=50000 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid [mysqldump] quick max_allowed_packet = 16M [isamchk] key_buffer = 1G sort_buffer = 384M read_buffer = 512M write_buffer = 256M [myisamchk] key_buffer = 1G sort_buffer = 384M read_buffer = 512M write_buffer = 256M #### Per connection configuration #### sort_buffer_size = 1M join_buffer_size = 1M thread_stack = 192K Code (markup): please tell us what changes on it .
You probably need all the tables to be innodb. Have you checked that you don't have too many indexes - or too few? I can't help with the config but there may be some tuning to do in your code or in the database itself.
You could try to tune your MySQL performance using MySQL tuner script. MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. Download the script using: wget --no-check-certificate https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl Then, run the following commands: chmod +x mysqltuner.pl perl mysqltuner.pl More information on this you can find at mysqltuner.com . Also, you may find this article useful.
One of the widely used method to reduce the server load and memory consumption on servers is apachebooster. If your server is based on cpanel, then you may consider using the apachebooster.
You might want to have your code reviewed, and decrease max_connections, key_buffer, and raise query_cache_limit to half of query_cache_size. Also do 'explain' runs on your SQL queries to see if it's using indexes.
did you tried to learn which scripts eating most of the CPU? Example i have in WHM control panel section called "Daily Process Log" when you know this, maybe you can take a look what these do with mysql?
thanks for all . actually here is not load with script . load problem mysql . actually one time too many query insert and selected . then processor high load we are using webuzo panel . when load high above 100 percent then server crash mysql database. may this problem of webuzo panel if yes then tell me cpanel best for high load
No, it has nothing to do with the control panel. You should look at the queries that are running and see if they are actually required.. sometimes some queries are placed in scripts which shouldn't be there and when a visitor request that web page, the query is executed but isnt of any importance. This is where a developer can optimize as well as place queries where they are required so it doesnt add up to resource usage. This also happens if some 3rd party plugins or such are installed on the website.
can its possible mysql load on ram not a cpu load. we want when MySQL queries executes its all load on our ram
Yes, it is possible and in that case, you should tweak the mysql parameters to lessen the RAM usage although, if some queries are not optimized properly, tweaking of parameters is of little help.
There are lots of tutorials available on the net of tweaking the parameters but most importantly, you must know what exactly is causing the problem so to tweak the parameters properly.
Have you optimised your queries, your database design and your indexes? No point tweaking the server when it's the code that is causing the problem