FreeBSD stacksize problem, possibly?
I am running mysql-standard-4.0.18-unknown-freebsd4.7-i386-linuxthreads and I am unable to achieve more than 440 connections. I receive the common Can't create a new thread (errno 35). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug. Now, I have 1.5GB of free memory, so that's not the problem. I've also thought about the limits issue, I've checked, they are as follows: Resource limits (current): cputime infinity secs filesize infinity kb datasize 2096128 kb stacksize 524288 kb coredumpsize infinity kb memoryuseinfinity kb memorylocked infinity kb maxprocesses 5547 openfiles 11095 sbsize infinity bytes vmemoryuse infinity kb My thread_stack is about 192kb in MySQL, so 192*440 is only 84MB, which should be under 512MB. I'm not running out of processes or file handles, or sockets from sysctl -a. Does anyone have any other suggestions or things to double check? Nick
RE: FreeBSD 4.9 + MySQL 4.0 + LinuxThreads
Interesting idea, but I should be ok. I don't plan to have large amounts of MySQL threads. I just had FreeBSD setup to allow 2GB of memory per process because of memcached which I also plan to run on this server. I just compiled it to a little under 2GB and MySQL ran fine, it was just hard to track down at first because the manually compiled version of MySQL didn't give me near the details the binary version did. Thanks! nickg -Original Message- From: Jeremy Zawodny [mailto:] Sent: Wednesday, February 11, 2004 12:55 AM To: Nick Cc: [EMAIL PROTECTED] Subject: Re: FreeBSD 4.9 + MySQL 4.0 + LinuxThreads On Mon, Feb 02, 2004 at 02:00:46AM -0500, Nick wrote: Alas, when I tried the binary version, it gave me an error that google helped link me to the following: Fatal error 'Can't create gc thread' at line ? in file /usr/src/lib/libc_r/uthread/uthread_create.c (errno = ?) mysqld got signal 6; http://lists.freebsd.org/pipermail/freebsd-stable/2003-May/000979.html Apparently you have to keep the MAXDSIZ below 2GB for FreeBSD to create threads. Maybe I'll submit something for the docs people to update to include this, since a recompile and reboot now prove all installs work properly. Yes, it's a byproduct of the memory map that FreeBSD sets up. With that out of the way, you should find it to be quite solid. Another trick we've used is to recompile LinuxThreads with a smaller thred stack. It's only necessary if you expect to have LOTS of concurrent connections. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 150 days, processed 1,628,781,557 queries (125/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: C API: undefined reference
http://www.mysql.com/doc/en/mysql_connect.html
This function is deprecated. It is preferable to use
mysql_real_connect() instead.
#includestdio.h
#includemysql.h
int main(void) {
MYSQL mysql;
if(mysql_init(mysql) == NULL) {
fprintf(stderr,Unable to initlize MySQL structure.\n);
return(1);
}
if(mysql_real_connect(mysql,127.0.0.1,user,password, db, 0,
NULL, 0) == NULL) {
fprintf(stderr,Unable to connect to MySQL: %s\n,
mysql_error(mysql));
return(1);
}
return(0);
}
mybox:/home/nickgsu gcc seeLog.c -o seeLog -I/usr/local/mysql/include
-L/usr/local/mysql/lib -lmysqlclient -lz
mybox:/home/nickgsu ./seeLog
Unable to connect to MySQL: Access denied for user: '[EMAIL PROTECTED]'
(Using password: YES)
Good luck,
nickg
-Original Message-
From: hAj [mailto:[EMAIL PROTECTED]
Sent: Monday, November 10, 2003 4:03 PM
To: [EMAIL PROTECTED]
Subject: C API: undefined reference
Hello MySQL pros worldwide,
~/www -cat seeLog.c
#define USE_OLD_FUNCTIONS
#include stdio.h
#include mysql/mysql.h
int main() {
MYSQL mysql;
MYSQL *mysqldb = NULL;
mysqldb = mysql_connect(mysql, geneofcube.net, USERID,
PASSWORD);
return 0;
}
~/www -gcc seeLog.c -o seeLog -I/usr/include -L/usr/lib -lmysqlclient
/tmp/ccEj3tmv.o: In function `main':
/tmp/ccEj3tmv.o(.text+0x2a): undefined reference to `mysql_connect'
collect2: ld returned 1 exit status
~/www -
As shown above, I'm having a problem getting rid of a compilation error
(undefined reference) coming out with a very simple c code (seeLog.c)
which
I wrote for a testing purpose.
Got no I idea what I'm doing wrong or missing here.
I'd appreciate any of your suggestions.
Best,
hAj
--
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]
FW: FreeBSD 4.8 runaway MySQL 4.0.14
I am running the binary installation of MySQL 4.0.14 on FreeBSD 4.8, all MyISAM tables, and after alittle while mysqld will jump to 100% of my CPU and go crazy. I've investigated SHOW INNODB STATUS, and as you can see below, there is nothing happening with Innodb. Infact my Innodb files have not even changed since I last restarted. But for some reason MySQLd uses 100% of my CPU with no queries happening. I tried to run ktrace and this was all I saw: 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL poll(0x83b6000,0xc,0) 30488 mysqld RET poll 0 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld RET poll 1 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld RET poll 1 With an occasional 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld PSIG SIGPROF caught handler=0x2842562c mask=0x0 code=0x0 30488 mysqld RET poll 1 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL sigreturn(0x83a9db8) 30488 mysqld RET sigreturn JUSTRETURN Has anyone experienced a runway mysqld on FreeBSD before? Is there anything I can do to fix this or solve this problem? Below is show innodb status\g = 030828 14:36:37 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 4, signal count 4 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 6, OS waits 3; RW-excl spins 1, OS waits 1 TRANSACTIONS Trx id counter 0 1280 Purge done for trx's n:o 0 0 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 299134976 MySQL thread id 176926, query id 516226 localhost root show innodb status FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 40 OS file reads, 4 OS file writes, 4 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 0, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 43892 Log flushed up to 0 43892 Last checkpoint at 0 43892 0 pending log writes, 0 pending chkp writes 9 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 16338600; in additional pool allocated 641280 Buffer pool size 512 Free buffers 493 Database pages 19 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 19, created 0, written 0 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -- ROW OPERATIONS -- 0 queries inside InnoDB, 0 queries in queue Main thread id 158370816, state: waiting for server activity Number of rows inserted 0, updated 0, deleted 0, read 0 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s END OF INNODB MONITOR OUTPUT Nick -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: FreeBSD 4.8 runaway MySQL 4.0.14
Ken, Thanks for the response. I really wish MySQL AB would compile FreeBSD binaries with LinuxThreads. It's really a pain when you find a bug, the first thing they ask is have you compiled this yourself when in this case you have no choice but to compile it yourself. Maybe Jeremy could compile unofficial but recommended binaries that MySQL AB would support more like official binaries? :) If he's got nothing better to do of course, he already puts in so much time to the MySQL community as is. Nick -Original Message- From: Ken Menzel [mailto:[EMAIL PROTECTED] Sent: Thursday, August 28, 2003 4:14 PM To: Nick Gaugler; [EMAIL PROTECTED] Subject: Re: FreeBSD 4.8 runaway MySQL 4.0.14 Hi Nick, This is due (so I am told) to a problem in FreeBSD threads. I have this happen once in a while also. There have been some threads changes for 4.9 but I am not sure there is any change in this. The common suggestion is to compile with LINUXTHREADS option using the ports tree. This will solve that and give you better performance on a multi CPU system. (cd /usr/ports/databases/mysql41-server; more Makefile ) or see http://jeremy.zawodny.com/blog/archives/000458.html Another option would be to update to FreeBSD 5.1 and use the new threading (not so easy). I personaly hope 4.9 will settle this down some as I don't see the need to go to LINUX Threads for a single proc machine! Best of Luck Ken - Original Message - From: Nick Gaugler [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, August 28, 2003 3:53 PM Subject: FW: FreeBSD 4.8 runaway MySQL 4.0.14 I am running the binary installation of MySQL 4.0.14 on FreeBSD 4.8, all MyISAM tables, and after alittle while mysqld will jump to 100% of my CPU and go crazy. I've investigated SHOW INNODB STATUS, and as you can see below, there is nothing happening with Innodb. Infact my Innodb files have not even changed since I last restarted. But for some reason MySQLd uses 100% of my CPU with no queries happening. I tried to run ktrace and this was all I saw: 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL poll(0x83b6000,0xc,0) 30488 mysqld RET poll 0 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld RET poll 1 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld RET poll 1 With an occasional 30488 mysqld CALL poll(0x83b6000,0xd,0x126) 30488 mysqld PSIG SIGPROF caught handler=0x2842562c mask=0x0 code=0x0 30488 mysqld RET poll 1 30488 mysqld CALL gettimeofday(0x2846e288,0) 30488 mysqld RET gettimeofday 0 30488 mysqld CALL sigreturn(0x83a9db8) 30488 mysqld RET sigreturn JUSTRETURN Has anyone experienced a runway mysqld on FreeBSD before? Is there anything I can do to fix this or solve this problem? Below is show innodb status\g = 030828 14:36:37 INNODB MONITOR OUTPUT = Per second averages calculated from the last 16 seconds -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 4, signal count 4 Mutex spin waits 0, rounds 0, OS waits 0 RW-shared spins 6, OS waits 3; RW-excl spins 1, OS waits 1 TRANSACTIONS Trx id counter 0 1280 Purge done for trx's n:o 0 0 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, OS thread id 299134976 MySQL thread id 176926, query id 516226 localhost root show innodb status FILE I/O I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 40 OS file reads, 4 OS file writes, 4 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 0, seg size 2, 0 inserts, 0 merged recs, 0 merges Hash table size 34679, used cells 0, node heap has 0 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 0 43892 Log flushed up to 0 43892 Last checkpoint at 0 43892 0 pending log writes, 0 pending chkp writes 9 log i/o's done, 0.00 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 16338600; in additional pool allocated 641280 Buffer pool size 512 Free buffers 493 Database pages 19 Modified db pages 0 Pending reads 0 Pending writes
RE: SELECT with 1,000,000 ROWS
Hello, I have aproximately 1,000,000 rows and I would like to do some query. The first one is to get the number of row so I do : mysql SELECT COUNT(*) FROM `Log`; +--+ | COUNT(*) | +--+ | 969129 | +--+ 1 row in set (0.00 sec) MyISAM tables keep a specific count of the number of rows in the table, that is why this query is extremely fast. mysql SELECT COUNT(*) AS `Nb` FROM `Log` WHERE `ID` = 49; ++ | Nb | ++ | 969129 | ++ 1 row in set (1 min 20.99 sec) This query is slow, presumably, because MySQL must read all 969129 rows off of the disk and count them, which will take some time depending on the size of the rows and the speed of the system. But like you can see it, it take a long with the WHERE clause. I use Pentium III at 650 Mhz with 48 Mb of ram. I think that the probleme come from the computer but I'm not shure (I need more RAM ?). Yes, your ram will make a difference because the file system will do caching. You may want to read up on Indexes in MySQL and see how they are used to optimize queries and how they can also be cached in the key_buffer. http://www.mysql.com/doc/en/MySQL_indexes.html Thanks in advance. -- Arno nickg -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
3.23.56 Replication Bug
I know the replication method is different in MySQL 4.0 then MySQL
3.23.x, but I have a bug that causes problems. The following query will
cause MySQL's logic to not properly read any of the following my.cnf
commands on slave servers:
replicate-wild-do-table
replicate-wild-ignore-table
replicate-ignore-table
I am sure it would make any of the match commands fail as well. This
query caused replication to stop on 2 of my slaves because it was in a
database that was not made to be replicated on the slave.
Below is the query:
UPDATE phpbb_search_results
SET search_id = 1929162862, search_array =
'a:7:{s:14:search_results;s:28:521, 577, 592, 593, 597,
598;s:17:total_
match_count;i:6;s:12:split_search;N;s:7:sort_by;i:0;s:8:sort_dir;
s:4:DESC;s:12:show_results;s:6:topics;s:12:return_chars;i:200;}
'
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5''
Below is the error log entry:
030803 16:24:33 Slave: connected to master
'[EMAIL PROTECTED]:3306', replication started in log
'blue-bin.013' at position 72475077
030803 16:24:33 Slave: did not get the expected error running query
from master - expected: 'Duplicate entry '%-.64s' for key %d' (1062),
got
'no error' (0)
030803 16:24:33 Slave: error running query 'UPDATE
phpbb_search_results
SET search_id = 1929162862, search_array =
'a:7:{s:14:search_results;s:28:521, 577, 592, 593, 597,
598;s:17:total_
match_count;i:6;s:12:split_search;N;s:7:sort_by;i:0;s:8:sort_dir;
s:4:DESC;s:12:show_results;s:6:topics;s:12:return_chars;i:200;}
'
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5''
030803 16:24:33 Error running query, slave aborted. Fix the problem,
and re-start the slave thread with mysqladmin start-slave. We stopped
a
t log 'blue-bin.013' position 72475077
030803 16:24:33 Slave thread exiting, replication stopped in log
'blue-bin.013' at position 72475077
Below is the binlog entry, keep in mind, it will ignore all of the
queries after this one, it will just not ignore a query with those
special characters in it:
# at 72475077
#030803 0:35:26 server id 1 Query thread_id=191959
exec_time=0 error_code=1062
use bluecustforum;
SET TIMESTAMP=1059888926;
UPDATE phpbb_search_results
SET search_id = 1929162862, search_array =
'a:7:{s:14:search_results;s:28:521, 577, 592, 593, 597,
598;s:17:total_
match_count;i:6;s:12:split_search;N;s:7:sort_by;i:0;s:8:sort_dir;
s:4:DESC;s:12:show_results;s:6:topics;s:12:return_chars;i:200;}
'
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5';
# at 72475467
#030803 0:35:29 server id 1 Query thread_id=191960
exec_time=0 error_code=0
SET TIMESTAMP=1059888929;
UPDATE phpbb_topics
SET topic_views = topic_views + 1
WHERE topic_id = 18;
# at 72475580
#030803 0:35:34 server id 1 Query thread_id=191961
exec_time=0 error_code=0
SET TIMESTAMP=1059888934;
UPDATE phpbb_sessions
SET session_time =
1059888934, session_page = 9
WHERE session_id =
'b85542aa0e83fdc6ee306e82ce1a6ad5';
# at 72475755
#030803 0:35:34 server id 1 Query thread_id=191961
exec_time=0 error_code=0
SET TIMESTAMP=1059888934;
UPDATE phpbb_users
SET
user_session_time = 1059888934, user_session_page = 9
WHERE user_id =
8;
Below is the version info from mysqlbug
VERSION=3.23.56
COMPILATION_COMMENT=Official MySQL RPM
BUGmysql=[EMAIL PROTECTED]
# This is set by configure
COMP_ENV_INFO=CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium'
CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -feli
de-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS=''
CONFIGURE_LINE=./configure '--disable-shared'
'--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static'
'--without-berkeley-db'
'--without-innodb' '--enable-assembler' '--enable-local-infile'
'--with-mysqld-user=mysql'
'--with-unix-socket-path=/var/lib/mysql/mysql.sock'
'--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr'
'--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share'
'--locals
tatedir=/var/lib/mysql' '--infodir=/usr/share/info'
'--includedir=/usr/include' '--mandir=/usr/share/man'
'--with-comment=Official MySQL RPM' '
CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6
-fno-omit-frame-pointer -felide-constructors
-fno-exceptions
-fno-rtti -mpentium' 'CXX=gcc'
nickg
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
