building mysql 5.5.8 with default charset
Hello, is it a bug or a feature that if building mysql 5.5.8 from source with the options 'cmake . -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci' the charset in include/config.h remains latin1: #define MYSQL_DEFAULT_CHARSET_NAME latin1 #define MYSQL_DEFAULT_COLLATION_NAME latin1_swedish_ci Probably there are some other places as well. As a result, if php is built against this mysql installation, it assumes the default charset to be latin1, so that many old php applications require adding the explicit connection settings (set names utf8) to work correctly. The amazing thing is that in the 5.1 branch it works the other way - replaces everywhere to utf8, so 'set names utf8' is not needed. Is there any way to make it so in 5.5.8? Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)718-3322, 718-3115(fax) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: default connection charset - SOLVED
Hi, Luckily I already found the solution, may be it'll be usefull for someone else. All that was needed was adding the following line to my.cnf file: [mysqld] init-connect=SET NAMES koi8r Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)718-3322, 718-3115(fax) On Wed, 29 Jun 2005, Gleb Paharenko wrote: Privet. As I know, PHP usually takes the character set from the server. Please send part of your configuration file related to character sets. Varshavchick Alexander [EMAIL PROTECTED] wrote: Hi, I have mysql 4.1.12 started with the default charset 'koi8r' and I have the following problem: when connecting from PHP, the default results and connection charsets become latin1, so all not-latin characters get lost (they are displayed as ?). Of cause, if I explicitely set these charsets to koi8r in the PHP scripts after the mysql_connect statement, it starts working correctly, but the problem is, there are so many php scripts calling mysql_connect function that I cannot change them all. I tried adding the default-character-set options into my.cnf in the [mysql] section and it workso for mysql command line but not for the php. So is there any way of setting the default connection charset which will work from php? Or the only way is downgrading to mysql 4.0 to make the existing php script work? Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)718-3322, 718-3115(fax) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
default connection charset
Hi, I have mysql 4.1.12 started with the default charset 'koi8r' and I have the following problem: when connecting from PHP, the default results and connection charsets become latin1, so all not-latin characters get lost (they are displayed as ?). Of cause, if I explicitely set these charsets to koi8r in the PHP scripts after the mysql_connect statement, it starts working correctly, but the problem is, there are so many php scripts calling mysql_connect function that I cannot change them all. I tried adding the default-character-set options into my.cnf in the [mysql] section and it workso for mysql command line but not for the php. So is there any way of setting the default connection charset which will work from php? Or the only way is downgrading to mysql 4.0 to make the existing php script work? Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)718-3322, 718-3115(fax) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
speed of 4.0.20 vs. 4.1
Hello, did anybody make any tests to check the speed of 4.1 version comparing with 4.0.*? Does the peformance of 4.1 depend in which client libraries are used - 4.0 or 4.1? As the docs say, the new client/server protocol with support for prepared statements has to be faster, and it would be usefull to learn for how much... Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
4.0.18 repeatedly restarts
Hello, After upgrating mysql 4.0.7 to 4.0.18/FreeBSD 4.6.2, the mysqld restarts each time it receives a remote connect from another client machine. Here is what is written into the log: 040415 08:51:00 mysqld started 040415 8:51:00 Warning: setrlimit returned ok, but didn't change limits. Max open files is 22154 (request: 25010) 040415 8:51:00 Warning: Changed limits: max_connections: 5000 table_cache: 8572 040415 8:51:01 InnoDB: Started /usr/local/mysql40/libexec/mysqld: ready for connections. Version: '4.0.18-log' socket: '/tmp/mysql4.sock' port: 3310 Number of processes running now: 0 040415 08:51:15 mysqld restarted 040415 8:51:15 Warning: setrlimit returned ok, but didn't change limits. Max open files is 22154 (request: 25010) What the client gets is the error ERROR 2013: Lost connection to MySQL server during query Connections from the same server, both using unix socket or tcp port, get through without any problems. The mysql 4.0.18 was compiled from ports with the linuxthreads option. 4.0.7 is working fine on the same machine. Any help will be greatly apprecialted. Thanks Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
create threads rate
Hi, I have a 4.0.7 mysql server which services requests from several virtual web sites. Most of the times all works well but periodically the mysql server suddently gets loaded to impossibly high values - load average up to several hundred. During such periods the server sleeps and is not responding. I suspect that the cause of this can be in a spontaneous very high rate of new connections when many new threads are being created and running, and it is too much for the server. The question is if there is some mechanism in mysql which can control the rate in which the new threads are created, something similar to MAX_SPAWN_RATE option in apache web server daemon, or something of the sort? Server mysql 4.0.7 with linux threads runs under FreeBSD 4.6.2. Thanks Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB or OS restriction?
Hi Heikki, here is a snip from the original posting which you probably have overlooked: The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Mon, 20 Oct 2003, Heikki Tuuri wrote: Date: Mon, 20 Oct 2003 22:05:07 +0300 From: Heikki Tuuri [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: InnoDB or OS restriction? Alex, in FreeBSD user process memory space is often restricted to 512 MB. You have to reconfigure or recompile the FreeBSD kernel to increase that limit. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB: now also backs up your MyISAM tables - Original Message - From: alex [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Monday, October 20, 2003 1:30 PM Subject: Re: InnoDB or OS restriction? Hi again, as there was not a single answer to my question I can imagine that no one encountered the same issue, but anyways, can there be any hints? First of all, are there any means of looking at mysql memory allocation list grouped by some major parts - for example, innodb main pool - can be retrieved from innodb monitor innodb additional pool - can be retrieved from innodb monitor myisam main cache - ...? myisam sort buffer - ...? memory allocated from OS - ...? The questiion is why mysql is trying to allocate memory via malloc from OS while the innodb additional pool is occupied only by 50%? Or is it myisam buffer that mysql is trying to extend? Regards --- Alex On Tue, 14 Oct 2003, alex wrote: Hi people, I have mysql 4.0.7-gamma/linuxthreads running under FreeBSD 4.6.2 (server has 4G phisical memory), and occasionally mysql traps with the message: InnoDB: Fatal error: cannot allocate 1064960 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 513951016 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! The first strange thing is that MAXDSIZ and DFLDSIZ in kernel config are setted to 1536M, and the second one is that each time it happens, while the number of bytes reported to cannot be allocated is different, total memory allocated by InnoDB is exactly the same - 513951016 bytes. Which restrictions are the cause of this - InnoDB's or FreeBSD's? Thanks in advance Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
natural left join
Hello, If it's a documented feature which I've missed - please excuse, but after upgrading from 4.0.2 to 4.0.7 the following sql statement started to behave differently then before: select Sections.ReportName, Creatives.Name, from HourlyStats natural left join Sections left join Creatives on HourlyStats.CreativeID=Creatives.CreativeID where HourlyStats.CreativeID=185; Instead of returning only rows existing in BOTH tables, it now returns rows which exist at least in one table. To make this statement working correctly, the brackets have to be inserted: select Sections.ReportName, Creatives.Name, from (HourlyStats natural left join Sections) left join Creatives on HourlyStats.CreativeID=Creatives.CreativeID where HourlyStats.CreativeID=185; Are the brackets here required by sql standart and was it a bug in the 4.0.2 which permitted working correctly even without them? Or how can it be explained? Thanks Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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: Problems with INNODB in MySQL 4.0.6-gamma
On Tue, 4 Feb 2003, Kees Hoekzema wrote: I had the same problem, it went on until I had 44G of InnoDB space, with only 4G free. After getting tired of having to increase the number of files every week, I decided to dump all data with mysqldump, remove the files+ logs and recreate them. After that there was more than 24G free, and the database was a bit faster too :) (it took me more than 12 hours to dump restore, but it was worth the effort). In your case I think you should do the same only if you are running out of space every week or something. I too noticed that innodb won't give up it space, so this was the only solution that came to my mind, maybe there is a better way, but i haven't seen any tools to defragment an innodb database. What if making this procedure one database or table at a time, will it bring the effect, or does the _whole_ database file need to be recreated? Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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
natural left join
Hello, If it's a documented feature which I've missed - please excuse, but after upgrading from 4.0.2 to 4.0.7 the following sql statement started to behave differently then before: select Sections.ReportName, Creatives.Name, from HourlyStats natural left join Sections left join Creatives on HourlyStats.CreativeID=Creatives.CreativeID where HourlyStats.CreativeID=185; Instead of returning only rows existing in BOTH tables, it now returns rows which exist at least in one table. To make this statement working correctly, the brackets have to be inserted: select Sections.ReportName, Creatives.Name, from (HourlyStats natural left join Sections) left join Creatives on HourlyStats.CreativeID=Creatives.CreativeID where HourlyStats.CreativeID=185; Are the brackets here required by sql standart and was it a bug in the 4.0.2 which permitted working correctly even without them? Or how can it be explained? Thanks mysql, query Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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
natural left join
Hello, If it's a documented feature which I've missed - please excuse, but after upgrading from 4.0.2 to 4.0.7 the following sql statement started to behave differently then before: select Sections.ReportName, Creatives.Name, from HourlyStats natural left join Sections left join Creatives on HourlyStats.CreativeID=Creatives.CreativeID where HourlyStats.CreativeID=185; Instead of returning only rows existing in BOTH tables, it now returns rows existing at least in one table. To make this statement to work correctly, the brackets have to be inserted: select Sections.ReportName, Creatives.Name, from (HourlyStats natural left join Sections) left join Creatives on HourlyStats.CreativeID=Creatives.CreativeID where HourlyStats.CreativeID=185; Are the brackets here required by sql standart and was it a bug in the 4.0.2 which permitted working correctly even without them? Or how can it be explained? Thanks Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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: Using more than one CPU on FreeBSD?
Did you try installing mysql4 also from ports? I didn't try 4.0.9 but 4.0.4 builded nicely this way for me. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Wed, 15 Jan 2003, Tommy F. Eriksen wrote: Date: Wed, 15 Jan 2003 09:23:18 +0100 From: Tommy F. Eriksen [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Using more than one CPU on FreeBSD? Okay. By the way, sorry about sending you the reply directly before. However, I've run into a new problem new. Tried to compile mysql-4.0.9 with linuxthreads-2.2.3_9 (the most recent from ports). Linuxthreads was installed from ports - and for MySQL I used the following configure-string: CFLAGS=-D__USE_UNIX98 -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads \ -felide-constructors -fno-rtti -fno-exceptions \ ./configure --localstatedir=/var/db/mysql --without-perl --without-debug \ --without-readline --without-bench --with-mit-threads=no --with-libwrap \ --program-prefix= --with-innodb --enable-assembler --prefix=/usr/local \ --with-named-thread-libs=-L/usr/local/lib -llthread -llgcc_r When doing this, it configures and builds nicely, but when I try to start the mysql-server, nothing much happens: -bash-2.05b# /usr/local/share/mysql/mysql.server start -bash-2.05b# Starting mysqld daemon with databases from /var/db/mysql 030115 08:20:35 mysqld ended -And in the hostname.err-file I get: 030115 08:20:35 mysqld started InnoDB: Error: trx_t size is 396 in ha_innodb.cc but 416 in srv0start.c InnoDB: Check that pthread_mutex_t is defined in the same way in these InnoDB: compilation modules. Cannot continue. 030115 8:20:35 Can't init databases 030115 08:20:35 mysqld ended Any ideas? :) Thanks, Tommy -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 15, 2003 8:51 AM To: Tommy F. Eriksen Subject: RE: Using more than one CPU on FreeBSD? Yes, linuxthreads work superbly with mysql4, which in conjunction with the query cache improves performance quite a lot. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Wed, 15 Jan 2003, Tommy F. Eriksen wrote: Date: Wed, 15 Jan 2003 08:47:05 +0100 From: Tommy F. Eriksen [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED] Subject: RE: Using more than one CPU on FreeBSD? Hi, First of all, we can presume you're using SMP kernel. Then I'd suggest using Linuxthreads despite your dislike for them - by my experience, they work far better for mysql/FreeBSD than native threads. May be you try it again - compile mysql from the ports (WITH_LINUXTHREADS=yes), and it should solve it. Okay, it's been a while since I last tested them, so my info seems to be outdated ;) Do they work as well with mysql4? (We're currently using mysql4 and are quite fond of the query cache and the likes). Thanks, /Tommy - 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 - 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: Using more than one CPU on FreeBSD?
Hi, First of all, we can presume you're using SMP kernel. Then I'd suggest using Linuxthreads despite your dislike for them - by my experience, they work far better for mysql/FreeBSD than native threads. May be you try it again - compile mysql from the ports (WITH_LINUXTHREADS=yes), and it should solve it. Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Fri, 10 Jan 2003, Tommy F. Eriksen wrote: Date: Fri, 10 Jan 2003 09:14:18 +0100 From: Tommy F. Eriksen [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Using more than one CPU on FreeBSD? Hi, I've inherited a FreeBSD/MySQL database-server (Compaq DL360, dual P3 1GHz), running (at the moment): Server version: 4.0.3-beta However, as far as I can tell, MySQL/FreeBSD 4.6.2-RELEASE still can't agree on utilizing more than one CPU. My question is this: A year or two ago, someone mentioned simply running two mysqld's on the same database-files (using file-locking) and then, using some form for loadbalancing between the two, was able to use more than one CPU for the mysqlds. Is this still the recommended way of doing this? I know I could use Linuxthreads, but the times I've tried them in the past, they have done more harm than good (low performance, unstability etc). Any advice would be appreciated :) Kind regards, Tommy Eriksen - 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 - 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
Problem with 4.0.4
Hi people, I have a problem running mysql 4.0.4 using mostly innodb tables: mysql starts without any problems, then after about a half-hour it returns queries with errors that databases are not found. And indeed, show databases command shows the single database with a name vi.recover. The system is FreeBSD 4.6.2, mysql was compiled from ports. If I use 4.0.2 (the version is the only change, all else is exactly the same), the problem dissapears completely. What can it be? Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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
3.23.55
Hi people, what is the status of version 3.23.55? It's already available for download from www.mysql.com, but the links on the official download page all points to 3.23.54a... regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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
Can't create a new thread error
Hi people, Why such a message appears at random times when connecting to mysql: ERROR 1135: Can't create a new thread (errno 35). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug The system is FreeBSD 4.5 with mysql 4.0.2, and there is a lot of available memory. May be mysql is not configured optimally, if so which options should I try tweaking in my.cnf? Any other suggestions? Thanks Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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: Can't create a new thread error
Hi Simon, here are the current values (from 'show variables'): max_connections: 5000 max_user_connections: 0 And looking at 'show extended-status': Max_used_connections: 945 Concerning freebsd threads mechanism, I'm using linux threads, and the maximum number of existing thread processes went even higher during the peak load times. So something else went wrong... Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Wed, 2 Oct 2002, Simon Green wrote: Date: Wed, 2 Oct 2002 10:45:07 +0100 From: Simon Green [EMAIL PROTECTED] To: 'Varshavchick Alexander' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: Can't create a new thread error Sets number of connectons per user/host set-variable= max_user_connections = 100 Sets total number of connections. set-variable= max_connections = 500 Also see number of threads for FreeBSD at www.FreeBSD.org Also in MySQL log on and do a show processlist and see how many connocetons you have. Simon -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: 02 October 2002 09:31 To: [EMAIL PROTECTED] Subject: Can't create a new thread error Hi people, Why such a message appears at random times when connecting to mysql: ERROR 1135: Can't create a new thread (errno 35). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug The system is FreeBSD 4.5 with mysql 4.0.2, and there is a lot of available memory. May be mysql is not configured optimally, if so which options should I try tweaking in my.cnf? Any other suggestions? Thanks Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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 - 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 - 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: 3.23.52 hangs sometimes
Hi Andrew, Did you try switching into InnoDB tables, or which table type are you using? Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 24 Sep 2002, Andrew Maltsev wrote: Date: Tue, 24 Sep 2002 20:21:36 -0700 From: Andrew Maltsev [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: 3.23.52 hangs sometimes I know it is a bad problem description, but that's all I can tell right now -- 3.23.52 sometimes goes into deadlock under heavy load. Heavy load means approximately 400 queries/second, for extended periods of time. This is a web application backend. There is nothing special about the database, except may be the number of indexes on one table -- it currently has 26 indexes on different integer fields. Biggest table in terms of number of rows is about 100 thousands rows, and one smaller table has blob field that holds data chunks up to 100k each. Any suggestions how to approach the problem? How and what to test? It happens randomly, can work for a day or two with no problems and then hang three times in one hour. And obviously I can't reproduce it in my test environment however hard I stress test it. The system is Linux 2.4.18-3, stock redhat 7.3 on 1.1GHz Athlon with 1 Gb RAM. MySQL is out of RPM packages downloaded from mysql.com Thanx. - 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 - 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 datafile usage map
Hi people, Is there any way I can see how much space each database is using from the innodb data file? Something showing how the space is divided among the databases. Thanks. sql,query Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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: Performance Problems with InnoDB Row Level Locking...
Hi Heikki, one more question please about innodb_flush_log_at_trx_commit: if there was some way of increasing the delay between log flushes more than 1 sec, can you estimate will it bring any real effect in performance? I know it'll raise the risk of losing some last transactions if something crashes, but we can go for it gaining the speed. How can it be done if it's worth doing? Thanks sql, query Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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: Performance Problems with InnoDB Row Level Locking...
Heikki, thank you for the answer. So on the systems other than Linux or Solaris the best flush method should be fdatasync, is it correct? In this case, if I don't specify innodb_flush_method option, fdatasync will not be used - it'll be fsync be default instead? My system is FreeBSD, so which value for innodb_flush_method can be optimal? Thanks Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Fri, 6 Sep 2002, Heikki Tuuri wrote: Date: Fri, 6 Sep 2002 10:27:03 +0300 From: Heikki Tuuri [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Performance Problems with InnoDB Row Level Locking... Alexander, - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: 'Heikki Tuuri' [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, September 06, 2002 10:08 AM Subject: RE: Performance Problems with InnoDB Row Level Locking... Hi Heikki, one more question please about innodb_flush_log_at_trx_commit: if there was some way of increasing the delay between log flushes more than 1 sec, can you estimate will it bring any real effect in performance? I know it'll raise the risk of losing some last transactions if something crashes, but we can go for it gaining the speed. How can it be done if it's worth doing? it should not be worth doing. A disk can do some 70 random writes per second, and the log flush (calling fsync on the log file) typically uses 2 disk writes: (1) writing the end of the log to the log file on disk, and (2) updating the file access timestamps in the 'inode' of the file, if we are using a Unix file system. Thus the performance benefit of less than 1 log flush per second is small. On the other hand, we might add an option which allows flushing the log 1 - 50 times per second. Note that the file flush method fdatasync is supposed to eliminate the write (2) above. Unfortunately there was evidence fadatasync caused file corruption in Linux and Solaris, and it is currently mapped to the ordinary fsync. Thanks sql, query Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, hot backup, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.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 - 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: Performance Problems with InnoDB Row Level Locking...
Heikki, one little question - is it a mistype, or can a flush log interval duration be controlled by this option? The value should only be 0 or 1 as the documentation says... On Thu, 5 Sep 2002, Heikki Tuuri wrote: You can try setting innodb_flush_log_at_trx_commit=2 if you can afford losing some last transactions in a power outage or an operating system crash. sql,query Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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 too slow
Hi, why innodb queries work MUCH slower (100 times) than if the table was of myisam type? It's mysql 4.0.3 on FreeBSD 4.5 server. The innodb monitor outputs many messages like the following, why are they there and what do they mean? Purge done for trx's n:o 0 782 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 3811, ACTIVE 0 sec, OS thread id 10250 fetching rows MySQL thread id 2, query id 3044 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3812, sees 0 3807 ---TRANSACTION 0 3810, ACTIVE 1 sec, OS thread id 12300 fetching rows MySQL thread id 4, query id 3043 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3811, sees 0 3806 ---TRANSACTION 0 3809, ACTIVE 1 sec, OS thread id 13325 fetching rows MySQL thread id 5, query id 3042 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3811, sees 0 3806 ---TRANSACTION 0 3808, ACTIVE 1 sec, OS thread id 14350 fetching rows MySQL thread id 6, query id 3041 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3809, sees 0 3804 ---TRANSACTION 0 3807, ACTIVE 1 sec, OS thread id 11275 fetching rows MySQL thread id 3, query id 3040 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3808, sees 0 3803 Any help will be appreciated, thanks! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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: Innodb too slow
Thank you Ken for the answer, but here the cause was different from what you say. count() works fast enough for innodb as well, expecially if the query cache feature is turned on. In my case, I used a mysql 4.0.3 compiled from ports, which had a debug option turned on, and it appeared to be critical for performance which dropped more then by 100 times because of it! As I was going further, 4.0.3 version went out of control after about 10 minutes of being uptime - it suddenly just silently refused showing all configured databases :( So I'm now installing 4.0.2, hope it'll be behaving better. Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 3 Sep 2002, Ken Menzel wrote: Date: Tue, 3 Sep 2002 10:34:06 -0400 From: Ken Menzel [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: Innodb too slow Hi Alexander, I believe that the the 'count()' function works differently under INNODB type tables. With MyISAM count(*) is stored in a 'table status' area, but INNODB must scan the tables and count the rows (very slow). All other types of queries should perform much better for you, but 'count(*)' is not impelemented the same way. Hope this helps, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, September 03, 2002 3:56 AM Subject: Innodb too slow Hi, why innodb queries work MUCH slower (100 times) than if the table was of myisam type? It's mysql 4.0.3 on FreeBSD 4.5 server. The innodb monitor outputs many messages like the following, why are they there and what do they mean? Purge done for trx's n:o 0 782 undo n:o 0 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 3811, ACTIVE 0 sec, OS thread id 10250 fetching rows MySQL thread id 2, query id 3044 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3812, sees 0 3807 ---TRANSACTION 0 3810, ACTIVE 1 sec, OS thread id 12300 fetching rows MySQL thread id 4, query id 3043 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3811, sees 0 3806 ---TRANSACTION 0 3809, ACTIVE 1 sec, OS thread id 13325 fetching rows MySQL thread id 5, query id 3042 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3811, sees 0 3806 ---TRANSACTION 0 3808, ACTIVE 1 sec, OS thread id 14350 fetching rows MySQL thread id 6, query id 3041 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3809, sees 0 3804 ---TRANSACTION 0 3807, ACTIVE 1 sec, OS thread id 11275 fetching rows MySQL thread id 3, query id 3040 localhost handy Sending data SELECT count(*) from stat Trx read view will not see trx with id = 0 3808, sees 0 3803 Any help will be appreciated, thanks! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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 - 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: upgrade 3.23 - 4.0.3 question
I'm using 3.23.49 and it behaves perfectly. Okay I'll try 4.0.2 and see if it helps, thanks for the hint. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Fri, 30 Aug 2002, Steven Roussey wrote: Date: Fri, 30 Aug 2002 10:49:28 -0700 From: Steven Roussey [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Cc: 'Varshavchick Alexander' [EMAIL PROTECTED] Subject: Re: upgrade 3.23 - 4.0.3 question - The real cause for the question lays in the problem which occured after I upgraded to 4.0.3 WITHOUT recompiling DBI module: the new mysql version worked without any visible errors, however it was unable to process more than about 30 queries per seconds, while the normal rate for this server is more than 100 queries/sec. How can it be so slow? Evidently something went wrong, but what could it be? - Would you be willing to try using 4.0.2 instead of 4.0.3? If you do not have the problem with 4.0.2, then the issue lies in something in 4.0.3. At least I have a problem with 4.0.3 in that it works fine for a few queries, but when I let it go at a normal 3000 queries per second it choked up and died (much like 3.23.51 -- by the way, which 3.23.x are you using now?). Thanks! Sincerely, Steven Roussey http://Network54.com/?pp=e - 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: upgrade 3.23 - 4.0.3 question
No, switching to 4.0.2 didn't help either, and here is an additional clue which may help: all mysql threads on the 4.0.* are in the sending data state, while for the 3.23 the state is changing all the time to writing to the net, connecting and others. It seems as though the 4.0 version has problems in communicating with DBI module which was left unchanged. So again we're returning to the questuin should DBI be reinstalled or no? regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Mon, 2 Sep 2002, Varshavchick Alexander wrote: Date: Mon, 2 Sep 2002 11:02:54 +0400 (MSD) From: Varshavchick Alexander [EMAIL PROTECTED] To: Steven Roussey [EMAIL PROTECTED] Cc: Mysql [EMAIL PROTECTED] Subject: Re: upgrade 3.23 - 4.0.3 question I'm using 3.23.49 and it behaves perfectly. Okay I'll try 4.0.2 and see if it helps, thanks for the hint. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Fri, 30 Aug 2002, Steven Roussey wrote: Date: Fri, 30 Aug 2002 10:49:28 -0700 From: Steven Roussey [EMAIL PROTECTED] To: Mysql [EMAIL PROTECTED] Cc: 'Varshavchick Alexander' [EMAIL PROTECTED] Subject: Re: upgrade 3.23 - 4.0.3 question - The real cause for the question lays in the problem which occured after I upgraded to 4.0.3 WITHOUT recompiling DBI module: the new mysql version worked without any visible errors, however it was unable to process more than about 30 queries per seconds, while the normal rate for this server is more than 100 queries/sec. How can it be so slow? Evidently something went wrong, but what could it be? - Would you be willing to try using 4.0.2 instead of 4.0.3? If you do not have the problem with 4.0.2, then the issue lies in something in 4.0.3. At least I have a problem with 4.0.3 in that it works fine for a few queries, but when I let it go at a normal 3000 queries per second it choked up and died (much like 3.23.51 -- by the way, which 3.23.x are you using now?). Thanks! Sincerely, Steven Roussey http://Network54.com/?pp=e - 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 - 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: Installing mysql 4.0.3 with linux threads on freebsd
On Thu, 29 Aug 2002, Dan Nelson wrote: Please don't hijack threads; you replied to a message titled performance tunning 4.x. sorry, my fault... In the last episode (Aug 29), Varshavchick Alexander said: I have a FreeBSD server where I installed mysql 3.23 with linux threads from a port collection, the procedure was simple enough (WITH_LINUXTHREADS=yes make). All my attempts then to build it from sources were unsuccessfull, however it was not important consdering that it could be installed from ports. But now I wish to upgrade it to 4.0.3 (keeping the linuxthreads option), so can anybody advice on the procedure of building it from scratch? Try the attached port; it builds and runs fine on my -current system, and it should work on 4.*. Extract it, uncomment the WITH_LINUXTHREADS=yes line, and make. thanks a bit, this is just what was needed! -- Dan Nelson [EMAIL PROTECTED] Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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
upgrade 3.23 - 4.0.3 question
Hi, It seems that the instructuons on http://www.mysql.com/doc/en/Upgrading-from-3.23.html are contradicting themselves, or I'm mistaken: *** first snap *** Ensure that you don't have any MySQL clients that uses shared libraries (like the perl Msql-Mysql-modules). If you have, you should recompile them as structures used in `libmysqlclient.so' have changed. * *** second snap *** MySQL 4.0 will work even if you don't do the above, but you will not be able to use the new security privileges that MySQL 4.0 and you may run into problems when upgrading later to MySQL 4.1 or newer. * *** third snap *** Old clients should work with a Version 4.0 server without any problems. * Structures in libmysqlclient having been changed seems to be a serious thing, so should or should not Msql-Mysql-modules be recompiled? The real cause for the question lays in the problem which occured after I upgraded to 4.0.3 WITHOUT recompiling DBI module: the new mysql version worked without any visible errors, however it was unable to process more than about 30 queries per seconds, while the normal rate for this server is more than 100 queries/sec. How can it be so slow? Evidently something went wrong, but what could it be? Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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
Installing mysql 4.0.3 with linux threads on freebsd
Hi people, I have a FreeBSD server where I installed mysql 3.23 with linux threads from a port collection, the procedure was simple enough (WITH_LINUXTHREADS=yes make). All my attempts then to build it from sources were unsuccessfull, however it was not important consdering that it could be installed from ports. But now I wish to upgrade it to 4.0.3 (keeping the linuxthreads option), so can anybody advice on the procedure of building it from scratch? If I look at the options which were in effect for the port version they are a bit complex, should I just try repeating them now and it'll solve it, or the procedure should be some different? Here are the options for reference: Compilation info: CC='cc' CFLAGS='-O -pipe -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads' CXX='cc' CXXFLAGS='-O -pipe -D__USE_UNIX98 -D_REENTRANT -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' Configure command: ./configure '--with-comment=FreeBSD port: mysql-server-3.23.49' --with-extra-charsets=complex --enable-thread-safe-client --enable-assembler --with-named-z-libs=no --disable-shared --with-innodb '--with-named-thread-libs=-DHAVE_GLIBC2_STYLE_GETHOSTBYNAME_R -D_THREAD_SAFE -I/usr/local/include/pthread/linuxthreads -L/usr/local/lib -llthread -llgcc_r' --prefix=/usr/local/mysql-linux i386-portbld-freebsd4.5 Thank you for any hints on the subject. Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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
Extending tablespace for innodb
Hello, How can I increase the size of the tablespace for innodb tables? Can you say if this is a correct procedure: 1. Dumping the database; 2. Stopping it; 3. Deleting innodb log files (ib_logfile0, ...) and innodb data file specified in innodb_data_file_path; 4. Changing size of data file in innodb_data_file_path; 5. Starting mysql; 6. Restoring the databases from the dump file. Does it seems to be a correct procedure, or I've messed up something? Regards Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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: Mysql performance question
Hi Ken, Here are the values: kern.maxfiles: 24616 kern.maxfilesperproc: 22154 They are slightly less than 25716, is there any way to check if this upper limit becoming reached sometimes? Now there are question about innodb tables and 4.0.1. 1. What can be gained by switching to innodb inside 3.23 version, will the effect be significant? 2. Going to 4.0.1 with innodb - will it bring additional effect, what is more important - innodb or 4.0.1 or both? 3. How can I run both 3.23 and 4.0.1 on a single server, so I can play with 4.0.1 before transfering all databases to it? Starting both versions of daemons seems to be simple enough, but it seems the problem will start with the client part - how perl libraries (DBD) can deside to which mysql version they should connect? So can the mysql 3.23 clients libraries be used with 4.0.1, or what are the ways of solving it? Luck Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Ken Menzel wrote: Date: Tue, 19 Feb 2002 15:14:39 -0500 From: Ken Menzel [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Mysql performance question Your welcome Alexander! OK the value for table_cache is 8572 * 3 = 25716 file descriptors, how big is kern.maxfiles and kern.maxfilesproc? These need to be set above 26000 to allow all those tables to be opened! I know I have missed this sometime, or not reset them after a kernel compile. Also you don't need 4.0.1 for innodb tables, they work fine on 3.23.49 and LINUX_THREADS option from the ports package I think is only available for 3.23.xx versions. The only problems I have had with 4.0.1 have been self inflicted. There are some bugs in some of the newer features, 4.0.2 should be out soon. I have been kicking around the idea of using 4.0.1 in production as none of the bugs so far really affect my application. You can see the online change log for a list of changes and fixes since 4.0.1 at http://www.mysql.com/doc/N/e/News-4.0.2.html Good Luck, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: Ken Menzel [EMAIL PROTECTED] Cc: Simon Green [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 11:31 AM Subject: Re: Mysql performance question Thanks a lot for the advices. The value for table_cache is 8572, and I'm indeed accessing a lot of tables. So as I gather it the main effect should be expected from transfering to MySQL 4.0.1 compiled with LINUX_THREADS and using innodb tables, true? Now what can you say about reliability issue, aren't these products currently in the development stage and can they be used for the production server? Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Ken Menzel wrote: Date: Tue, 19 Feb 2002 10:07:22 -0500 From: Ken Menzel [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED], Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Mysql performance question Hi Simon, A couple of things, unless you have compiled WITH_LINUX_THREADS from the /usr/ports/databases then adding more processors will be unlikely to help. The native threads lib on FreeBSD runs a threaded app on 1 processor currently, use the LINUX_THREADS option in the ports to get around this if you would like to add more processors. Do you have softupdates on also? Have you read 'man tuning'? Are you accessing alot of tables/DBs? If yes what is your table_cache value? What is in 'SHOW STATUS' and 'SHOW VARIABLES'? Also MySQL 4.0.1 has a query cache that is incredible! It works great. 6000 queries per minute is pretty good performance. Have you looked at using heap tables where possible? Another suggestion if everything is hitting the same table/database have you tried innodb tables? They work very well under high loads. I would study all the 'SHOW STATUS' output and see if you can spot anything in the manual page for each of the variables. Best of Luck, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 8:30 AM Subject: RE: Mysql performance question Here is the hardware: 8x U160 SCA IBM UltraStar 36LZX Discovery 4MB cache 1 rpm 18.2GB, Adaptec 3200S 64MB Cache 32/64 bit PCI RAID U160 SCSI, FreeBSD says when booting: ADAPTEC RAID-50 370F Fixed Direct Access SCSI-2 device Tell me please if any other info is needed. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Simon Green wrote: Date: Tue, 19 Feb 2002 13:22:04 - From: Simon Green
Mysql performance question
Hi people, May be anybody can advice from the personal experience tweeking which options both in the mysql configuration and server hardware can help in increasing mysql performance speed? As it is now, mysql is configured to occupy about 600M RAM, and queries per second avg goes up to about 100, yet under the top load it seems to be not enough. Here are the config options: set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=16 set-variable= max_write_lock_count=10 set-variable= thread_concurrency=8 The server itself is 2x PIII Intel Xeon 700 MHz, Intel KOA4 platform, with 4G RAM. Mysql 3.23.47 (binary distribution), FreeBSD 4.5 system. All mysql queries are rewritten so the INSERT/UPDATE's has been reduced or replaced by DELAYED as much as possible, to ease mysql locking. The mysql tables are indexed, all documentation from the main mysql site having been studied. And under the top load the mysql starts locking anyways. So what can be the best ways of helping it: - Changing mysql options, may be increasing key_buffer some more or what else? - Adding 2 more CPU's to the server (it can bear up to 4 CPU); - May be there can be some other ways? Thanks in advance! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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: Mysql performance question
Here is the hardware: 8x U160 SCA IBM UltraStar 36LZX Discovery 4MB cache 1 rpm 18.2GB, Adaptec 3200S 64MB Cache 32/64 bit PCI RAID U160 SCSI, FreeBSD says when booting: ADAPTEC RAID-50 370F Fixed Direct Access SCSI-2 device Tell me please if any other info is needed. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Simon Green wrote: Date: Tue, 19 Feb 2002 13:22:04 - From: Simon Green [EMAIL PROTECTED] To: 'Varshavchick Alexander' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: Mysql performance question What disk drive have you got? We have found that this can help. Simon -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: 19 February 2002 13:14 To: [EMAIL PROTECTED] Subject: Mysql performance question Hi people, May be anybody can advice from the personal experience tweeking which options both in the mysql configuration and server hardware can help in increasing mysql performance speed? As it is now, mysql is configured to occupy about 600M RAM, and queries per second avg goes up to about 100, yet under the top load it seems to be not enough. Here are the config options: set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=16 set-variable= max_write_lock_count=10 set-variable= thread_concurrency=8 The server itself is 2x PIII Intel Xeon 700 MHz, Intel KOA4 platform, with 4G RAM. Mysql 3.23.47 (binary distribution), FreeBSD 4.5 system. All mysql queries are rewritten so the INSERT/UPDATE's has been reduced or replaced by DELAYED as much as possible, to ease mysql locking. The mysql tables are indexed, all documentation from the main mysql site having been studied. And under the top load the mysql starts locking anyways. So what can be the best ways of helping it: - Changing mysql options, may be increasing key_buffer some more or what else? - Adding 2 more CPU's to the server (it can bear up to 4 CPU); - May be there can be some other ways? Thanks in advance! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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 - 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: Mysql performance question
Thanks a lot for the advices. The value for table_cache is 8572, and I'm indeed accessing a lot of tables. So as I gather it the main effect should be expected from transfering to MySQL 4.0.1 compiled with LINUX_THREADS and using innodb tables, true? Now what can you say about reliability issue, aren't these products currently in the development stage and can they be used for the production server? Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Ken Menzel wrote: Date: Tue, 19 Feb 2002 10:07:22 -0500 From: Ken Menzel [EMAIL PROTECTED] To: Varshavchick Alexander [EMAIL PROTECTED], Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: Mysql performance question Hi Simon, A couple of things, unless you have compiled WITH_LINUX_THREADS from the /usr/ports/databases then adding more processors will be unlikely to help. The native threads lib on FreeBSD runs a threaded app on 1 processor currently, use the LINUX_THREADS option in the ports to get around this if you would like to add more processors. Do you have softupdates on also? Have you read 'man tuning'? Are you accessing alot of tables/DBs? If yes what is your table_cache value? What is in 'SHOW STATUS' and 'SHOW VARIABLES'? Also MySQL 4.0.1 has a query cache that is incredible! It works great. 6000 queries per minute is pretty good performance. Have you looked at using heap tables where possible? Another suggestion if everything is hitting the same table/database have you tried innodb tables? They work very well under high loads. I would study all the 'SHOW STATUS' output and see if you can spot anything in the manual page for each of the variables. Best of Luck, Ken - Original Message - From: Varshavchick Alexander [EMAIL PROTECTED] To: Simon Green [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, February 19, 2002 8:30 AM Subject: RE: Mysql performance question Here is the hardware: 8x U160 SCA IBM UltraStar 36LZX Discovery 4MB cache 1 rpm 18.2GB, Adaptec 3200S 64MB Cache 32/64 bit PCI RAID U160 SCSI, FreeBSD says when booting: ADAPTEC RAID-50 370F Fixed Direct Access SCSI-2 device Tell me please if any other info is needed. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Simon Green wrote: Date: Tue, 19 Feb 2002 13:22:04 - From: Simon Green [EMAIL PROTECTED] To: 'Varshavchick Alexander' [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: RE: Mysql performance question What disk drive have you got? We have found that this can help. Simon -Original Message- From: Varshavchick Alexander [mailto:[EMAIL PROTECTED]] Sent: 19 February 2002 13:14 To: [EMAIL PROTECTED] Subject: Mysql performance question Hi people, May be anybody can advice from the personal experience tweeking which options both in the mysql configuration and server hardware can help in increasing mysql performance speed? As it is now, mysql is configured to occupy about 600M RAM, and queries per second avg goes up to about 100, yet under the top load it seems to be not enough. Here are the config options: set-variable= key_buffer=512M set-variable= max_allowed_packet=1M set-variable= table_cache=512 set-variable= sort_buffer=8M set-variable= record_buffer=1M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=16 set-variable= max_write_lock_count=10 set-variable= thread_concurrency=8 The server itself is 2x PIII Intel Xeon 700 MHz, Intel KOA4 platform, with 4G RAM. Mysql 3.23.47 (binary distribution), FreeBSD 4.5 system. All mysql queries are rewritten so the INSERT/UPDATE's has been reduced or replaced by DELAYED as much as possible, to ease mysql locking. The mysql tables are indexed, all documentation from the main mysql site having been studied. And under the top load the mysql starts locking anyways. So what can be the best ways of helping it: - Changing mysql options, may be increasing key_buffer some more or what else? - Adding 2 more CPU's to the server (it can bear up to 4 CPU); - May be there can be some other ways? Thanks in advance! Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) - 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: Generating SQL code?
See mysqldump utility, it does exactly what you ask. Alexander Varshavchick, Metrocom Joint Stock Company Phone: (812)118-3322, 118-3115(fax) On Tue, 19 Feb 2002, Sterling J. Anderson wrote: Date: Tue, 19 Feb 2002 11:14:11 -0600 From: Sterling J. Anderson [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Generating SQL code? I am going to be copying some tables from one DB to another and was wondering if it is possible to have MySQL output the SQL Create Table syntax? I know there are products that will do that (ie. MyPHPAdmin) but can MySQL do it? I'd rather not put anything extra on my server. Thanks. Sterling J. Anderson email: [EMAIL PROTECTED] - 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 - 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
