| View previous topic :: View next topic |
| Author |
Message |
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
 Posted: Fri Nov 09, 2007 1:58 am Post subject: MySQL Help [VERY URGENT] |
 |
|
Hello All
I'm getting desperate for some MySQL help on one of my servers. It started mysteriously lastnight. One of my applications (code has not changed in serveral months to a year), complained about corrupted tables. Viewing the logs, I was definitely seeing MyISAM corruption, which I could replicate by issuing a OPTIMIZE after the sql performed a UPDATE. Again, this code has been like this for many months. I commented out the OPTIMIZE and repaired the tables, and that seemed to have fixed the problem.
| Code: | 071029 16:58:30 InnoDB: Started; log sequence number 0 4530733
071029 16:58:31 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.44' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.44
071106 22:38:14 [Warning] Warning: Optimize table got errno 12 on compdb.tblUsers, retrying
071106 22:38:14 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './compdb/tblUsers.MYI'; try to repair it
071106 22:38:14 [ERROR] /usr/sbin/mysqld: Table './compdb/tblUsers' is marked as crashed and last (automatic?) repair failed
071106 22:38:14 [ERROR] /usr/sbin/mysqld: Table './compdb/tblUsers' is marked as crashed and last (automatic?) repair failed
071106 22:44:19 [ERROR] /usr/sbin/mysqld: Table './compdb/tblUsers' is marked as crashed and last (automatic?) repair failed
071106 22:44:19 [ERROR] /usr/sbin/mysqld: Table './compdb/tblUsers' is marked as crashed and last (automatic?) repair failed
071106 23:34:14 [ERROR] /usr/sbin/mysqld: Table './compdb/tblUsers' is marked as crashed and last (automatic?) repair failed
071106 23:34:14 [ERROR] /usr/sbin/mysqld: Table './compdb/tblUsers' is marked as crashed and last (automatic?) repair failed
071106 23:34:14 [ERROR] /usr/sbin/mysqld: Table './compdb/tblUsers' is marked as crashed and last (automatic?) repair failed
071106 23:34:14 [ERROR] /usr/sbin/mysqld: Table './compdb/tblUsers' is marked as crashed and last (automatic?) repair failed
071106 23:34:14 [ERROR] /usr/sbin/mysqld: Table './compdb/tblUsers' is marked as crashed and last (automatic?) repair failed |
Later on that night, another one of my servers began seeing the same thing, but I received additional error info (slightly different than the first server)...
| Code: | 071107 23:35:28 [Warning] Warning: Optimize table got errno 12 on compdb2.tblProducts, retrying
071107 23:36:59 [Warning] Warning: Optimize table got errno 12 on compdb2.tblProducts, retrying
071107 23:42:42 [Warning] Warning: Optimize table got errno 12 on compdb2.tblProducts, retrying
071107 23:42:42 [ERROR] /usr/sbin/mysqld: Incorrect key file for table './compdb2/tblProducts.MYI'; try to repair it
071107 23:42:42 [ERROR] /usr/sbin/mysqld: Table './compdb2/tblProducts' is marked as crashed and last (automatic?) repair failed
071107 23:42:42 [ERROR] /usr/sbin/mysqld: Table './compdb2/tblProducts' is marked as crashed and last (automatic?) repair failed
071108 2:50:24 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 179256 bytes)
071108 2:50:24 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
071108 3:01:47 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 179256 bytes)
071108 3:01:47 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space |
Now, this server has a similar environment.. not much has changed (ntp, debianutils, pciutils.. are about it for both servers). As we can see, this one is complaining about memory usage. The odd thing, is that no swap was used and the load was under '1' during this. I restarted the MySQL server, and everything looked good again. I also removed the OPTIMIZE call here as well. After some investigation, I saw that you could raise mem limits to help the OPTIMIZATION, so I edit'd my.cnf on both servers and edit the following
| Code: | #sort_buffer_size = 512K
sort_buffer_size = 2M
#read_buffer_size = 256K
read_buffer_size = 2M |
Now, the first server spirals out of control (at random) with the following messages:
| Code: | 071108 8:06:25 InnoDB: Started; log sequence number 0 4530733
071108 8:06:25 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.44' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.44
071108 15:14:22 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677372 bytes)
071108 15:14:26 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677372 bytes)
071108 15:15:01 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677564 bytes)
071108 15:15:25 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677372 bytes)
071108 15:15:27 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1258272 bytes)
071108 15:15:36 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677372 bytes)
071108 15:16:16 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677696 bytes)
071108 15:16:22 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677720 bytes)
071108 15:16:22 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677720 bytes)
071108 15:16:37 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677720 bytes)
--- mysql restart here |
approximately 2 hours later it happened again..
| Code: | 071108 15:39:40 InnoDB: Started; log sequence number 0 4530733
071108 15:39:40 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.0.44' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.44
071108 17:15:10 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677372 bytes)
071108 17:15:21 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677372 bytes)
071108 17:15:30 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677372 bytes)
071108 17:15:40 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677372 bytes)
071108 17:16:01 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677372 bytes)
071108 17:16:21 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677372 bytes)
071108 17:16:26 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677720 bytes)
071108 17:16:30 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 1677372 bytes)
--- mysql restart here |
The odd things are, load is not outragous when this happens, load averages are really low on both servers, so high is 1, both server were seeing 0 swap usage. Both servers have PHP applications that have not changed in many months. Both servers have had the standard updates with out any incident. I'll supply genlop output in case there is a issue I missed.
| Code: | Sun Nov 4 08:37:17 2007 >>> sys-process/lsof-4.78-r1
Sun Nov 4 08:37:31 2007 >>> sys-apps/pciutils-2.2.7-r1
Tue Nov 6 07:53:22 2007 >>> net-misc/ntp-4.2.4_p4
Thu Nov 8 08:31:13 2007 >>> sys-apps/debianutils-2.25
Thu Nov 8 08:37:17 2007 >>> dev-libs/openssl-0.9.8g |
Here is my current version of MySQL:
| Code: | | [ebuild R ] dev-db/mysql-5.0.44 USE="latin1 perl ssl -berkdb -big-tables -cluster -debug -embedded -extraengine -max-idx-128 -minimal (-selinux) -static" 23,869 kB |
Here is my output of emerge --info (same for both servers)
| Code: | Portage 2.1.3.16 (default-linux/x86/2007.0, gcc-4.1.2, glibc-2.6.1-r0, 2.6.22-hardened-r8 i686)
=================================================================
System uname: 2.6.22-hardened-r8 i686 AMD Sempron(tm) 2600+
Timestamp of tree: Thu, 08 Nov 2007 09:30:04 +0000
app-shells/bash: 3.2_p17
dev-lang/python: 2.4.4-r6
dev-python/pycrypto: 2.0.1-r6
sys-apps/baselayout: 1.12.9-r2
sys-apps/sandbox: 1.2.18.1-r2
sys-devel/autoconf: 2.13, 2.61-r1
sys-devel/automake: 1.4_p6, 1.5, 1.6.3, 1.7.9-r1, 1.8.5-r3, 1.9.6-r2, 1.10
sys-devel/binutils: 2.18-r1
sys-devel/gcc-config: 1.3.16
sys-devel/libtool: 1.5.24
virtual/os-headers: 2.6.22-r2
ACCEPT_KEYWORDS="x86"
CBUILD="i686-pc-linux-gnu"
CFLAGS="-march=athlon-xp -O3 -pipe"
CHOST="i686-pc-linux-gnu"
CONFIG_PROTECT="/etc /var/bind"
CONFIG_PROTECT_MASK="/etc/env.d /etc/gconf /etc/php/apache2-php5/ext-active/ /etc/php/cgi-php5/ext-active/ /etc/php/cli-php5/ext-active/ /etc/revdep-rebuild /etc/terminfo /etc/udev/rules.d"
CXXFLAGS="-O2 -mcpu=i686 -pipe"
DISTDIR="/usr/portage/distfiles"
FEATURES="distlocks metadata-transfer sandbox sfperms strict unmerge-orphans userfetch"
GENTOO_MIRRORS="http://distfiles.gentoo.org http://distro.ibiblio.org/pub/linux/distributions/gentoo"
PKGDIR="/usr/portage/packages"
PORTAGE_RSYNC_OPTS="--recursive --links --safe-links --perms --times --compress --force --whole-file --delete --delete-after --stats --timeout=180 --exclude=/distfiles --exclude=/local --exclude=/packages --filter=H_**/files/digest-*"
PORTAGE_TMPDIR="/var/tmp"
PORTDIR="/usr/portage"
PORTDIR_OVERLAY="/usr/local/portage"
SYNC="rsync://rsync.gentoo.org/gentoo-portage"
USE="apache2 berkdb bzip2 cli cracklib crypt dri fortran gdbm gpm iconv innodb isdnlog maildir midi mudflap mysql ncurses nptl nptlonly openmp openssh pam pcre perl php pppd pwdb python readline reflection sasl session snmp snortsam spl ssl tcpd unicode x86 xorg zlib" ALSA_CARDS="ali5451 als4000 atiixp atiixp-modem bt87x ca0106 cmipci emu10k1 emu10k1x ens1370 ens1371 es1938 es1968 fm801 hda-intel intel8x0 intel8x0m maestro3 trident usb-audio via82xx via82xx-modem ymfpci" ALSA_PCM_PLUGINS="adpcm alaw asym copy dmix dshare dsnoop empty extplug file hooks iec958 ioplug ladspa lfloat linear meter mulaw multi null plug rate route share shm softvol" ELIBC="glibc" INPUT_DEVICES="keyboard mouse evdev" KERNEL="linux" LCD_DEVICES="bayrad cfontz cfontz633 glk hd44780 lb216 lcdm001 mtxorb ncurses text" USERLAND="GNU" VIDEO_CARDS="apm ark chips cirrus cyrix dummy fbdev glint i128 i740 i810 imstt mach64 mga neomagic nsc nv r128 radeon rendition s3 s3virge savage siliconmotion sis sisusb tdfx tga trident tseng v4l vesa vga via vmware voodoo"
Unset: CPPFLAGS, CTARGET, EMERGE_DEFAULT_OPTS, INSTALL_MASK, LANG, LC_ALL, LDFLAGS, LINGUAS, MAKEOPTS, PORTAGE_COMPRESS, PORTAGE_COMPRESS_FLAGS, PORTAGE_RSYNC_EXTRA_OPTS |
Here is a snap of top so you can see what kind of memory I have, etc.
| Code: | top - 18:19:18 up 10 days, 10:21, 5 users, load average: 0.08, 0.08, 0.10
Tasks: 113 total, 1 running, 112 sleeping, 0 stopped, 0 zombie
Cpu(s): 2.9%us, 0.5%sy, 0.0%ni, 95.8%id, 0.7%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 1540848k total, 1492248k used, 48600k free, 266468k buffers
Swap: 976744k total, 0k used, 976744k free, 537800k cached |
Here is ps of mysql
| Code: | | 31159 mysql 18 0 1336m 30m 4776 S 0.7 2.0 0:08.91 mysqld |
Please.. if anyone can help.. that would be awesome!!!
Thanks!
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
 |
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Fri Nov 09, 2007 2:14 am Post subject: |
|
|
Here is my.cnf file...
| Code: | [client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1
[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1
[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1
[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1
[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1
[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1
[myisamchk]
character-sets-dir=/usr/share/mysql/charsets
[myisampack]
character-sets-dir=/usr/share/mysql/charsets
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
[mysqld]
character-set-server = latin1
default-character-set = latin1
user = mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysqld.err
basedir = /usr
datadir = /var/lib/mysql
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
language = /usr/share/mysql/english
wait_timeout = 10000000
set-variable = local-infile=0
bind-address = 127.0.0.1
set-variable = max_connections=175
query-cache-type = 1
query-cache-size = 20M
log-slow-queries = /var/log/mysql/mysql-slow.log
server-id = 1
tmpdir = /tmp/
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_data_file_path = ibdata1:10M:autoextend:max:128M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
set-variable = innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout |
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Fri Nov 09, 2007 3:56 pm Post subject: |
|
|
On server 1, I re-emerged mysql and I thought that fixed it. I also re-emerged mysql on server 2 and no incidences occurred last night. Of course, this morning, I thought I was in the clear.. and sure enough, server 2 is now crashing (only server 1 was crashing hard yesterday.. no server 2 is).
Please.. any help??
Thanks!
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Fri Nov 09, 2007 4:46 pm Post subject: |
|
|
Connected to the server with mysql-administrator to see Memory Usage in action while I hit the box. I was able to replicate the crash here:
http://www.uno-code.com/files/crash.gif
Next I reset the mem values back to
| Code: | sort_buffer_size = 512K
read_buffer_size = 256K |
And I was not able to recreate the crash (in my initial 'refresh' test)
Here is a graph showing me hitting the server with new values set in mysql. I sent requests for approximately 5 minutes without a issue.
http://www.uno-code.com/files/crash2.gif
I still things are broken, so any mysql-gurus out there.. please chime in.
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
BitJam
Advocate
Joined: 12 Aug 2003
Posts: 2508
Location: Silver City, NM
|
| Posted: Fri Nov 09, 2007 6:12 pm Post subject: |
|
|
This is an interesting problem. Assuming everything you said is true, one wonders why the problem would start showing up now instead of months ago. You haven't mentioned if these servers are public or private. My guess is that the thing that changed is someone has broken you php security (such that it is) and is attacking your system with SQL injection, but this is only a guess.
I suggest you try to correlate your web/php logs with you MySQL error log and see if you can find out which particular transaction is initiating the error condition. You may need to increase some of the logging in order to do this.
Another idea would be to pay MySQL for support on this issue. I think this would be the quickest path to a solution and well worth the minor expense. |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Fri Nov 09, 2007 7:19 pm Post subject: |
|
|
Hello BitJam
First off.. thanks for replying to my thread!
These are public servers, but I do watch the logs very carefully, including the Apache access_logs via logcheck. I've also been going over them very closely, since that did cross my mind as well.
I have a crazy theory right now, which might be holding up. I have sites on both servers that retrieve XML/RSS feeds and stores them in the database.. and immediately after a OPTIMIZE is called. I feel like the RSS size drastically changed and caused the corruption as seen in the first code blocks up above. That happened approxmitely at 11:30PM, but later on that night at 3AMish, I see the out of memory error. I initially thought the OPTIMIZE and out of memory were linked, but I think the out of memory may have been related to crashed tables and possibly related to my database backup dump which happens nightly.
I then started down the path of trying to figure out how to increase the memory, so I adjusted those server values. I think that was a mistake. I'm not 100% sure what's going on, but I think I told mysql it had 2MBs of buffer, but actually it did not, and this caused the crashes yesterday and today. I reset the values to the original, and now, I'm unable to cause the out of memory crash by hitting the server. I was definitely able to replicate it by sending many requests to the server.
Now, I'm trying to focus on what exactly caused the OPTIMIZATION error. I re-added that snip of code and started hammering the site, and it's handling it fine as it did before. Both servers received similar RSS files that night, and that could explain why both servers nearly the same time had similar table corruptions.
Thanks!
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Sun Dec 02, 2007 4:25 pm Post subject: |
|
|
Crap....
Yep.. started again lastnight. I was able to get a few more clues though. Here is a snip of the mysqld.err log:
| Code: | Version: '5.0.44-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 Gentoo Linux mysql-5.0.44-r2
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:15:03 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 257408 bytes)
071202 1:16:51 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 256008 bytes)
......
|
Here is a snap of `top` during the hosage, and mysql process:
| Code: | top - 07:19:33 up 33 days, 23:21, 3 users, load average: 0.24, 0.27, 0.28
Tasks: 99 total, 1 running, 98 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.4%us, 0.5%sy, 0.0%ni, 95.3%id, 0.7%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 1540848k total, 1505512k used, 35336k free, 314200k buffers
Swap: 976744k total, 376k used, 976368k free, 411056k cached
1212 mysql 15 0 4095m 56m 4828 S 0.0 3.8 30:30.85 mysqld |
Here is the weird thing, MySQL was not completely down.. just for Apache. Meaning, mail authentication continued to work (virtual mail MTA set up), cronjobs that use MySQL ran fine, and cacti poller worked fine.
I was able to fingerprint some of the errors to apps. I have custom PHP error handling that emails me reports on 'issues'. These would include the specific error message complaining about needed bytes. These were somewhat distinct depending on the site. I was able to match them with page requests in the access_log down to the second. I was unable to find this error (Needed 257408 bytes), but I was able to correlate it to the logs though.. and that was server-status?auto. Cacti uses this for apache stats. Apache stats continued to graph throughout the outage.
My updated my.cnf is this:
| Code: | [client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysql]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1
[mysqladmin]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1
[mysqlcheck]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1
[mysqldump]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1
[mysqlimport]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1
[mysqlshow]
character-sets-dir=/usr/share/mysql/charsets
default-character-set=latin1
[myisamchk]
character-sets-dir=/usr/share/mysql/charsets
[myisampack]
character-sets-dir=/usr/share/mysql/charsets
[mysqld_safe]
err-log = /var/log/mysql/mysql.err
[mysqld]
character-set-server = latin1
default-character-set = latin1
user = mysql
port = 3306
socket = /var/run/mysqld/mysqld.sock
pid-file = /var/run/mysqld/mysqld.pid
log-error = /var/log/mysql/mysqld.err
basedir = /usr
datadir = /var/lib/mysql
skip-locking
key_buffer = 16M
max_allowed_packet = 1M
table_cache = 64
sort_buffer_size = 512K
net_buffer_length = 8K
read_buffer_size = 256K
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 8M
language = /usr/share/mysql/english
wait_timeout = 10000000
set-variable = local-infile=0
bind-address = xxx.xxx.xxx.xxx
set-variable = max_connections=175
query-cache-type = 1
query-cache-size = 20M
log-slow-queries = /var/log/mysql/mysql-slow.log
#log = /tmp/sql.log
server-id = 1
tmpdir = /tmp/
innodb_buffer_pool_size = 16M
innodb_additional_mem_pool_size = 2M
innodb_data_file_path = ibdata1:10M:autoextend:max:128M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
set-variable = innodb_log_files_in_group=2
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
[isamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[myisamchk]
key_buffer = 20M
sort_buffer_size = 20M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout |
One difference is wait_timeout, my 'working' server does not have this. Looking at traffic lastnight, it was pretty low, not much activity. This is a low traffic server for sure. Also, thought that snort might be related, and looking through BASE, only a few alerts were present before and during this outage.
Here is a more detailed output of memory..
| Code: | MemTotal: 1540848 kB
MemFree: 37360 kB
Buffers: 310400 kB
Cached: 425496 kB
SwapCached: 36 kB
Active: 852720 kB
Inactive: 389072 kB
HighTotal: 638912 kB
HighFree: 1168 kB
LowTotal: 901936 kB
LowFree: 36192 kB
SwapTotal: 976744 kB
SwapFree: 976708 kB
Dirty: 180 kB
Writeback: 0 kB
AnonPages: 505912 kB
Mapped: 25736 kB
Slab: 253712 kB
SReclaimable: 242696 kB
SUnreclaim: 11016 kB
PageTables: 2236 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
CommitLimit: 1747168 kB
Committed_AS: 1047296 kB
VmallocTotal: 114680 kB
VmallocUsed: 652 kB
VmallocChunk: 113928 kB |
Any help is greatly appreciated.
Thanks!
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Sun Dec 02, 2007 4:48 pm Post subject: |
|
|
I was even able to run full mysql backups at 3:10AM last night without a problem. I'm wondering if this is related to apache. Could apache be holding a mysql thread or connection indefinitely? mysql_connect() closes connections after script execution..
http://us3.php.net/manual/en/function.mysql-connect.php
| Quote: | | Note: The link to the server will be closed as soon as the execution of the script ends, unless it's closed earlier by explicitly calling mysql_close(). |
Another factor is the kernel possibly... or recent updates. The server was rebooted 34 days ago with a new kernel ( 2.6.22-hardened-r8 ), the first time I had problems was 11/06, 11/08, 11/23 and 12/02
| Code: | Sun Oct 28 22:42:30 2007 >>> sys-kernel/hardened-sources-2.6.22-r8
Thu Nov 8 19:54:13 2007 >>> dev-db/mysql-5.0.44 <- rebuild attempt
Sun Nov 11 18:44:22 2007 >>> dev-db/mysql-5.0.44-r1
Sat Nov 17 09:32:34 2007 >>> dev-db/mysql-5.0.44-r2 |
arg! the frustration....
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
steveb
Advocate
Joined: 18 Sep 2002
Posts: 4564
|
| Posted: Sun Dec 02, 2007 6:13 pm Post subject: Re: MySQL Help [VERY URGENT] |
|
|
| hanj wrote: | Here is ps of mysql | Code: | | 31159 mysql 18 0 1336m 30m 4776 S 0.7 2.0 0:08.91 mysqld |
|
Is that 1.3GB of memory for MySQL? What is the output of: | Code: | | ps -ylC mysqld --sort:rss |
What crazy stuff are you doing with MySQL? Do you have InnoDB with crazy cashing?
// SteveB |
|
| Back to top |
|
|
steveb
Advocate
Joined: 18 Sep 2002
Posts: 4564
|
| Posted: Sun Dec 02, 2007 6:18 pm Post subject: |
|
|
175 maximum connections? | Code: | | set-variable = max_connections=175 |
Seems low to me. What MPM are you using with Apache? Could you post your Apache MPM configuration?
// SteveB |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Sun Dec 02, 2007 6:20 pm Post subject: Re: MySQL Help [VERY URGENT] |
|
|
| steveb wrote: | Is that 1.3GB of memory for MySQL? What is the output of: | Code: | | ps -ylC mysqld --sort:rss |
|
Here is that output you requested.
| Code: | # ps -ylC mysqld --sort:rss
Warning: /usr/src/linux/System.map not parseable as a System.map
S UID PID PPID C PRI NI RSS SZ WCHAN TTY TIME CMD
S 60 21507 1 0 75 0 33604 58695 ffffff ? 00:01:01 mysqld |
| steveb wrote: | | What crazy stuff are you doing with MySQL? Do you have InnoDB with crazy cashing? |
As for the 1.3GB of mem.. not sure. I'm not doing any crazy caching with innodb as far as I know.. nothing special, besides the base my.cnf above. Also.. look at the last ps I reported.. it shows:
| Code: | | 1212 mysql 15 0 4095m 56m 4828 S 0.0 3.8 30:30.85 mysqld |
Not sure if that really means 4GB??? I don't have that much memory on the system combining RAM/SWAP I'm at 3.5GB.
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Sun Dec 02, 2007 6:29 pm Post subject: |
|
|
| steveb wrote: | 175 maximum connections? | Code: | | set-variable = max_connections=175 |
Seems low to me. What MPM are you using with Apache? Could you post your Apache MPM configuration?
// SteveB |
Here is 00_mpm.conf... it's stock
| Code: | PidFile /var/run/apache2.pid
<IfModule mpm_prefork_module>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 150
MaxRequestsPerChild 0
</IfModule>
<IfModule mpm_worker_module>
StartServers 2
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 0
</IfModule>
<IfModule mpm_event_module>
StartServers 2
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 0
</IfModule>
<IfModule mpm_peruser_module>
ServerLimit 256
MaxClients 256
MinSpareProcessors 2
MaxProcessors 10
MaxRequestsPerChild 1000
KeepAlive Off
ExpireTimeout 1800
Multiplexer nobody nobody
Processor apache apache
</IfModule>
|
This server is pretty low traffic, and I've never seen 'out of connection' errors (yet). What is a good value you would recommend?
Thanks!
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
steveb
Advocate
Joined: 18 Sep 2002
Posts: 4564
|
| Posted: Sun Dec 02, 2007 6:30 pm Post subject: Re: MySQL Help [VERY URGENT] |
|
|
[quote="hanj"] | steveb wrote: | | Code: | # ps -ylC mysqld --sort:rss
Warning: /usr/src/linux/System.map not parseable as a System.map
S UID PID PPID C PRI NI RSS SZ WCHAN TTY TIME CMD
S 60 21507 1 0 75 0 33604 58695 ffffff ? 00:01:01 mysqld |
|
Currently your mysqld is using 33MB of memory. This is not that much.
| hanj wrote: | | Code: | | 1212 mysql 15 0 4095m 56m 4828 S 0.0 3.8 30:30.85 mysqld |
Not sure if that really means 4GB??? I don't have that much memory on the system combining RAM/SWAP I'm at 3.5GB. |
How have you started ps to get that output? Without knowing that it is difficult to say for what this 4095m stands for.
// SteveB |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Sun Dec 02, 2007 6:31 pm Post subject: |
|
|
I'm not starting apache with any MPM flags
| Code: |
apache 26700 0.0 0.8 56704 12700 ? S 11:30 0:00 /usr/sbin/apache2 -D SECURITY -D DEFAULT_VHOST -D SSL -D SSL_DEFAULT_VHOST -D INFO -D BW -D LIMITIPCONN -D PHP5 -d /usr/lib/apache2 -f /etc/apache2/httpd.conf -k start |
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
steveb
Advocate
Joined: 18 Sep 2002
Posts: 4564
|
| Posted: Sun Dec 02, 2007 6:32 pm Post subject: |
|
|
| hanj wrote: | Here is 00_mpm.conf... it's stock
| Code: | PidFile /var/run/apache2.pid
<IfModule mpm_prefork_module>
StartServers 5
MinSpareServers 5
MaxSpareServers 10
MaxClients 150
MaxRequestsPerChild 0
</IfModule>
<IfModule mpm_worker_module>
StartServers 2
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 0
</IfModule>
<IfModule mpm_event_module>
StartServers 2
MaxClients 150
MinSpareThreads 25
MaxSpareThreads 75
ThreadsPerChild 25
MaxRequestsPerChild 0
</IfModule>
<IfModule mpm_peruser_module>
ServerLimit 256
MaxClients 256
MinSpareProcessors 2
MaxProcessors 10
MaxRequestsPerChild 1000
KeepAlive Off
ExpireTimeout 1800
Multiplexer nobody nobody
Processor apache apache
</IfModule>
|
|
But what MPM do you use? Worker? Prefork? Do you have any modules accessing MySQL?
// SteveB |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Sun Dec 02, 2007 6:34 pm Post subject: Re: MySQL Help [VERY URGENT] |
|
|
| steveb wrote: |
| hanj wrote: | | Code: | | 1212 mysql 15 0 4095m 56m 4828 S 0.0 3.8 30:30.85 mysqld |
Not sure if that really means 4GB??? I don't have that much memory on the system combining RAM/SWAP I'm at 3.5GB. |
How have you started ps to get that output? Without knowing that it is difficult to say for what this 4095m stands for.
// SteveB |
That was from top output while mysql was hosed this morning.
| Code: |
top - 07:19:33 up 33 days, 23:21, 3 users, load average: 0.24, 0.27, 0.28
Tasks: 99 total, 1 running, 98 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.4%us, 0.5%sy, 0.0%ni, 95.3%id, 0.7%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 1540848k total, 1505512k used, 35336k free, 314200k buffers
Swap: 976744k total, 376k used, 976368k free, 411056k cached
1212 mysql 15 0 4095m 56m 4828 S 0.0 3.8 30:30.85 mysqld |
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Sun Dec 02, 2007 6:36 pm Post subject: |
|
|
| steveb wrote: | But what MPM do you use? Worker? Prefork? Do you have any modules accessing MySQL?
// SteveB |
Hello
I apologize, if I'm not following 100%. I built apache2 with mpm-prefork.
| Code: | | [ebuild R ] www-servers/apache-2.2.6 USE="mpm-prefork no-suexec ssl -debug -doc -ldap -mpm-event -mpm-itk -mpm-peruser -mpm-worker (-selinux) -static-modules -threads" 4,664 kB |
Are you asking if there are any apache modules that access MySQL directly ie: mod_auth_mysql? If so.. no.
Thanks!
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
steveb
Advocate
Joined: 18 Sep 2002
Posts: 4564
|
| Posted: Sun Dec 02, 2007 6:41 pm Post subject: Re: MySQL Help [VERY URGENT] |
|
|
| hanj wrote: | That was from top output while mysql was hosed this morning.
| Code: |
top - 07:19:33 up 33 days, 23:21, 3 users, load average: 0.24, 0.27, 0.28
Tasks: 99 total, 1 running, 98 sleeping, 0 stopped, 0 zombie
Cpu(s): 3.4%us, 0.5%sy, 0.0%ni, 95.3%id, 0.7%wa, 0.0%hi, 0.1%si, 0.0%st
Mem: 1540848k total, 1505512k used, 35336k free, 314200k buffers
Swap: 976744k total, 376k used, 976368k free, 411056k cached
1212 mysql 15 0 4095m 56m 4828 S 0.0 3.8 30:30.85 mysqld |
|
Aha. This is output from top. Well then the line with 4095m is the total amount of virtual memory used by mysqld. It is everything together (swap + non swapped physical memory). 56m is the resident size (aka: non swapped physical memory). So your mysql was using 56 MB memory and had much more swapped out but you write that you only have 2GB swap. Is that right?
top can be tricky to interpret. It could count stuff more then once. To really know what is happening you should go with the ps command I posted.
// SteveB |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Sun Dec 02, 2007 6:45 pm Post subject: |
|
|
I have 1.5GB or RAM and 1GB or swap on the system.
Thanks for the ps output.. will use it from now on.
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
steveb
Advocate
Joined: 18 Sep 2002
Posts: 4564
|
| Posted: Sun Dec 02, 2007 6:46 pm Post subject: |
|
|
How much is Apache consuming memory? | Code: | | ps -ylC httpd --sort:rss |
|
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Sun Dec 02, 2007 6:47 pm Post subject: |
|
|
| steveb wrote: | How much is Apache consuming memory? | Code: | | ps -ylC httpd --sort:rss |
|
| Code: | ps -ylC apache2 --sort:rss
Warning: /usr/src/linux/System.map not parseable as a System.map
S UID PID PPID C PRI NI RSS SZ WCHAN TTY TIME CMD
S 81 26700 23582 0 75 0 15268 14441 - ? 00:00:00 apache2
S 81 26699 23582 0 75 0 15732 14702 - ? 00:00:00 apache2
S 0 23582 1 0 78 0 15764 14175 - ? 00:00:00 apache2
S 81 23717 23582 0 75 0 16476 23500 - ? 00:00:04 apache2
S 81 23598 23582 0 75 0 16672 30480 - ? 00:00:06 apache2
S 81 23633 23582 0 75 0 16760 26213 - ? 00:00:04 apache2
S 81 23625 23582 0 75 0 16928 26852 - ? 00:00:04 apache2
S 81 23595 23582 0 75 0 17480 20417 - ? 00:00:05 apache2
S 81 23594 23582 0 75 0 17808 40405 - ? 00:00:09 apache2
S 81 23708 23582 0 75 0 17808 15635 - ? 00:00:03 apache2
S 81 23597 23582 0 75 0 19720 20530 - ? 00:00:06 apache2 |
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
steveb
Advocate
Joined: 18 Sep 2002
Posts: 4564
|
| Posted: Sun Dec 02, 2007 7:10 pm Post subject: |
|
|
Between 15mb to 20mb per Apache instance and you have 150 MaxClients. This is way too much. Assume the following:
Total memory available 1536MB
Memory used for the OS 256MB (every thing other then MySQL and Apache. OS, other stuff, etc)
Memory used for MySQL 64MB (I know it is not much but I want to keep it low for now)
Memory used by Apache per process 20MB
Calculation: 1536 - 256 - 64 = available memory for Apache = 1216MB
Now if each Apache process can take up to 20 MB this leaves us: 1216 / 20 = +/- 60 Apache processes (and you have 150!)
You wrote that you don't have much traffic. Okay. But assume some one starts an attack against you and starts to use Apache connections without doing much other things. The attacker could quickly consume all your memory. Now if you have modules in Apache connecting to MySQL each of those connections from the attacker to Apache will consume as well a connection to MySQL. And your MySQL is limited to 175 connections. And I don't think that Apache is the only one connecting to MySQL. Probably other tools do as well consume connections. I think the connection pool of MySQL is pretty quick exhausted.
If you don't have that much traffic, then lower the possible connections from Apache. You don't need that much.
Another thing: What other applications do connect to MySQL? Postfix? Amavis-New? Other tools/applications?
Could you look how much all of those applications/tools together combined would use MySQL connections?
// SteveB |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Sun Dec 02, 2007 7:11 pm Post subject: |
|
|
Going through the logs further, I see one site that was getting hit hard with mp3 requests. I originally added mod_limitipconn to counter this about a year ago (limiting 1 mp3 request per IP). I just tried to download a mp3 fromt he site and was immediately rejected. I had to restart Apache to get the expected behavior again. I wonder if this series of downloads (at 1:00:59 last night) caused a problem with Apache, and it held mysql threads open??
I wonder if there was an issue from apache-2.0 to 2.2? I did rebuild mod_limitipconn against 2.2.
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
hanj
Veteran
Joined: 19 Aug 2003
Posts: 1490
|
| Posted: Sun Dec 02, 2007 7:18 pm Post subject: |
|
|
| steveb wrote: |
Another thing: What other applications do connect to MySQL? Postfix? Amavis-New? Other tools/applications?
Could you look how much all of those applications/tools together combined would use MySQL connections?
// SteveB |
I have the following:
cacti-poller (poller.php) run every 5 minutes
amavisnewsql tools
-process_bsmtp.php -> 5 minutes
-cleanquarantine.php -> 15 minutes
-generateddigest.php -> one a day
rss updaters (2 running every 5 minutes .. extremely quick)
postfix
amavisd-new
snort
courier
apache
That should be it.
hanji
_________________
Server Admin Blog - Uno-Code.com |
|
| Back to top |
|
|
steveb
Advocate
Joined: 18 Sep 2002
Posts: 4564
|
| Posted: Sun Dec 02, 2007 7:44 pm Post subject: |
|
|
| hanj wrote: | cacti-poller (poller.php) run every 5 minutes
amavisnewsql tools
-process_bsmtp.php -> 5 minutes
-cleanquarantine.php -> 15 minutes
-generateddigest.php -> one a day
rss updaters (2 running every 5 minutes .. extremely quick)
postfix
amavisd-new
snort
courier
apache |
Which of them is connecting to MySQL and how many connections could they make? Do you use MySQL with amavisd-new? What about Postfix? Do you connect from Postfix to MySQL? Do you use proxy maps in Postfix to connect to MySQL? And what about Courier? Connecting as well to MySQL? And what about Apache? Using stuff like AUTH_IMAP, AUTH_MYSQL, PHP, etc?
// SteveB |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
|
Portage & Programming
