Mysql - lock wait timeout while updating 70 million records 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??
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.