Re: Moving data between two servers with SQL
Nevermind -- it's working absolutely perfectly between 5.5.8 and 4.1.x. Thanks again for the push. -Hank On Tue, Jan 4, 2011 at 5:14 PM, Hank wrote: > > Also, can I do this: > > insert into federated_table select * from local_table? > > -Hank > > > On Tue, Jan 4, 2011 at 4:15 PM, Shawn Green (MySQL) < > shawn.l.gr...@oracle.com> wrote: > >> On 1/4/2011 15:53, Hank wrote: >> >>> Hello, >>> >>>I have a background process that runs as a combination of PHP&MySQL. >>> The >>> end results are records in a table on server #1 (but not the entire >>> table, >>> just a small subset of the table needs to move). What's the >>> easiest/cleanest way of moving those records to an identical table on >>> another server? In Oracle, we used to be able to set up connection >>> profiles >>> and move data between servers with SQL, but I'm guessing that's not easy >>> to >>> do with MySQL. I'd prefer not to use mysql command line client commands >>> to >>> save the data as an OS file and then import that into the other server >>> using >>> another mysql command line client command. I'd like to find something >>> cleaner than that. >>> >>> I'm using 5.5.8. >>> >>> thanks, >>> >>> -Hank >>> >>> Have you looked at the FEDERATED storage engine? >> http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html >> >> -- >> Shawn Green >> MySQL Principal Technical Support Engineer >> Oracle USA, Inc. >> Office: Blountville, TN >> > >
Re: Moving data between two servers with SQL
Also, can I do this: insert into federated_table select * from local_table? -Hank On Tue, Jan 4, 2011 at 4:15 PM, Shawn Green (MySQL) < shawn.l.gr...@oracle.com> wrote: > On 1/4/2011 15:53, Hank wrote: > >> Hello, >> >>I have a background process that runs as a combination of PHP&MySQL. >> The >> end results are records in a table on server #1 (but not the entire table, >> just a small subset of the table needs to move). What's the >> easiest/cleanest way of moving those records to an identical table on >> another server? In Oracle, we used to be able to set up connection >> profiles >> and move data between servers with SQL, but I'm guessing that's not easy >> to >> do with MySQL. I'd prefer not to use mysql command line client commands >> to >> save the data as an OS file and then import that into the other server >> using >> another mysql command line client command. I'd like to find something >> cleaner than that. >> >> I'm using 5.5.8. >> >> thanks, >> >> -Hank >> >> Have you looked at the FEDERATED storage engine? > http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html > > -- > Shawn Green > MySQL Principal Technical Support Engineer > Oracle USA, Inc. > Office: Blountville, TN >
Re: Moving data between two servers with SQL
Wow, that might just work! I've seen "Federated" tables mentioned about, but I never knew that's what they are here for.. thanks. Can I have a host (remote) table on a MySQL 4.1.x server, and the federated table on a 5.5.8 server? -Hank On Tue, Jan 4, 2011 at 4:15 PM, Shawn Green (MySQL) < shawn.l.gr...@oracle.com> wrote: > On 1/4/2011 15:53, Hank wrote: > >> Hello, >> >>I have a background process that runs as a combination of PHP&MySQL. >> The >> end results are records in a table on server #1 (but not the entire table, >> just a small subset of the table needs to move). What's the >> easiest/cleanest way of moving those records to an identical table on >> another server? In Oracle, we used to be able to set up connection >> profiles >> and move data between servers with SQL, but I'm guessing that's not easy >> to >> do with MySQL. I'd prefer not to use mysql command line client commands >> to >> save the data as an OS file and then import that into the other server >> using >> another mysql command line client command. I'd like to find something >> cleaner than that. >> >> I'm using 5.5.8. >> >> thanks, >> >> -Hank >> >> Have you looked at the FEDERATED storage engine? > http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html > > -- > Shawn Green > MySQL Principal Technical Support Engineer > Oracle USA, Inc. > Office: Blountville, TN >
Re: Moving data between two servers with SQL
On 1/4/2011 15:53, Hank wrote: Hello, I have a background process that runs as a combination of PHP&MySQL. The end results are records in a table on server #1 (but not the entire table, just a small subset of the table needs to move). What's the easiest/cleanest way of moving those records to an identical table on another server? In Oracle, we used to be able to set up connection profiles and move data between servers with SQL, but I'm guessing that's not easy to do with MySQL. I'd prefer not to use mysql command line client commands to save the data as an OS file and then import that into the other server using another mysql command line client command. I'd like to find something cleaner than that. I'm using 5.5.8. thanks, -Hank Have you looked at the FEDERATED storage engine? http://dev.mysql.com/doc/refman/5.5/en/federated-storage-engine.html -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, 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: moving data to MySQL
Well, it is just to have a console that is able to connect to every database, ODBC and videogames are one of the few things good about microsoft! I used it to import export data from/to access/sql server/mysql/oracle. Cheers Claudio 2009/2/22 Haidong Ji > Hi, > > Never thought of the MS Access solution, but it sounds interesting and I am > intrigued. If the data volume is not that big, it could be a quick and dirty > way of doing it. > > Sql Server 2005 and Sql Server 2008 Integration Service (SSIS) can do this, > but as far as usability is concerned, it is a step back from DTS (Data > Transformation Service from Sql Server 7 and Sql Server 2000 days). SSIS has > a fairly steep learning curve. > > I am also for the CSV flat file approach. One could use Sql Server bcp > utility to dump tables out as tab delimited file, then import it into MySQL > with LOAD DATA INFILE. > > Sample of bcp command: > bcp "MyDb.dbo.t1" out "c:\junk\t1.txt" -c -q -SMySqlServerInstance -T > > Sample of LOAD DATA command: > load data local infile '/home/MyFolder/t1.txt' into table MyDb.t1 lines > terminated by 'r\n' > > It looks like your first task is to restore this backup into a Sql Server > database. Sql Server Express management studio might have a GUI way of doing > this. If not, here is a sample script: > > restore database MyDb from disk = 'c:\LocationOfMyDbBackup' > with recovery, replace, move 'MyDbLogicalDataFile' to > 'c:\MyDirectory\MyDb.mdf', > move 'MyDbLogicalLogFile' to 'c:\MyDirectory\MyDb.ldf' > > Regards, > > Haidong "Alex" Ji > http://www.HaidongJi.com/tech > > > > On Sat, Feb 21, 2009 at 5:48 PM, Claudio Nanni wrote: > >> Hi, >> >> Using ODBC Drivers and MS Access you can do the job. >> This is a quick list of steps(if you need more in depth just ask): >> >> Download ODBC drivers for MySQL from www.mysql.com >> Install the ODBC drivers >> Create a System Data Source Name that point to the MySQL database (Control >> Panel->Administration Tools->ODBC Data Sources) >> Create a System Data Source Name that point to the MSSQL database (Control >> Panel->Administration Tools->ODBC Data Sources) >> Using Microsoft Access you can import-export-query the tables as you like. >> >> Cheers >> Claudio Nanni >> >> >> >> 2009/2/21 Chris Rehm >> >> > I want to write some programs to work with data from eveonline.com but >> my >> > installed database is MySQL and the data format they provide is a backup >> of >> > MSSQL. They recommend installing SQL Server 2005 Express and I've >> downloaded >> > that and am willing to install it, I just want to know if there is a >> > programmatic way of transferring the data to MySQL. Any help or insight >> > would be appreciated, I have been away from coding for several years >> because >> > of health issues and I'm trying to knock the rust off my brain and get >> going >> > again. >> > >> > Chris Rehm >> > ch...@javadisciple.com >> > >> > -- >> > 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: moving data to MySQL
Hi, Never thought of the MS Access solution, but it sounds interesting and I am intrigued. If the data volume is not that big, it could be a quick and dirty way of doing it. Sql Server 2005 and Sql Server 2008 Integration Service (SSIS) can do this, but as far as usability is concerned, it is a step back from DTS (Data Transformation Service from Sql Server 7 and Sql Server 2000 days). SSIS has a fairly steep learning curve. I am also for the CSV flat file approach. One could use Sql Server bcp utility to dump tables out as tab delimited file, then import it into MySQL with LOAD DATA INFILE. Sample of bcp command: bcp "MyDb.dbo.t1" out "c:\junk\t1.txt" -c -q -SMySqlServerInstance -T Sample of LOAD DATA command: load data local infile '/home/MyFolder/t1.txt' into table MyDb.t1 lines terminated by 'r\n' It looks like your first task is to restore this backup into a Sql Server database. Sql Server Express management studio might have a GUI way of doing this. If not, here is a sample script: restore database MyDb from disk = 'c:\LocationOfMyDbBackup' with recovery, replace, move 'MyDbLogicalDataFile' to 'c:\MyDirectory\MyDb.mdf', move 'MyDbLogicalLogFile' to 'c:\MyDirectory\MyDb.ldf' Regards, Haidong "Alex" Ji http://www.HaidongJi.com/tech On Sat, Feb 21, 2009 at 5:48 PM, Claudio Nanni wrote: > Hi, > > Using ODBC Drivers and MS Access you can do the job. > This is a quick list of steps(if you need more in depth just ask): > > Download ODBC drivers for MySQL from www.mysql.com > Install the ODBC drivers > Create a System Data Source Name that point to the MySQL database (Control > Panel->Administration Tools->ODBC Data Sources) > Create a System Data Source Name that point to the MSSQL database (Control > Panel->Administration Tools->ODBC Data Sources) > Using Microsoft Access you can import-export-query the tables as you like. > > Cheers > Claudio Nanni > > > > 2009/2/21 Chris Rehm > > > I want to write some programs to work with data from eveonline.com but > my > > installed database is MySQL and the data format they provide is a backup > of > > MSSQL. They recommend installing SQL Server 2005 Express and I've > downloaded > > that and am willing to install it, I just want to know if there is a > > programmatic way of transferring the data to MySQL. Any help or insight > > would be appreciated, I have been away from coding for several years > because > > of health issues and I'm trying to knock the rust off my brain and get > going > > again. > > > > Chris Rehm > > ch...@javadisciple.com > > > > -- > > 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: moving data to MySQL
Hi, Using ODBC Drivers and MS Access you can do the job. This is a quick list of steps(if you need more in depth just ask): Download ODBC drivers for MySQL from www.mysql.com Install the ODBC drivers Create a System Data Source Name that point to the MySQL database (Control Panel->Administration Tools->ODBC Data Sources) Create a System Data Source Name that point to the MSSQL database (Control Panel->Administration Tools->ODBC Data Sources) Using Microsoft Access you can import-export-query the tables as you like. Cheers Claudio Nanni 2009/2/21 Chris Rehm > I want to write some programs to work with data from eveonline.com but my > installed database is MySQL and the data format they provide is a backup of > MSSQL. They recommend installing SQL Server 2005 Express and I've downloaded > that and am willing to install it, I just want to know if there is a > programmatic way of transferring the data to MySQL. Any help or insight > would be appreciated, I have been away from coding for several years because > of health issues and I'm trying to knock the rust off my brain and get going > again. > > Chris Rehm > ch...@javadisciple.com > > -- > 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: moving data to MySQL
2009/2/21 Chris Rehm > I want to write some programs to work with data from eveonline.com but my > installed database is MySQL and the data format they provide is a backup of > MSSQL. They recommend installing SQL Server 2005 Express and I've downloaded > that and am willing to install it, I just want to know if there is a > programmatic way of transferring the data to MySQL. Any help or insight > would be appreciated, I have been away from coding for several years because > of health issues and I'm trying to knock the rust off my brain and get going > again. > > Chris Rehm > ch...@javadisciple.com > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: > http://lists.mysql.com/mysql?unsub=dimitar.vassi...@gmail.com > > You can make a ODBC/JDBC connection, export to CSV file, create a db link also
Re: moving data to MySQL
There are products out there that convert from ms sql to MySql. Though I would test them before trusting them --Original Message-- From: Chris Rehm To: mysql@lists.mysql.com Subject: moving data to MySQL Sent: Feb 21, 2009 10:47 I want to write some programs to work with data from eveonline.com but my installed database is MySQL and the data format they provide is a backup of MSSQL. They recommend installing SQL Server 2005 Express and I've downloaded that and am willing to install it, I just want to know if there is a programmatic way of transferring the data to MySQL. Any help or insight would be appreciated, I have been away from coding for several years because of health issues and I'm trying to knock the rust off my brain and get going again. Chris Rehm ch...@javadisciple.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=chaim.rie...@gmail.com Sent via BlackBerry from T-Mobile
RE: Moving Data between Tables
"I hope you're not about to try this for the first time on your production data :-) " I dumped the lockers table before beginning my experiments. If I had trashed lockers2 (my experimental file) in the process, I would have truncated it and re-inserted the 1492 records from my batch file. - "It's not quite right. " You're correct. I tried my UPDATE/implicit JOIN query shortly after posting my email. I expected the query to fail, which it did. Not knowing how long it would be before someone responded, I went ahead and solved my problem with a PHP script. It was nice that the problem was solve-able with PHP, but it was frustrating to write 20 lines of PHP because I didn't know how to write the 1 line of MySQL that would have solved my problem. - "update foo inner join bar on ... set a = b, c = d..." I run a backup of the database and associated PHP files every few nights and then carry a copy of the backup home to expand the PHP and incorporate the new things I learn about MySQL into my table structures or into batch files. You can be assured I'll be spending time with our old friends foo and bar and this update syntax between now and Monday. Thanks again, Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data between Tables
Hi John, Kebbel, John wrote: I moved information about our school locks (serial numbers, combinations, student, etc) from FileMaker to MySQL into a table called lockers and wrote PHP pages so our teachers could record the locks returned at the end of the year. Unfortunately, I missed transferring close to 200 locks. I duplicated the structure of lockers (1313 locks) as lockers2 and reloaded all the information from Filemaker (1492 locks). My problem is moving the information from lockers to lockers2 that was added AFTER the Filemaker migration (2nd semester teacher, returned, paid). This is what I'm getting set to try, but I've never seen a JOIN in an update statement before. Am I on the right track for this? UPDATE lockers2 SET lockers2.returned = lockers.returned, lockers2.teacher2nd = lockers.teacher2nd, lockers2.paid = lockers.paid WHERE lockers2.serialNumber = lockers.serialNumber; It's not quite right. I hope you're not about to try this for the first time on your production data :-) Maybe you can grab 100 rows from each table into scratch tables and play with it to be sure you will get what you want. The general syntax (with JOIN -- I can't do comma-joins) is more like update foo inner join bar on ... set a = b, c = d... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data
Are there any variables here I can tune to try and increase the the query? mysql> show variables like '%buff%'; +-+---+ | Variable_name | Value | +-+---+ | bdb_log_buffer_size | 262144| | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size| 131072| | key_buffer_size | 402653184 | | myisam_sort_buffer_size | 67108864 | | net_buffer_length | 16384 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144| | sort_buffer_size| 2097144 | +-+---+ mysql> show variables like '%size%'; +-+--+ | Variable_name | Value| +-+--+ | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | binlog_cache_size | 32768| | bulk_insert_buffer_size | 8388608 | | delayed_queue_size | 1000 | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | join_buffer_size| 131072 | | key_buffer_size | 402653184| | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_heap_table_size | 16777216 | | max_join_size | 18446744073709551615 | | max_relay_log_size | 0| | myisam_max_extra_sort_file_size | 268435456| | myisam_max_sort_file_size | 2147483647 | | myisam_sort_buffer_size | 67108864 | | query_alloc_block_size | 8192 | | query_cache_size| 33554432 | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 2097144 | | thread_cache_size | 8| | tmp_table_size | 33554432 | | transaction_alloc_block_size| 8192 | | transaction_prealloc_size | 4096 | +-+--+ JS. _ It's fast, it's easy and it's free. Get MSN Messenger today! http://www.msn.co.uk/messenger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data
SAN --- I think that's the key piece to this puzzle. It doesn't matter how fat your network pipes are, you will always encounter some network lag when reading or writing data from and to a SAN. There is an abstraction layer that must bundle your request to the disk then translate it from the device that will translate into additional CPU overhead PER DISK TRANSACTION. Most people won't notice the difference when they are moving their personal files but a database certainly does. The number of disk reads and writes per second normally handled by hardware translates into network round-trips per second when you are using a SAN. Multiply each round trip (thousands, millions?) by the average lag and you have one source of your slowdown. Add just 1 millisecond to each read request and it quickly adds up to several seconds per query for tables your size. I just became really pressed for time today so must I defer to others on the list to interpret your settings. Please everyone, pitch in on this one, OK? Thanks! Shawn Green Database Administrator Unimin Corporation - Spruce Pine "J S" <[EMAIL PROTECTED]> wrote on 09/10/2004 10:55:23 AM: > How fast do your disks rotate? What kind of disk controller are you using > (ATA, SATA, SCSI, ...)? Are they in a RAID array or not. If they are what > RAID scheme are you using? > > > I had to ask about this one because I'm not too sure about these sort of > things. Apparently the box is using ESS disks on SAN, and they're RAID 5. > > > How big are your buffers and other working areas? (show variables like > '%buff%'; show variables like '%size%';). > > > mysql> show variables like '%buff%'; > +-+---+ > | Variable_name | Value | > +-+---+ > | bdb_log_buffer_size | 262144| > | bulk_insert_buffer_size | 8388608 | > | innodb_buffer_pool_size | 8388608 | > | innodb_log_buffer_size | 1048576 | > | join_buffer_size| 131072| > | key_buffer_size | 402653184 | > | myisam_sort_buffer_size | 67108864 | > | net_buffer_length | 16384 | > | read_buffer_size| 2093056 | > | read_rnd_buffer_size| 262144| > | sort_buffer_size| 2097144 | > +-+---+ > > mysql> show variables like '%size%'; > +-+--+ > | Variable_name | Value| > +-+--+ > | bdb_cache_size | 8388600 | > | bdb_log_buffer_size | 262144 | > | binlog_cache_size | 32768| > | bulk_insert_buffer_size | 8388608 | > | delayed_queue_size | 1000 | > | innodb_additional_mem_pool_size | 1048576 | > | innodb_buffer_pool_size | 8388608 | > | innodb_log_buffer_size | 1048576 | > | innodb_log_file_size| 5242880 | > | join_buffer_size| 131072 | > | key_buffer_size | 402653184| > | max_binlog_cache_size | 4294967295 | > | max_binlog_size | 1073741824 | > | max_heap_table_size | 16777216 | > | max_join_size | 18446744073709551615 | > | max_relay_log_size | 0| > | myisam_max_extra_sort_file_size | 268435456| > | myisam_max_sort_file_size | 2147483647 | > | myisam_sort_buffer_size | 67108864 | > | query_alloc_block_size | 8192 | > | query_cache_size| 33554432 | > | query_prealloc_size | 8192 | > | range_alloc_block_size | 2048 | > | read_buffer_size| 2093056 | > | read_rnd_buffer_size| 262144 | > | sort_buffer_size| 2097144 | > | thread_cache_size | 8| > | tmp_table_size | 33554432 | > | transaction_alloc_block_size| 8192 | > | transaction_prealloc_size | 4096 | > +-+--+ > 30 rows in set (0.01 sec) > > Your CPUs are probably up to >the task but we need to make sure you are using as much available memory > as you can. I see PowerPC in your system description... how fast is your > system bus (or does anyone else think that would make that much of a > difference here)? > > > Not sure how to check the system bus but this is the info on the CPUs: > > RS/6000 & pSeries Details > CPU Architecture=PowerPC Implementation=RS64-III, 64 bit > Machine has 2 CPUs (2 CPUs activated) > CPU Level 1 Cache is Combined Instruction=131072 b
Re: Moving Data
J S wrote: How fast do your disks rotate? What kind of disk controller are you using (ATA, SATA, SCSI, ...)? Are they in a RAID array or not. If they are what RAID scheme are you using? I had to ask about this one because I'm not too sure about these sort of things. Apparently the box is using ESS disks on SAN, and they're RAID 5. See www.baarf.com for comments against RAID 5 Colin Bull -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data
How fast do your disks rotate? What kind of disk controller are you using (ATA, SATA, SCSI, ...)? Are they in a RAID array or not. If they are what RAID scheme are you using? I had to ask about this one because I'm not too sure about these sort of things. Apparently the box is using ESS disks on SAN, and they're RAID 5. How big are your buffers and other working areas? (show variables like '%buff%'; show variables like '%size%';). mysql> show variables like '%buff%'; +-+---+ | Variable_name | Value | +-+---+ | bdb_log_buffer_size | 262144| | bulk_insert_buffer_size | 8388608 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | join_buffer_size| 131072| | key_buffer_size | 402653184 | | myisam_sort_buffer_size | 67108864 | | net_buffer_length | 16384 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144| | sort_buffer_size| 2097144 | +-+---+ mysql> show variables like '%size%'; +-+--+ | Variable_name | Value| +-+--+ | bdb_cache_size | 8388600 | | bdb_log_buffer_size | 262144 | | binlog_cache_size | 32768| | bulk_insert_buffer_size | 8388608 | | delayed_queue_size | 1000 | | innodb_additional_mem_pool_size | 1048576 | | innodb_buffer_pool_size | 8388608 | | innodb_log_buffer_size | 1048576 | | innodb_log_file_size| 5242880 | | join_buffer_size| 131072 | | key_buffer_size | 402653184| | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_heap_table_size | 16777216 | | max_join_size | 18446744073709551615 | | max_relay_log_size | 0| | myisam_max_extra_sort_file_size | 268435456| | myisam_max_sort_file_size | 2147483647 | | myisam_sort_buffer_size | 67108864 | | query_alloc_block_size | 8192 | | query_cache_size| 33554432 | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size| 2093056 | | read_rnd_buffer_size| 262144 | | sort_buffer_size| 2097144 | | thread_cache_size | 8| | tmp_table_size | 33554432 | | transaction_alloc_block_size| 8192 | | transaction_prealloc_size | 4096 | +-+--+ 30 rows in set (0.01 sec) Your CPUs are probably up to the task but we need to make sure you are using as much available memory as you can. I see PowerPC in your system description... how fast is your system bus (or does anyone else think that would make that much of a difference here)? Not sure how to check the system bus but this is the info on the CPUs: RS/6000 & pSeries Details CPU Architecture=PowerPC Implementation=RS64-III, 64 bit Machine has 2 CPUs (2 CPUs activated) CPU Level 1 Cache is Combined Instruction=131072 bytes & Data=131072 bytes Level 2 Cache size=4194304 AIX 4.3.3.86 On another thoughtI was under the impression that you were trying to create a single "daily" table for testing purposes (hence the date in the table name) yet you seem to have 25M+ entries? Are you sure that's just one day's worth of information? In fact from the time you sent the earliest message in this post until you posted your table status, you added about 10M new rows. I was tailing the proxies from about lunchtime yesterday and piping the output into the database. By the end of the day there were 25m row. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving data
Hi Shawn, Thanks for helping me out again! I've cut and pasted the answers below your questions: I would love to say how adequate your hardware should be but I have forgotten what hardware you have, sorry! Would you mind re-posting all of the specs for your DB server? 2 x 500Mhz CPUs 8GB Memory mysql-max-4.0.20-ibm-aix4.3.3.0-powerpc Also could you post the results of : EXPLAIN select * From t20040908 where uid=454 to make sure the query is using the `uid` key (it should be but it never hurts to check). mysql> EXPLAIN select * From t20040909 where uid=454; +---+--+---+--+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+---+--+-+ | t20040909 | ref | uid | uid | 4 | const | 4275 | Using where | +---+--+---+--+-+---+--+-+ 1 row in set (0.07 sec) Could you also respond with the results of : SHOW TABLE STATUS LIKE 't20040908' mysql> SHOW TABLE STATUS LIKE 't20040909'; +---+++--++-+-+--+---++-+-+-++-+ | Name | Type | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Create_options | Comment | +---+++--++-+-+--+---++-+-+-++-+ | t20040909 | MyISAM | Fixed | 25209614 | 32 | 806707648 |137438953471 |222495744 | 0 | NULL | 2004-09-09 14:24:41 | 2004-09-10 01:01:35 | 2004-09-09 14:25:40 || | +---+++--++-+-+--+---++-+-+-++-+ 1 row in set (0.01 sec) Part of the time it takes to query any table is the time it takes to load the correct index in from the disk so... maybe? you hardware could be too slow for this volume of information? I just can't say with any degree of confidence one way or the other yet. I did notice that this query returned in only about 11% of the time it took a similar query on the full dataset to return (6.87 seconds vs. 61 seconds a similar lookup on "internet_usage"). Better but not exactly linearly related to the reduction in the data size. I would think this is related to the fact that the time it takes to find records using a B-TREE index degrades roughly logarithmically. I thought about putting a HASH index on that column but those are only available to the MEMORY table type (bummer). BTW, if you and I take this thread back into the list everyone else will also have a chance to review your hardware specs and throw in their two cents, too! Shawn Green Database Administrator Unimin Corporation - Spruce Pine "J S" <[EMAIL PROTECTED]> wrote on 09/09/2004 04:48:47 AM: > Hi Shawn, > > I'm working at the moment on the new database! I had a quick question for > you. Here's my table structure: > > t20040908 | CREATE TABLE `t20040908` ( > `uid` int(10) unsigned NOT NULL default '0', > `time` timestamp(14) NOT NULL, > `ip` int(10) unsigned NOT NULL default '0', > `urlid` int(10) unsigned NOT NULL default '0', > `timetaken` smallint(5) unsigned default '0', > `cs_size` int(10) unsigned default '0', > `sc_size` int(10) unsigned default '0', > `method_ID` tinyint(3) unsigned NOT NULL default '0', > `action_ID` tinyint(3) unsigned NOT NULL default '0', > `virus_ID` tinyint(3) unsigned NOT NULL default '0', > `useragent_ID` smallint(5) unsigned NOT NULL default '0', > KEY `uid` (`uid`) > ) TYPE=MyISAM > > and it currently has 15263552 rows. > > I ran the command: > select * from t20040908 where uid=454; > > which came back with 8347 rows in set (6.87 sec) > > > Does 6.87 seconds seem rather slow to you ? It could be the box is just too > old and slow. But I just wanted to check in case there was anything else I > should check? > > Cheers, > > JS. > > >There are two ways that I can think of that will combine smaller tables > >into one longer (not wider) table: MERGE tables and UNION queries. A third > >option is to manually add rows from multiple source tables to a staging > >table using INSERT...SELECT... statements. > > > >Since we never know from day to day what you will need to query on, > >creating one big MERGE table would probably be as impractical as your > >existing setup. That leaves the other two options available to run repo
Re: moving data
> I want to copy data from one table to another. However I need to do this > carefully because I haven't got much of space left. I was thinking of maybe > selecting data from every 10 rows of the old table, inserting it into > the new table, then deleting those rows from the old table. > > Could someone help me out with the SQL for this please? Or tell me if > there's a better way of doing this? CREATE TABLE new_able SELECT * FROM old_table; Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: moving data
A) 0. optionally: start transaction; 1. create temporary table mytemptable select * from mytable order by mytableprimarykey limit 10; 2. insert into destinationtable select * from mytemptable; 3. delete * from mytable order by mytableprimarykey limit 10; 4. drop mytemptable; 5. optionally: commit; 6. Goto step 0. if you don't want to "play it save", then you can skip step 1 and insert your data straight into the destination table. or B) Study the MySQL Manual for the mysqldump's commandline parameters, to dump all your data to a file first. Then recreate your table on the database/table you wish. or C) You can use many of the GUI tools around most of which can backup your data to scripts or other databases/tables. Ex. http://www.scibit.com/products/mycon 0. Double click your table, sort and limit your data as you wish 1. Click Backup 2. Use the resulting script against your destination table. 3. Hit Ctrl+A in your table's grid and then Del to delete the select records and then hit Refresh to start again (if you have selected only a 100,000 batch for example). 4. Goto step 1. By default your resulting script will contain records batched 100 per insert statement and thus for a 100,000 records you will only have a 1000 insert statements. This will of course execute much faster than 100,000 separate insert statements. It would be highly recommended to use an intermediate local "dump" script file (if your space problem is not a concern for your local computer), that way you have all your data backed up for "just in case". Kind Regards SciBit MySQL Team http://www.scibit.com > > -Original Message- > From: "J S" <[EMAIL PROTECTED]> > To: "[EMAIL PROTECTED]" <[EMAIL PROTECTED]> > CC: > Subject: moving data > Sent: Thu, 02 Sep 2004 12:38:30 GMT > Received: Thu, 02 Sep 2004 12:40:48 GMT > Read: Thu, 02 Sep 2004 13:23:07 GMT > Hi, > > I want to copy data from one table to another. However I need to do this > carefully because I haven't got much of space left. I was thinking of maybe > selecting data from every 10 rows of the old table, inserting it into > the new table, then deleting those rows from the old table. > > Could someone help me out with the SQL for this please? Or tell me if > there's a better way of doing this? > > Thanks, > > JS. > > _ > Want to block unwanted pop-ups? Download the free MSN Toolbar now! > http://toolbar.msn.co.uk/ > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving data
J S wrote: Hi, I want to copy data from one table to another. However I need to do this carefully because I haven't got much of space left. I was thinking of maybe selecting data from every 10 rows of the old table, inserting it into the new table, then deleting those rows from the old table. This will not free up the disk space used by those 10 rows. Could someone help me out with the SQL for this please? Or tell me if there's a better way of doing this? Thanks, JS. _ Want to block unwanted pop-ups? Download the free MSN Toolbar now! http://toolbar.msn.co.uk/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Moving Data Between Proprietary Database and MySQL
Hello,
> Because most web providers will not allow
> direct TCP/IP access to
> MySQL servers, I am considering the following
> strategy:
>
> a) Save the proprietary database to a file
> containing a series of SQL
> CREATE TABLE and INSERT INTO calls.
>
> b) Upload the SQL file to the server via FTP.
>
> c) Launch a PHP script on the web server from
> my program that executes
> the SQL file to insert the data into the MySQL
> database.
Have you tried phpMyAdmin? It's a web-based
administration tool written in PHP to handle mysql
databases.
Check http://www.phpwizard.net/projects/phpMyAdmin/
> Another issue I am running into is the
> difference between the
> proprietary database and MySQL in how quotes
> are escaped. The
> proprietary database wants two sequential quote
> characters, MySQL uses
> a backslash before the quote character.
You can do this with a simple search & replace. Does
the other database provides any means to do it in the
query itself? i.e., in mysql you can query like this:
SELECT replace(some_field, ',', '.') FROM some_table
This query substitutes commas (,) for periods (.) for
some_field.
If you'd like to loose your sanity, you could start
having lots of fun with strings, like this:
SELECT concat('INSERT INTO TABLE_X (field_1) VALUES
(''', replace(some_field, ',', '.'), ''');');
and you can send the result to a text file. this way
you are building a script using a query.
Hope this helps!
>
> Has anyone else gone down this road before? Any
> comments on this
> strategy? Are there any tools or routines
> available to help facilitate
> this process?
>
> --
> Bruce Vander Werf
> [EMAIL PROTECTED]
>
> -
> Please check
> "http://www.mysql.com/Manual_chapter/manual_toc.html";
> before
> posting. To request this thread, e-mail
> [EMAIL PROTECTED]
>
> To unsubscribe, send a message to the address
> shown in the
> List-Unsubscribe header of this message. If you
> cannot see it,
> e-mail [EMAIL PROTECTED]
> instead.
>
>
~avalon~
-
Email Enviado utilizando o serviço MegaMail
-
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: Moving data in table from mysql server A to server B
* Kemp Randy-W18971 > If I have mysql on server A and an identical version on server B, > what is the easiest way to move data in Table C from server A to server B? Asuming your databases are stored in /usr/local/mysql/var/ and the relevant database is named "database": >From Server A: scp /usr/local/mysql/var/database/Table_C.* Server_B:/usr/local/mysql/var/database/. If the data is big and the connection is slow, you may want to compress the data before you transfer it. -- Roger - 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: Moving Data
It depends on what version of MySQL you are running. MyISAM tables will move across platforms fine, but ISAM tables will not. You could always choose the failsafe and use mysqldump to dump the contents of your database into a file, and then use that to reload the contents on the new machine. John Barton Unix Systems Administrator Primary Networks, Inc. [EMAIL PROTECTED] On Mon, 9 Apr 2001, Mat Murdock wrote: > We will be moving our MySql databases from a Winnt server to a linux server. Can I >just copy the data files from one machine to the other? If not then how should I do >it? > > Mat Murdock > > > Your mouse has moved. Windows must be restarted for the change to take > effect. Reboot now? [ OK ] > - 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
