0

Site is an ecommerce site: 5 Million records in table Two servers: one for webserver and other contains mysql Search is happenning through Sphinx server. So search queries do not come to MySQL

Mysql configuration: Dual Quad Core Zeoo 2.0, 146 GB, 16 GB RAM. Webserver configuration: Dual Quad Core Zeoo 2.0, 146 GB, 16 GB RAM.

For past four days I find MySQL is using CPU continuously for at least 6-7 hours in a day. It becomes normal after that. Even if I restart, it doesnt stop. It again uses CPU in 2 to 3 mins. I even tried stopping Apache and made sure no one is connecting to Mysql.

Webinfo2011
  • 11
  • 6
  • 3
    Did you chack with `mysqladmin pr` what's going on ? Maybe it's rebuilding an index or repairing a corrupt table – nos Jul 15 '11 at 17:12
  • I am not sure if table is repair. Because its working properly for atleast 12 hours. How to check if its rebuilding index. – Webinfo2011 Jul 15 '11 at 17:18
  • I also took some info using SHOW ENGINE INNODB STATUS – Webinfo2011 Jul 15 '11 at 17:25
  • I also found this in mysqld log110715 22:23:43 [ERROR] /usr/libexec/mysqld: Incorrect key file for table '/tmp/#sql_2184_2.MYI'; try to repair it 110715 22:23:49 [ERROR] /usr/libexec/mysqld: Disk is full writing './morecom_db/system_log.MYD' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space) 110715 22:23:49 [ERROR] /usr/libexec/mysqld: Retry in 60 secs. Message reprinted in 600 secs – Webinfo2011 Jul 15 '11 at 17:47
  • That means your mysql server is in a very serious condition. Take it offline, get more space on the machine (it might just be the mysql tmp folder that runs out of space), repair the tables. – nos Jul 15 '11 at 17:58
  • I can see I have more space. Space actually grows automatically and reduces automatically. I dont know why. I had only 5 GB free space and now I can see more. Filesystem Size Used Avail Use% Mounted on /dev/sda3 124G 108G 9.8G 92% / /dev/sda1 99M 12M 82M 13% /boot tmpfs 7.9G 0 7.9G 0% /dev/shm – Webinfo2011 Jul 15 '11 at 18:00
  • I repaired the table fully still getting the same error – Webinfo2011 Jul 16 '11 at 04:19

1 Answers1

1

I just noticed the same issue on my server and tried to do something about it. I upgraded mysql and all it did was reduce the amount of CPU used - from 100% to 55%

I think my issue is Leap Second related - I notice your question is 15 days after the lead second.

I found: EC2 Amazon Linux AMI MySQL CPU @ 62% When Idle?

Which lead me to: Why Does the Leap Second Cause Problems?

Lastly I did the classic windows fix and rebooted the linux box - this worked (did not check if kernel had been upgraded in mean time)

Community
  • 1
  • 1
Adrian Cornish
  • 23,227
  • 13
  • 61
  • 77