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
Subject
Views
Written By
Posted
Performance trouble at 35M rows
4599
November 05, 2004 11:47PM
2452
November 07, 2004 09:02PM
2537
November 10, 2004 06:55PM
2522
November 26, 2004 11:57PM
2428
November 19, 2004 04:49AM
2464
November 26, 2004 01:43AM
2495
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.