Re: Innodb and bulk writes

How big is your /tmp folder, can u move this to other fast moving disk. On Fri, Mar 19, 2010 at 4:27 AM, Raj Shekhar wrote: > Max Bube gmail.com> writes: > > > > The problem starts when I run bulk writes like an alter table or a > restore > > from mysqldump, its starts processing more than 5000

Re: Innodb and bulk writes

Max Bube gmail.com> writes: > The problem starts when I run bulk writes like an alter table or a restore > from mysqldump, its starts processing more than 5 rows/s but suddenly > the ratio goes down to 100 rows /sec. and then its stucked at this ratio > even if I restart MySQL. The only way

Re: MySQL Storage Engine

At 04:18 AM 3/18/2010, Tompkins Neil wrote: Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your exp

MySQL Encryption

In terms of encryption functions AES_DECRYPT and AES_ENCRYPT, can anyone point to any good links or offer any suggestions in terms of best practices on storage of the associated symmetric key? I've found very little information on this when searching. Does MySQL offer any asymmetric encryption

Re: 7-day average

You're exactly right, that's an important point that I neglected when putting together my example. Good catch. On Mar 18, 2010, at 11:27 AM, Chris W wrote: > I think I would change the math. Since there are several days in there where > there are no hits, that should in my opinion count again

Re: 7-day average

I changed the names slightly when I tested it but here is the query that gives the result you want... SELECT a.`AccountID`, a.`company`, sum(h.hits), count(h.AccountID), sum(h.hits)/count(h.AccountID) AS AvgHits FROM account a JOIN hitsperday h using (AccountID) WHERE `date` < DATE(now()) AND

Re: 7-day average

Brian Dunning wrote: My brain just squirted out my ear trying to figure this out, so maybe one of you can help me scoop it back up. Yummy, fresh brain! ;-) I have a table of accounts, and a table of hits-per-day per account. I'm trying to sort my accounts by the most hits-per-day, averaged ov

7-day average

My brain just squirted out my ear trying to figure this out, so maybe one of you can help me scoop it back up. I have a table of accounts, and a table of hits-per-day per account. I'm trying to sort my accounts by the most hits-per-day, averaged over the preceding 7 days (8 days ago through yes

Re: Innodb and bulk writes

This is an output from console when its performnace goes dows Query OK, 65469 rows affected (0.82 sec) Records: 65469 Duplicates: 0 Warnings: 0 Query OK, 65469 rows affected (0.78 sec) Records: 65469 Duplicates: 0 Warnings: 0 Query OK, 65469 rows affected (10 min 57.30 sec) Records: 65469 D

Re: Innodb and bulk writes

when the writes are happening, please run "show full processlist" and let us know the out put. regards anandkl On Thu, Mar 18, 2010 at 9:09 PM, Max Bube wrote: > Hi list, > > Im having problems with bulk writes (restores from mysqldumps, alters, > delete in (select ...)) with innodb. The server

Innodb and bulk writes

Hi list, Im having problems with bulk writes (restores from mysqldumps, alters, delete in (select ...)) with innodb. The servers are at amazon EC2 instances w/ 15G ram and raid0 4disks EBS. The problem starts when I run bulk writes like an alter table or a restore from mysqldump, its starts proces

RE: Question about DELETE

Would wrapping the DELETE in a TRANSACTION improve the performance any? Also, when you say to "after each mass delete, rebuilt the indexes..." would running OPTIMIZE TABLE tablename; be the way to do this, or how? Thanks, -Randall Price From: Ananda Kumar [mailto:anan...@gmail.com] Sent: Thur

Re: Question about DELETE

delete will also cause the undo(before image) to be generated, in case u want to rollback. This will also add up to the delete completion time. After each mass delete, rebuild indexes to remove gaps in indexes(remove fragmentatio in the index). This will improve next delete or select. regards ana

RE: Question about DELETE

I have the MySQL Administrator running and on the Server Connections menu on the Threads tab I can see the thread running (i.e., DELETE FROM table WHERE ...). I refresh this tab periodically to see what stage the process is in. It does not display any information about rebuilding indexes, just

RE: Question about DELETE

Hi Randall, If you're talking about processes that are taking that long, then running SHOW PROCESSLIST several times during the operation should give you a rough idea what it is doing at each stage. Also, do you have an index on the id column? It could just be taking a long time to identify all t

RE: Question about DELETE

Thanks for your responses on this. However, I suspect that the indexes are being rebuilt over and over during the mass delete operation. If I delete a small number of records (i.e., DELETE FROM table WHERE id BETWEEN 1 AND 5) it may only take a minute or so. If I delete a large number of recor

Pb: auto_increment - insert zero value

Hi, I have problem in the insertion of 0 in auto_increment. I have set in my.ini file as follows, sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO" Even now the auto increment filed is not allows to insert a zero; If i insert zero, 1 is getting i

Re: Question about DELETE

Given that OP is talking about a single delete statement, I'm gonna be very surprised if he manages to squeeze an intermediate commit in there :-) For a single-statement delete on a single table, the indexes will be rebuilt only once. I'm not entirely sure what happens to cascaded deletes, though.

Re: MySQL Storage Engine

Thanks for the useful information. Can you let me know any other things I need to consider ? Regards Neil On Thu, Mar 18, 2010 at 9:50 AM, Krishna Chandra Prajapati < prajapat...@gmail.com> wrote: > Hi Neil, > > Many times we need to generate reports (weekly, montly, yearly) from the > data we

Re: Replication - LINUX to WIN

Hi Vikram, As far as I know it's just table names: I think it's related to the fact that the Windows file system is case-insensitive, while Linux filesystems generally are not. On Thu, 2010-03-18 at 15:18 +0530, Vikram A wrote: > Hello Ian Simpson, > > Thank you for reply. We defined table names

Re: MySQL Storage Engine

Hi Neil, Many times we need to generate reports (weekly, montly, yearly) from the data we have. For detailed reports we have to use joins on many tables. So, it takes time from 2 to 5. So these types of activities must be performed on slave server. If you need reports then you should have master

Re: Replication - LINUX to WIN

Hello Ian Simpson, Thank you for reply. We defined table names, fields in lower cases except the KEYS . I hope the Key will not taken into the account[all in upper case]. Regarding the version we will keep same version as you said. Thank you --- On Thu, 18/3/10, Ian Simpson wrote: From: Ian

Re: MySQL Storage Engine

Hi How do you mean "executed on slave" ? Neil On Thu, Mar 18, 2010 at 9:32 AM, Krishna Chandra Prajapati < prajapat...@gmail.com> wrote: > Hi Neil, > > Recommend : Innodb storage engine > > Even, i would suggest inner join select query (if report query might takes > few seconds) should be execu

Re: MySQL Storage Engine

Hi Neil, As Krishna said, Innodb is generally always superior to MyISAM unless you need full-text search (and even if you do there are alternatives to MyISAMs full-text search, which I've often found to be lacking in performance). A poorly optimised query will be slow whether it is called from a

Re: Replication - LINUX to WIN

Hi Vikram, There are a couple of potential problems with Linux to Windows replication, but they are easily avoidable. 1) Table names: There are case-sensitivity differences between Linux and Windows: Linux is case-sensitive as regards table names, whereas Windows is not. Given that you are replic

Re: MySQL Storage Engine

Hi Neil, Recommend : Innodb storage engine Even, i would suggest inner join select query (if report query might takes few seconds) should be executed on slave. Before doing any thing. Plan out all the requirement. Regards, Krishna On Thu, Mar 18, 2010 at 2:48 PM, Tompkins Neil wrote: > Hi >

MySQL Storage Engine

Hi I'm currently looking to develop an on line web application - which is going to be used by around 200+ concurrent users at any one time. Can you recommend to me the best/preferred MySQL database engine to use for example MyISAM ? Also, in your experiences, do you recommend that SELECT stateme

Re: Replication - LINUX to WIN

Yes, go ahead. -Krishna On Thu, Mar 18, 2010 at 2:03 PM, Vikram A wrote: > Hi, > I have done replication with Win to Win servers with mysql version > 5.0.41-community-nt. > > Now, > We have Enterprise Linux(Red Hat 4.1.2-14) and windows server2003. > Is it possible do the replication LINUX(Mast

Replication - LINUX to WIN

Hi, I have done replication with Win to Win servers with mysql version 5.0.41-community-nt. Now, We have Enterprise Linux(Red Hat 4.1.2-14) and windows server2003. Is it possible do the replication LINUX(Master) to WINDOWS SERVER2003(Slave) ? It will be great help to me. Thank you. VIKRAM A