Mysql+apache Optimization.
Hello All, Redhat 9 (shrike) Mysql 4.0.15 Apache 1.3.29 PHP 4.3.3 Server Conf [4 CPUs] Dual Xeon 2.4 1GB RAM 73GB SCSI, 80GB IDE HD No of Sites : only 2 (which are very Busy and mysql intensive ) Http.conf = Timeout 300 KeepAlive On MaxKeepAliveRequests 120 KeepAliveTimeout 10 MinSpareServers 10 MaxSpareServers 20 StartServers 5 MaxClients 512 MaxRequestsPerChild 1000 my.cnf == [mysqld] set-variable = max_connections=700 safe-show-database key_buffer = 384M myisam_sort_buffer_size = 64M join_buffer_size = 1M read_buffer_size = 1M sort_buffer_size = 1M table_cache = 512 thread_cache_size = 1500 wait_timeout = 10 interactive_timeout=10 connect_timeout = 10 max_allowed_packet = 16M max_connect_errors = 10 thread_concurrency= 8 query_cache_limit = 2M query_cache_size = 32M query_cache_type = 1 skip-innodb log-slow-queries = /var/log/mysql_slow_query_log long_query_time = 5 [mysqld_safe] err-log=/var/log/mysqld.log open_files_limit = 8192 [mysqldump] quick max_allowed_packet = 16M [isamchk] key_buffer = 64M sort_buffer_size = 64M read_buffer = 16M write_buffer = 16M [myisamchk] key_buffer = 64M sort_buffer_size = 64M read_buffer = 16M write_buffer = 16M Turck-MMCache Enabled (Zend Extension) top 05:46:25 up 12 days, 17:35, 3 users, load average: 7.46, 9.02, 10.76 509 processes: 501 sleeping, 6 running, 1 zombie, 1 stopped CPU0 states: 64.2% user 19.1% system0.1% nice 0.0% iowait 16.0% idle CPU1 states: 64.0% user 19.1% system0.0% nice 0.0% iowait 16.3% idle CPU2 states: 60.3% user 23.1% system0.0% nice 0.0% iowait 16.0% idle CPU3 states: 60.2% user 20.5% system0.0% nice 0.0% iowait 18.3% idle Mem: 1030244k av, 912116k used, 118128k free, 0k shrd, 187872k buff 534216k actv, 49792k in_d, 18180k in_c Swap: 2096440k av, 132220k used, 1964220k free 441464k cached PID USER PRI NI SIZE RSS SHARE STAT %CPU %MEM TIME CPU COMMAND 12228 mysql 15 0 23588 20M 1444 S99.9 2.0 56:42 3 mysqld 3419 root 15 0 2676 1684 1616 S 7.0 0.1 13:57 1 httpd Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL+Apache Optimization
Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. Try the following changes: Apache: Turn KeepAlives off + increase your MaxRequestsPerChild. Apache is probably spending too much time recreating httpd processes. (I presume Apache MySQL are running on the same box). Also are your using mysql_connect() or pconnects? MySQL: Try increasing your table cache. Does your thread cache need to be that high? Maybe you should increase the thread concurrency first. According to the my.cnf, you can increase this based on the amount of CPUs, you start off by setting it to 24-32? Your max_connections settings is high and that will take up a fair amount of file descriptors. I suspect your table cache isn't big enough (try 2048 as a starting point, see http://www.mysql.com/doc/en/Table_cache.html for more info) (Not intending to hijack this thread), but I'm going through a similar issue with my Dual Xeon, 6GB RAM and RAID5 SCSI, but I'm running out of file-descriptors and max_connections=520 I hope my suggestions are useful and it'll be interesting to see whether they help... httpd.conf === KeepAlive Off MaxRequestsPerChild 9000 my.cnf == [mysqld] table_cache = 2048 thread_cache_size = 256 thread_concurrency= 32 _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL+Apache Optimization
Hello, Thank you for the Reply, I'll follow you suggestions and will post the results here, Also I use connect only and not aothers. Thank you again. ;) Vishal. - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:06 PM Subject: MySQL+Apache Optimization Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. Try the following changes: Apache: Turn KeepAlives off + increase your MaxRequestsPerChild. Apache is probably spending too much time recreating httpd processes. (I presume Apache MySQL are running on the same box). Also are your using mysql_connect() or pconnects? MySQL: Try increasing your table cache. Does your thread cache need to be that high? Maybe you should increase the thread concurrency first. According to the my.cnf, you can increase this based on the amount of CPUs, you start off by setting it to 24-32? Your max_connections settings is high and that will take up a fair amount of file descriptors. I suspect your table cache isn't big enough (try 2048 as a starting point, see http://www.mysql.com/doc/en/Table_cache.html for more info) (Not intending to hijack this thread), but I'm going through a similar issue with my Dual Xeon, 6GB RAM and RAID5 SCSI, but I'm running out of file-descriptors and max_connections=520 I hope my suggestions are useful and it'll be interesting to see whether they help... httpd.conf === KeepAlive Off MaxRequestsPerChild 9000 my.cnf == [mysqld] table_cache = 2048 thread_cache_size = 256 thread_concurrency= 32 _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL+Apache Optimization
Hello , What are Questions in Mysql , I mean is there something know as Questions in Mysql, I got a Script in PHP called as testload.php and it shows me this output : total processes are 55 Mysql Status is Uptime: 1839 Threads: 55 Questions: 175421 == What does this Stands For ??? Slow queries: 0 Opens: 142 Flush tables: 1 Open tables: 136 Queries per second avg: 95.389 Any comments will appreciated. Thank you, Vishal. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:35 PM Subject: Re: MySQL+Apache Optimization Hello, Thank you for the Reply, I'll follow you suggestions and will post the results here, Also I use connect only and not aothers. Thank you again. ;) Vishal. - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:06 PM Subject: MySQL+Apache Optimization Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. Try the following changes: Apache: Turn KeepAlives off + increase your MaxRequestsPerChild. Apache is probably spending too much time recreating httpd processes. (I presume Apache MySQL are running on the same box). Also are your using mysql_connect() or pconnects? MySQL: Try increasing your table cache. Does your thread cache need to be that high? Maybe you should increase the thread concurrency first. According to the my.cnf, you can increase this based on the amount of CPUs, you start off by setting it to 24-32? Your max_connections settings is high and that will take up a fair amount of file descriptors. I suspect your table cache isn't big enough (try 2048 as a starting point, see http://www.mysql.com/doc/en/Table_cache.html for more info) (Not intending to hijack this thread), but I'm going through a similar issue with my Dual Xeon, 6GB RAM and RAID5 SCSI, but I'm running out of file-descriptors and max_connections=520 I hope my suggestions are useful and it'll be interesting to see whether they help... httpd.conf === KeepAlive Off MaxRequestsPerChild 9000 my.cnf == [mysqld] table_cache = 2048 thread_cache_size = 256 thread_concurrency= 32 _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL+Apache Optimization
Questions - Number of queries sent to the server. See http://www.mysql.com/doc/en/SHOW_STATUS.html for more info MySQL user From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: MySQL+Apache Optimization Date: Wed, 7 Jan 2004 15:12:19 +0530 Hello , What are Questions in Mysql , I mean is there something know as Questions in Mysql, I got a Script in PHP called as testload.php and it shows me this output : total processes are 55 Mysql Status is Uptime: 1839 Threads: 55 Questions: 175421 == What does this Stands For ??? Slow queries: 0 Opens: 142 Flush tables: 1 Open tables: 136 Queries per second avg: 95.389 Any comments will appreciated. Thank you, Vishal. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:35 PM Subject: Re: MySQL+Apache Optimization Hello, Thank you for the Reply, I'll follow you suggestions and will post the results here, Also I use connect only and not aothers. Thank you again. ;) Vishal. - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:06 PM Subject: MySQL+Apache Optimization Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. Try the following changes: Apache: Turn KeepAlives off + increase your MaxRequestsPerChild. Apache is probably spending too much time recreating httpd processes. (I presume Apache MySQL are running on the same box). Also are your using mysql_connect() or pconnects? MySQL: Try increasing your table cache. Does your thread cache need to be that high? Maybe you should increase the thread concurrency first. According to the my.cnf, you can increase this based on the amount of CPUs, you start off by setting it to 24-32? Your max_connections settings is high and that will take up a fair amount of file descriptors. I suspect your table cache isn't big enough (try 2048 as a starting point, see http://www.mysql.com/doc/en/Table_cache.html for more info) (Not intending to hijack this thread), but I'm going through a similar issue with my Dual Xeon, 6GB RAM and RAID5 SCSI, but I'm running out of file-descriptors and max_connections=520 I hope my suggestions are useful and it'll be interesting to see whether they help... httpd.conf === KeepAlive Off MaxRequestsPerChild 9000 my.cnf == [mysqld] table_cache = 2048 thread_cache_size = 256 thread_concurrency= 32 _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL+Apache Optimization
Hello all, Guys Where will i Find out If Mysql database is Optimized or Not, I have read the Documentation given on Mysql.com and also checked the database which tells that It has been Optimized well, Actually When I See the Mysql processlists it put all the Update Statement and Select Count(col1) from table name statements, Only one table is Heavily used. I also thought of Killing Mysql connections after a Specific time, where will i configure that ??? Thanks for any comments. Vishal. - Original Message - From: MySQL User Bob [EMAIL PROTECTED] To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 3:25 PM Subject: Re: MySQL+Apache Optimization Questions - Number of queries sent to the server. See http://www.mysql.com/doc/en/SHOW_STATUS.html for more info MySQL user From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: MySQL+Apache Optimization Date: Wed, 7 Jan 2004 15:12:19 +0530 Hello , What are Questions in Mysql , I mean is there something know as Questions in Mysql, I got a Script in PHP called as testload.php and it shows me this output : total processes are 55 Mysql Status is Uptime: 1839 Threads: 55 Questions: 175421 == What does this Stands For ??? Slow queries: 0 Opens: 142 Flush tables: 1 Open tables: 136 Queries per second avg: 95.389 Any comments will appreciated. Thank you, Vishal. - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:35 PM Subject: Re: MySQL+Apache Optimization Hello, Thank you for the Reply, I'll follow you suggestions and will post the results here, Also I use connect only and not aothers. Thank you again. ;) Vishal. - Original Message - From: my5ql _ [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, January 07, 2004 2:06 PM Subject: MySQL+Apache Optimization Can Anyone please tell me why I am not able to get the Load Down and the site Load Faster.The server load is always above 5.00 Thanks in Advance. Vishal. Try the following changes: Apache: Turn KeepAlives off + increase your MaxRequestsPerChild. Apache is probably spending too much time recreating httpd processes. (I presume Apache MySQL are running on the same box). Also are your using mysql_connect() or pconnects? MySQL: Try increasing your table cache. Does your thread cache need to be that high? Maybe you should increase the thread concurrency first. According to the my.cnf, you can increase this based on the amount of CPUs, you start off by setting it to 24-32? Your max_connections settings is high and that will take up a fair amount of file descriptors. I suspect your table cache isn't big enough (try 2048 as a starting point, see http://www.mysql.com/doc/en/Table_cache.html for more info) (Not intending to hijack this thread), but I'm going through a similar issue with my Dual Xeon, 6GB RAM and RAID5 SCSI, but I'm running out of file-descriptors and max_connections=520 I hope my suggestions are useful and it'll be interesting to see whether they help... httpd.conf === KeepAlive Off MaxRequestsPerChild 9000 my.cnf == [mysqld] table_cache = 2048 thread_cache_size = 256 thread_concurrency= 32 _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
