Re: Table Locking (Was: Best CPU config for a busy DB server)
Table locking will occur with MyISAM tables when any row(s) of the table is being updated (Update,Delete,Insert,Load Data etc). If you are only executing Select statements, then they can be executed in parallel and won't be blocked. Just curious: you say with MyISAM tables - do any of the other table types (InnoDB, Falcon, etc) behave differently? Thanks, JW -- -- System Administrator - Cedar Creek Software http://www.cedarcreeksoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Best CPU config for a busy DB server
Hey everyone, I'm pretty sure this is right but I wanted to double-check: Is it correct that mysql 5.0 is threaded in such a way that a DB server taking lots of queries from many clients will be able to utilize lots of CPUs/core on a multi-cpu, multi-core system? Or are multi CPUs/cores a waste? Thanks, JW -- -- System Administrator - Cedar Creek Software http://www.cedarcreeksoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Locking (Was: Best CPU config for a busy DB server)
On Friday 09 May 2008 04:32:10 pm Saravanan wrote: --- On Sat, 5/10/08, JW [EMAIL PROTECTED] wrote: From: JW [EMAIL PROTECTED] Is it correct that mysql 5.0 is threaded in such a way that a DB server taking lots of queries from many clients will be able\ to utilize lots of CPUs/core on a multi-cpu, multi-core system? Or are multi CPUs/cores a waste? Thanks, JW Yes it can use multiple cores. Mysqld is a multithreaded service. Saravanan I just found this interesting tidbit: *** MySQL On Multi-Core Machines - The DevShed technical tour explains that MySQL can spawn new threads, each of which can execute on a different processor/core. What it doesn’t say is that a single thread can only execute on a single core, and if that thread locks a table, then no other threads that need that table can execute until the locking thread/query is complete. Short answer: MySQL works well on multi-core machines until you lock a table. One of our programmers was wondering if this is referring to such implicit lock such as when you you read from a table (SELECT) or only explicit table locking, which we don't (currently) use in any of our code. Does anyone know? JW -- -- System Administrator - Cedar Creek Software http://www.cedarcreeksoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dismal performance on a 16G memory/8 core server - my.cnf settings?
Hello, We recently purchased a Dell PowerEdge 6650 thinking it would be a real fast server. Specs are: OS: Linux Debian 4.0/Etch RAID 5 on 4x U320 15k rpm drives (uses a perc-raid 3/DC hardware raid controller) 16GB of RAM 4 3.0 Ghz Xeon processors - I think they're dual core, in /proc/cpuinfo it shows up as 8 processors - maybe it's only HT I first made the mistake of using the default kernel, which provides SMP support but not large memory support. I have the output of a mysql sql-bench run from mysql on a Mac Mini to compare performance with. The server was only 0.35 (relative) the speed of the Mac mini - that means an 8 core 3.0 Ghz Xeon server with 16GB of RAM was only about 3x as fast as a as a single-core 1.25 Ghz G4 with 1GB of RAM (and a mini uses those little laptop hard drives, too). Needless to say my employer was shocked at the terrible performance and decided to sell the 6650 right away. But I can't help but wonder if there's not something terribly wrong with the settings - either the OS or mysql settings. I changed the kernel to the -bigmem kernel. It now sees all the RAM, but the sql-bench output on this try was _exactly_ the same: 0.35 I copied the my-huge.cnf from the examples directory and changed the thread_concurrency setting to 8 (because it said to set it to No. of CPUs*2). I also set the tmpdir, basedir, datadir and language, which were set in the original my.cnf I ran sql-bench again and the performance was even worse this time: 0.36 Someone suggested I try the -amd64 kernels which provide 64 bit but when I try to boot it I get various errors about this CPU does not support long (something) please use a 32-bit OS - the 64 bit install CD says the same message. So I assume these are not 64 bit CPUs. Any idea how I can configure this server to maximize performace? I think the multiple CPUs are a waste: I'm not looking for lots of concurrency, I want 1 query done really fast. Thanks. JW -- -- System Administrator - Cedar Creek Software http://www.cedarcreeksoftware.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Mysq[-Max] 3.23.50, .51 with autoextend.... where are they?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hey, At various places on the MySQL web site there's mention of 3.23.50 and 3.23.51, and the new autoextend feature. However, the most recent on the download page is 3.23.49a. Was .50/.51 not released because of bugs or something? - -- - Jonathan Wilson System Administrator Clickpatrol.com Cedar Creek Software http://www.cedarcreeksoftware.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8xxpNQ5u80xXOLBcRAp7pAKCiWlx8zXy0KDZQ+KXXSP02juSJdgCgtkVg D+E9qbYmboMS8tV3bse0sh4= =WH2e -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Auto-configuration of my.cnf?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, Some time ago I set up a rather large mySQL server with InnoDB and had a hard time customising my.cnf. At the time there was some discussion of createing a tool that someone could enter their hardware info into and get a customised configuration i.e. a my.cnf calculator. Has any work been done on that? I'm setting up an even bigger server this time (Quad Xeon, 8GB RAM) and I must be figuring something wrong, because I'm comming up with nearly the same figures I had for my dual PIII/2GB RAM. If no ones working on the conf generator, is there any interest in it still? If anyone is working on it, who? :-) - -- - Jonathan Wilson System Administrator Clickpatrol.com Cedar Creek Software http://www.cedarcreeksoftware.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8xgm/Q5u80xXOLBcRAmdGAKCUhgP0IDk9HqA+t/zn7B//HNEhKACg2Fa0 HoIsqzl5lNegfdAxYVXG3sk= =JES3 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
VERY URGENT, possible bug. More on: What does this error message mean: InnoDB: Warning: difficult to find free blocks from the buffer pool..
I forgot to point out that this is InnoDB I've searched Google and found the following disquiteing thing: http://www.innodb.com/oldbugs.html: Closed or old bug reports: Almost all of these bugs have been fixed. There are some old bug reports where the cause of the bug was never found, but because there have been no bug reports for newer versions of InnoDB, these reports are not considered actual any more. snip August 13, 2001: The fsync problem which was fixed in 3.23.40b and .41 could cause the following warning message on some Unix flavors: Innobase: Warning: difficult to find free blocks from Innobase: the buffer pool! Consider increasing the Innobase: buffer pool size. If you encounter the above message, upgrade to 3.23.41. However, I'm already using a newer vresion than that: ccs012:~ # rpm -qa |grep mysql mysql-shared-3.23.44-5 mysql-Max-3.23.44-5 mysql-devel-3.23.44-5 mysql-navigator-1.2.3-106 mysql-client-3.23.44-5 mysql-3.23.44-5 mysql-bench-3.23.44-5 ccs012:~ # This is a mission critical DB. Am I the lucky un-fortunate to re-dicover this supposedly fixed bug? JW I'm getting this error messge constantly in my error log: JW JW 020406 18:02:50 *** JW InnoDB: Warning: difficult to find free blocks from JW InnoDB: the buffer pool (200 search iterations)! Consider JW InnoDB: increasing the buffer pool size. JW InnoDB: It is also possible that in your Unix version JW InnoDB: fsync is very slow, or completely frozen inside JW InnoDB: the OS kernel. Then upgrading to a newer version JW InnoDB: of your operating system may help. Look at the JW InnoDB: number of fsyncs in diagnostic info below. JW InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0 JW InnoDB: 5703 OS file reads, 502 OS file writes, 82 OS fsyncs JW InnoDB: Starting InnoDB Monitor to print further JW InnoDB: diagnostics to the standard output. JW JW Running SuSE Linux 7.3: JW JW ccs012:/var/lib/mysql # uname -a ; df -h ; free -m JW Linux ccs012 2.4.10-64GB-SMP #1 SMP Fri Sep 28 17:26:36 GMT 2001 i686 unknown JW FilesystemSize Used Avail Use% Mounted on JW /dev/sda7 67G 59G 8.2G 88% / JW /dev/sda5 63M 36M 26M 58% /boot JW shmfs1007M 0 1006M 0% /dev/shm JW total used free sharedbuffers cached JW Mem: 2013 2008 4 0 10657 JW -/+ buffers/cache: 1340672 JW Swap: 1035 0 1035 JW JW JW TIA JW JW -- JW JW JW Jonathan Wilson JW System Administrator JW Clickpatrol.com JW Cedar Creek Software http://www.cedarcreeksoftware.com JW JW JW JW - JW Before posting, please check: JWhttp://www.mysql.com/manual.php (the manual) JWhttp://lists.mysql.com/ (the list archive) JW JW To request this thread, e-mail [EMAIL PROTECTED] JW To unsubscribe, e-mail [EMAIL PROTECTED] JW Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php JW JW JW -- Jonathan Wilson System Administrator Clickpatrol.com Cedar Creek Software http://www.cedarcreeksoftware.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: VERY URGENT, possible bug. More on: What does this error message mean: InnoDB: Warning: difficult to find free blocks from the buffer pool..
I belive I have found a bug here, possibly. The last known commands to be run on the DB that are suspicious were a join between 2 tables. This is the info I got from the user who was workign on the DB: He was joining between keyword and billing select distinct(a.customerid) from cpcustomer.customerkeyword a left join cpbilling.billdetail b on a.customerid=b.customerid where b.customerid is null and active='y'; For some reason he hit ^c to exit (it didn't seem to be responding or somethign) He logged came back on to MySQL and ran: alter table add key bdetailcid (customerid); show create billdetail; It crashed (locked up) durning the last query mentioned above. I tried to shut down the server, but it wouldn't die so I ran the init-stop script a second time. It seems that the init start/stop script gets forceful on the second try. Started it back up, it took a _very_ long time recovering (InnoDB), then started in with the error message I first posted. Any ideas? I'm currently restoring from backups we'll see how it goes. Thanks. JW I forgot to point out that this is InnoDB JW JW I've searched Google and found the following disquiteing thing: JW http://www.innodb.com/oldbugs.html: JW JW Closed or old bug reports: Almost all of these bugs have been fixed. There are some old bug reports where the cause of the bug was never found, but because there have been no bug reports for newer versions of InnoDB, these reports are not considered actual any more. JW snip JW August 13, 2001: JW The fsync problem which was fixed in 3.23.40b and .41 could cause the following warning message on some Unix flavors: JW JW Innobase: Warning: difficult to find free blocks from JW Innobase: the buffer pool! Consider increasing the JW Innobase: buffer pool size. JW JW If you encounter the above message, upgrade to 3.23.41. JW JW However, I'm already using a newer vresion than that: JW JW ccs012:~ # rpm -qa |grep mysql JW mysql-shared-3.23.44-5 JW mysql-Max-3.23.44-5 JW mysql-devel-3.23.44-5 JW mysql-navigator-1.2.3-106 JW mysql-client-3.23.44-5 JW mysql-3.23.44-5 JW mysql-bench-3.23.44-5 JW ccs012:~ # JW JW This is a mission critical DB. Am I the lucky un-fortunate to re-dicover this supposedly fixed bug? JW JW JW I'm getting this error messge constantly in my error log: JW JW JW JW 020406 18:02:50 *** JW JW InnoDB: Warning: difficult to find free blocks from JW JW InnoDB: the buffer pool (200 search iterations)! Consider JW JW InnoDB: increasing the buffer pool size. JW JW InnoDB: It is also possible that in your Unix version JW JW InnoDB: fsync is very slow, or completely frozen inside JW JW InnoDB: the OS kernel. Then upgrading to a newer version JW JW InnoDB: of your operating system may help. Look at the JW JW InnoDB: number of fsyncs in diagnostic info below. JW JW InnoDB: Pending flushes (fsync) log: 0; buffer pool: 0 JW JW InnoDB: 5703 OS file reads, 502 OS file writes, 82 OS fsyncs JW JW InnoDB: Starting InnoDB Monitor to print further JW JW InnoDB: diagnostics to the standard output. JW JW JW JW Running SuSE Linux 7.3: JW JW JW JW ccs012:/var/lib/mysql # uname -a ; df -h ; free -m JW JW Linux ccs012 2.4.10-64GB-SMP #1 SMP Fri Sep 28 17:26:36 GMT 2001 i686 unknown JW JW FilesystemSize Used Avail Use% Mounted on JW JW /dev/sda7 67G 59G 8.2G 88% / JW JW /dev/sda5 63M 36M 26M 58% /boot JW JW shmfs1007M 0 1006M 0% /dev/shm JW JW total used free sharedbuffers cached JW JW Mem: 2013 2008 4 0 10657 JW JW -/+ buffers/cache: 1340672 JW JW Swap: 1035 0 1035 JW JW JW JW JW JW TIA JW JW JW JW -- JW JW JW JW JW JW Jonathan Wilson JW JW System Administrator JW JW Clickpatrol.com JW JW Cedar Creek Software http://www.cedarcreeksoftware.com JW JW JW JW JW JW JW JW - JW JW Before posting, please check: JW JWhttp://www.mysql.com/manual.php (the manual) JW JWhttp://lists.mysql.com/ (the list archive) JW JW JW JW To request this thread, e-mail [EMAIL PROTECTED] JW JW To unsubscribe, e-mail [EMAIL PROTECTED] JW JW Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php JW JW JW JW JW JW JW JW -- JW JW JW Jonathan Wilson JW System Administrator JW Clickpatrol.com JW Cedar Creek Software http://www.cedarcreeksoftware.com JW JW JW JW - JW Before posting, please check: JWhttp://www.mysql.com/manual.php (the manual) JWhttp://lists.mysql.com/ (the list archive) JW JW To request this thread, e-mail [EMAIL PROTECTED] JW To unsubscribe, e-mail [EMAIL
Re: Best hardware for a very large MySQL server? looking at x86
On Thursday 04 April 2002 09:48 am, you wrote: I think you'll find that the RISC systems have fewer processors and run at lower clockspeeds for the same total performance. 100k is a HUGE amount of money to drop on a system. You could get 2 full racks of high performance 1U systems, including everything, for less, but whatever! did you mean to say You could get 2 full racks of high performance 1U x86 systems ? Or are you saying I should get several smaller systems either way? Thanks. sql query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
More then 2-way SMP, process threading Sun Fire v880: Re: Best hardware for a very large MySQL server? looking at x86
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 DN In the last episode (Apr 04), JW said: DN Heh. plus the maintenence nightmare of managing 48 servers, 96 mirrored DN boot disks, 96 power supplies, etc etc.. DN DN A comparable system to the Dell link you pasted is the Sun Fire v880. DN For $120k, you get 8x750mhz Ultrasparc III CPUs, 32GB of RAM, and 12 DN 36gb fibrechannel disks. Those are 64-bit CPUs with 8MB of cache DN (compared to the 32bit Xeons w/2M you'd get with the Dell), and should DN easily outperform the Dell box. DN DN http://store.sun.com/catalog/doc/BrowsePage.jhtml?cid=71713 I was already looking at these... Does anyone have any experience with MySQL a v880? Someone on another list was saying: ou've got to remember that in a lot of scenerios, more processors can degrade preformance instead of enhancing it. 4 Dual processing machines always beats one 8-way machine as far as network services related things like File/Web/Print serving. 4-way machines can add benefit in fine-grain tightly coupled processes like a heavily threaded DB server (like Oracle). You start to loose I/O performance with most x86 architectures after 4-way. What do ya'll say to that? Does MySQL performance really go down with more processors? - --- filter bypass: query sql - -- - Jonathan Wilson System Administrator Clickpatrol.com Cedar Creek Software http://www.cedarcreeksoftware.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8rJrCW3F87Q8SQQARAhBJAKDZxvdJEZNFMi/+iEmerwSD/VENcwCfUlpT 5FeCpR8noGhFV/sQ/isp5KU= =t1aj -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Best hardware for a very large MySQL server? looking at x86
Trying to send this again... SPAM filter messing with me...
this is a query about what hardware might make for a really good sql server
There :-p
Hello,
I need some advise.
We are about to purchase a huge system for use as a DB/web application server (mostly
DB).
I'd like to point out that upper management (not me) has decided to do this... please
don't tell me that I don't need something that big, or that I should use an x86
cluster - that's already out of the question, and out of my hands.
The server they had already decided to get is a Dell PowerEdge 8450 with an external
PowerVault storage array. See details here:
PowerEdge 8450:
http://configure.us.dell.com/dellstore/config.asp?customer_id=04keycode=6W300order_code=PE8450cfgpg=1#updatepriceNS
PowerVault 22xS
http://configure.us.dell.com/dellstore/config.asp?order_code=PV22XScustomer_id=04keycode=6W300family_id=9171
The server is an eight-way PIII Xeon , 32GB of RAM, price approx. $99,000 USD - let's
say $100,000 In some configurations we've gotten higher.
PowerVault approx. $7,500 USD for five-disk RAID 5, 36GB 15k SCSI disks.
Management asked me and another tech. to figure out exactly what we need. We called
Dell, and the Dell tech said this would be going head on with RISC based systems.
Which got me to thinking I am personally not fond of x86, and don't want to pass
up an opportunity to get a RISC system, like an Alpha, SPARC or PPC.
Management has given me permission to make a comparison, I'm hoping someone here has
experience with RISC systems.
I'd _really_ like to have a RISC system but I've got no idea how to go about
comparing them.
For me it's like trying to compare apples and oranges when you don't even know what
an orange is =)
Does anyone here know how much an Alpha, SPARC, or PPC system that has comparable
power would be?
Or even _what_ systems are comparable? As I said, I've never dealt with anything
besides x86 and Apple PPC before, so I'm venturing into totally new territory.
If someone can give me a clue, I'd really appreciate it. Apparently we are mostly
after processing power (CPU+RAM), my boss said we wouldn't even need a GigaBit NIC
(though of course he may be wrong).
I basically need to find a RISC system that produces the same amount of power for
less $$$, or at LEAST more power for the same amount of $$$.
It must run Linux of course, much preferably SuSE. I know SuSE Enterprise edition
runs on SPARC, PPC ({i|p|z}Series and Itanium/IA64 ( I really don't want the latter,
though if someone gives me a convincing argument, I'll consider it), and Professional
7.1 runs on Alpha, 7.3 runs on PowerPC
Any help would be greatly appreciated.
Thanks!
Jonathan Wilson
System Administrator
Clickpatrol.com
Cedar Creek Software http://www.cedarcreeksoftware.com
Jonathan Wilson
System Administrator
Clickpatrol.com
Cedar Creek Software http://www.cedarcreeksoftware.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQLdMax crashed (for unknown reasons), please help
Hello, We're been running a pretty large MySQLd with InnoDB support, last night it crashed on us in the middle of the night. I have never sent in a bug report like this before so please give me a little slack. I do not have any clue as to what actually caused the crash, I only have the logs and confs. In order: 1. System specs 2. my.cnf directives and 3. MySQL error log = 1. System Specs Dell PowerEdge 2450 Dual PIII 850 2G RAM 5-disk RAID5 for a total of 67G in / (27 used, 39 free) I think swap is also 2GB but I'm not sure (1060258+ blocks). SuSE Linux 7.3 ccs012:~ # uname -a Linux ccs012 2.4.10-64GB-SMP #1 SMP Fri Sep 28 17:26:36 GMT 2001 i686 unknown ccs012:~ # free -m total used free sharedbuffers cached Mem: 2013 2007 5 0 23695 -/+ buffers/cache: 1288724 Swap: 1035 0 1035 Not running any major service except MySQL, standalone sshd and inetd (for telnet) = 2. my.cnf ccs012:~ # grep -v # /etc/my.cnf [client] port= 3306 socket = /var/lib/mysql/mysql.sock [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking set-variable= key_buffer=384M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=2M set-variable= record_buffer=2M set-variable= thread_cache=8 set-variable= max_connections=150 set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=64M log-bin server-id = 1 innodb_data_file_path = ibdata1:2G innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=156M set-variable = innodb_log_buffer_size=12M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=1024M set-variable = innodb_additional_mem_pool_size=8M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout [safe_mysqld] open-files-limit=256 = 3. MySQLd-Max error log output ccs012:~ # less /var/lib/mysql/ccs012.err 020216 19:46:15 mysqld started 020216 19:46:20 InnoDB: Started /usr/sbin/mysqld-max: ready for connections InnoDB: Error: undo-id is 137339008 InnoDB: Assertion failure in thread 27591729 in file trx0undo.c line 1316 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] 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 agaist 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=402649088 record_buffer=2093056 sort_buffer=2097144 max_used_connections=150 max_connections=150 threads_connected=68 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 1007010 K bytes of memory Hope that's ok, if not, decrease some variables in the equation InnoDB: Thread 27614285 stopped in file btr0pcur.c line 202 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Stack range sanity check OK, backtrace follows: 0x806c9b9 0x8249998 0x81acb51 0x81a27d4 0x81949f3 0x817d565 0x817d949 0x817dc42 0x8170a1d 0x80baff9 0x809b855 0x80749a5 0x8076548 0x80725d4 0x8071ac7 Stack trace seems successful - bottom reached Please read http://www.mysql.com/doc/U/s/Using_stack_trace.html and follow instructions on how to resolve the stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x86f4e00 = delete from cpsearchenginedata.customerkeywordbid where customerkeywordid='5695' thd-thread_id=1329667 Successfully dumped variables, if you ran with --log, take a look at the details of what thread 1329667 did to cause the crash. In some cases of really bad corruption, the values shown above may be invalid The manual page at http://www.mysql.com/doc/C/r/Crashing.html contains information that should help you find out what is causing the
Re: Really good idea on Performance Tuning???
At 12:22 PM 1/30/2002 -0600, you wrote: I sent a reply back to JW about his problem on performance tuning and came up with I thought a really good idea. But there weren't any bites so I thought I start it on a new thread. I'd like to point out that I'm pretty sure you sent the reply to me directly - I don't think it ever reached this list. That probably explains the lack of bytes ;-) There are a lot of threads about setting up MySQL for the best performance. This might help to solve the problem. What I'd like to see is a web page that has a MySQL/InnoDb configuration calculator where you simply enter the specifics of your hardware (like amount of RAM, # processors), # of connections, operating system, and database size, and it would tell you how best to configure the cnf file. In fact, it could also generate the lines for the .cnf file so you can just copy and paste it into your own file (this avoids typing mistakes too). Even the first part would help! The web page could be hosted on MySQL or InnoDb web site. That way it would also be accessible from the client's office too. This would at least provide the novice user with a setup configuration that is more accurate than he could achieve by pen and paper the first time out. It would also help avoid confusion of the inconsistent documentation. I don't mean to make any doc authors feel bad but there's something that don't match up on the InnoDB setup page. The web page could be written in PHP and is easy enough to implement and the benefits would be enormous when you consider the # of MySQL sites out there that could use it. No kidding! Doubtless it would be worth it just to lower the noise level on the list. Ok, what do you think. Will this idea fly? I hope so :-) I do not know PHP but if there's some way I can help with this I'd be willing to try and pitch in. If there's at least one or 2 gurus out there would could answer questions one at a time, I could maybe start by making a list of what the page needs and what it should do for at least some of the values. If you'd like, I could write a static page with forms for the server-info to save some PHP coder at least that much time. What do you think? Brent Heikki, are you listening? :-) Presuming that Heikki is some authoritative soul, I'd like to repeat that question :-) Jonathan Wilson System Administrator Cedar Creek Software http://www.cedarcreeksoftware.com Central Texas IT http://www.centraltexasit.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Performance tuning
= myisam_sort_buffer_size=64M log-bin server-id = 1 # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #set-variable = bdb_cache_size=384M #set-variable = bdb_max_lock=10 innodb_data_file_path = ibdata1:2G innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ #set-variable = innodb_mirrored_log_groups=1 set-variable = innodb_log_files_in_group=3 # Conflicting instructions. One says 15% of innodb_buffer_pool_size, # the other says 1/3 of it. # 1/3 is 341.2992 MB # We use 15% cause it's smaller :-) # set-variable = innodb_log_file_size=156M #set-variable = innodb_log_buffer_size=8M set-variable = innodb_log_buffer_size=12M innodb_flush_log_at_trx_commit=1 innodb_log_archive=0 set-variable = innodb_buffer_pool_size=1024M #start with 2 MB, add more if you have many tables set-variable = innodb_additional_mem_pool_size=8M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 # threads: default is 8. If you have low performance and innodb_monitor reveals many threads waiting for semaphores,\ then you may have thread thrashing and should try setting this parameter lower. #If you have a computer with many processors and disks, you can try setting this value higher to better utilize the \resources of you computer. A value 'number of processors + number of disks' is recommended. #leaving it set to default for now (would be 7 for us with dual procs and 5-disk RAID5) #set-variable = innodb_thread_concurrency=5 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=256M set-variable= sort_buffer=256M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout # # Added by JW 2002-01-26 per MySQL manual.txt suggestion # [safe_mysqld] open-files-limit=256 TIA for any help anyone can give me :-) Jonathan Wilson System Administrator Cedar Creek Software http://www.cedarcreeksoftware.com Central Texas IT http://www.centraltexasit.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Upgrading from MySQL to MySQL max on a production server
At 08:27 AM 12/11/2001 +0200, you wrote: Hi! I do not understand how replacing just the executable mysqld can make your 'mysql start' (or is it 'mysql.server start'?) script or safe_mysqld script to search the executable from a different directory than it did before. I do not understand it either, but I have done exactly that on both a SuSE 7.1 server and a RedHat server, and in both cases it then started looking in /usr/local/mysql for things. Neither the SuSE box nor the RedHat box had a /usr/local/mysql directory before. Are you sure you did not change anything else in the system? I strongly assure you that I did not change the path, nor the my.cnf nor the init script nor any other thing on the whole system. Additionally, as soon as I run mv mysqld mysqld_inno ; mv mysqld_redhat mysqld I can call the init script restart and it immediately works fine again. What ever the problem is, it's hard-coded inside the mysql-max binary. I will say that I did not use the rpm, I used the tarball and copied mysqld out of the tarball's bin/ directory. Anyway, it is best that you make a new installation of MySQL-Max. The version 3.23.38 is very old and many bugs have been fixed to .46. From the manual I found a useful page: http://www.mysql.com/doc/A/u/Automatic_start.html There has been no change in MySQL table formats since .38, hence your database should run ok with .46 -Max too. For MyISAM type tables MySQL and MySQL -Max are equivalent. But if you have somehow a nonstandard installation, The SuSE installation is 100% standard SuSE, the RedHat installation is MySQL from an RPM from mysql.com, nothing edited or customized. Thanks. or have edited the startup scripts, better be prepared for some problems in starting up mysqld. Regards, Heikki http://www.innodb.com -- Order commercial MySQL/InnoDB support at https://order.mysql.com/ Hello, We're needing to use MySQL-Max because we need the functionality provided by InnoDB. According to InnoDB's web site, all I have to do is download the tarball and replace the /usr/sbin/mysqld with the mysqld in the tarball. I tried that on SuSE and I had to install a few other things and make a few symlinks too, but it did eventually work quite fine. However I was just trying that method on a redhat 7.0 production box and it doesn't work because when I call /etc/init.d/mysql start it says it can't find /usr/local/mysql/libexec/mysqld which is interesting, because I can't find any libexec directories associated with mysql on any of my linux boxes, no matter what distro. Anyway (unless someone has an answer to the libexec problem), I think I'm going to need to upgrade my whole MySQL version to a full MySQL-Max installation. Currently we're running MySQL-3.23.38-1 from an RPM from mysql.com. Our problem is that we are already using quite a few databases in our current mysql and we need to know _before_ we upgrade exactly what's going to happen to the databases we already have. Will MySQL-Max just start working with them without a hitch, or do we need to go through some sort of initialization routine? (note: we're not trying to convert our current DBs to InnoDB that is a whole separate issue. We just want to know what we'll need to to be able to continue accessing the data once we install MySQL-Max. Is it perfectly compatible with MySQL(non-max)? Just backup, install and /etc/init.d/mysql start? Or is there more to it then that? Thanks a lot. Jonathan Wilson System Administrator Cedar Creek Software http://www.cedarcreeksoftware.com Central Texas IT http://www.centraltexasit.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php Jonathan Wilson System Administrator Cedar Creek Software http://www.cedarcreeksoftware.com Central Texas IT http://www.centraltexasit.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Upgrading from MySQL to MySQL max on a production server
Hello, We're needing to use MySQL-Max because we need the functionality provided by InnoDB. According to InnoDB's web site, all I have to do is download the tarball and replace the /usr/sbin/mysqld with the mysqld in the tarball. I tried that on SuSE and I had to install a few other things and make a few symlinks too, but it did eventually work quite fine. However I was just trying that method on a redhat 7.0 production box and it doesn't work because when I call /etc/init.d/mysql start it says it can't find /usr/local/mysql/libexec/mysqld which is interesting, because I can't find any libexec directories associated with mysql on any of my linux boxes, no matter what distro. Anyway (unless someone has an answer to the libexec problem), I think I'm going to need to upgrade my whole MySQL version to a full MySQL-Max installation. Currently we're running MySQL-3.23.38-1 from an RPM from mysql.com. Our problem is that we are already using quite a few databases in our current mysql and we need to know _before_ we upgrade exactly what's going to happen to the databases we already have. Will MySQL-Max just start working with them without a hitch, or do we need to go through some sort of initialization routine? (note: we're not trying to convert our current DBs to InnoDB that is a whole separate issue. We just want to know what we'll need to to be able to continue accessing the data once we install MySQL-Max. Is it perfectly compatible with MySQL(non-max)? Just backup, install and /etc/init.d/mysql start? Or is there more to it then that? Thanks a lot. Jonathan Wilson System Administrator Cedar Creek Software http://www.cedarcreeksoftware.com Central Texas IT http://www.centraltexasit.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: [SLE] Trouble with InnoDB: Error message file errmsg.sys doesn't have enough messages?
Well, I got around the problem by replacing the old mysql/english directory with a new one from the tarball. I hope that was an o.k. thing to do. At 04:35 PM 12/5/2001 -0600, you wrote: Using mysql-3.23.33-4 on SuSE Linux 7.1: root@fluorite:/usr/sbin uname -a Linux fluorite 2.4.0-64GB-SMP #1 SMP Wed Jan 24 15:52:30 GMT 2001 i686 unknown We've been using MySQL for a while and we're trying to test InnoDB. According the InnoDB page all we need to do is untar the package and overwrite /usr/sbin/mysqld. I also found I have to create to following directory/symlink: root@fluorite:/usr/bin ls -l /usr/local/mysql/share/ total 0 lrwxrwxrwx1 root root 17 Dec 5 16:05 mysql - /usr/share/mysql/ becauwe without that the new mysqld (mysqld-max) couldn't find /usr/local/mysql/share/mysql/english/errmsg.sys. Now that I have that all set up, I'm getting the following errors: root@fluorite:/usr/bin /usr/sbin/mysqld /usr/sbin/mysqld: Fatal error: Error message file '/usr/local/mysql/share/mysql/english/errmsg.sys' had only 203 error messages, but it should have at least 218 error messages. Check that the above file is the right version for this program! I don't think it's a version problem because the RPM was mysql-3.23.33-4 and the tarball is mysql-max-3.23.46-pc-linux-gnu-i686.tar.gz. Can anyone give me some tips on what's wrong? Is there's something wrong with the SuSE version of mysql-3.23.33-4 that's incompatible with mysql-max-3.23.46-pc-linux-gnu-i686.tar.gz ? TIA Jonathan Wilson System Administrator Cedar Creek Software http://www.cedarcreeksoftware.com Central Texas IT http://www.centraltexasit.com -- To unsubscribe send e-mail to [EMAIL PROTECTED] For additional commands send e-mail to [EMAIL PROTECTED] Also check the FAQ at http://www.suse.com/support/faq and the archives at http://lists.suse.com Jonathan Wilson System Administrator Cedar Creek Software http://www.cedarcreeksoftware.com Central Texas IT http://www.centraltexasit.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB: How to increase table/DB size after the fact?
Hello, To use InnoDB you have to put how large of a file MySQL is allowed to make by putting an entry in /etc/my.cnf such as the one I used: innodb_data_home_dir = /var/lib/mysql/innodb innodb_data_file_path = indb1:200M;indb2:200M that of course gives us 400M total. I have not yet found in the documentation just what we will need to do when our tables fill up and we need more space. Because of the way the InnoDB tables are created initially I get the impression that you can't increase the size shown in /etc/my.cnf later on. Or am I wrong - do we just increase the MB shown in /etc/my.cnf? Do we add a third fiels there (Example: innodb_data_file_path = indb1:200M;indb2:200M;indb3:500MB ) ? TIA Jonathan Wilson System Administrator Cedar Creek Software http://www.cedarcreeksoftware.com Central Texas IT http://www.centraltexasit.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[solved] Re: InnoDB: How to increase table/DB size after the fact?
Ah, I finally found mention of this in the manual. Sorry to bother everyone. Thanks. --- Hello, To use InnoDB you have to put how large of a file MySQL is allowed to make by putting an entry in /etc/my.cnf such as the one I used: innodb_data_home_dir = /var/lib/mysql/innodb innodb_data_file_path = indb1:200M;indb2:200M that of course gives us 400M total. I have not yet found in the documentation just what we will need to do when our tables fill up and we need more space. Because of the way the InnoDB tables are created initially I get the impression that you can't increase the size shown in /etc/my.cnf later on. Or am I wrong - do we just increase the MB shown in /etc/my.cnf? Do we add a third files there (Example: innodb_data_file_path = indb1:200M;indb2:200M;indb3:500MB ) ? TIA Jonathan Wilson System Administrator Cedar Creek Software http://www.cedarcreeksoftware.com Central Texas IT http://www.centraltexasit.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
