Re: upgrading mysql
Hi, The step 6 in simple terms is Here we need to build two server ( both master and slave ). Instead of building two server as it takes double the time of building in one server. After building an server, make a copy of the first server files at OS level and copy it to the server and start the same. Configure the replication between the two server. By doing this, We will save the import time in second server. Thanks Suresh Kuna MySQL DBA On Wed, Jan 13, 2010 at 3:58 AM, Tom Worster wrote: > Frankly, I didn't entirely understand what you were proposing. I got lost > around step 6. > > Is the issue total time for the procedure or service downtime? > > > On 1/12/10 12:58 PM, "Lawrence Sorrillo" wrote: > > > This is two upgrades done in sequence(the reload takes about three hours > > per machine) . I can do what I am proposing in parallel. > > > > Do you see it as problematic? > > > > ~Lawrence > > > > > > Tom Worster wrote: > >> How about: > >> > >> 1 shut down the slave, upgrade it, restart it, let it catch up. > >> > >> 2 shut down the master, upgrade it, restart it, let the slave catch up. > >> > >> ? > >> > >> > >> > >> > >> > >> On 1/12/10 12:34 PM, "Lawrence Sorrillo" wrote: > >> > >> > >>> Hi: > >>> > >>> I want to upgrade a master and slave server from mysql 4.1 to mysql > 5.1. > >>> > >>> I want to so something like follows: > >>> > >>> 1. Stop all write access to the master server. > >>> 2. Ensure that replication on the slave is caught up to the last change > >>> on the master. > >>> 3. stop binary logging on the master. > >>> 4. stop replication on the slave. > >>> 5. dump the master, stop old 4.1 server, start new 5.1 server and > reload > >>> master dump file under 5.1 server ( binary logging is turned off) > >>> 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload > >>> slave dump file under 5.1 server. > >>> 7. After loading is complete, test then start binary logging on master > >>> while still preventing updates to updates. > >>> 8. After loading slave, test then start slave (get configs in place and > >>> restart server). > >>> > >>> I am thinking that in this scenario I dont have to bother with > recording > >>> binlog file names and position etc etc. > >>> That both servers will have the same databases abd replication and > >>> binary logging will start on the two databases with no data loss and > >>> continue forward. > >>> > >>> > >>> Comments? > >>> > >>> ~Lawrence > >>> > >>> > >>> > >>> > >> > >> > >> > > > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=sureshkumar...@gmail.com > > -- Thanks Suresh Kuna MySQL DBA
Re: upgrading mysql
Frankly, I didn't entirely understand what you were proposing. I got lost around step 6. Is the issue total time for the procedure or service downtime? On 1/12/10 12:58 PM, "Lawrence Sorrillo" wrote: > This is two upgrades done in sequence(the reload takes about three hours > per machine) . I can do what I am proposing in parallel. > > Do you see it as problematic? > > ~Lawrence > > > Tom Worster wrote: >> How about: >> >> 1 shut down the slave, upgrade it, restart it, let it catch up. >> >> 2 shut down the master, upgrade it, restart it, let the slave catch up. >> >> ? >> >> >> >> >> >> On 1/12/10 12:34 PM, "Lawrence Sorrillo" wrote: >> >> >>> Hi: >>> >>> I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. >>> >>> I want to so something like follows: >>> >>> 1. Stop all write access to the master server. >>> 2. Ensure that replication on the slave is caught up to the last change >>> on the master. >>> 3. stop binary logging on the master. >>> 4. stop replication on the slave. >>> 5. dump the master, stop old 4.1 server, start new 5.1 server and reload >>> master dump file under 5.1 server ( binary logging is turned off) >>> 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload >>> slave dump file under 5.1 server. >>> 7. After loading is complete, test then start binary logging on master >>> while still preventing updates to updates. >>> 8. After loading slave, test then start slave (get configs in place and >>> restart server). >>> >>> I am thinking that in this scenario I dont have to bother with recording >>> binlog file names and position etc etc. >>> That both servers will have the same databases abd replication and >>> binary logging will start on the two databases with no data loss and >>> continue forward. >>> >>> >>> Comments? >>> >>> ~Lawrence >>> >>> >>> >>> >> >> >> > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: file per table performance
There are a few articles on this at MySQL Performance Blog: http://www.mysqlperformanceblog.com/?s=innodb_file_per_table+performance On Tue, Jan 12, 2010 at 3:05 PM, Bryan Cantwell wrote: > Anyone have information they can provide on the performance hit of using > innodb_file_per_table? > I'd assume that since there are many individual tables that this would slow > performance, but perhaps not. > In a huge database, is this not a good idea, or a better one? > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net > > -- - Johnny Withers 601.209.4985 joh...@pixelated.net
file per table performance
Anyone have information they can provide on the performance hit of using innodb_file_per_table? I'd assume that since there are many individual tables that this would slow performance, but perhaps not. In a huge database, is this not a good idea, or a better one? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading mysql
On Jan 12, 2010, at 12:36 PM, Lawrence Sorrillo wrote: > Hi: > > I want to ensure that right after the reload that the same data is present in > both the master and the slave. They are in perfect sync. Then I think its > safe to consider starting binary logging and replication etc. And after these > are started, changes can start? > > And in setting up replication in this manner I would not use the CHANGE > MASTER... I will just > > master-host=xxx.xxx.xxx.xxx > master-connect-retry=60 > master-user=auser > master-password=apassword > > in the my.cnf file and restart the slave server. From there it should start > reading the binary logs and committing changes properly. > > Is this correct? You're upgrading to MySQL 5.1, for which several of those options no longer have any effect. Better to use CHANGE MASTER. See: http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html http://dev.mysql.com/doc/refman/5.1/en/news-5-1-17.html -- Paul DuBois Sun Microsystems / MySQL Documentation Team Madison, Wisconsin, USA www.mysql.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: upgrading mysql
Hi: I want to ensure that right after the reload that the same data is present in both the master and the slave. They are in perfect sync. Then I think its safe to consider starting binary logging and replication etc. And after these are started, changes can start? And in setting up replication in this manner I would not use the CHANGE MASTER... I will just master-host=xxx.xxx.xxx.xxx master-connect-retry=60 master-user=auser master-password=apassword in the my.cnf file and restart the slave server. From there it should start reading the binary logs and committing changes properly. Is this correct? ~Lawrence Shawn Green wrote: Lawrence Sorrillo wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. ok 2. Ensure that replication on the slave is caught up to the last change on the master. why? You are just going to replace it later. 3. stop binary logging on the master. why? You can just disconnect the slave 4. stop replication on the slave. You can do this at step 2. Just issue STOP SLAVE IO_THREAD; The SQL thread can keep moving along. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) Yes. No need to create binary logs for the rebuild. 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. There is a faster way. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. Once you have QA-ed your new 5.1 master, you can shut it down then copy the entire image (binaries and all) directly to the slave machine. This is much faster than rebuilding from a dump and it ensures that you have identical data to start replication with. After the copy, then restart the master with binary logging. 8. After loading slave, test then start slave (get configs in place and restart server). Yes, it's always good to test any server image before putting it online. The CHANGE MASTER TO command to use for the slave will be at position 4 of the first binary log created after the binary image was captured. I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. You are correct. Because you are re-imaging your slave from your master, there is no need to track binary log or relay log positions. See also: http://dev.mysql.com/doc/refman/5.1/en/replication-howto-rawdata.html ** SAFETY ADVICE ** - always ensure you have a clean binary backup of any server you want to perform major maintenance to. In the off-chance that something does happen to go wrong, you will have it available for the fastest possible restore-to-original-state -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: upgrading mysql
Lawrence Sorrillo wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. ok 2. Ensure that replication on the slave is caught up to the last change on the master. why? You are just going to replace it later. 3. stop binary logging on the master. why? You can just disconnect the slave 4. stop replication on the slave. You can do this at step 2. Just issue STOP SLAVE IO_THREAD; The SQL thread can keep moving along. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) Yes. No need to create binary logs for the rebuild. 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. There is a faster way. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. Once you have QA-ed your new 5.1 master, you can shut it down then copy the entire image (binaries and all) directly to the slave machine. This is much faster than rebuilding from a dump and it ensures that you have identical data to start replication with. After the copy, then restart the master with binary logging. 8. After loading slave, test then start slave (get configs in place and restart server). Yes, it's always good to test any server image before putting it online. The CHANGE MASTER TO command to use for the slave will be at position 4 of the first binary log created after the binary image was captured. I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. You are correct. Because you are re-imaging your slave from your master, there is no need to track binary log or relay log positions. See also: http://dev.mysql.com/doc/refman/5.1/en/replication-howto-rawdata.html ** SAFETY ADVICE ** - always ensure you have a clean binary backup of any server you want to perform major maintenance to. In the off-chance that something does happen to go wrong, you will have it available for the fastest possible restore-to-original-state -- 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: upgrading mysql
This is two upgrades done in sequence(the reload takes about three hours per machine) . I can do what I am proposing in parallel. Do you see it as problematic? ~Lawrence Tom Worster wrote: How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, "Lawrence Sorrillo" wrote: Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: upgrading mysql
Also see http://dev.mysql.con/doc/refman/5.0/en/mysql-upgrade.html. And make sure you make a backup before you do anything :) -Original Message- From: Tom Worster [mailto:f...@thefsb.org] Sent: Tuesday, January 12, 2010 10:47 AM To: Lawrence Sorrillo; mysql@lists.mysql.com Subject: Re: upgrading mysql How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, "Lawrence Sorrillo" wrote: > Hi: > > I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. > > I want to so something like follows: > > 1. Stop all write access to the master server. > 2. Ensure that replication on the slave is caught up to the last change > on the master. > 3. stop binary logging on the master. > 4. stop replication on the slave. > 5. dump the master, stop old 4.1 server, start new 5.1 server and reload > master dump file under 5.1 server ( binary logging is turned off) > 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload > slave dump file under 5.1 server. > 7. After loading is complete, test then start binary logging on master > while still preventing updates to updates. > 8. After loading slave, test then start slave (get configs in place and > restart server). > > I am thinking that in this scenario I dont have to bother with recording > binlog file names and position etc etc. > That both servers will have the same databases abd replication and > binary logging will start on the two databases with no data loss and > continue forward. > > > Comments? > > ~Lawrence > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jgor...@westernwats.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: upgrading mysql
How about: 1 shut down the slave, upgrade it, restart it, let it catch up. 2 shut down the master, upgrade it, restart it, let the slave catch up. ? On 1/12/10 12:34 PM, "Lawrence Sorrillo" wrote: > Hi: > > I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. > > I want to so something like follows: > > 1. Stop all write access to the master server. > 2. Ensure that replication on the slave is caught up to the last change > on the master. > 3. stop binary logging on the master. > 4. stop replication on the slave. > 5. dump the master, stop old 4.1 server, start new 5.1 server and reload > master dump file under 5.1 server ( binary logging is turned off) > 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload > slave dump file under 5.1 server. > 7. After loading is complete, test then start binary logging on master > while still preventing updates to updates. > 8. After loading slave, test then start slave (get configs in place and > restart server). > > I am thinking that in this scenario I dont have to bother with recording > binlog file names and position etc etc. > That both servers will have the same databases abd replication and > binary logging will start on the two databases with no data loss and > continue forward. > > > Comments? > > ~Lawrence > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
upgrading mysql
Hi: I want to upgrade a master and slave server from mysql 4.1 to mysql 5.1. I want to so something like follows: 1. Stop all write access to the master server. 2. Ensure that replication on the slave is caught up to the last change on the master. 3. stop binary logging on the master. 4. stop replication on the slave. 5. dump the master, stop old 4.1 server, start new 5.1 server and reload master dump file under 5.1 server ( binary logging is turned off) 6. dump the slave, stop old 4.1 server, start new 5.1 server and reload slave dump file under 5.1 server. 7. After loading is complete, test then start binary logging on master while still preventing updates to updates. 8. After loading slave, test then start slave (get configs in place and restart server). I am thinking that in this scenario I dont have to bother with recording binlog file names and position etc etc. That both servers will have the same databases abd replication and binary logging will start on the two databases with no data loss and continue forward. Comments? ~Lawrence -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: optimize mysql
Some other info you can post to the list to help us zero in on the problem: 1) What is the SQL used to update the tables 2) What is the table structure? "Show Create Table mytablex" 3) How long does it take for how many rows? 4) How busy is the CPU when the updates are occuring? 5) Show Status -- After updates have completed 6) Show Variables Mike At 09:35 AM 1/12/2010, madunix wrote: I need your recommendation, I have the following config file need to be optimized, can any one recommend any modification on this file, lately mysql went slow with update procedure # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M Added by ashakhshir for logging## #long_query_time=5 #log-slow-queries=/var/log/slowQueryLog.log #log-queries-not-using-indexes ### # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, #MASTER_USER=, MASTER_PASSWORD= ; # #where you replace , , by quoted strings and # by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = # # The username the slave will use for authentication when connecting # to the master - required #master-user = # # The password the slave will authenticate with when connecting to # the master - required #master-password = # # The port the master is listening on. # optional - defaults to 3306 #master-port = # # binary logging - not required for slaves, but recommended #log-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 1 # Uncomment 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/ #innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_s
optimize mysql
I need your recommendation, I have the following config file need to be optimized, can any one recommend any modification on this file, lately mysql went slow with update procedure # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /var/lib/mysql) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/lib/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 16M max_allowed_packet = 1M table_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M Added by ashakhshir for logging## #long_query_time=5 #log-slow-queries=/var/log/slowQueryLog.log #log-queries-not-using-indexes ### # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication log-bin # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - #the syntax is: # #CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=, #MASTER_USER=, MASTER_PASSWORD= ; # #where you replace , , by quoted strings and # by the master's port number (3306 by default). # #Example: # #CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, #MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then #start replication for the first time (even unsuccessfully, for example #if you mistyped the password in master-password and the slave fails to #connect), the slave will create a master.info file, and any later #change in this file to the variables' values below will be ignored and #overridden by the content of the master.info file, unless you shutdown #the slave server, delete master.info and restart the slaver server. #For that reason, you may want to leave the lines below untouched #(commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = # # The username the slave will use for authentication when connecting # to the master - required #master-user = # # The password the slave will authenticate with when connecting to # the master - required #master-password = # # The port the master is listening on. # optional - defaults to 3306 #master-port = # # binary logging - not required for slaves, but recommended #log-bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using BDB tables #bdb_cache_size = 4M #bdb_max_lock = 1 # Uncomment 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/ #innodb_log_arch_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 = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash #
RE: Help with export and import into Oracle
>It seems that one of the tables we need to export and import >contains rows which is used for dropdown menus. > > > >This has the following effect: > > > >. Each item in the "text" field is added in the field by entering >the country name then pressing enter and then entering the next, etc > >. When exporting the data to a file (even when enclosing each field >within quotes) it still writes the control characters causing each item to >be read as a different line and thus the import into Oracle fails. > > [JS] Leaving aside my opinion that this is a bad way to store options for a dropdown menu, I think you will need to use the REPLACE() function liberally. In fact, you might consider using CONCAT_WS() to cram all of your fields into one and then using REPLACE() on the result. I don't know how that will work with your data, but I've done it before when otherwise stumped. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Force index command in query
Hi, After analysing slow query log i found that some queries are not using index and so i used the force index command in query and test it and now it starts using index properly.Accordingly i implemented the same query with force index in my application code and regeneratet the slow query log. Now i found that the same queries having force index clause are againg not using index and surprisingly its starting using index without any force index clause. Please suggest how it happened and should i continue with the force index command in that query or remove the force index clause from those queries ? Thanks & Regards Jeetendra Ranjan
Re: Help with export and import into Oracle
machiel.richards wrote: Good day guys [snip] . Each item in the "text" field is added in the field by entering the country name then pressing enter and then entering the next, etc . When exporting the data to a file (even when enclosing each field within quotes) it still writes the control characters causing each item to be read as a different line and thus the import into Oracle fails. Any idea on how we can resolve this as the process needs to be cronned to run on a weekly basis and thus we need to get this process resolved. You haven't described what process you're using to read the file for the Oracle import - all of Oracle's interfaces (oci, SQL, PL/SQL, load utilities like SQL*Loader and imp/impdp, external tables, etc.) can handle multi-line records like this. Given you're dumping to a file, it's mostly likely you're using SQL*Loader (i.e. sqlldr). The INFILE clause for the control file includes an os_file_proc_clause which let's you set the record delimiter, and override the default end of line behaviour. See http://www.orafaq.com/wiki/SQL*Loader_FAQ#How_does_one_load_records_with_multi-line_fields.3F for an example. If you're not using SQL*Loader, then more info would be required. Ciao Fuzzy :-) Dazed and confused about technology for 20 years http://fuzzydata.wordpress.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
