Re: MySQL variables
On 09/12/2009, at 6:56 PM, machiel.richards wrote: Good day guys (and girls if any) I am constantly in a position where variables on a production mysql database needs to be changed. The database runs a 24/7 system and thus to reboot is not preffered and should be the absolute last resort. How can I set variables to be effective immediately? ( I am still a junior in mysql dba and still learning) If you take a look at the manual in the section about server options and variables (http://dev.mysql.com/doc/refman/5.0/en/mysqld-option- tables.html in MySQL 5.0 or http://dev.mysql.com/doc/refman/5.1/en/ mysqld-option-tables.html in MySQL 5.1), then you can see which settings can be changed dynamically. To change a variable dynamically you need to have super privilege, and then set the variable as e.g. SET GLOBAL system_var_name = ... If I set these will it still be effective should the database be restarted somewhere in the future? The change above will not persist when the database is restarted. In order to ensure that, you will have to update the configuration file as well. - Jesper Thanks in advance for your help. Regards -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL variables
Good day guys (and girls if any) I am constantly in a position where variables on a production mysql database needs to be changed. The database runs a 24/7 system and thus to reboot is not preffered and should be the absolute last resort. How can I set variables to be effective immediately? ( I am still a junior in mysql dba and still learning) If I set these will it still be effective should the database be restarted somewhere in the future? Thanks in advance for your help. Regards
Problems with mine mysql variables and memory leak problem
Hello there guys, Latelly our database server is going into a very big problem, our current configuration (is in an attachment file), is having huge load of leak memory and a variable *innodb_log_file_size *is off cause when i up it the mysqld doesn't start, our server is having this status(average status) connected threads = 14 threads in cache = 36 threads executing = 12 with a an average of trafic sent_bytes = 5M bytes_recived = 310kB number of sql queries = 500 Query cache hitrate = 70% Key efficiency Varies a lot like 0% to 99% in a split second Qcache average = 35,124 Sometimes all of the threads in cache goes to the connected an begins the server slowdown and freeze the threads connected jumps to 14-100 in about 5s and then the threads executing jumps to 60-120 at the same time it goes like a ramp up load but the server doesn't comes to normal all threads stays in mysql and doesn't go away and our server starts to consume a VERY HUGE load of memory about 8GB-12GB if i doesn't restart mysqld then the server freeze... our server is having a big load of users connected about 500 we use moodle as an application plattaform. Our server hardware configuration is 2x Quad Core Intel® Xeon® E5450, 2x6MB Cache, 3.0GHz, 1333MHz FSB 8GB 800MHz Memory 2x SAS 73GB 15000RPM in RAID 1 And application server is: MySql 5.0.45 and our database size is about 9gb (we don't clear mdl_log) PHP 5.1.6 and we use moodle 1.7.2+ I hope you guys can help \o
Re: mysql variables
In the last episode (Apr 30), Krishna Chandra Prajapati said: > I am worried about Key_blocks_unused. when Key_blocks_unused reach to > 0. There will be no free blocks then how insert query will work. The oldest block will be written to disk if it is dirty, and discarded. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql variables
Krishna Chandra Prajapati schrieb: Hi Dan, I am worried about Key_blocks_unused. when Key_blocks_unused reach to 0. There will be no free blocks then how insert query will work. it is like any other cache system, if the cache is not usable (full, not accessible or whatever) the cache will be omitted, and writes/reads go directly to the disk and not the RAM (cache) you do not need to worry about loosing data caused by a full cache -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql variables
Hi Dan, I am worried about Key_blocks_unused. when Key_blocks_unused reach to 0. There will be no free blocks then how insert query will work. Thanks Krishna Chandra Prajapati On Wed, Apr 30, 2008 at 10:42 AM, Dan Nelson <[EMAIL PROTECTED]> wrote: > In the last episode (Apr 30), Krishna Chandra Prajapati said: > > Key_blocks_unused 952405 > > Key_blocks_used395539 > > > > Key_blocks_used is increasing day by day and Key_blocks_unused is > > decreasing day by day. Ater a month Key_blocks_unused will reach to > > 0. Does it mean that i need to increase the key_buffer_size. Already > > key_buffer_size=1G. > > > > While going through forums and lists , i found that when > > key_blocks_unused is less. then select query will become slow. So > > whats the right solution. > > In a perfect world, you would set key_buffer_size to the total size of > all your .MYI files. Depending on the size of your tables, that may > simply not be possible. Luckily, it's usually not necessary, either. > What is more important is the hit rate. Run "SHOW STATUS LIKE 'key%'", > and compare Key_read_requests (how many times mysql asked for a key) > against Key_reads (how many times mysql actually had to go to disk to > fetch a key). 1-(Key_reads/Key_read_requests) is your read hit rate. > If it's 0.99 or greater, that means that 99% of your key reads are > already coming from the key buffer, and adding more key buffers is > unlikely to give you a measurable performance increase. > > Don't worry too much about your write hit rate ( > 1-(Key_writes/Key_write_requests) ); it's always going to be lower > because mysql tries to keep the on-disk copy of the index up to date. > > -- >Dan Nelson >[EMAIL PROTECTED] > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/[EMAIL PROTECTED] > > -- Krishna Chandra Prajapati
Re: mysql variables
In the last episode (Apr 30), Krishna Chandra Prajapati said: > Key_blocks_unused 952405 > Key_blocks_used395539 > > Key_blocks_used is increasing day by day and Key_blocks_unused is > decreasing day by day. Ater a month Key_blocks_unused will reach to > 0. Does it mean that i need to increase the key_buffer_size. Already > key_buffer_size=1G. > > While going through forums and lists , i found that when > key_blocks_unused is less. then select query will become slow. So > whats the right solution. In a perfect world, you would set key_buffer_size to the total size of all your .MYI files. Depending on the size of your tables, that may simply not be possible. Luckily, it's usually not necessary, either. What is more important is the hit rate. Run "SHOW STATUS LIKE 'key%'", and compare Key_read_requests (how many times mysql asked for a key) against Key_reads (how many times mysql actually had to go to disk to fetch a key). 1-(Key_reads/Key_read_requests) is your read hit rate. If it's 0.99 or greater, that means that 99% of your key reads are already coming from the key buffer, and adding more key buffers is unlikely to give you a measurable performance increase. Don't worry too much about your write hit rate ( 1-(Key_writes/Key_write_requests) ); it's always going to be lower because mysql tries to keep the on-disk copy of the index up to date. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql variables
Hi, Key_blocks_unused 952405 Key_blocks_used 395539 Key_blocks_used is increasing day by day and Key_blocks_unused is decreasing day by day. Ater a month Key_blocks_unused will reach to 0. Does it mean that i need to increase the key_buffer_size. Already key_buffer_size=1G. While going through forums and lists , i found that when key_blocks_unused is less. then select query will become slow. So whats the right solution. Thanks, -- Krishna Chandra Prajapati
Re: suggestions - server options/mysql variables
We don't know anything about you structures, your data, or your indicies. You should show them along with the output of EXPLAIN on your query. Derick Smith wrote: Hi! All the select statements have indexes on appropriate fields. Mysql appears to run fast, I was just wondering if there was anyway to make it faster. It is currently running on Windows, I have also ported it to HP-UX 11. The queries are of the type : SELECT * FROM textl WHERE parsetext = "" and filter = "" GROUP BY code. I think the query code is optimized, I was wondering is there were any settings I could change in mysql to speed it up? or any other tricks to increase speed? I tried setting set-variable = key_buffer_size=64M, but that did not appear to make a big difference. Thanks Eric From: "Mike Hillyer" <[EMAIL PROTECTED]> To: "Derick Smith" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Subject: RE: suggestions - server options/mysql variables Date: Thu, 19 Jun 2003 09:13:33 -0600 If you have queries that are only selects, in small tables, with few rows, you should have very fast performance as is. Are your queries properly indexed? What hardware are you running on? What do these queries look like? Regards, Mike Hillyer www.vbmysql.com > -Original Message- > From: Derick Smith [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 19, 2003 9:08 AM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: suggestions - server options/mysql variables > > > Hi! > Does anyone know any mysql options I can change in the my.ini > file or mysql > variables to increase the speed of select statements? > > About database: > -uses only select statements for queries > -no transactions > -if database becomes corrupt in anyway, not a big deal I can > recreate it > -it is a small database, I do not need recovery, raid or any > other backup > mechanism > -queries generally return very little data > > I will test any suggestions people have for me. > Thanks > Eric > > _ > MSN 8 with e-mail virus protection service: 2 months FREE* > http://join.msn.com/?page=features/virus > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: suggestions - server options/mysql variables
Hi! All the select statements have indexes on appropriate fields. Mysql appears to run fast, I was just wondering if there was anyway to make it faster. It is currently running on Windows, I have also ported it to HP-UX 11. The queries are of the type : SELECT * FROM textl WHERE parsetext = "" and filter = "" GROUP BY code. I think the query code is optimized, I was wondering is there were any settings I could change in mysql to speed it up? or any other tricks to increase speed? I tried setting set-variable = key_buffer_size=64M, but that did not appear to make a big difference. Thanks Eric From: "Mike Hillyer" <[EMAIL PROTECTED]> To: "Derick Smith" <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]>, <[EMAIL PROTECTED]> Subject: RE: suggestions - server options/mysql variables Date: Thu, 19 Jun 2003 09:13:33 -0600 If you have queries that are only selects, in small tables, with few rows, you should have very fast performance as is. Are your queries properly indexed? What hardware are you running on? What do these queries look like? Regards, Mike Hillyer www.vbmysql.com > -Original Message- > From: Derick Smith [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 19, 2003 9:08 AM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: suggestions - server options/mysql variables > > > Hi! > Does anyone know any mysql options I can change in the my.ini > file or mysql > variables to increase the speed of select statements? > > About database: > -uses only select statements for queries > -no transactions > -if database becomes corrupt in anyway, not a big deal I can > recreate it > -it is a small database, I do not need recovery, raid or any > other backup > mechanism > -queries generally return very little data > > I will test any suggestions people have for me. > Thanks > Eric > > _ > MSN 8 with e-mail virus protection service: 2 months FREE* > http://join.msn.com/?page=features/virus > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] _ Protect your PC - get McAfee.com VirusScan Online http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: suggestions - server options/mysql variables
If you have queries that are only selects, in small tables, with few rows, you should have very fast performance as is. Are your queries properly indexed? What hardware are you running on? What do these queries look like? Regards, Mike Hillyer www.vbmysql.com > -Original Message- > From: Derick Smith [mailto:[EMAIL PROTECTED] > Sent: Thursday, June 19, 2003 9:08 AM > To: [EMAIL PROTECTED]; [EMAIL PROTECTED] > Subject: suggestions - server options/mysql variables > > > Hi! > Does anyone know any mysql options I can change in the my.ini > file or mysql > variables to increase the speed of select statements? > > About database: > -uses only select statements for queries > -no transactions > -if database becomes corrupt in anyway, not a big deal I can > recreate it > -it is a small database, I do not need recovery, raid or any > other backup > mechanism > -queries generally return very little data > > I will test any suggestions people have for me. > Thanks > Eric > > _ > MSN 8 with e-mail virus protection service: 2 months FREE* > http://join.msn.com/?page=features/virus > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?> [EMAIL PROTECTED] > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
suggestions - server options/mysql variables
Hi! Does anyone know any mysql options I can change in the my.ini file or mysql variables to increase the speed of select statements? About database: -uses only select statements for queries -no transactions -if database becomes corrupt in anyway, not a big deal I can recreate it -it is a small database, I do not need recovery, raid or any other backup mechanism -queries generally return very little data I will test any suggestions people have for me. Thanks Eric _ MSN 8 with e-mail virus protection service: 2 months FREE* http://join.msn.com/?page=features/virus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Variables
At 6:12 PM -0500 7/15/01, Paul DuBois wrote: >Sounds like that should do it. (O'Reilly also has a book "Using csh & tcsh" Actually, after having problems finding this file I started looking for a reference to buy and came across "Using csh & tcsh" and did recognize the author. This book has a 1995 release date on it which in internet time makes it an antique, but in the case of Unix probably not a whole lot has changed I imagine. Nevertheless, it would be a dream if you could write the version of "Using tcsh" specifically for Mac OS X. -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com database,sql,query,table - 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 Variables
At 12:39 PM -0700 7/15/01, Michael Collins wrote: >At 11:09 AM -0700 7/15/01, Michael Collins wrote: >>At 1:47 PM -0500 7/14/01, Paul DuBois wrote: >>Any solution I've tried using the LassoMySQL/var/my.cnf or >>~/.my.cnf files affect both the special installation as well as >>the standard MySQL installation. I find I will always get the >>standard port. > >Settings in the [client] group of your ~/.my.cnf file would affect any >client that you run. I would expect that all clients would therefore >connect to the same server... >>> >>>The clients won't use the option file in the data directory. They will >>>only use the shared option file /etc/my.cnf, and the .my.cnf in your home >>>directory. You might be better off creating aliases that specify the >>>options on the command line. >> >> >>Thank you Paul with your help so far. >>So what I am left with is that I will need to create a startup file >>to map the command you type in to the actual command with the >>-socket specified? > >Hey, maybe I answered part of my own question (thanks to the book >"Essential System Administration from O'Reilly). There is a file on >Mac OS X at: >/usr/share/init/tcsh/rc that starts with the following: > >## ># TCSH Expanded C-Shell INITIALIZATION FILE ># ># Wilfredo Sanchez Jr. | [EMAIL PROTECTED] ># July 09, 1992 ># ># MIT Project Athena ># ># ORIGINAL SOURCES: /usr/athena/lib/init/cshrc (ATHENA REL 7.3P) >## > >Adding the following and restarting the telnet session seems to work: > >alias specialmysqldump >/Applications/Special/SpecialMySQL/bin/mysqldump >--socket=/tmp/SpecialMySQL.sock >alias specialmysqladmin >/Applications/Special/SpecialMySQL/bin/mysqladmin >--socket=/tmp/SpecialMySQL.sock >alias specialmysql /Applications/Special/SpecialMySQL/bin/mysql >--socket=/tmp/SpecialMySQL.sock > >I now get the list of databases for the Special installation when >using specialmysql as a command. > >And if I use with parameters or redirect to a file it also works: > >specialmysqldump --opt special_site_1 > /temp.mysql > >And this actually added a database called special: > >root% specialmysqladmin create special > >Hurray! Sounds like that should do it. (O'Reilly also has a book "Using csh & tcsh" specifically on the shell that you're using that has a chapter on startup files and another on aliases. It's written by, er ... never mind.) > >-- >Michael >__ >||| Michael Collins ||| >||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] >||| Seattle, WA, USA ||| http://www.lassodev.com -- Paul DuBois, [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 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Variables
At 11:09 AM -0700 7/15/01, Michael Collins wrote: >At 1:47 PM -0500 7/14/01, Paul DuBois wrote: >Any solution I've tried using the LassoMySQL/var/my.cnf or >~/.my.cnf files affect both the special installation as well as >the standard MySQL installation. I find I will always get the >standard port. Settings in the [client] group of your ~/.my.cnf file would affect any client that you run. I would expect that all clients would therefore connect to the same server... >> >>The clients won't use the option file in the data directory. They will >>only use the shared option file /etc/my.cnf, and the .my.cnf in your home >>directory. You might be better off creating aliases that specify the >>options on the command line. > > >Thank you Paul with your help so far. >So what I am left with is that I will need to create a startup file >to map the command you type in to the actual command with the >-socket specified? Hey, maybe I answered part of my own question (thanks to the book "Essential System Administration from O'Reilly). There is a file on Mac OS X at: /usr/share/init/tcsh/rc that starts with the following: ## # TCSH Expanded C-Shell INITIALIZATION FILE # # Wilfredo Sanchez Jr. | [EMAIL PROTECTED] # July 09, 1992 # # MIT Project Athena # # ORIGINAL SOURCES: /usr/athena/lib/init/cshrc (ATHENA REL 7.3P) ## Adding the following and restarting the telnet session seems to work: alias specialmysqldump /Applications/Special/SpecialMySQL/bin/mysqldump --socket=/tmp/SpecialMySQL.sock alias specialmysqladmin /Applications/Special/SpecialMySQL/bin/mysqladmin --socket=/tmp/SpecialMySQL.sock alias specialmysql /Applications/Special/SpecialMySQL/bin/mysql --socket=/tmp/SpecialMySQL.sock I now get the list of databases for the Special installation when using specialmysql as a command. And if I use with parameters or redirect to a file it also works: specialmysqldump --opt special_site_1 > /temp.mysql And this actually added a database called special: root% specialmysqladmin create special Hurray! -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.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 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Variables
At 1:47 PM -0500 7/14/01, Paul DuBois wrote: Any solution I've tried using the LassoMySQL/var/my.cnf or ~/.my.cnf files affect both the special installation as well as the standard MySQL installation. I find I will always get the standard port. >>> >>>Settings in the [client] group of your ~/.my.cnf file would affect any >>>client that you run. I would expect that all clients would therefore >>>connect to the same server... > >The clients won't use the option file in the data directory. They will >only use the shared option file /etc/my.cnf, and the .my.cnf in your home >directory. You might be better off creating aliases that specify the >options on the command line. Thank you Paul with your help so far. As a reminder my situation is having a Mac OS X server with two MySQL server binaries installed (with a complete set of MySQL installed files in the two separate locations). One uses the standard /usr/local/bin location for MySQL the other installs into the "Application" directory. I am trying to find an easier way to not have to specify the socket option each time I use one of the MySQL client programs for databases hosted by Lasso MySQL. As an example to run these commands they need to use the form: root% ./bin/mysql --socket=/tmp/SpecialMySQL.sock MCExample < /Import_MCExample.mysql root% ./bin/mysqlshow --socket=/tmp/SpecialMySQL.sock root% ./bin/mysqladmin --socket=/tmp/SpecialMySQL.sock create newDatabaseName In short, it becomes burdensome to type --socket=/tmp/LassoMySQL.sock each time. So what I am left with is that I will need to create a startup file to map the command you type in to the actual command with the -socket specified? Example, create a .cshrc (Mac OS X uses the tcsh shell) and locate it in my user directory or in /etc (I am not sure about this?). Set up the alias lines as follows: alias mysqldump /Applications/special/specialMySQL/bin/mysqldump --socket=/tmp/SpecialMySQL.sock alias mysqladmin /Applications/special/specialMySQL/bin/mysqladmin --socket=/tmp/SpecialMySQL.sock alias mysql /Applications/special/specialMySQL/bin/mysql --socket=/tmp/SpecialMySQL.sock I would actually need to use a somewhat different name for the alias as this will interfere with use of the command for the standard install. The question though is will parameters be passed in? Will it then work, for example, to import a file using: root% mysql MCExample < /Import_MCExample.mysql I cannot test it since so far the alias file idea does not work, I put the .cshrc file into my home directory, logged out and started a new telnet session. -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Variables
>At 12:25 PM -0500 7/14/01, Paul DuBois wrote: >>>Any solution I've tried using the LassoMySQL/var/my.cnf or >>>~/.my.cnf files affect both the special installation as well as >>>the standard MySQL installation. I find I will always get the >>>standard port. >> >>Settings in the [client] group of your ~/.my.cnf file would affect any >>client that you run. I would expect that all clients would therefore >>connect to the same server... > >But if I use /Path/specialMySQL/var/my.cnf and my option file contains: > >[client] >port=14551 >socket=/tmp/LassoMySQL.sock > >Then, invoking the client from within /Path/specialMySQL/bin/mysql >would use the different port and socket?> > >And, using /usr/local/mysql would then use the default datadirectory and port? The clients won't use the option file in the data directory. They will only use the shared option file /etc/my.cnf, and the .my.cnf in your home directory. You might be better off creating aliases that specify the options on the command line. > >BTW, this is on Mac OS X, I wouldn't think it would matter but I >thought to mention it. Shouldn't make any difference. I've noticed no special problems with MySQL on Mac OS X other than that mysqladmin shutdown doesn't work. >-- >Michael >__ >||| Michael Collins ||| >||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] >||| Seattle, WA, USA ||| http://www.lassodev.com -- Paul DuBois, [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
Re: MySQL Variables
At 12:51 PM -0500 7/14/01, MikeBlezien wrote:
>is this the default location it looks for a 'my.cnf' file or can
>this be defined
>some where?
Straight from the manual:
4.16.5 Option Files
MySQL Version 3.22 can read default startup options for the server
and for clients from option files. MySQL reads default options from
the following files on Unix:
Filename Purpose
/etc/my.cnf Global options
DATADIR/my.cnf Server-specific options
defaults-extra-file The file specified with {defaults-extra-file=#
~/.my.cnf User-specific options
DATADIR is the MySQL data directory (typically
`/usr/local/mysql/data' for a binary installation or `/usr/local/var'
for a source installation). Note that this is the directory that was
specified at configuration time, not the one specified with --datadir
when mysqld starts up! (--datadir has no e ect on where the server
looks for option files, because it looks for them before it processes
any command-line arguments.)
MySQL tries to read option files in the order listed above. If
multiple option files exist, an option specified in a file read later
takes precedence over the same option specified in a file read
earlier. Options specified on the command line take precedence over
options specified in any option file. Some options can be specified
using environment variables. Options specified on the command line or
in option files take precedence over environment variable values. See
Appendix A [Environment variables], page 540.
--
Michael
__
||| Michael Collins |||
||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED]
||| Seattle, WA, USA ||| http://www.lassodev.com
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Variables
At 12:51 PM -0500 7/14/01, MikeBlezien wrote: > >>On Sat, 14 Jul 2001 11:46:15 -0500, Paul DuBois <[EMAIL PROTECTED]> wrote: > >>> Also if a /etc/my.cnf is going to be used, once it's been created does the MySQL server need to be shutdown and restarted to recognize the new my.cnf file?? >>> >>>Yes. > >is this the default location it looks for a 'my.cnf' file or can >this be defined >some where? See: http://www.mysql.com/doc/O/p/Option_files.html > > >Mike(mickalo)Blezien >=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= >Thunder Rain Internet Publishing >Providing Internet Solutions that work! >http://www.thunder-rain.com >Tel: 1(225)686-2002 >=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- Paul DuBois, [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
Re: MySQL Variables
At 12:25 PM -0500 7/14/01, Paul DuBois wrote: >>Any solution I've tried using the LassoMySQL/var/my.cnf or >>~/.my.cnf files affect both the special installation as well as the >>standard MySQL installation. I find I will always get the standard >>port. > >Settings in the [client] group of your ~/.my.cnf file would affect any >client that you run. I would expect that all clients would therefore >connect to the same server... But if I use /Path/specialMySQL/var/my.cnf and my option file contains: [client] port=14551 socket=/tmp/LassoMySQL.sock Then, invoking the client from within /Path/specialMySQL/bin/mysql would use the different port and socket?> And, using /usr/local/mysql would then use the default datadirectory and port? BTW, this is on Mac OS X, I wouldn't think it would matter but I thought to mention it. -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Variables
>>On Sat, 14 Jul 2001 11:46:15 -0500, Paul DuBois <[EMAIL PROTECTED]> wrote: >> >>> Also if a /etc/my.cnf is going to be used, once it's >>>been created does the MySQL server need to be shutdown and restarted to >>>recognize the new my.cnf file?? >> >>Yes. is this the default location it looks for a 'my.cnf' file or can this be defined some where? Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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 Variables
At 10:11 AM -0700 7/14/01, Michael Collins wrote: >At 11:46 AM -0500 7/14/01, Paul DuBois wrote: >>At 11:32 AM -0500 7/14/01, MikeBlezien wrote: >>>when you do a mysqladmin variables, where exactly are these >>>variables stored, >>>and can they be changed. >> >>They can be changed by placing set-variable= var_name=var_value lines >>in the [mysqld] group of the /etc/my.cnf option file (or any other option >>file that the server reads at startup time). > >I too have been trying to get a "my.cnf" file working properly with >little success. The twist in my tale is that I have two copies of >MySQL installed, one of which uses the standard install location >(/usr/local/) and port, and the other a custom location, port, and >socket. I would like for the second copy to always use the special >port or socket whenever I utilize any of the mysql client programs >(which I invoke from the special location of those files). If each server is compiled to use a different data directory, then you should be able to put a my.cnf file in the data directory to affect the respective server using the [mysqld] group in each file. > >I have read the manual about option files and looked for the answer >in the MySQL DuBois book. I have used what I believe to be the >correct parameters based on a file I found at >/usr/local/share/mysql/my-small.cnf). This is a source install so I >understand from the manual that the "my.cnf" file goes in the var >directory. > >I have also tried a simple version of this: > >[client] >port=14551 >socket=/tmp/LassoMySQL.sock > >Any solution I've tried using the LassoMySQL/var/my.cnf or ~/.my.cnf >files affect both the special installation as well as the standard >MySQL installation. I find I will always get the standard port. Settings in the [client] group of your ~/.my.cnf file would affect any client that you run. I would expect that all clients would therefore connect to the same server... > >Help? > >-- >Michael >__ >||| Michael Collins ||| >||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] >||| Seattle, WA, USA ||| http://www.lassodev.com -- Paul DuBois, [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
Re: MySQL Variables
At 11:46 AM -0500 7/14/01, Paul DuBois wrote: >At 11:32 AM -0500 7/14/01, MikeBlezien wrote: >>when you do a mysqladmin variables, where exactly are these variables stored, >>and can they be changed. > >They can be changed by placing set-variable= var_name=var_value lines >in the [mysqld] group of the /etc/my.cnf option file (or any other option >file that the server reads at startup time). I too have been trying to get a "my.cnf" file working properly with little success. The twist in my tale is that I have two copies of MySQL installed, one of which uses the standard install location (/usr/local/) and port, and the other a custom location, port, and socket. I would like for the second copy to always use the special port or socket whenever I utilize any of the mysql client programs (which I invoke from the special location of those files). I have read the manual about option files and looked for the answer in the MySQL DuBois book. I have used what I believe to be the correct parameters based on a file I found at /usr/local/share/mysql/my-small.cnf). This is a source install so I understand from the manual that the "my.cnf" file goes in the var directory. I have also tried a simple version of this: [client] port=14551 socket=/tmp/LassoMySQL.sock Any solution I've tried using the LassoMySQL/var/my.cnf or ~/.my.cnf files affect both the special installation as well as the standard MySQL installation. I find I will always get the standard port. Help? -- Michael __ ||| Michael Collins ||| ||| Kuwago Web Services ||| mailto:[EMAIL PROTECTED] ||| Seattle, WA, USA ||| http://www.lassodev.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Variables
At 11:32 AM -0500 7/14/01, MikeBlezien wrote: >when you do a mysqladmin variables, where exactly are these variables stored, >and can they be changed. They can be changed by placing set-variable= var_name=var_value lines in the [mysqld] group of the /etc/my.cnf option file (or any other option file that the server reads at startup time). > Also if a /etc/my.cnf is going to be used, once it's >been created does the MySQL server need to be shutdown and restarted to >recognize the new my.cnf file?? Yes. > >Again, Thx for the help :) > >Mike(mickalo)Blezien >=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= >Thunder Rain Internet Publishing >Providing Internet Solutions that work! >http://www.thunder-rain.com >Tel: 1(225)686-2002 >=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- Paul DuBois, [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
MySQL Variables
when you do a mysqladmin variables, where exactly are these variables stored, and can they be changed. Also if a /etc/my.cnf is going to be used, once it's been created does the MySQL server need to be shutdown and restarted to recognize the new my.cnf file?? Again, Thx for the help :) Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - 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: Help! MySQL variables laugh at me
if faq had a sequenced primary key you could do the following select a.sezione, IF(a.sezione = b.sezione, "no","yes") as hasChanged from faq as a, faq as b where faq.id = faq.id -1 -Original Message- From: Luca Accomazzi [mailto:[EMAIL PROTECTED]] Sent: Monday, June 04, 2001 11:02 AM To: [EMAIL PROTECTED] Subject: Help! MySQL variables laugh at me I've been frying my brain over this problem for the past 24 hours and I'll be very grateful for any hints. I'm trying to recognize when the value for a field is changing value from one record to the next. For an example, look at this: mysql> select sezione from faq limit 20; +---+ | sezione | +---+ | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Macintosh | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | +---+ 20 rows in set (0.01 sec) As you see, the field's value is almost always "Internet", but it becomes "Macintosh" in the middle. I'd like to recognize this change (and, later, act on it). I thought I had found the solution by employing a bit of variable magic. And it seemed to work just fine: mysql> select sezione, IF(@a=sezione, "no", "yes") as hasChanged, (@a:=sezione) as newValueForMemory from faq limit 20; +---++---+ | sezione | hasChanged | newValueForMemory | +---++---+ | Internet | yes| Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Macintosh | yes| Macintosh | | Internet | yes| Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | +---++---+ 20 rows in set (0.01 sec) I keep a copy of the previous value in a variable, then check that variable against the current value for the field. All is well. BUT, as soon as I tackle my real problem (on a nasty database, complex queries, and the real problem, all hell breaks loose: mysql> select ordine, IF(@oldOrdine=ordine, "no", "yes") as hasChanged, (@oldOrdine:=ordine) as newValueForMemory from aghi1, Not_needle_aux where (GENMATERIA = 'MONOSOF' OR GENMATERIA = 'DERMALON*') and (Needle1='pre-cut' or Needle1='stdlth' or Needle1='reel') and Needle1=Needle_code order by ordine, SIZEMR, LENGTHMETR; +++---+ | ordine | hasChanged | newValueForMemory | +++---+ | 1 | yes| 1 | | 1 | no | 1 | | 1 | no | 1 | | 1 | no | 1 | | 1 | yes| 1 | | 1 | no | 1 | | 1 | no | 1 | | 1 | no | 1 | | 3 | yes| 3 | | 3 | no | 3 | | 3 | yes| 3 | | 3 | no | 3 | | 3 | no | 3 | +++---+ 13 rows in set (0.10 sec) WHY OH WHY is MySQL behaving like this? I tried a bunch on variations (using two variables, applying a function on the field and ever weirder ones), to no avail. It gets even more unpredictable, as if the variables refused to be set and reset. mysql> select @a, ordine, @b:=ordine, @b, IF (@a=@b, 'Very Thin', 'Thin') as divider, @a:=@b from aghi1, Not_needle_aux where (GENMATERIA = 'MONOSOF' OR GENMATERIA = 'DERMALON*') and (Needle1='pre-cut' or Needle1='stdlth' or Needle1='reel') and Needle1=Needle_code order by ordine, SIZEMR, LENGTHMETR; +--+++--+---++ | @a | ordine | @b:=ordine | @b | divider | @a:=@b | +--+++--+---++ | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1|
Re: Help! MySQL variables laugh at me
Ok. This is what I think you are wanting. Have a datetime field or even a datestamp. Before you insert, do a select to find the value of the most recent row that was added then you can calculate what you should insert from there. BTW you should include history incase some else has ideas. - Original Message - From: "Luca Accomazzi" <[EMAIL PROTECTED]> To: "Rolf Hopkins" <[EMAIL PROTECTED]>; <[EMAIL PROTECTED]> Sent: Monday, June 04, 2001 20:47 Subject: Re: Help! MySQL variables laugh at me > > I have only had a quick look over this and already have a question for the > > simple example you've provided. How are you determining the last value > > entered before entering the new value? The only way I see you of > > effectively accomplishing this is through the use of a datetime column. Or > > am I missing something? > > Wait wait wait! I see what you mean. What I'm seeing as a loop (rows upon > rows of data from the database) is a single query to MySQL. So it's fist > selecting, then calculating my variable, then sorting. And in sorting, I see > "wrong" values for my "is different from the previous" pseudo field. > > Glact. > > Is there anything I can do to get the functionality I'm after in any other > way? I seem to remember there's a "having" clause which gets computed in the > end? > > L.A. - 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: Help! MySQL variables laugh at me
> I have only had a quick look over this and already have a question for the > simple example you've provided. How are you determining the last value > entered before entering the new value? The only way I see you of > effectively accomplishing this is through the use of a datetime column. Or > am I missing something? Wait wait wait! I see what you mean. What I'm seeing as a loop (rows upon rows of data from the database) is a single query to MySQL. So it's fist selecting, then calculating my variable, then sorting. And in sorting, I see "wrong" values for my "is different from the previous" pseudo field. Glact. Is there anything I can do to get the functionality I'm after in any other way? I seem to remember there's a "having" clause which gets computed in the end? L.A. - 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
FW: Help! MySQL variables laugh at me
> I have only had a quick look over this and already have a question for the > simple example you've provided. How are you determining the last value > entered before entering the new value? The only way I see you of > effectively accomplishing this is through the use of a datetime column. Or > am I missing something? I don't understand perfectly your question. Do you mean "how do I keep records in order" or "how do I keep track of the previous record's field value in the SELECTing query"? "how do I keep records in order" In the simple example, I don't. I don't care what order the records come out, it's just an example database. :-) In the complex (real-life) example there is an ORDER BY clause. "how do I keep track of the previous record's field value when SELECTing" I use a variable called @oldOrdine. The last thing I do in the SELECT is stuffing in there the value of the record for the current field. Then, on the subsequent record iteration, I compare its current value with the then-current field value. L.A. -- End of Forwarded Message - 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: Help! MySQL variables laugh at me
I have only had a quick look over this and already have a question for the simple example you've provided. How are you determining the last value entered before entering the new value? The only way I see you of effectively accomplishing this is through the use of a datetime column. Or am I missing something? - Original Message - From: "Luca Accomazzi" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Monday, June 04, 2001 17:02 Subject: Help! MySQL variables laugh at me > I've been frying my brain over this problem for the past 24 hours and I'll > be very grateful for any hints. > > I'm trying to recognize when the value for a field is changing value from > one record to the next. For an example, look at this: > > mysql> select sezione from faq limit 20; > +---+ > | sezione | > +---+ > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Macintosh | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > | Internet | > +---+ > 20 rows in set (0.01 sec) > > As you see, the field's value is almost always "Internet", but it becomes > "Macintosh" in the middle. I'd like to recognize this change (and, later, > act on it). > > I thought I had found the solution by employing a bit of variable magic. And > it seemed to work just fine: > > mysql> select sezione, IF(@a=sezione, "no", "yes") as hasChanged, > (@a:=sezione) as newValueForMemory from faq limit 20; > +---++---+ > | sezione | hasChanged | newValueForMemory | > +---++---+ > | Internet | yes| Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Macintosh | yes| Macintosh | > | Internet | yes| Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > | Internet | no | Internet | > +---++---+ > 20 rows in set (0.01 sec) > > I keep a copy of the previous value in a variable, then check that variable > against the current value for the field. All is well. > > BUT, as soon as I tackle my real problem (on a nasty database, complex > queries, and the real problem, all hell breaks loose: > > mysql> select ordine, IF(@oldOrdine=ordine, "no", "yes") as hasChanged, > (@oldOrdine:=ordine) as newValueForMemory from aghi1, Not_needle_aux where > (GENMATERIA = 'MONOSOF' OR GENMATERIA = 'DERMALON*') and (Needle1='pre-cut' > or Needle1='stdlth' or Needle1='reel') and Needle1=Needle_code order by > ordine, SIZEMR, LENGTHMETR; > +++---+ > | ordine | hasChanged | newValueForMemory | > +++---+ > | 1 | yes| 1 | > | 1 | no | 1 | > | 1 | no | 1 | > | 1 | no | 1 | > | 1 | yes| 1 | > | 1 | no | 1 | > | 1 | no | 1 | > | 1 | no | 1 | > | 3 | yes| 3 | > | 3 | no | 3 | > | 3 | yes| 3 | > | 3 | no | 3 | > | 3 | no | 3 | > +++---+ > 13 rows in set (0.10 sec) > > WHY OH WHY is MySQL behaving like this? I tried a bunch on variations (using > two variables, applying a function on the field and ever weirder ones), to > no avail. It gets even more unpredictable, as if the variables refused to be > set and reset. > > mysql> select @a, ordine, @b:=ordin
Help! MySQL variables laugh at me
I've been frying my brain over this problem for the past 24 hours and I'll be very grateful for any hints. I'm trying to recognize when the value for a field is changing value from one record to the next. For an example, look at this: mysql> select sezione from faq limit 20; +---+ | sezione | +---+ | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Macintosh | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | | Internet | +---+ 20 rows in set (0.01 sec) As you see, the field's value is almost always "Internet", but it becomes "Macintosh" in the middle. I'd like to recognize this change (and, later, act on it). I thought I had found the solution by employing a bit of variable magic. And it seemed to work just fine: mysql> select sezione, IF(@a=sezione, "no", "yes") as hasChanged, (@a:=sezione) as newValueForMemory from faq limit 20; +---++---+ | sezione | hasChanged | newValueForMemory | +---++---+ | Internet | yes| Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Macintosh | yes| Macintosh | | Internet | yes| Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | | Internet | no | Internet | +---++---+ 20 rows in set (0.01 sec) I keep a copy of the previous value in a variable, then check that variable against the current value for the field. All is well. BUT, as soon as I tackle my real problem (on a nasty database, complex queries, and the real problem, all hell breaks loose: mysql> select ordine, IF(@oldOrdine=ordine, "no", "yes") as hasChanged, (@oldOrdine:=ordine) as newValueForMemory from aghi1, Not_needle_aux where (GENMATERIA = 'MONOSOF' OR GENMATERIA = 'DERMALON*') and (Needle1='pre-cut' or Needle1='stdlth' or Needle1='reel') and Needle1=Needle_code order by ordine, SIZEMR, LENGTHMETR; +++---+ | ordine | hasChanged | newValueForMemory | +++---+ | 1 | yes| 1 | | 1 | no | 1 | | 1 | no | 1 | | 1 | no | 1 | | 1 | yes| 1 | | 1 | no | 1 | | 1 | no | 1 | | 1 | no | 1 | | 3 | yes| 3 | | 3 | no | 3 | | 3 | yes| 3 | | 3 | no | 3 | | 3 | no | 3 | +++---+ 13 rows in set (0.10 sec) WHY OH WHY is MySQL behaving like this? I tried a bunch on variations (using two variables, applying a function on the field and ever weirder ones), to no avail. It gets even more unpredictable, as if the variables refused to be set and reset. mysql> select @a, ordine, @b:=ordine, @b, IF (@a=@b, 'Very Thin', 'Thin') as divider, @a:=@b from aghi1, Not_needle_aux where (GENMATERIA = 'MONOSOF' OR GENMATERIA = 'DERMALON*') and (Needle1='pre-cut' or Needle1='stdlth' or Needle1='reel') and Needle1=Needle_code order by ordine, SIZEMR, LENGTHMETR; +--+++--+---++ | @a | ordine | @b:=ordine | @b | divider | @a:=@b | +--+++--+---++ | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 1 | 1 |1 | Very Thin | 1 | | 1| 3 | 3 |1 | Very Thin | 1 | | 1| 3 | 3 |1 | Very Thin | 1 | | 1| 3 | 3 |1 | Very Thin | 1 | | 1| 3 | 3 |1 | Very Thin | 1 | | 1| 3 |
Re: A few queries about optimising MySQL variables
On Wed, Jan 31, 2001 at 09:12:05AM +, Basil Hussain wrote: > > I think I understand this now. The table_cache is the maximum number > of tables MySQL can keep open all the time. If your table_cache is > lower than the actual number of tables in use (in my case 64 versus > 70), then when a table that is not already open needs to be used, > MySQL must open it, and so increments the Opened_tables status > variable. In an ideal world, Opened_tables would be nil (or very > low), as MySQL would already have all the tables open, yes? Yes. > > Queries that require a full table scan (those which don't or can't > > use indexes). "Sequential scans" can mean "full table scans". > > So, basically, a large record_buffer is only useful if your queries > operate in a non-efficient fashion by not using indexes? I try to > optimise all of my queries, and I'm pretty sure that reducing it to > something like 4Mb would be alright. Basically, yes. But there are cases in which a full table scan is the fastest way to do something, and MySQL may decide to do that. > > You didn't mention the key_buffer. If configured well, you can get a > > great performance boost from it. > > My key_buffer is currently at 128Mb, but I'm considering upping that > to 192Mb or maybe even 256Mb. Probably the former, as I don't have > tables with hundereds of thousands of records to contend with. Sounds like you've got a good understanding of what's going on now. Good luck. :-) Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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: A few queries about optimising MySQL variables
Hi, >> 1. I think I need to optimise table_cache. It's currently set to the >> default of 64. There are a total of 70 tables on the system. The >> manual says you should increase table_cache if the Opened_tables >> status variable is big. How large is 'big'? This value is currently >> 274 on my system. By how much should I increase table_cache? As a >> guide, the my-large.cnf sample config sets this to 256 - would this >> suit my setup? > > Sounds reasonable. It shouldn't make a big impact, but it shouldn't > hurt either. I think I understand this now. The table_cache is the maximum number of tables MySQL can keep open all the time. If your table_cache is lower than the actual number of tables in use (in my case 64 versus 70), then when a table that is not already open needs to be used, MySQL must open it, and so increments the Opened_tables status variable. In an ideal world, Opened_tables would be nil (or very low), as MySQL would already have all the tables open, yes? My Opened_tables figure goes up by about 150-200 every day, so I would assume that a table_cache of 64 is no-way enough! So, as you say, 256 can't hurt! I think I'll do that. >> 2. I currently have sort_buffer at 8 MB. The sample 'large' config >> suggests 1 Mb. The manual says this should be increased to improve >> sorts and grouping performance - which my queries do a lot >> of. Performance seems fine at the moment, but am I unnecessarily >> using too much memory? > > It is allocated on a per-thread basis but only when needed. You can > try to increase it and see if you notice a difference. But with 512MB > and few queries doing sorts, it may not be a big deal. However, if a > lot of your queries require sorting large amounts of data, you may > really benefit. So, I suppose it's best to leave it at 8Mb then. > Really, it's best to run some benchmarks that reflect the workload on > your server and tweak the parameters to see what difference (if any) > the changes make. Hmm, yes. I might have to do that at some point. >> 3. My record_buffer is set to 16 Mb (at the suggestion of someone >> else). The sample 'large' config suggests also 1 Mb. I'm not quite >> sure I understand what this affects. The manual states that: "if you >> do many sequential scans, you may want to increase this value" - but >> what kind of query would make a sequential scan? > > Queries that require a full table scan (those which don't or can't use > indexes). "Sequential scans" can mean "full table scans". So, basically, a large record_buffer is only useful if your queries operate in a non-efficient fashion by not using indexes? I try to optimise all of my queries, and I'm pretty sure that reducing it to something like 4Mb would be alright. > You didn't mention the key_buffer. If configured well, you can get a > great performance boost from it. My key_buffer is currently at 128Mb, but I'm considering upping that to 192Mb or maybe even 256Mb. Probably the former, as I don't have tables with hundereds of thousands of records to contend with. Regards, Basil Hussain ([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
Re: A few queries about optimising MySQL variables
On Tue, Jan 30, 2001 at 10:56:56AM +, Basil Hussain wrote: > > Having just upgraded my MySQL server to the latest 3.23.32 version, > I thought it would be a good idea to have a review of the > configuration and try to optimise the setup further. I hope someone > can help me by answering a few queries I have. > > Here's a bit of info about the spec. of the server: > > * Pentium III 600 Mhz > * 512 Mb RAM > * Data directories stored on RAID 0 partition > * RedHat Linux 6.2 (with 2.2.16-22 kernel, retrofitted from RH7) Got it. > The majority of the queries to the server are from a few web servers > using PHP4, connecting using persistant connections (it normally > runs with approx. 100-150 idle threads because of this). > > So, I have a few questions: > > 1. I think I need to optimise table_cache. It's currently set to the > default of 64. There are a total of 70 tables on the system. The > manual says you should increase table_cache if the Opened_tables > status variable is big. How large is 'big'? This value is currently > 274 on my system. By how much should I increase table_cache? As a > guide, the my-large.cnf sample config sets this to 256 - would this > suit my setup? Sounds reasonable. It shouldn't make a big impact, but it shouldn't hurt either. > 2. I currently have sort_buffer at 8 MB. The sample 'large' config > suggests 1 Mb. The manual says this should be increased to improve > sorts and grouping performance - which my queries do a lot > of. Performance seems fine at the moment, but am I unnecessarily > using too much memory? It is allocated on a per-thread basis but only when needed. You can try to increase it and see if you notice a difference. But with 512MB and few queries doing sorts, it may not be a big deal. However, if a lot of your queries require sorting large amounts of data, you may really benefit. Really, it's best to run some benchmarks that reflect the workload on your server and tweak the parameters to see what difference (if any) the changes make. > 3. My record_buffer is set to 16 Mb (at the suggestion of someone > else). The sample 'large' config suggests also 1 Mb. I'm not quite > sure I understand what this affects. The manual states that: "if you > do many sequential scans, you may want to increase this value" - but > what kind of query would make a sequential scan? Queries that require a full table scan (those which don't or can't use indexes). "Sequential scans" can mean "full table scans". > Any answers gratefully accepted! You didn't mention the key_buffer. If configured well, you can get a great performance boost from it. Jeremy -- Jeremy D. Zawodny, <[EMAIL PROTECTED]> Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - 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
A few queries about optimising MySQL variables
Hi all, Having just upgraded my MySQL server to the latest 3.23.32 version, I thought it would be a good idea to have a review of the configuration and try to optimise the setup further. I hope someone can help me by answering a few queries I have. Here's a bit of info about the spec. of the server: * Pentium III 600 Mhz * 512 Mb RAM * Data directories stored on RAID 0 partition * RedHat Linux 6.2 (with 2.2.16-22 kernel, retrofitted from RH7) The majority of the queries to the server are from a few web servers using PHP4, connecting using persistant connections (it normally runs with approx. 100-150 idle threads because of this). So, I have a few questions: 1. I think I need to optimise table_cache. It's currently set to the default of 64. There are a total of 70 tables on the system. The manual says you should increase table_cache if the Opened_tables status variable is big. How large is 'big'? This value is currently 274 on my system. By how much should I increase table_cache? As a guide, the my-large.cnf sample config sets this to 256 - would this suit my setup? 2. I currently have sort_buffer at 8 MB. The sample 'large' config suggests 1 Mb. The manual says this should be increased to improve sorts and grouping performance - which my queries do a lot of. Performance seems fine at the moment, but am I unnecessarily using too much memory? 3. My record_buffer is set to 16 Mb (at the suggestion of someone else). The sample 'large' config suggests also 1 Mb. I'm not quite sure I understand what this affects. The manual states that: "if you do many sequential scans, you may want to increase this value" - but what kind of query would make a sequential scan? Should I reduce this value? Any answers gratefully accepted! Regards, Basil Hussain ([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
