help, Hight load Mysql

Portal Home > Knowledgebase > Articles Database > help, Hight load Mysql

Posted by Sanyh, 10-14-2009, 02:51 PM
hi Hight load Mysql , help me i have server 2 x Intel Dual Core Xeon 5130 2.0Ghz 8Gb Ram centos 5 32bit Nginx+Apache pid 2517 mysql 0 cpu29.8% mem3.6% /usr/sbin/mysqld --basedir=/ --datadir=/var/lib/mysql --user=mysql --pid-file=/var/lib/mysql/l.net.pid --skip-external-locking my.cnf
Posted by TH-Guy, 10-14-2009, 04:04 PM
When you have high load, please run the "top" command and show us the output.
Posted by activelobby4u, 10-14-2009, 04:27 PM
I suggest you lookout for the slow queries and optimize them
Posted by relaxiha, 10-14-2009, 06:24 PM
Installation is extremely simple: Download the script Change the permissions for the file Run it Apply the sugesttions This can help to figure out better
Posted by JohnCS, 10-15-2009, 02:30 AM
1- Run this command and show us the output ps auxf | grep mysql 2- Change max_connections = 500 to max_connections = 100 mysql uses the memory based on max_connections even the connections are not in use
Posted by eth10, 10-15-2009, 03:46 AM
mysqladmin proc stat check who is creating that load.
Posted by bluenile, 10-15-2009, 04:49 AM
1. Check all the running process/queries in the mysql database # mysqladmin -u root -p processlist +----+------+-----------+----+---------+------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+----+---------+------+-------+------------------+ | 20 | root | localhost | | Sleep | 36 | | | | 23 | root | localhost | | Query | 0 | | show processlist | +----+------+-----------+----+---------+------+-------+------------------+ You will see an output as above. You can use this command effectively to debug any performance issue and identify the query that is causing problems. You can kill a hanging MySQL Client Process. Use the kill option and pass the process_id as shown below. To kill multiple process you can pass comma separated process ids. # mysqladmin -u root -p kill 20 2. Enable Logging of Slow Queries. In your my.cnf "log-slow-queries" parameter is commented. You can uncomment this and restart mysql. After enabling slow query log, MySQL will create, capture and log to the log file with all SQL statements that took more than long_query_time seconds to execute, which is by default set to 10 seconds. You can then examine all the SQL queries that took longer than the pre-defined number of seconds (10 seconds by default) in the host_name-slow.log, and then take the necessary steps to optimize the SQL statements. The slow query log will tell you about what was time the query completed, how long the query took to run, how long it took to secure its locks, how many rows were sent back as a result, how many rows were examined to determine the result, which database was used, and the actual query itself. But bear in mind that a SQL query contained in the log may have already optimum, but executed slowly due to the system resources been used up by the actual slow statement that need to be fine tuned.
Posted by bluenile, 10-15-2009, 04:55 AM
3. You can run the following to obtain how many mysql queries a user has open: 4. Run the following to check the database with the most queries:
Posted by Alistair Smith, 10-15-2009, 07:07 AM
Try reducing the value of the following variables wait_timeout interactive_timeout connection_timeout to a smaller value (say 20 or 30). The issue should be resolved to a particular extend.
Posted by Sanyh, 10-17-2009, 04:41 AM
I have made optimisation and rapair mysqlcheck -u root -p -Aor But has changed nothing
Posted by JohnCS, 10-17-2009, 02:10 PM
Is it cPanel server?
Posted by Sanyh, 10-17-2009, 06:25 PM
ps auxf | grep mysql
Posted by Sanyh, 10-17-2009, 06:27 PM
yes mysqladmin -u root -p processlist
Posted by Sanyh, 10-17-2009, 06:29 PM
mysqladmin pr | awk -F\| {'print $3'} | sort -nk1 | awk -F_ {'print $1'} |uniq -c |sort
Posted by Sanyh, 10-17-2009, 06:31 PM
mysqladmin pr | awk -F\| {'print $5'} | sort -nk1 |uniq -c |sort
Posted by Sanyh, 10-17-2009, 06:34 PM
this my top
Posted by Sanyh, 10-17-2009, 06:43 PM
tuning-primer.sh
Posted by JohnCS, 10-18-2009, 10:16 AM
If you are using cPanel Current 11.25.0 then downgrade it to 11.24 Release or stable tree. Due to bug in cPanel Current 11.25.0 Mysql is using more resources. There was the same issue with my one client and solved after downgrade to cPanel 11.24 release.
Posted by Sanyh, 10-18-2009, 02:15 PM
i use cPanel 11.24.5-R38506 - WHM 11.24.2 - X 3.9