Tonic-_- Posted May 8, 2011 Share Posted May 8, 2011 For the past month my setup has been going just fine and haven't had any issues out of MySQL... I upgraded the CPU's recently to something a tiny bit more powerful and since then i've had nothing but problems... SELECT Count has just randomly became very very slow. The table I am using count on is around 6.4GB's big with around 60 million entries. As I said didn't have a problem till I swapped the CPU's out for something a bit higher up. The tables storage engines is MyISAM with a few indexes. But the slowness doesn't just effect this it can also effect another table at times that is 3 million entries but it's not AS slow as the primary one. My my.cnf [mysqld] socket = /var/lib/mysql/mysql.sock max_allowed_packet=64MB datadir=/var/lib/mysql safe-show-database old_passwords max_connections = 2000 key_buffer = 1024M myisam_sort_buffer_size = 150M join_buffer_size = 2M read_buffer_size = 2M sort_buffer_size = 2M table_cache = 2048 thread_cache_size = 50 interactive_timeout = 60 wait_timeout = 30 connect_timeout = 10 tmp_table_size = 200M max_heap_table_size = 200M max_connect_errors = 10 read_rnd_buffer_size = 4M query_cache_limit = 120M query_cache_size = 900M query_cache_type = 1 default-storage-engine = MyISAM log_slow_queries=/var/log/mysqld.slow.log long_query_time=2 key_buffer=64M sort_buffer=64M read_buffer=16M #write_buffer=16M query_prealloc_size = 65536 query_alloc_block_size = 131072 max_heap_table_size = 256M Has anyone ever ran into something similar to this? Or know of any way to fix this? I don't see why the CPU upgrade would cause this, haven't changed anything software configuration wise just hardware :\ Quote Link to comment Share on other sites More sharing options...
sunfighter Posted May 8, 2011 Share Posted May 8, 2011 First step in trouble shooting -- What have you changed???? Can you change it back???? The CPU is probably over heating (You hope) Did you use heat sink compound on it ? Did you made sure the heat sink is making good contact? I'd check those things first. There is a program that monitors cpu temp (I think it measures current input?) use it to take a look. Quote Link to comment Share on other sites More sharing options...
Tonic-_- Posted May 9, 2011 Author Share Posted May 9, 2011 lol I don't think that's the problem... I have a dedicated server in Dallas Texas. Dual Xeon E5620 12GB of ram 2 x 250GB RE4 HDD's I've tried various tuning options and still can't seem to get MySQL to perform faster when doing queries that select a large amount of rows, I've tried running optimize on the table to defragment it and that doesn't do any good, i've tried loading the indexes into the cache, still doesn't help. Quote Link to comment Share on other sites More sharing options...
fenway Posted May 13, 2011 Share Posted May 13, 2011 64MB packet size? Why? Post the query, and the explain output, the create table, and the size of the data/index files. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.