0

i have deployed my website and mysql server on a single server.

Disk space - 20 GB;

RAM - 16GB;

Currently there are 70 million records in the database and i am writing update query to change the "status" attributes of all this 70 million records. after executing the update query i am getting error as "mysql lock wait timeout". below is my sql query

update authentication_codes set status=true;

I think i am getting this error because mysql is not able to execute this query within lock timeout specified in mysql cofig file. increase lock timeout value will work but in the future database will go on increasing so i think this is not good solution.

Please tell me what mysql settings i have to change to increase the mysql performance. Do i have to increase the ram for processing 70 million records?? Do i have to increase hard disk size??

Improve this question
1
  • 1
    You can for example do update authentication_codes set status=true where status=false limit 1000000; - just execute it multiple times until it returns without updating any row. If it still gives errors or takes too long for one run, use 100k instead of 1M limit. It would be good if there were index on the status field (maybe you need to do status != true, that cannot use index anyway)
    – jkavalik
    Commented Mar 6, 2016 at 18:50

1 Answer 1

Reset to default
1

Redesign. Relational databases let you update every row in a table, but it is poor design to need to do such. Please elaborate on what you are really doing; we can probably discuss another way to do the task.

  • Compute the flag as needed instead of needing the massive update.
  • Avoid the flag completely.
  • Have a parallel table with nothing but the id and flag.
  • Walk through the table 1K rows at a time -- takes longer to finish, but is less invasive. (No, do not use OFFSET.)

A disk of only 20GB is very tiny by today's standards. How much of that is your 70M table? It sounds like you have no room to maneuver. So, we may have to take that into account when redesigning.

Please provide SHOW CREATE TABLE; perhaps we can shrink some of the datatypes to conserve space.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.