Issues with threading/SMP (?) on Debian-AMD64... and MySQL

[Guest]

i desperately need help.

i run 4 separate instances of mysql on this machine and all 4 of them are trying to use as much CPU as possible (basically taking 100% of the machine's CPU resources between all of them)

mysql 722 29.1 0.6 45900 12436 ? S Jul09 1991:07 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --da
mysql 725 12.7 1.0 82572 21084 ? S Jul09 868:09 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --dat
mysql 731 42.7 1.8 80044 38156 ? S Jul09 2920:10 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --da
mysql 733 47.3 1.4 58024 28972 ? S Jul09 3233:11 /usr/local/mysql/libexec/mysqld --basedir=/usr/local/mysql --da

something is going on. there have been many bugs and questions raised about this, and i'm sure there has to be some sort of workaround for now, if nothing else.

- debian: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=314408
- redhat: http://sources.redhat.com/ml/libc-hacke ... 00019.html
- ubuntu: https://bugzilla.ubuntu.com/show_bug.cgi?id=11730
- mysql: http://bugs.mysql.com/bug.php?id=8555
- mysql: http://bugs.mysql.com/bug.php?id=7254

- someone's blog: http://trainedmonkey.com/entry/2248

i've tried 2 or 3 versions of mysql (4.1.10 right now) - 4.1.12 just froze up totally with table locks no matter what i did.

it appears that mysql is/was heavily dependent on linuxthreads originally, which is not available on amd64 arch. libpthread or libpntl? or whatever seems to be the options... i found a patch that added this to the 'make' line for mysql and seems to help (right now the servers function at least...)

CFLAGS="$CFLAGS -DUSE_MUTEX_INSTEAD_OF_RW_LOCKS -DPTHREAD_ADAPTIVE_MUTEX_INITIALIZER_NP"

can anyone help me out? perhaps whomever has access or compiles the debian apt package for mysql can shed some light on the 'configure' and 'make' options they use? this is a dual opteron, running mysql 4.1.x, linux kernel 2.6.11.2 - sarge distro - please help

i've posted this on mysql mailing list and their forum and it basically has been all but ignored. i think it's too advanced for them...

thanks in advance. i'm willing to try just about anything.

- mike503

[mike503]

please note, i'm exhausted. to summarize:

i can't seem to find the "magic formula" with mysql 4.1.x and debian-amd64 on SMP systems.

i can't tell if my single processor systems are having the same issues or not (since i don't use them as my primary database server.)

i'll pay. i want my mysql servers to be running as fast as possible - and tuned. money can be involved here if someone is willing to commit to something.

[lacek]

I don't quite understand your problem, but I'm trying to answer anyway :-)

Maybe I get you wrong, but your problem is that you don't know the configure and make options for compiling mysql. If you knew, you would change those to use the pthread library instead of the default one (whatever this should be).

If so, you can do the following:
Get the mysql sources with this command:
apt-get source mysql-server
It will extract sources to the current directory. Change to that directory, and edit the debian/rules file. Search for the configure-stamp target. It's on the 50th line on my Sarge, maybe somewhere else for you. Got a bit down and you'll find an "sh -c" command which runs through a number of lines. There are the switches and environmental variables you need to compile your program. The ones you see there were used to create the .deb package. You can put anything there. So put your CFLAGS to the CFLAGS line, and add the --with-pthread switch among the switches (don't forget to put a \ to the end of the line, to make the shell not stop here).
If you're done, use the
dpkg-buildpackage -us -uc
command to make packages from the source. After it is done, you can install those packages and try them.

Should the new packages fail badly, you can reinstall the original ones with the
apt-get install --reinstall <packagename>
command.

Hope this helps.

[mike503]

well - i have tried different configure options, and have googled and found mailing lists and such (that's where i got the MUTEX patch/etc) - but there was never a straight "all you need is --use-pthread" or whatever. it sounded like each threading library had it's own set of issues with mysql.

i was *hoping* i could download the source apt package and see what the maintainer used to compile - originally i couldn't find anything - but i'll check it out, maybe my lack of understanding what's in a source .tar was stopping me there. i usually compile directly from the actual source, or just use apt install - not download the source from apt.

thanks - this is the first attempt anyone's tried yet from debian or mysql

also it appears that from mysql 4.1.10 -> 4.1.12 it changed how it detects linuxthreads and makes it even more difficult on amd64 (from my experience)

[mike503]

Maybe I get you wrong, but your problem is that you don't know the configure and make options for compiling mysql. If you knew, you would change those to use the pthread library instead of the default one (whatever this should be).

just as i suspected, even the debian build doesn't use configure to determine the threading library.

so it's not that "i don't know how" - i've been compiling and trying to optiimize the compilation of mysql (and other packages) now for years. typically though i don't have to tell it which libraries to use, the package is updated enough to include the proper detection.

however, even though running mysql on SMP AMD64 systems seems to be quite popular it appears that the mysql team has not spent enough time getting the package to be perfectly tuned using default configure settings.

anyhow, i looked at how debian is compiling it, found a few extra options that might help (but nothing that screamed "YES!") - and then i need to examine the patches that it applies (like 13_configure__AMD64-LinuxThreads-vs-NPTL.dpatch), that's probably where the real fix is done.

[mike503]

vanilla 4.1.12 source.

./configure \
--prefix=/usr/local/mysql \
--enable-assembler \
--without-debug \
--with-mysqld-user=mysql \
--without-innodb \
--without-isam \
--enable-thread-safe-client \
--without-extra-tools \
--without-docs \
--without-bench \
--with-client-ldflags=-lstdc++ \
--without-openssl \
--with-mysqld-ldflags=-all-static \
--without-embedded-server \
--enable-shared \
--enable-static \
--with-extra-charsets=all \
--with-pthread

checking "Linux threads"... "starting"
checking "getconf GNU_LIBPTHREAD_VERSION"... "NPTL"
"no need to check headers"
checking "for pthread_create in -lpthread"... "yes"
checking "named thread libs:"... "-lpthread"
checking for strtok_r in -lpthread... yes

checking for pthread_attr_create... no
checking for pthread_attr_getstacksize... yes
checking for pthread_attr_setprio... no
checking for pthread_attr_setschedparam... yes
checking for pthread_attr_setstacksize... yes
checking for pthread_condattr_create... no
checking for pthread_getsequence_np... no
checking for pthread_key_delete... yes
checking for pthread_rwlock_rdlock... yes
checking for pthread_setprio... no
checking for pthread_setprio_np... no
checking for pthread_setschedparam... yes
checking for pthread_sigmask... yes

checking "args to pthread_getspecific"... POSIX
checking "args to pthread_mutex_init"... POSIX
checking "args to readdir_r"... POSIX
checking "style of sigwait"... POSIX
checking "for pthread_attr_setscope"... yes
checking "can netinet files be included"... "yes"

when compiling i get this, not sure if it's safe or not:

sql_udf.o(.text+0x32e): In function `udf_init()':
: warning: Using 'dlopen' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking
mysqld.o(.text+0x2e56): In function `server_init()':
: warning: Using 'initgroups' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking
mysqld.o(.text+0x521b): In function `check_user(char const*)':
: warning: Using 'getpwnam' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking
mysqld.o(.text+0x527d): In function `check_user(char const*)':
: warning: Using 'getpwuid' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking
../mysys/libmysys.a(mf_pack.o)(.text+0x690): In function `expand_tilde':
: warning: Using 'endpwent' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking
hostname.o(.text+0x330): In function `ip_to_hostname(in_addr*, unsigned*)':
: warning: Using 'gethostbyaddr' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking
mysqld.o(.text+0x1fc0): In function `get_one_option':
: warning: Using 'gethostbyname' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking
../mysys/libmysys.a(my_gethostbyname.o)(.text+0xe): In function `my_gethostbyname_r':
: warning: Using 'gethostbyname_r' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking
mysqld.o(.text+0x5184): In function `set_ports()':
: warning: Using 'getservbyname' in statically linked applications requires at runtime the shared libraries from the glibc version used for linking


and when i run the server it continuously crashes:



050714 15:08:02 mysqld restarted
/usr/local/mysql/libexec/mysqld: ready for connections.
Version: '4.1.12' socket: '/tmp/mysql.sock' port: 3308 Source distribution
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=16777216
read_buffer_size=131072
max_used_connections=1
max_connections=2500
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1616364 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.


Number of processes running now: 0
050714 15:08:02 mysqld restarted
/usr/local/mysql/libexec/mysqld: ready for connections.
Version: '4.1.12' socket: '/tmp/mysql.sock' port: 3308 Source distribution


with that, other info:

[root@raid01 mysql]# getconf GNU_LIBPTHREAD_VERSION
NPTL 0.60