Re: SCALING INSERT
Hi shawn, As the data grows to 20 millions the insert rate will become very slow. In such case i am getting 2000 insert/seconds only. Therefore my objective is not achieved. I cannot slow up the insert rate of 10,000/second. I am getting data (inserted by users at this rate) Is there any other way to do so. (distributed servers) Thanks, Krishna On Thu, Jan 21, 2010 at 7:39 PM, Shawn Green shawn.gr...@sun.com wrote: Krishna Chandra Prajapati wrote: Hi list, I want to insert 1 records/sec into table. There can be n number of tables with unique data in each. What are the possible ways to do ? Thanks, Krishna The manual is your friend. It doesn't hurt to consult it. INSERT ... http://dev.mysql.com/doc/refman/5.1/en/insert.html LOAD DATA INFILE ... http://dev.mysql.com/doc/refman/5.1/en/load-data.html And we even have both general and specific suggestions on improving INSERT performance in our Optimization chapter: http://dev.mysql.com/doc/refman/5.1/en/optimization.html Warmest regards, -- Shawn Green, MySQL Senior Support Engineer Sun Microsystems, Inc. Office: Blountville, TN
Re: SCALING INSERT
non-linearity in the insert rate means you have indexes on some columns. depending on your situation, mysql can be more efficient if drop those indexes, do bulk inserts, and then add the indexes again. On 1/23/10 5:02 AM, Krishna Chandra Prajapati prajapat...@gmail.com wrote: Hi shawn, As the data grows to 20 millions the insert rate will become very slow. In such case i am getting 2000 insert/seconds only. Therefore my objective is not achieved. I cannot slow up the insert rate of 10,000/second. I am getting data (inserted by users at this rate) Is there any other way to do so. (distributed servers) Thanks, Krishna On Thu, Jan 21, 2010 at 7:39 PM, Shawn Green shawn.gr...@sun.com wrote: Krishna Chandra Prajapati wrote: Hi list, I want to insert 1 records/sec into table. There can be n number of tables with unique data in each. What are the possible ways to do ? Thanks, Krishna The manual is your friend. It doesn't hurt to consult it. INSERT ... http://dev.mysql.com/doc/refman/5.1/en/insert.html LOAD DATA INFILE ... http://dev.mysql.com/doc/refman/5.1/en/load-data.html And we even have both general and specific suggestions on improving INSERT performance in our Optimization chapter: http://dev.mysql.com/doc/refman/5.1/en/optimization.html Warmest regards, -- 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: Best way to synchronize two database schemas
As Mr. Withers also indicated, I meant frefixing the *filename* of each change script with the date, and of course also keeping all changes in a single (or limited set of) folder(s), so you can easily collect and sequentially apply all of them when release time comes. Also, it is preferable that database structure changes are handled by one person or team, usually the database administrator(s) if you have them. On Fri, Jan 22, 2010 at 11:12 PM, Daevid Vincent dae...@daevid.com wrote: Huh? This makes no sense. There is one single file UPDATES.sql that has ALL the changes in it. In order to prefix a line, it would need to be commented. It would also need to be one single change per line. Highly inefficient. This still doesn't solve the fact that different developers checkout or update the code on different days/times. How would you automate the fact that b/c I did an update today, which specific UPDATE.sql commands need to be run since some have already been after the last update. You'd need to store a file somewhere with a date stamp. Now, we could split each block of SQL commands into separate files and the script could check file dates, but you still have to save off the last time you updated somewhere. Anyways, it's just easier for a developer to mentally keep track of what the last SQL they remember running was. And if they forgot, all they have to do is check their version of the database schema against what the SQL command wants to do. A diff if you will. ÐÆ5ÏÐ *Light travels faster than sound. This is why some people appear bright until you hear them speak.* -- *From:* vegiv...@gmail.com [mailto:vegiv...@gmail.com] *On Behalf Of *Johan De Meersman *Sent:* Friday, January 22, 2010 1:06 AM *To:* Daevid Vincent *Cc:* mysql@lists.mysql.com; Price, Randall *Subject:* Re: Best way to synchronize two database schemas Simple: prefix the change files with mmddhhmm formatted timestamps, so they sort correctly :-) On Thu, Jan 21, 2010 at 11:31 PM, Daevid Vincent dae...@daevid.comwrote: Exactly what Johan said. I keep structure like so: develo...@mypse /var/www/dart2/UPDATES $ ll -rw-rw-rw- 1 developer developer 551097 2009-12-22 23:16 airports_city_country.sql drwxrwxrwx 2 developer developer 4096 2010-01-21 04:51 CVS -rw-rw-rw- 1 developer developer 3063 2009-07-15 01:40 fix_airports.php -rw-r--r-- 1 developer developer 23414 2010-01-21 03:52 ps_access_to_mysql.sql -rw-rw-rw- 1 developer developer 12259 2010-01-06 05:22 UPDATES.sql Any and all changes to DB schema are in the UPDATES/UPDATES.sql file and each are commented with the date of the change, who did it, and why. This file/dir is part of your repository, so as each developer checks out, they would run the appropriate part of the script as well. I've not yet found a good (and safe) way to automate this process. /* 2009-06-01 [dv] fix the privileges for various users as they were all whacked out * http://dev.mysql.com/doc/refman/5.0/en/grant.html */ REVOKE ALL PRIVILEGES, GRANT OPTION FROM 'foo'@'10.10.10.%'; ... If you're using Subversion, you may find my Subversion Flagged Update script helpful... http://daevid.com/content/examples/snippets.php I also can vouche for SQLYog. Aside from being the absolute BEST mySQL GUI I've ever used, it has a feature to create the schema differences between two live databases. I've used it before to get a DEV and PROD server in sync, so that I could then implement the above methodology. -Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Thursday, January 21, 2010 10:35 AM To: Price, Randall Cc: mysql@lists.mysql.com Subject: Re: Best way to synchronize two database schemas The best way is to keep track of all individual changes to your staging environment, including fire-and-forget style scripts; and apply those to your production environment as needed. This is part of the process of change management, and generally a very good idea :-) Lacking that, there are several tools that can generate a differential script to do exactly this. I don't really use them, but I seem to remember that SQLyog and some expensive but excellent Quest tool could do it. On 1/21/10, Price, Randall randall.pr...@vt.edu wrote: I have a two databases, one in a production environment (let's call it db_prod) and the other in a testing environments (Let's call it db_test). What is the best way to synchronize the database schemas? db_test has had a few indexes and constraints added to several tables and I need to generate a MySQL script to apply these changes to db_prod. So basically I want to dump the schemas of the two database, compare, and generate the necessary script to apply to db_prod. Thanks, Randall Price -- Bier met grenadyn Is als mosterd by den wyn Sy
Re: Best way to synchronize two database schemas
One of the ways is to keep db scema under revision control system. And update it every N minutes. % crontab -l 0 * * * * mysqldump testdb --no-data testdb_schema.sql svn ci -m db schema: `date` /dev/null I have a two databases, one in a production environment (let's call it db_prod) and the other in a testing environments (Let's call it db_test). What is the best way to synchronize the database schemas? db_test has had a few indexes and constraints added to several tables and I need to generate a MySQL script to apply these changes to db_prod. So basically I want to dump the schemas of the two database, compare, and generate the necessary script to apply to db_prod. Thanks, Randall Price -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: SCALING INSERT
If you only need very fast INSERTs, you might try to use ARCHIVE storage engine ( http://dev.mysql.com/tech-resources/articles/storage-engine.html ). It was developed for handling INSERTs very fast. Many peoples use it, for example, for storing logs. Hi list, I want to insert 1 records/sec into table. There can be n number of tables with unique data in each. What are the possible ways to do ? Thanks, Krishna -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best way to synchronize two database schemas
On 1/21/10 12:03 PM, Price, Randall randall.pr...@vt.edu wrote: I have a two databases, one in a production environment (let's call it db_prod) and the other in a testing environments (Let's call it db_test). What is the best way to synchronize the database schemas? db_test has had a few indexes and constraints added to several tables and I need to generate a MySQL script to apply these changes to db_prod. So basically I want to dump the schemas of the two database, compare, and generate the necessary script to apply to db_prod. i'd use export structure in phpmyadmin on both dbs, then run diff on the two export files, and write a script by hand based on the diff. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best way to synchronize two database schemas
What is the best way to synchronize the database schemas? db_test has had a few indexes and constraints added to several tables and I need to generate a MySQL script to apply these changes to db_prod. Ruby on Rails comes with tools to dump and load db schemas; it's trivial to create a skeleton app and point it at your DB if you want to try it out. Of course, Rails also uses what are called migrations to make changes to existing DBs, which keeps you out of this problem in the first place :-) I haven't looked, but I wouldn't be surprised if other comparable (i.e., Rails clone) platforms have similar features. FWIW, -- Hassan Schroeder hassan.schroe...@gmail.com twitter: @hassan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Best way to synchronize two database schemas
Hi, Have you checked out our tool Database Workbench yet? It includes a Schema Compare tool that generates a script. See www.upscene.com With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! Database questions? Check the forum: http://www.databasedevelopmentforum.com I have a two databases, one in a production environment (let's call it db_prod) and the other in a testing environments (Let's call it db_test). What is the best way to synchronize the database schemas? db_test has had a few indexes and constraints added to several tables and I need to generate a MySQL script to apply these changes to db_prod. So basically I want to dump the schemas of the two database, compare, and generate the necessary script to apply to db_prod. Thanks, Randall Price -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Event feature already working in Server 5.1.37
Hi, I just wanted to remark, that the Event feature is already working in server version 5.1.37 (installed on Debian). In tech resources is mentioned that this feature would be available since version 5.1.6 (see http://dev.mysql.com/tech-resources/articles/event-feature.html). So I wanted to give up using it- but fortunately I tried it on my 5.1.37 server and it works fine.. So I you have a MySQL server version prior 5.1.6 an need the Event feature - I suggest just give it a try . Friendly Philipp Maske Software Developer Dipl.-Oec. Philipp Maske Location Bretagne Maske Maske GbR Ferienhausvermittlung Am Wasserturm 13 31303 Burgdorf b. Hannover Deutschland / Allemagne Email: mailto:i...@location-bretagne.de i...@location-bretagne.de WWW: http://www.location-bretagne.de www.location-bretagne.de Mobile: +49 (0)172 4523977 OpenBC: http://www.openbc.com/hp/Philipp_Maske http://www.openbc.com/hp/Philipp_Maske
