View Single Post
  #41  
Old 27th October 2010, 06:39
crypted crypted is offline
Senior Member
 
Join Date: Dec 2006
Location: Oklahoma, USA
Posts: 429
Thanks: 3
Thanked 14 Times in 6 Posts
Default

Most queries to SQL come from packaged scripts like that of Wordpress and other web packages.

What do you suggest for the SQL long query wait to be instead of 10 seconds? I agree there's a good bit being written to the harddrive. Below is my.cnf, let me know what your suggested changes are.

Code:
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
#
# 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
#
# * Fine Tuning
#
key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 128K
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        = 200
table_cache            = 650
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 24M
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 = 2
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        = 10
max_binlog_size         = 100M
#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
join_buffer_size=3M
max_heap_table_size=375M
tmp_table_size=375M
low_priority_updates=1
concurrent_insert=2

innodb_buffer_pool_size=64M



[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer              = 64M

#
# * 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/

#join_buffer_size=2M
#max_heap_table_size=80M
#tmp_table_size=80M

#low_priority_updates=1

#concurrent_insert=2
As far as errors, I'm getting the following in web8's error log:
Code:
[Mon Oct 25 06:59:57 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 07:14:39 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 07:18:19 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 07:24:16 2010] [warn] mod_fcgid: read data timeout in 360 seconds
[Mon Oct 25 07:24:16 2010] [error] [client 66.118.142.211] Premature end of script headers: wp-cron.php
[Mon Oct 25 07:29:19 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 07:55:06 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 08:02:16 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 08:24:02 2010] [warn] (104)Connection reset by peer: mod_fcgid: read data from fastcgi server error.
[Mon Oct 25 08:24:02 2010] [error] [client 66.118.142.211] Premature end of script headers: wp-cron.php
[Mon Oct 25 09:14:35 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 09:24:31 2010] [warn] mod_fcgid: read data timeout in 360 seconds
[Mon Oct 25 09:24:31 2010] [error] [client 66.118.142.211] Premature end of script headers: wp-cron.php
[Mon Oct 25 09:44:25 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 10:13:35 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 10:24:37 2010] [warn] mod_fcgid: read data timeout in 360 seconds
[Mon Oct 25 10:24:37 2010] [error] [client 66.118.142.211] Premature end of script headers: wp-cron.php
[Mon Oct 25 10:28:31 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 10:37:36 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 10:51:02 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 10:52:24 2010] [error] [client 72.198.65.173] File does not exist: /var/www/inter5.org/web/wp-content/plugins/statpress$
[Mon Oct 25 10:59:24 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 11:09:18 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 11:23:57 2010] [warn] (104)Connection reset by peer: mod_fcgid: read data from fastcgi server error.
[Mon Oct 25 11:23:57 2010] [error] [client 66.118.142.211] Premature end of script headers: wp-cron.php
[Mon Oct 25 11:48:25 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
Web5 (the other big site that has a lot of spawns of php-cgi) has the following:
Code:
[Mon Oct 25 05:41:13 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:14 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:16 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:17 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:19 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:21 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:22 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:24 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:25 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:27 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:28 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:30 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:32 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:33 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:35 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:36 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:38 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:39 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:41 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:43 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:44 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:46 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:47 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
[Mon Oct 25 05:41:49 2010] [warn] (103)Software caused connection abort: mod_fcgid: ap_pass_brigade failed in handle_request functi$
Repeats quite often for both sites, just warns really though.
__________________
ISPC3 on Debian! It's great!
Reply With Quote
Sponsored Links