4- Question

Hi guys, i have a server with 20GB InnoDb on a single database and i
use just one user for its. I use mysql 5.4

1- If i change my SCSI 15k RPM to a SSD i will increase my performance ?

2- What is the fast method to a backup to this InnoDB database ? And
the more easy ? And how i restore its ?

3-I need make a clean on one table, its something like:

table 1 = 400k rows ( Index field )
table 2 = 15kk rows ( Index field )
table 3 = 150k rows ( Index field )
table 4 = 15kk rows ( Index field )

Its like: "delete from table 4 where id NOT IN ( select id from table1
) and id NOT IN ( select id from table2 ) id NOT IN ( select id from
table3 ) "

Have others better methos to make its ?

4- InnoDB have a optimize table ? For re-index or something to make a
better performance



Thanks guys !!

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: 4- Question

On Sun, May 2, 2010 at 11:50 PM, Junior Ortis wrote:
> Hi guys, i have a server with 20GB InnoDb on a single database and i
> use just one user for its. I use mysql 5.4
>
> 1- If i change my SCSI 15k RPM to a SSD i will increase my performance ?

Umm, maybe. More information is required. Does the entire dataset
float in RAM? Does an IO bottleneck impact performance at all? What
sort of SSD? How many drives in either configuration?

> 2- What is the fast method to a backup to this InnoDB database ? =A0And
> the more easy ? And how i restore its ?

20GB ain't much data. Consider "mysqldump --single-tranaction
--master-data > dump.sql ". (You are storing binary logs so that
master data is useful, right?) The restore is super simple with "mysql
< dump.sql".

The answer you may want is "xtrabackup". If you go that route a plain
text backup is still a good idea.

> 3-I need make a clean on one table, its something like:
>
> table 1 =3D 400k rows ( Index field )
> table 2 =3D 15kk rows ( Index field )
> table 3 =3D 150k rows ( Index field )
> table 4 =3D 15kk rows ( Index field )
>
> Its like: "delete from table 4 where id NOT IN ( select id from table1
> ) and id NOT IN ( select id from table2 ) id NOT IN ( select id from
> table3 ) "
>
> Have others better methos to make its ?

Define better. NOT IN with a sub query is generally a poor performer.
In my experience removing 10k rows at a time I have found to be often
ideal.

> 4- InnoDB have a optimize table ? For re-index or something to make a
> better performance

http://tinyurl.com/32ysu4s
I have lots of boxes and darn near never run optimize table on a
innodb table. Exceptions are made for massive bloat when file per
table is in use or statistics insanity that survives a analyze table.


--=20
Rob Wultsch
wultsch@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg