MySQL Forums
Forum List  »  Performance

Performance trouble at 35M rows
Posted by: Marcus
Date: November 05, 2004 11:47PM

Hi,

I've got a table that currently has over 35 million rows, and I've stopped seeing that legendary MySQL performance. My first few steps have been to spend a lot of time looking at memory settings in the my.cnf, but really haven't seen a notable change.

It's a hefty Quad Xeon server with 8GB of RAM that we're running on.

Table has 49 columns. The database operations that I'm seeing problems with are even as simple as creating an index. I'm currently looking at a 5 hour wait for index creation on a simple varchar(255) or integer(11) field, even tinyint(1).

Watching top, I see 99% CPU usage for 1 CPU during the entire operation, which leaves me thinking I'm not looking as disk performance issues, even though I haven't focussed on optimizing file locations yet. (Disks are hardware RAID).

Below are my my.cnf settings. They may be a little screwy cause I'm at that "try anything" stage.

key_buffer = 2048M
max_allowed_packet = 1M
table_cache = 512
sort_buffer_size = 10M
read_buffer_size = 5M
max_connections = 20
myisam_sort_buffer_size = 64M
thread_cache = 8
query_cache_size = 32M
thread_concurrency = 8

Table types are MyISAM.

Even dropping an index from a table (unique index in this case) has been running for over 3 hours now, so I expect it will be the same 5 hour process. What am I waiting on here? Any ideas?

Thanks in advance,

Marcus

Options: ReplyQuote


Subject
Views
Written By
Posted
Performance trouble at 35M rows
4444
November 05, 2004 11:47PM
2370
November 07, 2004 09:02PM
2459
November 10, 2004 06:55PM
2447
November 26, 2004 11:57PM
2362
November 19, 2004 04:49AM
2395
November 26, 2004 01:43AM
2419
November 27, 2004 09:21AM


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.