Re: escape quotes
Lex Thoonen,all UPDATE `jos_content` SET `introtext` = replace(`introtext`,h2 style=\margin: 0px 0px 5px; padding: 0px; font-family: 'Trebuchet MS',Arial,Helvetica,sans-serif; font-weight: bold; line-height: 30px; font-size: 30px; color: #b4b4be;\,h2) just gives me 0 results... How about this? UPDATE `jos_content` set `introtext` = h2 where `introtext` = h2 style=\margin: 0px 0px 5px; padding: 0px; font-family: 'TrebuchetMS',Arial,Helvetica,sans-serif; font-weight: bold; line-height: 30px;font-size: 30px; color: #b4b4be;\; (this query executed by MySQL ver 5.1.34-community) yusuke kadota -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
mysqldump Unknown command '\''.
We dumped a mysql 4.1.22 database using the mysqldump binaries from its version, We tried to import this in 5.0.82 and we have the error. mysqldump Unknown command '\''. It seems the the extended import (multiple inserts) is somewhere too big since the single insert dump works but takes ages and we need to import this 300gb database in a couple of days. Any suggestions? Thanks -- Claudio
Req. suitable .cnf file for Server used by 2000 users daily
Hi, Will you plesae guide me ? We are about to launch one website whose database is in MySQL. I am very exited about the server setting specially about .cnf file. I have below hardware and .cnf details. Will you please guide me is the .cnf file details sufficient to support current hardware. Initially 2000 users will visit this site everyday. Hardware and OS * Operating System : Red Hat Fedora Core 8 Processor : Intel Core 2 Quad - 2.83 GHz, RAM : 4 GB Total Disk Space : 600 GB (300 GB usable) RAID : RAID1 Disk Drive(s) : 300 GB (Drive #1), 300 GB (Drive #2) Bandwidth Quota : 500 GB Firewall : PIX 501 Version : 5.0.81-community-log Version_comment : MySQL Community Edition (GPL) Version Compile Machine : i686 Version Compile OS : pc-linux-gnu my.cnf details *** [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 key_buffer = 16M key_buffer_size=4M sort_buffer_size=2M query_cache_size=64M log-bin log_queries_not_using_indexes=1 long_query_time=1 log_slow_queries=slowQry.log join_buffer_size=4M max_connections=150 max_allowed_packet = 32M table_cache = 256 net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 8M thread_stack=5M thread_cache_size=128M connect_timeout=30 query_cache_limit=32M log-error # Comment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 4M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 16M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Thanks in advance Regards Jeetendra Ranjan
Re: mysqldump Unknown command '\''.
mysqldump is by no means the fastest way to move data between systems. For a bulk job of this magnitude, try this: http://dev.mysql.com/doc/refman/5.1/en/load-data.html - michael dykman On Wed, Oct 14, 2009 at 3:59 AM, Claudio Nanni claudio.na...@gmail.com wrote: We dumped a mysql 4.1.22 database using the mysqldump binaries from its version, We tried to import this in 5.0.82 and we have the error. mysqldump Unknown command '\''. It seems the the extended import (multiple inserts) is somewhere too big since the single insert dump works but takes ages and we need to import this 300gb database in a couple of days. Any suggestions? Thanks -- Claudio -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL Cluster / NDB MyISAM mix
Hi guys. Ok lets say i've 2 server running MySQL, NDB node and NDB manager on each. Now I don't want to convert all tables to NDB instead I leave a few as MyISAM. Is it officially supported if I setup a master-master replication between the two MySQL instances and add ignore entries for all NDB tables? So basically I would convert all important tables to NDB and leave other tables as MyISAM, but they would still be 'synced' via replication. I've tested it and it look like it works... but... does it work by coincidence? :) Thanks, chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql service will not stop
I have mysql 5.0.51a running on a linux 2.6.26 box. The service my6sql stop command fails and all that appears in the log or err log is the following... what can I do to repair this? 091014 17:00:24guardian: instance 'mysqld' is running, set state to STARTED. 091014 17:07:40The instance 'mysqld' is being stopped forcibly. Normallyit should not happen. Probably the instance has beenhanging. You should also check your IM setup 091014 17:07:40Listener_thread::run(): shutdown requested, exiting... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql service will not stop
It is Innodb. but most of our other identical database deployments seem to shutdown immediately, just not this one using the same image... Since dirty buffers needs to flush out. Thanks, Dilipkumar Database Engineering Mphasis, an EDS company phone: +91-44-6637 3167 mailto: dilipkumar.par...@eds.com Pager mail:9884430...@nma.vodafone.in -Original Message- From: Bryan Cantwell [mailto:bcantw...@firescope.com] Sent: Wednesday, October 14, 2009 8:40 PM To: mysql@lists.mysql.com Subject: Mysql service will not stop I have mysql 5.0.51a running on a linux 2.6.26 box. The service my6sql stop command fails and all that appears in the log or err log is the following... what can I do to repair this? 091014 17:00:24guardian: instance 'mysqld' is running, set state to STARTED. 091014 17:07:40The instance 'mysqld' is being stopped forcibly. Normallyit should not happen. Probably the instance has beenhanging. You should also check your IM setup 091014 17:07:40Listener_thread::run(): shutdown requested, exiting... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql service will not stop
Brian, You could spend a lot of time crawling through version release notes looking for something which might refer to your issue, or you might just upgrade to 5.0.86-0 which is the latest 5.0. There have been close to 2 years worth of bug-fixes since 5.0.51a - michael dykman On Wed, Oct 14, 2009 at 11:10 AM, Bryan Cantwell bcantw...@firescope.com wrote: I have mysql 5.0.51a running on a linux 2.6.26 box. The service my6sql stop command fails and all that appears in the log or err log is the following... what can I do to repair this? 091014 17:00:24 guardian: instance 'mysqld' is running, set state to STARTED. 091014 17:07:40 The instance 'mysqld' is being stopped forcibly. Normallyit should not happen. Probably the instance has beenhanging. You should also check your IM setup 091014 17:07:40 Listener_thread::run(): shutdown requested, exiting... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: MySQL Cluster / NDB MyISAM mix
I can think of no reason why this shouldn't work, My administrator colleagues would probably beat me with 2x4's for handing them such a delicate construct to maintain in production but it seems perfectly feasible to me :-) - michael dykman On Wed, Oct 14, 2009 at 11:03 AM, Christian Meisinger c.meisin...@livingliquid.com wrote: Hi guys. Ok lets say i've 2 server running MySQL, NDB node and NDB manager on each. Now I don't want to convert all tables to NDB instead I leave a few as MyISAM. Is it officially supported if I setup a master-master replication between the two MySQL instances and add ignore entries for all NDB tables? So basically I would convert all important tables to NDB and leave other tables as MyISAM, but they would still be 'synced' via replication. I've tested it and it look like it works... but... does it work by coincidence? :) Thanks, chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com Don’t worry about people stealing your ideas. If they’re any good, you’ll have to ram them down their throats! Howard Aiken -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL GUI Tools
What are the best MySQL GUI tools available at the moment, for example SQLyog. Thanks Neil
Re: error code 139 innodb
Kyong Kim kykimdba at gmail.com writes: For sure all of our columns combined do not exceed 64K. We're using latin 1 character set. I don't think we would be running into the 8K limit on row length since the culprit seems to be data being inserted into VARCHAR(255) column. Can you show us your create table? This might be a known bug http://bugs.mysql.com/bug.php?id=25945 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: About NoWait of MySql
門田 祐輔 yusuke-kadota at indexweb.co.jp writes: 1. Does MySQL have lock option like Oracle's nowait or alternative way. acctually I'd not like to use innodb_lock_wait_timeout configuration. for some reasons... I think that non-blocking queries are not supported by mysql driver natively. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Unable to Login to MySQL 5
Hello there, I have been using MySQL 5 on my OS X Leopard based laptop for quite some time now... Didn't have a password set and my login was root on localhost. Downloaded just now the Spring distribution was following the steps to run the petclinic sample application. Ran the following scripts to set up petclinic: CREATE DATABASE petclinic; GRANT ALL PRIVILEGES ON petclinic.* TO r...@localhost IDENTIFIED BY 'root'; Now, when I try to login to MySQL using the SequelPro client: http://www.sequelpro.com/ I am unable to login! This is the error Sequel Pro and also the command line states when I login as root with no password (which was working before I ran the petclinic script): Unable to connect to host because access was denied. Double-check your username and password and ensure that access from your current location is permitted. MySQL said: Access denied for user 'root'@'localhost' (using password: NO) Does anyone know what's wrong? I am not unable to access any of my local databases! How can I revert this? When I tried (from the command line): mysqladmin -u root password 'newpassword' I got this message: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Would really appreciate it if someone could help me resolve this issue... Thank you for taking the time to read this, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Benetl, a free ETL tool for files using postgreSQL, is out in version 3.2
Dear all, Benetl, a free ETL tool for files using postgreSQL and MySQL, is out in version 3.2. You can freely download it at : www.benetl.net This new version is now supporting Java SE 6 and using memory arguments for JVM. You can learn more about ETL tools at: http://en.wikipedia.org/wiki/Extract,_transform,_load Thanks for your interest. Regards, -- Benoît Carpentier www.benetl.net Founder of Benetl Java project manager -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Unable to Login to MySQL 5
Add -p to your mysqladmin statement to change password and use 'root' as password. you wanted to give extra grants to root but you just set the password for r...@localhost. you don't need to grant anything to root, usually. cheers Claudio On Oct 14, 2009 9:07 PM, James Dekker james.dek...@gmail.com wrote: Hello there, I have been using MySQL 5 on my OS X Leopard based laptop for quite some time now... Didn't have a password set and my login was root on localhost. Downloaded just now the Spring distribution was following the steps to run the petclinic sample application. Ran the following scripts to set up petclinic: CREATE DATABASE petclinic; GRANT ALL PRIVILEGES ON petclinic.* TO r...@localhost IDENTIFIED BY 'root'; Now, when I try to login to MySQL using the SequelPro client: http://www.sequelpro.com/ I am unable to login! This is the error Sequel Pro and also the command line states when I login as root with no password (which was working before I ran the petclinic script): Unable to connect to host because access was denied. Double-check your username and password and ensure that access from your current location is permitted. MySQL said: Access denied for user 'root'@'localhost' (using password: NO) Does anyone know what's wrong? I am not unable to access any of my local databases! How can I revert this? When I tried (from the command line): mysqladmin -u root password 'newpassword' I got this message: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Would really appreciate it if someone could help me resolve this issue... Thank you for taking the time to read this, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: Unable to Login to MySQL 5
Claudio, Thank you for the tip... Unfortunately, I tried your suggestion and it still didn't work! mysqladmin -u root -p root Enter password: (I entered root here) mysqladmin: Unknown command: 'root' When I tried it again (this time leaving the password as blank): mysqladmin -u root -p root Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Is there anyway to get past this issue? I really wish that I didn't grant the extra privileges! :-( Happy programming, James On Oct 14, 2009, at 12:25 PM, Claudio Nanni wrote: Add -p to your mysqladmin statement to change password and use 'root' as password. you wanted to give extra grants to root but you just set the password for r...@localhost. you don't need to grant anything to root, usually. cheers Claudio On Oct 14, 2009 9:07 PM, James Dekker james.dek...@gmail.com wrote: Hello there, I have been using MySQL 5 on my OS X Leopard based laptop for quite some time now... Didn't have a password set and my login was root on localhost. Downloaded just now the Spring distribution was following the steps to run the petclinic sample application. Ran the following scripts to set up petclinic: CREATE DATABASE petclinic; GRANT ALL PRIVILEGES ON petclinic.* TO r...@localhost IDENTIFIED BY 'root'; Now, when I try to login to MySQL using the SequelPro client: http://www.sequelpro.com/ I am unable to login! This is the error Sequel Pro and also the command line states when I login as root with no password (which was working before I ran the petclinic script): Unable to connect to host because access was denied. Double-check your username and password and ensure that access from your current location is permitted. MySQL said: Access denied for user 'root'@'localhost' (using password: NO) Does anyone know what's wrong? I am not unable to access any of my local databases! How can I revert this? When I tried (from the command line): mysqladmin -u root password 'newpassword' I got this message: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Would really appreciate it if someone could help me resolve this issue... Thank you for taking the time to read this, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: Unable to Login to MySQL 5
Fixed it! Did the following: mysql -u root -p mysql use mysql; update user set password=PASSWORD(password) where User='root'; mysql flush privileges; mysql quit I am now able to login using my SequelPro MySQL client! :-) Happy programming, James On Oct 14, 2009, at 1:00 PM, James Dekker wrote: Claudio, Thank you for the tip... Unfortunately, I tried your suggestion and it still didn't work! mysqladmin -u root -p root Enter password: (I entered root here) mysqladmin: Unknown command: 'root' When I tried it again (this time leaving the password as blank): mysqladmin -u root -p root Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Is there anyway to get past this issue? I really wish that I didn't grant the extra privileges! :-( Happy programming, James On Oct 14, 2009, at 12:25 PM, Claudio Nanni wrote: Add -p to your mysqladmin statement to change password and use 'root' as password. you wanted to give extra grants to root but you just set the password for r...@localhost. you don't need to grant anything to root, usually. cheers Claudio On Oct 14, 2009 9:07 PM, James Dekker james.dek...@gmail.com wrote: Hello there, I have been using MySQL 5 on my OS X Leopard based laptop for quite some time now... Didn't have a password set and my login was root on localhost. Downloaded just now the Spring distribution was following the steps to run the petclinic sample application. Ran the following scripts to set up petclinic: CREATE DATABASE petclinic; GRANT ALL PRIVILEGES ON petclinic.* TO r...@localhost IDENTIFIED BY 'root'; Now, when I try to login to MySQL using the SequelPro client: http://www.sequelpro.com/ I am unable to login! This is the error Sequel Pro and also the command line states when I login as root with no password (which was working before I ran the petclinic script): Unable to connect to host because access was denied. Double-check your username and password and ensure that access from your current location is permitted. MySQL said: Access denied for user 'root'@'localhost' (using password: NO) Does anyone know what's wrong? I am not unable to access any of my local databases! How can I revert this? When I tried (from the command line): mysqladmin -u root password 'newpassword' I got this message: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Would really appreciate it if someone could help me resolve this issue... Thank you for taking the time to read this, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
Re: Unable to Login to MySQL 5
At 03:00 PM 10/14/2009, James Dekker wrote: Claudio, Thank you for the tip... Unfortunately, I tried your suggestion and it still didn't work! mysqladmin -u root -p root Enter password: (I entered root here) mysqladmin: Unknown command: 'root' When I tried it again (this time leaving the password as blank): mysqladmin -u root -p root Enter password: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Is there anyway to get past this issue? I really wish that I didn't grant the extra privileges! :-( You should have typed: mysqladmin -uroot -p There is no space between the -u and root. There is a space before the -p. Mike Happy programming, James On Oct 14, 2009, at 12:25 PM, Claudio Nanni wrote: Add -p to your mysqladmin statement to change password and use 'root' as password. you wanted to give extra grants to root but you just set the password for r...@localhost. you don't need to grant anything to root, usually. cheers Claudio On Oct 14, 2009 9:07 PM, James Dekker james.dek...@gmail.com wrote: Hello there, I have been using MySQL 5 on my OS X Leopard based laptop for quite some time now... Didn't have a password set and my login was root on localhost. Downloaded just now the Spring distribution was following the steps to run the petclinic sample application. Ran the following scripts to set up petclinic: CREATE DATABASE petclinic; GRANT ALL PRIVILEGES ON petclinic.* TO r...@localhost IDENTIFIED BY 'root'; Now, when I try to login to MySQL using the SequelPro client: http://www.sequelpro.com/ I am unable to login! This is the error Sequel Pro and also the command line states when I login as root with no password (which was working before I ran the petclinic script): Unable to connect to host because access was denied. Double-check your username and password and ensure that access from your current location is permitted. MySQL said: Access denied for user 'root'@'localhost' (using password: NO) Does anyone know what's wrong? I am not unable to access any of my local databases! How can I revert this? When I tried (from the command line): mysqladmin -u root password 'newpassword' I got this message: mysqladmin: connect to server at 'localhost' failed error: 'Access denied for user 'root'@'localhost' (using password: NO)' Would really appreciate it if someone could help me resolve this issue... Thank you for taking the time to read this, James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Req. suitable .cnf file for Server used by 2000 users daily
jeetendra.ran...@sampatti.com wrote: Hi, Will you plesae guide me ? We are about to launch one website whose database is in MySQL. I am very exited about the server setting specially about .cnf file. I have below hardware and .cnf details. Will you please guide me is the .cnf file details sufficient to support current hardware. Initially 2000 users will visit this site everyday. Hardware and OS * ... snip ... Unfortunately, you have not asked us an answerable question. There are too many unknowns in your setup for us to estimate how much database usage per customer your site will create. If it's a single query per visitor, then you probably can omit any tuning at all. If each visit requires dozens of queries each using multiple tables then you have a totally different usage profile. It all boils down to what you are doing to the database, not how many people are doing it. What can you tell us about your application, how many rows of data you plan to manage, if your data is mostly MyISAM or InnoDB, and what performance numbers you have achieved so far using your lab equipment and a simulated user load. Also, is your MySQL database sharing the same box as your web server or are they located in separate machines? At that point, we may be able to make a reasonable guess about your situation. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Unable to Login to MySQL 5
You have set the root password on your mysql server to root with this command GRANT ALL PRIVILEGES ON petclinic.* TO r...@localhost IDENTIFIED BY 'root'; The IDENTIFIED BY clause of a grant sets the password. You shouldn't run a system without a root password anyway. Regards John Daisley Mobile +44(0)7812 451238 Email j...@butterflysystems.co.uk Certified MySQL 5 Database Administrator (CMDBA) Certified MySQL 5 Developer Cognos BI Developer --- Sent from HP IPAQ mobile device. -Original Message- From: James Dekker james.dek...@gmail.com Sent: 14 October 2009 20:06 To: mysql@lists.mysql.com Subject: Unable to Login to MySQL 5 Hello there, I have been using MySQL 5 on my OS X Leopard based laptop for quite some time now... Didn't have a password set and my login was root on localhost. Downloaded just now the Spring distribution was following the steps to run the petclinic sample application. Ran the following scripts to set up petclinic: CREATE DATABASE petclinic; GRANT ALL PRIVILEGES ON petclinic.* TO r...@localhost IDENTIFIED BY 'root'; Now, when I try to login to MySQL using the SequelPro client: http://www.sequelpro.com/ I am unable to login! This is the error Sequel Pro and also the command line states when I login as root with no password (which was working before I ran the petclinic script): Unable to connect to host because access was denied. [The entire original message is not included] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Req. suitable .cnf file for Server used by 2000 users daily
Hi, This script might help with some tuning suggestions, run it after you have some production traffic running against your database. https://launchpad.net/mysql-tuning-primer Also you should enable the slow query log, so you can capture queries to be optimized: http://dev.mysql.com/doc/mysql/en/Slow_query_log.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Regards, Gavin Towey -Original Message- From: jeetendra.ran...@sampatti.com [mailto:jeetendra.ran...@sampatti.com] Sent: Wednesday, October 14, 2009 3:21 AM To: mysql@lists.mysql.com Subject: Req. suitable .cnf file for Server used by 2000 users daily Hi, Will you plesae guide me ? We are about to launch one website whose database is in MySQL. I am very exited about the server setting specially about .cnf file. I have below hardware and .cnf details. Will you please guide me is the .cnf file details sufficient to support current hardware. Initially 2000 users will visit this site everyday. Hardware and OS * Operating System : Red Hat Fedora Core 8 Processor : Intel Core 2 Quad - 2.83 GHz, RAM : 4 GB Total Disk Space : 600 GB (300 GB usable) RAID : RAID1 Disk Drive(s) : 300 GB (Drive #1), 300 GB (Drive #2) Bandwidth Quota : 500 GB Firewall : PIX 501 Version : 5.0.81-community-log Version_comment : MySQL Community Edition (GPL) Version Compile Machine : i686 Version Compile OS : pc-linux-gnu my.cnf details *** [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 key_buffer = 16M key_buffer_size=4M sort_buffer_size=2M query_cache_size=64M log-bin log_queries_not_using_indexes=1 long_query_time=1 log_slow_queries=slowQry.log join_buffer_size=4M max_connections=150 max_allowed_packet = 32M table_cache = 256 net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 2M myisam_sort_buffer_size = 8M thread_stack=5M thread_cache_size=128M connect_timeout=30 query_cache_limit=32M log-error # Comment the following if you are using InnoDB tables innodb_data_home_dir = /var/lib/mysql/ innodb_data_file_path = ibdata1:10M:autoextend innodb_log_group_home_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high innodb_buffer_pool_size = 4M innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 16M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 1 innodb_lock_wait_timeout = 50 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Thanks in advance Regards Jeetendra Ranjan The information contained in this transmission may contain privileged and confidential information. It is intended only for the use of the person(s) named above. If you are not the intended recipient, you are hereby notified that any review, dissemination, distribution or duplication of this communication is strictly prohibited. If you are not the intended recipient, please contact the sender by reply email and destroy all copies of the original message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Re: Req. suitable .cnf file for Server used by 2000 users daily
Hi Shawn, Thanks for your reply. This is basically a marketing website for different products. The site will be visited by the marketer and the customer. Here is answer as you asked: 1) The customer and the marketer will visit only one database and they have no right to create database by them. 2) Its multiple query per visitor which uses tables in only one database. 3) Few tables will be very haeavy and there may be millions of rows in one tables. 4) Few heavy tables are Innodb and few are MyISAM 5) Yes, MySQL database sharing the same box as web server. I mostly concern about connectoins. Initially 2000 visitor including marketer and customer will visit this site. Thanks in advance. Regards Jeetendra Ranjan At 21:21 14/10/2009, Shawn Green wrote: jeetendra.ran...@sampatti.com wrote: Hi, Will you plesae guide me ? We are about to launch one website whose database is in MySQL. I am very exited about the server setting specially about .cnf file. I have below hardware and .cnf details. Will you please guide me is the .cnf file details sufficient to support current hardware. Initially 2000 users will visit this site everyday. Hardware and OS * ... snip ... Unfortunately, you have not asked us an answerable question. There are too many unknowns in your setup for us to estimate how much database usage per customer your site will create. If it's a single query per visitor, then you probably can omit any tuning at all. If each visit requires dozens of queries each using multiple tables then you have a totally different usage profile. It all boils down to what you are doing to the database, not how many people are doing it. What can you tell us about your application, how many rows of data you plan to manage, if your data is mostly MyISAM or InnoDB, and what performance numbers you have achieved so far using your lab equipment and a simulated user load. Also, is your MySQL database sharing the same box as your web server or are they located in separate machines? At that point, we may be able to make a reasonable guess about your situation. -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN
