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??