Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Hello, On Fri, 08.09.2006 at 10:51:00 +0200, Lukasz Sztachanski [EMAIL PROTECTED] wrote: mysql Ver 12.22 Distrib 4.0.27, for unknown-openbsd3.8 (i386) Uptime: 94 days 17 hours 17 min 50 sec mind you that my problems radically increased with 5.x - nobody is talking about 4.x anymore. Best, --Toni++
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Toni Mueller wrote: mind you that my problems radically increased with 5.x - nobody is talking about 4.x anymore. You checked these right? Many details that might help you. http://www.openbsdsupport.org/mysql.htm Just a thought. Daniel
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
* Zoong PHAM [EMAIL PROTECTED] [2006-09-08 18:04]: On Thursday, 7 September 2006 at 17:03:55 +0200, Henning Brauer wrote: as said before, our mysqlds on sparc64, mp- up-i386, and sparc are very stable; some see more than 400 queries/s sometimes. Do you mind to share your /etc/sysctl.conf and /etc/my.cnf ? we just run with increaeed maxfiles (kern.maxfiles=4096). the mysql config mysqld section: [mysqld] port= 3306 socket = /var/run/mysql/mysql.sock skip-locking key_buffer = 256M max_allowed_packet = 1M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size= 16M thread_concurrency = 8 log-bin server-id = 1 skip-innodb open_files_limit=2048 max_connections = 500 -- Henning Brauer, [EMAIL PROTECTED], [EMAIL PROTECTED] BS Web Services, http://bsws.de Full-Service ISP - Secure Hosting, Mail and DNS Services Dedicated Servers, Rootservers, Application Hosting - Hamburg Amsterdam
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
On Sunday, 10 September 2006 at 17:30:00 +0200, Henning Brauer wrote: we just run with increaeed maxfiles (kern.maxfiles=4096). the mysql config mysqld section: [mysqld] Thanks Henning for sharing the info. Zoong
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Here to reduce the questions on the installation and configuration of MySQL on OpenBSD. I put a document up with more details on it. http://openbsdsupport.org/mysql.htm Yeap, the English may not be perfect, but the steps are there. Hope this help anyway. Best, Daniel PS: I don't think I forgot anything, but if I did, fell free to correct me! (;
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
On Thu, Sep 07, 2006 at 01:57:19PM +0200, Edgars wrote: Same here, a lot of problems since 3.7. :( Because of that, two client servers was migrated to freebsd :/ -Original message- From: Toni Mueller [EMAIL PROTECTED] Date: Thu, 7 Sep 2006 13:09:09 +0300 To: misc@openbsd.org Subject: Re: Opinion of MySQL 5.xx on OpenBSD 3.9... Hello, On Thu, 22.06.2006 at 12:49:22 +0200, Henning Brauer [EMAIL PROTECTED] wrote: I haven't seen stability problems with mysql on OpenBSD in a long time. not even on sparc64. then you are very lucky, imho. then you're both doing smth wrong: mysql Ver 12.22 Distrib 4.0.27, for unknown-openbsd3.8 (i386) Uptime: 94 days 17 hours 17 min 50 sec ~26 databases( mostly evil bulletin boards). - Lukasz Sztachanski -- 0x058B7133 // 16AB 4EBC 29DA D92D 8DBE BC01 FC91 9EF7 058B 7133 http://entropy.pl http://entropy.pl/?blog
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Kyle George wrote: Not exactly related, but there's also a bug I've experienced on 3.9 with DBD::mysql. See: http://rt.cpan.org/Public/Bug/Display.html?id=20868 I have probably found that bug and I'm waiting for a fix from a guy at mysql. Here it is the last email from him. Bye. Original Message Subject: Re: Memory access problem with DBI or DBD-Mysql? Date: Mon, 04 Sep 2006 11:04:52 -0400 From: Patrick Galbraith [EMAIL PROTECTED] To: Federico Giannici [EMAIL PROTECTED] CC: dbi-users@perl.org, [EMAIL PROTECTED], Sam Smith [EMAIL PROTECTED] References: [EMAIL PROTECTED] [EMAIL PROTECTED] Federico, That may be the issue. I have encountered this issue in other parts of the driver. There is a better way to do this, and I can look at making sure what is being passed is the same data object. Thanks! Patrick Federico Giannici wrote: Since there has been no reply to my previous message, I have done further investigations trying to find the problem. Please note that my knowledge of DBI/DBD is almost null, so the followings are only simple suppositions. I have seen that mysql_st_internal_execute() function is executed by both the do and execute methods. It seems that the problems are only with the do method and not with the execute, so I looked for the differences between them. The main difference seems to be that execute passes a STATEMENT handle as first argument, while do passes a DATABASE handle. The mysql_st_internal_execute() function uses this handle to obtain the sth and then from this one the dbh. So, my hypothesis is that if the initial handle is a database one, the sth (and the derived dbh) obtained from this is not a valid one! Anybody can confirm (or negate) this wild hypothesis? Thanks. P.S. I want to repeat that the problem manifest itself only under OpenBSD because of it's memory management that cause the program to segfault if try to access a non allocated memory. In other operating systems, a random value is get for imp_dbh-bind_type_guessing, which is almost irrelevant. Federico Giannici wrote: It seems to me that there is some kind of memory access problem with DBI or DBD-Mysql. I'm using OpenBSD 3.9-stable amd64. On OpenBSD 3.3 i386 the problem didn't appeared. As you may know, recent version of OpenBSD have a new kind of memory handling that make the programs segfault when they try to access no (longer) allocated memory. I'm using DBI 1.45 and DBD-Mysql 2.9008. I tried DBI 1.52 and DBD-Mysql 3.0006, but the problems were more frequent, so I remained to the old versions. Here is the problem: frequently some do commands cause perl to crash with signal 11. The crashes seems to depend on a lot of factors. For example, loading more libraries could make the program to start working. I think it depends on the structure of the memory allocated to the program. Here is the bt output of the core dump: #0 0x5260a736 in mysql_st_internal_execute (h=0x4713b6e0, statement=0x479b7140, attribs=0x4aa5fd40, numParams=0, params=0x0, cdaPtr=0x7f7c8610, svsock=0x43c90498, use_mysql_use_result=0) at dbdimp.c:1654 #1 0x52612da3 in XS_DBD__mysql__db_do (cv=0x40970b20) at mysql.xs:222 #2 0x50ddf07b in XS_DBI_dispatch () from /usr/local/libdata/perl5/site_perl/amd64-openbsd/auto/DBI/DBI.so #3 0x4a5a1c47 in Perl_pp_entersub () at /usr/src/gnu/usr.bin/perl/pp_hot.c:2890 #4 0x4a60899e in Perl_runops_standard () at /usr/src/gnu/usr.bin/perl/run.c:37 #5 0x4a5f744d in S_run_body (oldscope=1) at /usr/src/gnu/usr.bin/perl/perl.c:1936 #6 0x4a5f7231 in perl_run (my_perl=0x45356258) at /usr/src/gnu/usr.bin/perl/perl.c:1855 #7 0x00401afe in main () I have found the problem is caused by accessing imp_dbh-bind_type_guessing for the call to ParseParam() inside mysql_st_internal_execute(). I have verified that imp_dbh is NOT null, but trying to access any member make the program segfault. So maybe the pointer is a stale one? I have not enough knowledge of DBI to make more debugging. Bye. -- ___ __ |- [EMAIL PROTECTED] |ederico Giannici http://www.neomedia.it ___
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
On Thursday, 7 September 2006 at 17:03:55 +0200, Henning Brauer wrote: as said before, our mysqlds on sparc64, mp- up-i386, and sparc are very stable; some see more than 400 queries/s sometimes. Do you mind to share your /etc/sysctl.conf and /etc/my.cnf ? Thanks Zoong
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Hey folks, On 6/20/06, Jesse Gumm [EMAIL PROTECTED] wrote: It's good, but if it's a multiprocessor machine, OpenBSD won't take full advantage of the other processors with MySQL since MySQL is multi-threaded. But it's stable, and runs reasonably fast (and you are assured the system is more secure, being OpenBSD and all). My only concern, then, is really if it's a multiprocessor machine, and you need breakneck performance, you might want to consider running MySQL on FreeBSD with linuxthreads. And mine, is that if do need performance with mysql, you would want Solaris lastest version. Its multithreaded support rocks the house. Even FreeBSD isn't even close to Solaris. That's the experience i had, of course. -Jesse On 6/20/06, Anders J [EMAIL PROTECTED] wrote: Hello List. A customer have a mailserver solution wich must be moved to a new hardware, today its is running on a old suse 9.2 but i really want to use OpenBSD if possible. It uses postfix, cyrus-imap and mysql as the backend for users domain info, contacts etc. (only 58 named users and 3 domains and several aliases) My self have experienced mixed issues with MysSQL on OpenBSD and also read and heard about performance and stability problems with MySQL on OpenBSD. So i'm a bit nervous implementing this on OpenBSD (Should i use FreeBSD instead)?. Has anyone a positive experience with 3.9+ MySQl mysql-server-5.0.21.tgz ftp://ftp.sunet.se/pub/OpenBSD/3.9/packages/i386/mysql-server-5.0.21.tgz(or later)? And by the way it is not an option to use postgresql since they also have several other apps which use the database in question .So migrate to an other db platform is NOT an option. Regards Anders
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Gustavo Rios wrote: Hey folks, On 6/20/06, Jesse Gumm [EMAIL PROTECTED] wrote: It's good, but if it's a multiprocessor machine, OpenBSD won't take full advantage of the other processors with MySQL since MySQL is multi-threaded. But it's stable, and runs reasonably fast (and you are assured the system is more secure, being OpenBSD and all). My only concern, then, is really if it's a multiprocessor machine, and you need breakneck performance, you might want to consider running MySQL on FreeBSD with linuxthreads. And mine, is that if do need performance with mysql, you would want Solaris lastest version. Its multithreaded support rocks the house. Even FreeBSD isn't even close to Solaris. That's the experience i had, of course. Then use what you see fit for your requirements. No one is forcing anyone to use OpenBSD for this. But stop spreading FUD as to MySQL is not stable or have less performance on OpenBSD. That simply BULL! I guess the difference is may be the one that are stable did look into their setup and configure it for their needs... This recurring MySQL FUD shit drive me nuts! Sorry guys, I had to let it out. If you don't like running it on OpenBSD, then DON'T! But saying it shouldn't, or couldn't because it's not stable, or efficient, is simply a lack of understanding and homework and simply spread FUD! Best, Daniel
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Hello, On Thu, 22.06.2006 at 12:49:22 +0200, Henning Brauer [EMAIL PROTECTED] wrote: I haven't seen stability problems with mysql on OpenBSD in a long time. not even on sparc64. then you are very lucky, imho. On a variety of OpenBSD boxes, and with a variety of MySQL versions, I experience random crashes or, mostly, hangs where the server does not respond anymore, but also doesn't crash. In such cases, a violent kill and a restart of the MySQL server is required to get going again. This is from 3.7 to 3.9 with MySQL versions from 4.0.x to 5.0.x (from ports), all on several i386 machines with different (PC-) hardware, with _low_ traffic and _ample_ resources (enough to hold all databases in RAM). When pushed, I see like 10 (15?) queries a second, but on average, I see less than 1 query every two seconds. Unless you're really pushing the limits, performance is not much of a problem either. with really extreme load, our threading library shows why we wanna go for rthreads. for the vast majority of uses out there, you will not see a difference. I very much hope to see a significant difference (or otherwise, more apps that don't depend as much on MySQL). Best, --Toni++
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Same here, a lot of problems since 3.7. :( Because of that, two client servers was migrated to freebsd :/ -Original message- From: Toni Mueller [EMAIL PROTECTED] Date: Thu, 7 Sep 2006 13:09:09 +0300 To: misc@openbsd.org Subject: Re: Opinion of MySQL 5.xx on OpenBSD 3.9... Hello, On Thu, 22.06.2006 at 12:49:22 +0200, Henning Brauer [EMAIL PROTECTED] wrote: I haven't seen stability problems with mysql on OpenBSD in a long time. not even on sparc64. then you are very lucky, imho. On a variety of OpenBSD boxes, and with a variety of MySQL versions, I experience random crashes or, mostly, hangs where the server does not respond anymore, but also doesn't crash. In such cases, a violent kill and a restart of the MySQL server is required to get going again. This is from 3.7 to 3.9 with MySQL versions from 4.0.x to 5.0.x (from ports), all on several i386 machines with different (PC-) hardware, with _low_ traffic and _ample_ resources (enough to hold all databases in RAM). When pushed, I see like 10 (15?) queries a second, but on average, I see less than 1 query every two seconds. Unless you're really pushing the limits, performance is not much of a problem either. with really extreme load, our threading library shows why we wanna go for rthreads. for the vast majority of uses out there, you will not see a difference. I very much hope to see a significant difference (or otherwise, more apps that don't depend as much on MySQL). Best, --Toni++ -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
* Toni Mueller [EMAIL PROTECTED] [2006-09-07 12:14]: Hello, On Thu, 22.06.2006 at 12:49:22 +0200, Henning Brauer [EMAIL PROTECTED] wrote: I haven't seen stability problems with mysql on OpenBSD in a long time. not even on sparc64. then you are very lucky, imho. no, you are very unlucky or doing something very wrong. On a variety of OpenBSD boxes, and with a variety of MySQL versions, I experience random crashes or, mostly, hangs where the server does not respond anymore, but also doesn't crash. In such cases, a violent kill and a restart of the MySQL server is required to get going again. This is from 3.7 to 3.9 with MySQL versions from 4.0.x to 5.0.x (from ports), all on several i386 machines with different (PC-) hardware, with _low_ traffic and _ample_ resources (enough to hold all databases in RAM). as said before, our mysqlds on sparc64, mp- up-i386, and sparc are very stable; some see more than 400 queries/s sometimes. -- BS Web Services, http://www.bsws.de/, [EMAIL PROTECTED], [EMAIL PROTECTED] OpenBSD-based Webhosting, Mail Services, Managed Servers, ... Unix is very simple, but it takes a genius to understand the simplicity. (Dennis Ritchie)
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Make sure that you have your 'open-files-limit' parameter set to a sane value in your my.cnf. If you don't have anything set for that limit the default is extremely low (so low that using views tended to not work on my dev box). I have been using open-files-limit = 8192, however YMMV. Tim Donahue On Thu, 7 Sep 2006 12:09:09 +0200 Toni Mueller [EMAIL PROTECTED] wrote: Hello, On Thu, 22.06.2006 at 12:49:22 +0200, Henning Brauer lists- [EMAIL PROTECTED] wrote: I haven't seen stability problems with mysql on OpenBSD in a long time. not even on sparc64. then you are very lucky, imho. On a variety of OpenBSD boxes, and with a variety of MySQL versions, I experience random crashes or, mostly, hangs where the server does not respond anymore, but also doesn't crash. In such cases, a violent kill and a restart of the MySQL server is required to get going again. This is from 3.7 to 3.9 with MySQL versions from 4.0.x to 5.0.x (from ports), all on several i386 machines with different (PC-) hardware, with _low_ traffic and _ample_ resources (enough to hold all databases in RAM). When pushed, I see like 10 (15?) queries a second, but on average, I see less than 1 query every two seconds. Unless you're really pushing the limits, performance is not much of a problem either. with really extreme load, our threading library shows why we wanna go for rthreads. for the vast majority of uses out there, you will not see a difference. I very much hope to see a significant difference (or otherwise, more apps that don't depend as much on MySQL). Best, --Toni++
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Original message Date: Thu, 7 Sep 2006 17:03:55 +0200 From: Henning Brauer [EMAIL PROTECTED] Subject: Re: Opinion of MySQL 5.xx on OpenBSD 3.9... To: misc@openbsd.org * Toni Mueller [EMAIL PROTECTED] [2006-09-07 12:14]: Hello, On Thu, 22.06.2006 at 12:49:22 +0200, Henning Brauer [EMAIL PROTECTED] wrote: I haven't seen stability problems with mysql on OpenBSD in a long time. not even on sparc64. then you are very lucky, imho. no, you are very unlucky or doing something very wrong. i too was unlucky until i read some posts on misc@ several months back. adding _mysql:\ :datasize=1024M:\ :maxproc=4096:\ :openfiles-cur=2048:\ :openfiles-max=8192:\ :stacksize-cur=16M:\ :tc=default: to /etc/login.conf to make a login class for mysql fixes the problem with mysql having too many open files when you start the server with a command like # Start MySQL server if [ -x /usr/local/bin/mysqld_safe ] ; then su -c _mysql root -c '/usr/local/bin/mysqld_safe --log-error=/var/mysql/error.log ' /dev/null fi in /etc/rc.local. as said before, our mysqlds on sparc64, mp- up-i386, and sparc are very stable; some see more than 400 queries/s sometimes. since i've made this change, mysql runs quite well. i can't speak for activity levels like henning quotes here, but i haven't had any problems since i made these aforementioned changes. this info is in the misc@ archives if you care to search for it. -- BS Web Services, http://www.bsws.de/, [EMAIL PROTECTED], [EMAIL PROTECTED] OpenBSD-based Webhosting, Mail Services, Managed Servers, ... Unix is very simple, but it takes a genius to understand the simplicity. (Dennis Ritchie)
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
On Thu, 7 Sep 2006, Matthew Peltzer wrote: On 9/7/06, Tim Donahue [EMAIL PROTECTED] wrote: Make sure that you have your 'open-files-limit' parameter set to a sane value in your my.cnf. If you don't have anything set for that limit the default is extremely low (so low that using views tended to not work on my dev box). I have been using open-files-limit = 8192, however YMMV. FYI, I was suggested to use this setting to correct what looked like all the databases in mysql getting DROPed after a short time (an hour or so) of light use. I soon after noticed this in /var/mysql/$host.err the file: 060830 22:37:02 [Warning] Could not increase number of max_open_files to more than 1772 (request: 8192) ...so I bumped it down to 1024 and all has been well enough. Of course, there is a per process limit (see ulimit and login.conf) and a system wide limit of open files (sysctl kern.maxfiles; guess what its default value is). -Otto
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
On 2006/09/07 09:35, Matthew Peltzer wrote: 060830 22:37:02 [Warning] Could not increase number of max_open_files to more than 1772 (request: 8192) $ sysctl kern.maxfiles kern.maxfiles=1772 login.conf limits also apply (exactly which section depends on how mysqld is started). this comes up from time to time, there's more in the list archives.
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
On Thu, 7 Sep 2006, Jacob Yocom-Piatt wrote: i too was unlucky until i read some posts on misc@ several months back. adding _mysql:\ :datasize=1024M:\ :maxproc=4096:\ :openfiles-cur=2048:\ :openfiles-max=8192:\ :stacksize-cur=16M:\ :tc=default: Besides /etc/login.conf changes, ulimit, and sysctl kern.maxfiles, I believe there is also an open-files-limit setting that can be placed in my.cnf. Not exactly related, but there's also a bug I've experienced on 3.9 with DBD::mysql. See: http://rt.cpan.org/Public/Bug/Display.html?id=20868 -- Kyle George [EMAIL PROTECTED]
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Make sure that you have your 'open-files-limit' parameter set to a sane value in your my.cnf. If you don't have anything set for that limit the default is extremely low (so low that using views tended to not work on my dev box). I have been using open-files-limit = 8192, however YMMV. A very easy test to do and see if you hit the limits on the number of open files for your database configurations and actual number of tables and databases in it, you can simply do this very simple step: mysqlcheck -m -A -hYourHost -uYourUsers -pYourPassword If you reach your limits on the open files, you will see the errors real quick on the screen when doing a simple tests on it. Then increase your limits to fit your needs and give yourself room obviously! This is the easiest test for this limits and many users report the same error 9 and that's what it is and this way to test for it real quick.
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
On Thu, 7 Sep 2006, Toni Mueller wrote: Hello, On Thu, 22.06.2006 at 12:49:22 +0200, Henning Brauer [EMAIL PROTECTED] wrote: I haven't seen stability problems with mysql on OpenBSD in a long time. not even on sparc64. then you are very lucky, imho. On a variety of OpenBSD boxes, and with a variety of MySQL versions, I experience random crashes or, mostly, hangs where the server does not respond anymore, but also doesn't crash. That's a pretty broad statement - how about a little more info? Were you using ISAM or InnoDB? How complex is the DB? What sort of operations are you doing (i.e. simple appends or complicated joins)? Lee Leland V. Lammert[EMAIL PROTECTED] Chief Scientist Omnitec Corporation Network/Internet Consultants www.omnitec.net
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Kyle George wrote: On Thu, 7 Sep 2006, Jacob Yocom-Piatt wrote: i too was unlucky until i read some posts on misc@ several months back. adding _mysql:\ :datasize=1024M:\ :maxproc=4096:\ :openfiles-cur=2048:\ :openfiles-max=8192:\ :stacksize-cur=16M:\ :tc=default: Besides /etc/login.conf changes, ulimit, and sysctl kern.maxfiles, I believe there is also an open-files-limit setting that can be placed in my.cnf. Yes there is and you also need to make sure to start MySQL using the new define class as well. Look in the archive for what needs to be there. I reposted all the details many times over. Some details again however: === in rc.local # Start MySQL server if [ -x /usr/local/bin/mysqld_safe ] ; then su -c _mysql root -c '/usr/local/bin/mysqld_safe ' /dev/null echo -n ' mysql' fi === in sysctl.conf # Increase for MySQL kern.maxfiles=8192 = in login.conf # Setting used by MySQL daemon _mysql:\ :datasize=infinity:\ :maxproc=infinity:\ :openfiles-cur=2048:\ :openfiles-max=4096:\ :stacksize-cur=8M:\ :localcipher=blowfish,8:\ :tc=default: === in my.cnf # The following options will be pass to mysqld_safe [mysqld_safe] open-files=2048 == Install from packages as well for trouble free. I have about 8 MySQL server all running for years and very stable. Some run on i386, AMD64 and Sun Sparc 64. None are unstable, or freeze. The time up only stop when I patch the system, either the OS, or install the package upgrades. If you have stability problem, you should troubleshoot it and get ride of your problem. May be different hardware between the screen and the keyboard might help get over that hump and get it to a stable stage. Hope this help you some. Daniel
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hej Daniel, Daniel Ouellet wrote: Marian Hettwer wrote: I'd love to have the time to give OpenBSD a chance on our production system. Seems unlikely, since we're running Linux only :( snipped install steps Really, a coffee break I tell you. That's all you need. The compare this with Fedora setup time on your Linux for fun. (: I do know how fast and easy it is to setup an OpenBSD box ;-) I just said, that I won't be allowed to deploy OpenBSD for our database servers. Besides, if you have 1000 servers (300 of 'em being MySQL boxes) then you do want something like kickstart, jumpstart, FAI, whatever. In our case, we're using FAI (Fully Automated Installer) which is based on and for Debian. It may be a coffee break to install one OpenBSD box and it may be just two coffee breaks to install two OpenBSD boxes, but you can't install the manual way 1000 servers with different purposes / configurations / packages. I do know, that some others did already some work in regards to auto deploying OpenBSD boxes. However, it's nowhere near the functionality of FAI. Different topic, though ;) ./Marian iD8DBQFEoQI6gAq87Uq5FMsRAjpAAKCsRIZ41EF0cS/3g/QRfCteAjsbCgCgqx/l 550x9GEAqa4RkCCjmm4fUMc= =bFg8 -END PGP SIGNATURE-
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Could i ask how your login class (/etc/login.conf) for mysql looks like? And maybe your [mysqld_safe] entry in/etc/my.cnf? I already modified my kern.maxfiles in /etc/sysctl.conf to kern.maxfiles=1 and in my own /etc/login.conf class i have set the follwing values: mysql:\ :datasize=infinity:\ :maxproc=infinity:\ :openfiles-cur=2048:\ :openfiles-max=8192:\ TIA Anders Trombley wrote: I've run postfix on OpenBSD using MySQL as the database for several years with +400 domains and +4k users; handling about 300K messages per day. Never gives me any problems. I had to bump the kern.maxproc and kern.maxfiles sysctl (man 8 sysctl) and add the following to /etc/my.cnf [mysqld_safe] open-files=2000 You may also want to alter the appropriate parts of /etc/login.conf (maxproc + openfiles) (man 5 login.conf) Hope it serves you as well as it has me.
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
On Mon, Jun 26, 2006 at 04:41:48PM +0200, Anders J wrote: Could i ask how your login class (/etc/login.conf) for mysql looks like? And maybe your [mysqld_safe] entry in/etc/my.cnf? I already modified my kern.maxfiles in /etc/sysctl.conf to kern.maxfiles=1 and in my own /etc/login.conf class i have set the follwing values: mysql:\ :datasize=infinity:\ :maxproc=infinity:\ :openfiles-cur=2048:\ :openfiles-max=8192:\ Relevant parts of /etc/my.cnf [mysqld_safe] open-files=2000 Looking at the machines here, doesn't appear I've actually altered anything in /etc/login.conf in reguards to mysql. In /etc/sysctl.conf: kern.maxproc=1536 kern.maxfiles=8192 Mind you, this isn't meant to imply that this is right - just what has been working for me. I have to admit that the various numbers for the sysctl and open-files were pulled out of thin air and tried until problems stopped. If there's a magic formula for it, I am unaware of it. TIA Anders Trombley wrote: I've run postfix on OpenBSD using MySQL as the database for several years with +400 domains and +4k users; handling about 300K messages per day. Never gives me any problems. I had to bump the kern.maxproc and kern.maxfiles sysctl (man 8 sysctl) and add the following to /etc/my.cnf [mysqld_safe] open-files=2000 You may also want to alter the appropriate parts of /etc/login.conf (maxproc + openfiles) (man 5 login.conf) Hope it serves you as well as it has me.
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
mysql status; -- 44 Open tables: 455 Queries per second avg: 5.117 -- # dmesg OpenBSD 3.9 (GENERIC) #617: Thu Mar 2 02:26:48 MST 2006 [EMAIL PROTECTED]:/usr/src/sys/arch/i386/compile/GENERIC cpu0: Intel Pentium III (GenuineIntel 686-class) 844 MHz real mem = 2138677248 (2088552K) avail mem = 1945370624 (1899776K) 5 queries per second ain't that much. At work we use to have 40 queries/second on a dual xeon 3,2 box running Debian Linux (2.4.31). However, I guess I can't compare that at all. Would be like comparing apples to oranges. It all winds down to your database design... If I recall the original question properly, it wasn't what system will run out or capacity first, or crash first, etc. It was if MySQL run well on OpenBSD and can handle good traffic. The answer is yes. Then the question was to get stats and DMESG, so I took the less busy and smaller system to show that even that can do well. Yes, 5 to 6 query per seconds is not that much, but how many actually even reach that. Plus, I am not running dual processor and at 3.2GHz, but run this on an old Pentium III at 850MHz. So that was a good example to show that OpenBSD sure can handle good traffic even on old equipment and just like I said, even on that slow speed and old hardware by today standard with only one processor, the load is very small. Quote: # uptime 3:53PM up 9 days, 10:34, 1 user, load averages: 0.21, 0.15, 0.14 So, in all you have a dual processor at 3.2 GHz doing 40 query per seconds, 4 times faster then this old one, with dual processor on top of that, so a minimum or 8 times I guess and then you run a dual XEON that is suppose to be better and faster with more optimization as well, so I sure hope you can do a minimum of 40 queries per seconds with it! But again that answer the question very well. Old slow hardware run very stable and very well on load that would be good for most and sure can do lots more and all this under a load that doesn't even break the system to sweat, just like I said before. In the end, run what you fell comfortable with, but to the original question, is MySQL run good on OpenBSD. The answer to that is YES! But nice to see statistics at all. I'd prefer to run OpenBSD or FreeBSD on our database servers anyway, but if you're searching the FreeBSD mail archives, Linux is still ahead in regards to speed with MySQL... Again, I don't think that was the question anyway. May be instead of speculating, it would be nice one day to have someone push each system to the limit and see witch crash, or doesn't keep up, but even that, wasn't the question. To cut that short: I'd use Linux for MySQL if it is all about speed and not security. If performance ain't the first goal, go with OpenBSD (or FreeBSD) :) Again use what you like and see fit, but know for me, I sure wouldn't run my database on Linux. My data is to important for me to risk it and yes security is also very important in the picture and I get the benefit of having a system that is very stable as well. Do, don't recall the question to be, what system will crash first and can handle the biggest load, but is it running good on OpenBSD and to that question the answer is again YES! Hope this answer the original question. Daniel
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Daniel, Daniel Ouellet wrote: In the end, run what you fell comfortable with, but to the original question, is MySQL run good on OpenBSD. The answer to that is YES! ACK :) sorry, I was just out for some statistics. Did some not serious benchmarking myself with MySQL + FreeBSD and MySQL + Linux. Had no time yet to do it with OpenBSD, though... But nice to see statistics at all. I'd prefer to run OpenBSD or FreeBSD on our database servers anyway, but if you're searching the FreeBSD mail archives, Linux is still ahead in regards to speed with MySQL... Again, I don't think that was the question anyway. May be instead of speculating, it would be nice one day to have someone push each system to the limit and see witch crash, or doesn't keep up, but even that, wasn't the question. You are indeed right... but as usual on mailing list, one can fade away from the original question. Sorry if it was offending... To cut that short: I'd use Linux for MySQL if it is all about speed and not security. If performance ain't the first goal, go with OpenBSD (or FreeBSD) :) Again use what you like and see fit, but know for me, I sure wouldn't run my database on Linux. My data is to important for me to risk it and yes security is also very important in the picture and I get the benefit of having a system that is very stable as well. hehe, and you are right again. I'd love to have the time to give OpenBSD a chance on our production system. Seems unlikely, since we're running Linux only :( Hope this answer the original question. it did. ./Marian iD8DBQFEoG6qgAq87Uq5FMsRAtrgAJ9rQCGw9hp173BId2qqTxdk/3orGgCeJZW+ Yu75Q0w19EcUpmiYZflNYwo= =HtDJ -END PGP SIGNATURE-
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Marian Hettwer wrote: I'd love to have the time to give OpenBSD a chance on our production system. Seems unlikely, since we're running Linux only :( Time, well a coffee break, that's all you need. See setting up OpenBSD in 5 minutes from scratch, even here with pause in the process too: http://osvids.com/files/page3-110-pop.html Then, you do: export PKG_PATH=ftp://ftp.openbsd.org/pub/OpenBSD/3.9/packages/`machine -a`/ Then pkg_add mysql-server If you have multiple choice, you can do pkg_add -i mysql-server and select 5.0.18 on AMD64, or even 5.0.22 on i386, or what ever else you have the choice for on your platform. Then follow the instruction to change your password at startup and just run MySQL /usr/local/share/mysql/mysql.server start And have fun. I bet you, from start to finish you are done before your coffee break is over. Just try it and you will see. By the way, the video is just an example, doesn't mean you should setup yours like that, but that give you an idea how quick this is! Really, a coffee break I tell you. That's all you need. The compare this with Fedora setup time on your Linux for fun. (: Daniel
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
thus Marian Hettwer spake: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Daniel Ouellet wrote: mysql status; -- 44 Open tables: 455 Queries per second avg: 5.117 -- # dmesg OpenBSD 3.9 (GENERIC) #617: Thu Mar 2 02:26:48 MST 2006 [EMAIL PROTECTED]:/usr/src/sys/arch/i386/compile/GENERIC cpu0: Intel Pentium III (GenuineIntel 686-class) 844 MHz real mem = 2138677248 (2088552K) avail mem = 1945370624 (1899776K) 5 queries per second ain't that much. At work we use to have 40 queries/second on a dual xeon 3,2 box running Debian Linux (2.4.31). However, I guess I can't compare that at all. Would be like comparing apples to oranges. It all winds down to your database design... But nice to see statistics at all. I'd prefer to run OpenBSD or FreeBSD on our database servers anyway, but if you're searching the FreeBSD mail archives, Linux is still ahead in regards to speed with MySQL... To cut that short: I'd use Linux for MySQL if it is all about speed and not security. If performance ain't the first goal, go with OpenBSD (or FreeBSD) :) unluckily, I don't have a choice at work and it'll be Linux :-( ./Marian wrt to your Linux vs. OpenBSD/FreeBSD stanza... i have a bunch of machines running NetBSD. i had to move a customer to another machine because the contract was at an end and the guy they moved to has absolutely no knowledge of computing. they also run a huge MySQL database and i took the chance and did a bit benchmarking. the results were surprising to me: NetBSD was a bit faster overall (than GNU/Linux, Fedora Core 5) on the same hardware! surprising because IMHO scheduling on FreeBSD is better, especially on MP machines (IIRC, NetBSD handles all IRQs on CPU0, but i am not sure). so it may be that FreeBSD is even faster. maybe worth a try... -- Timo Schoeler | http://riscworks.net/~tis | [EMAIL PROTECTED] RISCworks -- Perfection is a powerful message ISP | POWER PowerPC afficinados | Networking, Security, BSD services GPG Key fingerprint = B5F6 68A4 EC45 C309 6770 38C4 50E8 2740 9E0C F20A There are 10 types of people in the world. Those who understand binary and those who don't.
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Daniel Ouellet wrote: mysql status; -- 44 Open tables: 455 Queries per second avg: 5.117 -- # dmesg OpenBSD 3.9 (GENERIC) #617: Thu Mar 2 02:26:48 MST 2006 [EMAIL PROTECTED]:/usr/src/sys/arch/i386/compile/GENERIC cpu0: Intel Pentium III (GenuineIntel 686-class) 844 MHz real mem = 2138677248 (2088552K) avail mem = 1945370624 (1899776K) 5 queries per second ain't that much. At work we use to have 40 queries/second on a dual xeon 3,2 box running Debian Linux (2.4.31). However, I guess I can't compare that at all. Would be like comparing apples to oranges. It all winds down to your database design... But nice to see statistics at all. I'd prefer to run OpenBSD or FreeBSD on our database servers anyway, but if you're searching the FreeBSD mail archives, Linux is still ahead in regards to speed with MySQL... To cut that short: I'd use Linux for MySQL if it is all about speed and not security. If performance ain't the first goal, go with OpenBSD (or FreeBSD) :) unluckily, I don't have a choice at work and it'll be Linux :-( ./Marian iD8DBQFEnfcPgAq87Uq5FMsRAuR4AJ0VnyuA2TMJedcOGgpkAkCT8eZHsgCg7ZLA OIl4NptdC2sOzKCLDbY0GFo= =RtEN -END PGP SIGNATURE-
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Julian, Julian Bolivar wrote: I use MySQL 5.0.18 and OpenBSD 3.9 for AMD64 and work fine, and I used a lot of insert / hour in it, using Innodb tables. What means a lot ? Can you provide a mysqladmin status, or a show status from mysql? A dmesg would be great too :) regards, Marian iD8DBQFEm6fMgAq87Uq5FMsRAufCAJ0TvCRzyLNEuucFYX13ne7u3YwBrwCdHEiz RuWQpdhE52Ftv6f0yo7+VBE= =CcOr -END PGP SIGNATURE-
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Hi, I can't gift you exact numbers, but a lot are between 500,000 and 1,000,000 inserts by hour. Every hour I process a huge (~5GB ) text file and extract some records from it and are storage in MySQL DB. I can show you more information later. Thanks and Regards. Julian Marian Hettwer wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Julian, Julian Bolivar wrote: I use MySQL 5.0.18 and OpenBSD 3.9 for AMD64 and work fine, and I used a lot of insert / hour in it, using Innodb tables. What means a lot ? Can you provide a mysqladmin status, or a show status from mysql? A dmesg would be great too :) regards, Marian iD8DBQFEm6fMgAq87Uq5FMsRAufCAJ0TvCRzyLNEuucFYX13ne7u3YwBrwCdHEiz RuWQpdhE52Ftv6f0yo7+VBE= =CcOr -END PGP SIGNATURE-
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Marian Hettwer wrote: -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi Julian, Julian Bolivar wrote: I use MySQL 5.0.18 and OpenBSD 3.9 for AMD64 and work fine, and I used a lot of insert / hour in it, using Innodb tables. What means a lot ? Can you provide a mysqladmin status, or a show status from mysql? A dmesg would be great too :) regards, Marian Compare it to the same status sent two days ago. That's the less busy one and really don't even break to sweet at all. But it does run plenty of stuff. Hope this help you, but like it was said here before. There isn't any problem what so ever with running MySQL on OpenBSD and it is very stable as well! http://marc.theaimsgroup.com/?l=openbsd-miscm=115083417702165w=2 mysql status; -- mysql Ver 14.12 Distrib 5.0.22, for unknown-openbsd3.9 (i386) using readline 4.3 Connection id: 1174817 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Using delimiter:; Server version: 5.0.22-log Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 UNIX socket:/var/run/mysql/mysql.sock Uptime: 12 days 8 hours 22 min 53 sec Threads: 4 Questions: 5459805 Slow queries: 0 Opens: 0 Flush tables: 44 Open tables: 455 Queries per second avg: 5.117 -- # dmesg OpenBSD 3.9 (GENERIC) #617: Thu Mar 2 02:26:48 MST 2006 [EMAIL PROTECTED]:/usr/src/sys/arch/i386/compile/GENERIC cpu0: Intel Pentium III (GenuineIntel 686-class) 844 MHz cpu0: FPU,V86,DE,PSE,TSC,MSR,PAE,MCE,CX8,APIC,SEP,MTRR,PGE,MCA,CMOV,PAT,PSE36,SER,MMX,FXSR,SSE real mem = 2138677248 (2088552K) avail mem = 1945370624 (1899776K) using 4278 buffers containing 107036672 bytes (104528K) of memory mainbus0 (root) bios0 at mainbus0: AT/286+(00) BIOS, date 07/15/95, BIOS32 rev. 0 @ 0xfdb70 pcibios0 at bios0: rev 2.1 @ 0xf/0x1 pcibios0: PCI BIOS has 8 Interrupt Routing table entries pcibios0: PCI Interrupt Router at 000:07:0 (Intel 82371AB PIIX4 ISA rev 0x00) pcibios0: PCI bus #2 is the last bus bios0: ROM list: 0xc/0x9600 cpu0 at mainbus0 pci0 at mainbus0 bus 0: configuration mode 1 (no bios) pchb0 at pci0 dev 0 function 0 Intel 82440BX AGP rev 0x00 ppb0 at pci0 dev 1 function 0 Intel 82440BX AGP rev 0x00 pci1 at ppb0 bus 1 vga1 at pci1 dev 0 function 0 Chips and Technologies 69000 rev 0x64 wsdisplay0 at vga1 mux 1: console (80x25, vt100 emulation) wsdisplay0: screen 1-5 added (80x25, vt100 emulation) pcib0 at pci0 dev 7 function 0 Intel 82371AB PIIX4 ISA rev 0x02 pciide0 at pci0 dev 7 function 1 Intel 82371AB IDE rev 0x01: DMA, channel 0 wired to compatibility, channel 1 wired to compatibility wd0 at pciide0 channel 0 drive 0: QUANTUM FIREBALLP LM30 wd0: 16-sector PIO, LBA, 28629MB, 58633344 sectors wd1 at pciide0 channel 0 drive 1: QUANTUM FIREBALLP LM30 wd1: 16-sector PIO, LBA, 28629MB, 58633344 sectors wd0(pciide0:0:0): using PIO mode 4, Ultra-DMA mode 2 wd1(pciide0:0:1): using PIO mode 4, Ultra-DMA mode 2 atapiscsi0 at pciide0 channel 1 drive 1 scsibus0 at atapiscsi0: 2 targets cd0 at scsibus0 targ 0 lun 0: TEAC, CD-224E, 1.5A SCSI0 5/cdrom removable cd0(pciide0:1:1): using PIO mode 4, DMA mode 2 uhci0 at pci0 dev 7 function 2 Intel 82371AB USB rev 0x01pci_intr_map: no mapping for pin D : couldn't map interrupt piixpm0 at pci0 dev 7 function 3 Intel 82371AB Power rev 0x02: polling iic0 at piixpm0 admtemp0 at iic0 addr 0x18: max1617 admtemp1 at iic0 addr 0x1a: max1617 admtemp2 at iic0 addr 0x4c: max1617 admtemp3 at iic0 addr 0x4e: max1617 fxp0 at pci0 dev 17 function 0 Intel 8255x rev 0x08, i82559: irq 11, address 00:d0:a8:00:39:e3 inphy0 at fxp0 phy 1: i82555 10/100 PHY, rev. 4 fxp1 at pci0 dev 18 function 0 Intel 8255x rev 0x08, i82559: irq 10, address 00:d0:a8:00:39:e4 inphy1 at fxp1 phy 1: i82555 10/100 PHY, rev. 4 ppb1 at pci0 dev 20 function 0 DEC 21152 PCI-PCI rev 0x03 pci2 at ppb1 bus 2 isa0 at pcib0 isadma0 at isa0 pckbc0 at isa0 port 0x60/5 pckbd0 at pckbc0 (kbd slot) pckbc0: using irq 1 for kbd slot wskbd0 at pckbd0: console keyboard, using wsdisplay0 pcppi0 at isa0 port 0x61 midi0 at pcppi0: PC speaker spkr0 at pcppi0 npx0 at isa0 port 0xf0/16: using exception 16 biomask f3fd netmask fffd ttymask pctr: 686-class user-level performance counters enabled mtrr: Pentium Pro MTRR support dkcsum: wd0 matches BIOS drive 0x80 dkcsum: wd1 matches BIOS drive 0x81 root on wd0a rootdev=0x0 rrootdev=0x300 rawdev=0x302
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
* Anders J [EMAIL PROTECTED] [2006-06-20 21:06]: Hello List. A customer have a mailserver solution wich must be moved to a new hardware, today its is running on a old suse 9.2 but i really want to use OpenBSD if possible. It uses postfix, cyrus-imap and mysql as the backend for users domain info, contacts etc. (only 58 named users and 3 domains and several aliases) My self have experienced mixed issues with MysSQL on OpenBSD and also read and heard about performance and stability problems with MySQL on OpenBSD. So i'm a bit nervous implementing this on OpenBSD (Should i use FreeBSD instead)?. Has anyone a positive experience with 3.9+ MySQl mysql-server-5.0.21.tgz ftp://ftp.sunet.se/pub/OpenBSD/3.9/packages/i386/mysql-server-5.0.21.tgz(or later)? let's put some things straight. I haven't seen stability problems with mysql on OpenBSD in a long time. not even on sparc64. Unless you're really pushing the limits, performance is not much of a problem either. with really extreme load, our threading library shows why we wanna go for rthreads. for the vast majority of uses out there, you will not see a difference. -- BS Web Services, http://www.bsws.de/ OpenBSD-based Webhosting, Mail Services, Managed Servers, ... Unix is very simple, but it takes a genius to understand the simplicity. (Dennis Ritchie)
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Hi Brauer and Anders What is the diference between the actual threading library and rtheads? I use MySQL 5.0.18 and OpenBSD 3.9 for AMD64 and work fine, and I used a lot of insert / hour in it, using Innodb tables. Thanks and Regards, Julian Bolivar Henning Brauer wrote: * Anders J [EMAIL PROTECTED] [2006-06-20 21:06]: Hello List. A customer have a mailserver solution wich must be moved to a new hardware, today its is running on a old suse 9.2 but i really want to use OpenBSD if possible. It uses postfix, cyrus-imap and mysql as the backend for users domain info, contacts etc. (only 58 named users and 3 domains and several aliases) My self have experienced mixed issues with MysSQL on OpenBSD and also read and heard about performance and stability problems with MySQL on OpenBSD. So i'm a bit nervous implementing this on OpenBSD (Should i use FreeBSD instead)?. Has anyone a positive experience with 3.9+ MySQl mysql-server-5.0.21.tgz ftp://ftp.sunet.se/pub/OpenBSD/3.9/packages/i386/mysql-server-5.0.21.tgz(or later)? let's put some things straight. I haven't seen stability problems with mysql on OpenBSD in a long time. not even on sparc64. Unless you're really pushing the limits, performance is not much of a problem either. with really extreme load, our threading library shows why we wanna go for rthreads. for the vast majority of uses out there, you will not see a difference.
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
On 6/22/06, Julian Bolivar [EMAIL PROTECTED] wrote: Henning Brauer wrote: * Anders J [EMAIL PROTECTED] [2006-06-20 21:06]: Hello List. A customer have a mailserver solution wich must be moved to a new hardware, today its is running on a old suse 9.2 but i really want to use OpenBSD if possible. It uses postfix, cyrus-imap and mysql as the backend for users domain info, contacts etc. (only 58 named users and 3 domains and several aliases) My self have experienced mixed issues with MysSQL on OpenBSD and also read and heard about performance and stability problems with MySQL on OpenBSD. So i'm a bit nervous implementing this on OpenBSD (Should i use FreeBSD instead)?. Has anyone a positive experience with 3.9+ MySQl mysql-server-5.0.21.tgz ftp://ftp.sunet.se/pub/OpenBSD/3.9/packages/i386/mysql-server-5.0.21.tgz(or later)? let's put some things straight. I haven't seen stability problems with mysql on OpenBSD in a long time. not even on sparc64. Unless you're really pushing the limits, performance is not much of a problem either. with really extreme load, our threading library shows why we wanna go for rthreads. for the vast majority of uses out there, you will not see a difference. Hi Brauer and Anders What is the diference between the actual threading library and rtheads? I use MySQL 5.0.18 and OpenBSD 3.9 for AMD64 and work fine, and I used a lot of insert / hour in it, using Innodb tables. Thanks and Regards, Julian Bolivar rthreads is new. It makes things better. See http://www.openbsd.org/papers/eurobsd2005/tedu-rthreads.pdf -Nick
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
At 04:54 PM 6/20/06, Daniel Ouellet wrote: Bryan Irvine wrote: Works ok for me. Hasn't crashed or anything like that. I use mysql 5 on OpenBSD that some web apps talk too. I just did an import of a previous dump, and it took somewhere in the neighboorhood of 7 hours give or take. (for a few tens of million INSERTS that's not bad). This is run on a slighlty older sun 220r (450Mhz), and 10K rpm disks. Interesting. It takes me ~25 minutes for 9.5 millions records in many databases/tables. But my dump is/was done with --opt as to not create the index when you do the import, but only when all data is imported. This saves many hours if not use. Are you sure you do your dump with the --opt flag? If I don't do this, it sure will take me about 8 1/2 hours to do the same. Just a side note that might help, or it may not, but just thought to pass it along in case it help you. Actually, the option is really --disable-keys. The --opt option is just a shorthand for several options (including --disable-keys). WARNING: the man page for mysqldump says that defaults have changed in V5.
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Frank Bax wrote: Actually, the option is really --disable-keys. The --opt option is just a shorthand for several options (including --disable-keys). There is more as well and refer to the man page for all the details: http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html The --opt Doesn't only do the disable keys but the following as well: Quote This option is shorthand; it is the same as specifying --add-drop-table --add-locks --create-options --disable-keys --extended-insert --lock-tables --quick --set-charset. It should give you a fast dump operation and produce a dump file that can be reloaded into a MySQL server quickly. One very nice and quicker import is also the extended-insert, use compress if you do between two servers as well. The dump with lock will also speed up your dump and locking the table when you insert if you database is live is also a good thing, etc. Obviously you use it as you see fit and the options you want, but if you do want to get the maximum efficiency, you the --opt, not only the --disable-keys. I offer it as a suggestions, but if you want to help the users that will do this, let them use the proper feature to do this and also let them read the mysqldump man page to see what else the may see fit. The observation was on speed of import and using the --opt instead of just the --disable-keys will be more efficient, specially if you do have a lots of entry. Even more you can even speed this more by increasing the max_allowed_packet in mysql_dump as well as in the mysqld sections, or your extended-insert will stop in the import mode if your dump is much bigger then your mysqld setup and you do have many records in tables. Anyway, there is more then this, but that's not the list do talk about all of it. In any case, it would be nice if you do not provide wrong information to correct proper one. Just my $0.02 worth. The the option is really --disable-keys. will not give you the full benefit, but that's left for the reader. Your suggestion will only add problem and delay in import on a live system that may already have data on it and got corrupted data in one database or table that you need to restore quickly, or worst multiple table if the mysqlcheck can't fix it. I don't know about you, but if I restore database from dump, I hell sure want to start with empty tables and database first. So, the --opt will also add as well --add-drop-table --add-locks --create-options in your dump making your restore even more painless and quicker as well. But again, do it as you see fit. You do not have to do it the way I suggest by any mean, but don't cut it short for some users that may not have tested their restore scenario and think what they may do is good for them and when they will need it, that time, they will be stuck. Best, Daniel
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
It's good, but if it's a multiprocessor machine, OpenBSD won't take full advantage of the other processors with MySQL since MySQL is multi-threaded. But it's stable, and runs reasonably fast (and you are assured the system is more secure, being OpenBSD and all). My only concern, then, is really if it's a multiprocessor machine, and you need breakneck performance, you might want to consider running MySQL on FreeBSD with linuxthreads. -Jesse On 6/20/06, Anders J [EMAIL PROTECTED] wrote: Hello List. A customer have a mailserver solution wich must be moved to a new hardware, today its is running on a old suse 9.2 but i really want to use OpenBSD if possible. It uses postfix, cyrus-imap and mysql as the backend for users domain info, contacts etc. (only 58 named users and 3 domains and several aliases) My self have experienced mixed issues with MysSQL on OpenBSD and also read and heard about performance and stability problems with MySQL on OpenBSD. So i'm a bit nervous implementing this on OpenBSD (Should i use FreeBSD instead)?. Has anyone a positive experience with 3.9+ MySQl mysql-server-5.0.21.tgz ftp://ftp.sunet.se/pub/OpenBSD/3.9/packages/i386/mysql-server-5.0.21.tgz(or later)? And by the way it is not an option to use postgresql since they also have several other apps which use the database in question .So migrate to an other db platform is NOT an option. Regards Anders
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Anders J wrote: My self have experienced mixed issues with MysSQL on OpenBSD and also read and heard about performance and stability problems with MySQL on OpenBSD. I use it for years (7+) without issues. The only one I recall was with 3.23.46, yeap, really old, where the database restart itself running out of resources then, but that's really old stuff. I mean really old and that was on OpenBSD 3.0 then. The only issue always been configurations issues, like limits on tables, login.conf stuff, and the like. So i'm a bit nervous implementing this on OpenBSD (Should i use FreeBSD instead)?. Use what you are comfortable with, but OpenBSD sure can do the job just fine. Has anyone a positive experience with 3.9+ MySQl mysql-server-5.0.21.tgz ftp://ftp.sunet.se/pub/OpenBSD/3.9/packages/i386/mysql-server-5.0.21.tgz(or later)? As stated above. No problem here and with plenty of mirror as well. Just upgraded to 5.0.22 a few days ago (see below for some stats) in less then a minute using pkg_add -u as well and restart MySQL. Really easy to manage I tell you. I even have PF running on that box as well obviously to protect it and the box is really efficient in resources I tell you. Nothing wrong with it, use it as you see fit. # uptime 3:53PM up 9 days, 10:34, 1 user, load averages: 0.21, 0.15, 0.14 # ps -auxw | grep mysql _mysql1794 4.2 17.6 441240 367056 ?? S 11Jun06 539:55.12 /usr/local/libexec/mysqld --basedir=/usr/local --datadir=/var/mysql --user=_mysql --pid-file=/var/mysql/---.com.pid - mysql status; -- mysql Ver 14.12 Distrib 5.0.22, for unknown-openbsd3.9 (i386) using readline 4.3 Connection id: 879766 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Using delimiter:; Server version: 5.0.22-log Protocol version: 10 Connection: Localhost via UNIX socket Server characterset:latin1 Db characterset:latin1 Client characterset:latin1 Conn. characterset:latin1 UNIX socket:/var/run/mysql/mysql.sock Uptime: 9 days 10 hours 34 min 58 sec Threads: 6 Questions: 4092255 Slow queries: 0 Opens: 0 Flush tables: 1 Open tables: 540 Queries per second avg: 5.021 --
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
On Tue, Jun 20, 2006 at 08:56:36PM +0200, Anders J wrote: Hello List. A customer have a mailserver solution wich must be moved to a new hardware, today its is running on a old suse 9.2 but i really want to use OpenBSD if possible. It uses postfix, cyrus-imap and mysql as the backend for users domain info, contacts etc. (only 58 named users and 3 domains and several aliases) My self have experienced mixed issues with MysSQL on OpenBSD and also read and heard about performance and stability problems with MySQL on OpenBSD. So i'm a bit nervous implementing this on OpenBSD (Should i use FreeBSD instead)?. Has anyone a positive experience with 3.9+ MySQl mysql-server-5.0.21.tgz ftp://ftp.sunet.se/pub/OpenBSD/3.9/packages/i386/mysql-server-5.0.21.tgz(or later)? I've run postfix on OpenBSD using MySQL as the database for several years with +400 domains and +4k users; handling about 300K messages per day. Never gives me any problems. I had to bump the kern.maxproc and kern.maxfiles sysctl (man 8 sysctl) and add the following to /etc/my.cnf [mysqld_safe] open-files=2000 You may also want to alter the appropriate parts of /etc/login.conf (maxproc + openfiles) (man 5 login.conf) Hope it serves you as well as it has me. And by the way it is not an option to use postgresql since they also have several other apps which use the database in question .So migrate to an other db platform is NOT an option. Regards Anders
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Works ok for me. Hasn't crashed or anything like that. I use mysql 5 on OpenBSD that some web apps talk too. I just did an import of a previous dump, and it took somewhere in the neighboorhood of 7 hours give or take. (for a few tens of million INSERTS that's not bad). This is run on a slighlty older sun 220r (450Mhz), and 10K rpm disks. --Bryan On 6/20/06, Anders J [EMAIL PROTECTED] wrote: Hello List. A customer have a mailserver solution wich must be moved to a new hardware, today its is running on a old suse 9.2 but i really want to use OpenBSD if possible. It uses postfix, cyrus-imap and mysql as the backend for users domain info, contacts etc. (only 58 named users and 3 domains and several aliases) My self have experienced mixed issues with MysSQL on OpenBSD and also read and heard about performance and stability problems with MySQL on OpenBSD. So i'm a bit nervous implementing this on OpenBSD (Should i use FreeBSD instead)?. Has anyone a positive experience with 3.9+ MySQl mysql-server-5.0.21.tgz ftp://ftp.sunet.se/pub/OpenBSD/3.9/packages/i386/mysql-server-5.0.21.tgz (or later)? And by the way it is not an option to use postgresql since they also have several other apps which use the database in question .So migrate to an other db platform is NOT an option. Regards Anders
Re: Opinion of MySQL 5.xx on OpenBSD 3.9...
Bryan Irvine wrote: Works ok for me. Hasn't crashed or anything like that. I use mysql 5 on OpenBSD that some web apps talk too. I just did an import of a previous dump, and it took somewhere in the neighboorhood of 7 hours give or take. (for a few tens of million INSERTS that's not bad). This is run on a slighlty older sun 220r (450Mhz), and 10K rpm disks. Interesting. It takes me ~25 minutes for 9.5 millions records in many databases/tables. But my dump is/was done with --opt as to not create the index when you do the import, but only when all data is imported. This saves many hours if not use. Are you sure you do your dump with the --opt flag? If I don't do this, it sure will take me about 8 1/2 hours to do the same. Just a side note that might help, or it may not, but just thought to pass it along in case it help you. Daniel
