Re: How to improve performance in a table with 70 million rows

[Srikanth Mamidi]

> RAM size is 1.7GB
I know this is too small but I need to convince my boss !!

>buffer_pool of 1G might be too big.
Found in some articles that its good to have buffer_pool size around 70% of RAM.

> `friends` int(10) unsigned NOT NULL,
>Some people have a billion friends? Consider MEDIUMINT UNSIGNED (16M max) or...
Yes, the initial choice of data types and auto-increment column 'id' were not designed properly...now even if I want to alter the table with 70 million rows, it would take around half a day which cant be afforded.

>Are you doing INSERTs in multiple threads? If so, locking is an issue. >Replication is involved, too? There is no parallelism on the Slave.
> we have 2 java programs
>Just 2? 2 might not be much better than 1, and is possibly not as good as several.
There is no replication and yes the INSERTs are from 2 diff java programs, the reason is not to achieve multi-threading but the 2 programs are for 2 different type of inserts to the same table, one of the programs updates only 'friends' column and the other java program to update existing user info or to add new users.

How big is a "transaction"? (BEGIN ... COMMIT)
Each transaction has only one bulk INSERT query.

> bulk inserts in batches of 1000
>Good number. Do you then COMMIT? Are multiple threads doing such?
As I have mentioned above, 2 java programs do such INSERTs.

>Batch inserts with multiple keys are likely to get deadlocks. A partial fix is to >sort by a key before building the INSERT. Preferably, use a PRIMARY or UNIQUE key.
Will try this and let you know.
The queries were much faster (about 40% less time) after innodb_log_file size is changed to 100M but strangely the deadlocks were much frequent after I changed the innodb_log_file size to 100M and after reverting to 10M, I rarely see deadlocks.

>You have 3 keys. That means 3 things are updated for each row inserted. I suggest >how to get rid of one of them. I assume the user_name and the userid are both >jumping around? That would mean two 'random' updates to disk.
We can probably get rid of the key for 'lastUpd' but we still need keys for 'userID' and 'userName'