Prepared SQL query and bad_alloc

SGaist

Hi,

Just a few questions that might help:

are you on 32 or 64 bit ?

are you reading the file sequentially or all at once ?

Can you share the importation code ?

Vinorcola

I'm on a 32 bits computer.
And no the file is read line by line (otherwise, it will be a real problem for memory usage ;) )

The way I'm going is the following (I can't really share the code since it is for a company) :
I read a line in the file into a QString.
I parse this QString and split it into a QStringList (The first line is used to build the create table and the insert into query with the columns header names).
And then, I use this QStringList to bindValue into the prepared request.

I made sure The QString and the QStringList have been cleared before reading a new line from the file.

I'll look further with the memory analyser though I've never used a such tool. And i'll come back to you then.

Vinorcola

I forget to tell that if I just read and parse the whole file without inserting into the DB (commenting all the SQL queries), the program run fine.
So that's why it seams to come during the databse importation.

SGaist

Are you batching the inserts ? Or are you preparing the query with all the content from the file and only then execute it ?

Vinorcola

I'm reading a line from the file, parse it, bind the values, and execute the query.
Then I read the next line from the file, parse it, bind the values, and execute the query.
etc.
etc.

SGaist

Sounds right, can you show your code ?

By the way, what version of Qt/OS are you using ?

Vinorcola

Ok, I created a smal test that I run with Qt 5.2 under Windows 7. And I effectively notice the memory used by the program is constantly increasing until the work finish. Here is the code for the test.

main.cpp
@
#include <QApplication>
#include "Window.hpp"

int main(int argc, char *argv[])
{
// Configuration de l'application.
QApplication app(argc, argv);

// Création du contrôleur principal.
Window* window(new Window);
window->show();

return app.exec&#40;&#41;;

}
@

Window.hpp
@
#include <QPushButton>

class Window : public QWidget
{
Q_OBJECT

private:
    QPushButton* button;
    
public:
    Window(&#41;;
    
public slots:
    void start(&#41;;

};
@

Window.cpp
@
#include "Window.hpp"

#include <QMessageBox>
#include <QSqlDatabase>
#include <QSqlQuery>
#include <QVariant>
#include <QVBoxLayout>

Window::Window() :
QWidget(),
button(new QPushButton("Start"))
{
QVBoxLayout* layout(new QVBoxLayout);
layout->addWidget(button);
setLayout(layout);

setMinimumSize(400, 200);

connect(button, &QPushButton::clicked, this, &Window::start);

}

void Window::start()
{
// Database connection configuration...
QSqlDatabase db(QSqlDatabase::addDatabase("QPSQL"));
db.setHostName("");
db.setPort(5432);
db.setDatabaseName("");
db.setUserName("");
db.setPassword("");

// Test configuration...
int nbColumn(30);
int nbRow(1000000);

if (db.open())
{
    // Queries preparation...
    QString requestCreate("CREATE TABLE test (id INTEGER, ");
    QString requestInsert("INSERT INTO test (id");
    QString requestValues(") VALUES (?");
    for (int col(0); col < nbColumn; ++col)
    {
        requestCreate += "column" + QString::number(col) + " varchar(100), ";
        requestInsert += ", column" + QString::number(col);
        requestValues += ", ?";
    }
    requestCreate += "CONSTRAINT pk_test PRIMARY KEY (id))";
    requestInsert += requestValues + ")";
    
    // Table creation...
    QSqlQuery query(requestCreate, db);
    query.exec&#40;&#41;;
    
    // Data insertion...
    QSqlQuery query2(db&#41;;
    query2.prepare(requestInsert&#41;;
    for (int row(1&#41;; row <= nbRow; ++row)
    {
        query2.bindValue(0, row);// Insert the id
        for (int col(1); col <= nbColumn; ++col)
        {
            query2.bindValue(col, "Data from the row " + QString::number(row) + " and column " + QString::number(col));
        }
        query2.exec(&#41;;
    }
    
    // Close the connection.
    db.close(&#41;;
}

}
@

If some can test and see if the memory usage grows up too. If it does, it means apparently, there is something stored all along in QSqlQuery (maybe QSlqDatabase ?) which is released when the object is destroyed.

SGaist

I don't have a PostgreSQL server at hand. Can you do the test with sqlite ?

Vinorcola

I can try tomorow with MySQL. Can SQLite manager such big files? If it does, I can try tomorow too.
It could come from the Postgres driver? I'll also try on Ubuntu to see if I get the same issue.

SGaist

AFAIK, it should.

It can come from several places, first thing to do is determine whether it's specific to PostgreSQL then the rest

Vinorcola

Ok, so I made few different test on Windows (seven - 32bits - Qt 5.2.1) :
In the test configuration part of my code, I put nbColumn = 50 and nbRow = 500,000. I get those results:

Postgres:

at the end of the task: around 120,000 K of memory used
after exit the start() method: around 18,000 K of memory used

MySQL:

at the end of the task: around 58,000 K of memory used
after exit the start() method: around 6,000 K of memory used

SQLite:

at the end of the task: around 120,000 K of memory used
after exit the start() method: around 16,000 K of memory used

I did only 1 test for each because it take too much time. If some other people can to the test on another machine to see if they get the same result, it will be more relevant. I didn't have enough time to test on Ubuntu.

DoctorBit

I have this same problem. It's difficult to diagnose because one would expect lots of selects, updates, inserts, and deletes to increase memory usage due to caching - but one would not expect the process to crash with an out-of-memory condition when memory fills up. My app is only about 800 lines and I don't call 'malloc' or new at all anywhere in my code. All variables are local stack variables or local static variables. Here's the error message I get:

terminate called after throwing an instance of 'std::bad_alloc'
what(): std::bad_alloc
Aborted

My system:

Core i5 2500K processor
16 GB of RAM
Linux Mint 64 bit
Postgresql 9.4.0
GCC 4.9.2 64 bit
Qt Creator 3.1.1
Qt 5.2.1 (GCC 4.6.1, 64 bit)

DoctorBit

I have this same problem. It's difficult to diagnose because one would expect lots of selects, updates, inserts, and deletes to increase memory usage due to caching - but one would not expect the process to crash with an out-of-memory condition when memory fills up. My app is only about 800 lines and I don't call 'malloc' or new at all anywhere in my code. All variables are local stack variables or local static variables. Here's the error message I get:

terminate called after throwing an instance of 'std::bad_alloc'
what(): std::bad_alloc
Aborted

My system:

Core i5 2500K processor
16 GB of RAM
Linux Mint 64 bit
Postgresql 9.4.0
GCC 4.9.2 64 bit
Qt Creator 3.1.1
Qt 5.2.1 (GCC 4.6.1, 64 bit)

DoctorBit

Upgraded to Qt 5.4.0 and the problem seems to have gone away.

DoctorBit

Upgraded to Qt 5.4.0 and the problem seems to have gone away.