Explain Codes LogoExplain Codes Logo

Php: maximum execution time when importing large .SQL data files

sql
importing-large-data
performance-optimization
command-line-tools
Anton ShumikhinbyAnton Shumikhin·Oct 11, 2024
TLDR

To prolong PHP's script execution time for hefty SQL imports, use ini_set('max_execution_time', '300'); in your script for a 5-minute limit, or set_time_limit(300); for CLI operations. Adjust the '300' as needed, keeping sluggish timeouts at bay.

Handling PHP timeouts: What are the options?

Extend phpMyAdmin configuration

To stretch the limits in phpMyAdmin, find config.inc.php in your phpmyadmin directory. Can't locate it? Don't fret; just make a copy of config.sample.inc.php as config.inc.php, then add:

// Want unlimited power (or at least time)? Here you go! $cfg['ExecTimeLimit'] = 0;

This change dictates the maximum execution time. Chew on that, timeouts!

Tweak with PHP.ini

Adjust the following settings in php.ini ensuring they suit your file and script size needs:

max_execution_time = 0 // Unlimited power... I mean time. post_max_size = 128M // Adjust to file size, like a tailored suit. upload_max_filesize = 128M // Twinning! Should match or outpower post_max_size.

Server restart: Bathe in the Ambience of Fresh Changes

Just as you won't taste the salt you just added until you stir the soup, server settings won't come to effect until you restart your web server and MySQL services. Your changes need to mingle!

Power Importing Techniques: Befriending the Command Line

For colossal SQL files, it's best to summon the command-line tools like mysql. More efficient and quite the strongman!

mysql -u username -p database_name < file.sql // Flex those command-line muscles, mate!

Handling large imports: Making it work

Hands-free with Command-line Utilities

Trendy command-line tools can handle sizeable data sets, bypassing web server limitations:

mysql -u your_username -p your_database < your_whale_of_a_file.sql // Let the CLI do the heavyweight lifting!

Leveraging Database Management Tools

Programs like SQLYog or Sequel Pro, with dexterously designed data handling and efficient import methods, can handle large data sets baked into SQL imports like a breeze.

Performance awareness and long scripts

Be wary that long scripts can cause game stoppages or bumpy rides for your server. Plan imports during hush hours or opt for a "little by little" incremental import playbook.

Game On - Resuming After Timeouts

Timeout? Brush it off, apply the indicated settings, and rerun the import operation. Remember: Tools like BigDump can stagger the import helping dodge future timeouts without breaking a sweat.