MySQL Forums
Forum List  »  Performance

Re: How to improve performance in a table with 70 million rows
Posted by: Rick James
Date: August 10, 2012 12:16AM

> RAM size is 1.7GB
Yikes; that's tiny these days.
buffer_pool of 1G might be too big.
Don't let mysql swap -- that would be worse than having a smaller buffer_pool.

See also
http://forums.mysql.com/read.php?106,564229,564557

> `friends` int(10) unsigned NOT NULL,
Some people have a billion friends? Consider MEDIUMINT UNSIGNED (16M max) or...
That would shrink the data some. Smaller --> Faster (when I/O is involved).

How big is a "transaction"? (BEGIN ... COMMIT)

> select * from user_data where user_name in(<uname1>,<uname2>...);
> select * from user_data where userID =<userid>;
You can use partition pruning for one or the other, but not both. PARTITINOing buys you no performance for either of them.

> Whenever you insert a record, the partitioning engine locks the entire table.
Aftab, he is using InnoDB. So, I think it will _open_ each partition, but not _lock_ each one.

Are you doing INSERTs in multiple threads? If so, locking is an issue. Replication is involved, too? There is no parallelism on the Slave.

> bulk inserts in batches of 1000
Good number. Do you then COMMIT? Are multiple threads doing such?

innodb_log_file size matters if the transactions are large, or if you have big blobs (which you don't seem to have).

> we have 2 java programs
Just 2? 2 might not be much better than 1, and is possibly not as good as several.

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.

> might be both programs trying to update the table for same userID
More likely it is "gap" locking.

> like changing the batch size of bulk updates
Changing to 1 would work. But performance would be 10 times as slow.

> does partitioning help again
No.

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.