PHP，MySQL - 加载大约100k记录并将它们导出到xml

Question

I have MySQL InnoDB table with 1 milion of rows and I selecting 100K rows for export. Table have around 200 columns.

What have I done so far:

1. not select all with *

   SELECT column1, column2, ... FROM my_table WHERE deleted=0 -- load 100k records

2. using XMLWriter php library with flush

   $writer = new XMLWriter();
   $writer->openMemory();
   $writer->setIndent(true);
   $writer->startDocument('1.0', 'UTF-8');
   $writer->startElement('export');
   
   $iterator = 0;
   $data = $this->getData();
   
   foreach($adverts as $advert) {
       $writer->startElement('ad');
       $writer->writeElement('id', $data->id);
       // .. other columns
   
       $writer->endElement(); // end ad
   
       if (0 == $iterator % 1000) {
           file_put_contents($this->getFilePath(), $writer->flush(TRUE), FILE_APPEND);
       }
   
       $iterator++;
   }
   

But I still have Fatal error: Allowed memory size of ... bytes exhausted

Are there any other ways how to optimize that? I think I can maybe load data from database other ways, like load only ids in first round and then select IN (10k_ids), but I don't test this idea yet.

Thank you for your opinions.

---

I have very similar question as in How to export HTML table with 100K records with number formatting without memory exhaust

But there is not a way how to achieve low memory consuption.

Answer 1

I found solution, problem was that I load many data.

I made 3 upgrades:

1. use function to log memory limit

   /**
    * @param $message
    */
   protected function logMemoryUsage($message)
   {
       Debugger::log($message . ": " . memory_get_usage()/1048576 ." MB");
   }
   

2. then I use fopen + fwrite + fclose instead of file_put_contents

   $file = fopen($this->getFilePath(), 'a+');
   fwrite($file, $writer->flush(TRUE));
   fclose($file);
   

3. load data in loop (only 10k records in one time)

   $this->logMemoryUsage("Memory usage before load");
   $data = $this->getData($lastId);
   
   do {
       $this->logMemoryUsage("Memory usage");
       $lastId = NULL;
   
       foreach($data as $item) {
           $writer->startElement('ad');
           $writer->writeElement('id', $item->id);
           ...
   
           if (0 == $iterator % 5000) {
               fwrite($file, $writer->flush(TRUE));
           }
   
           $lastId = $item->id;
           $iterator++;
       }
   
       $data = $this->getData($lastId);
   
   } while(!empty($data));
   
   $this->logMemoryUsage("Memory usage after");
   fwrite($file, $writer->flush(TRUE));
   fclose($file);
   
   
   
   public function getData($fromId = NULL, $limit = 10000)
   {
       $data = db::query("SELECT a,b,c FROM my_table WHERE deleted=0 AND id>? ORDER BY id ASC LIMIT ?", $fromId, $limit)->fetchAll();
   }
   

And the output is now:

    export start
    Memory usage before load: 3.6202011108398 MB
    Memory usage: 59.487106323242 MB
    Memory usage: 124.53610229492 MB
    Memory usage: 124.89745330811 MB
    Memory usage: 124.43883514404 MB
    Memory usage: 124.20503234863 MB
    Memory usage: 124.2151184082 MB
    Memory usage: 124.46990966797 MB
    Memory usage: 106.50185394287 MB
    Memory usage: 53.009048461914 MB
    export end