2

I'm currently having an issue on 2 out of 3 SQL servers when copying large amounts of data to a MySQL table. The MySQL table currently houses ~6 million records, and the query that is running is trying to update / delete / insert another 100,000 records.

I've attempted to use OPENQUERY on the commands I'm running to attempt to do this but that's not working either and I am just presented with the error:

"[MySQL][ODBC 5.1 Driver][mysqld-5.6.35]MySQL client ran out of memory"

I thought it could be a driver issue so updated to the latest ODBC driver (5.2) but that did not work either.

The two servers i am using are both 32 bit operating systems which may have a great deal to do with it (the 64 bit server hasn't had an issue as yet).

Is there another setting i can have on the server that will let it pool values and process with what memory it has available or am I just stuck with an issue of needing more memory for processing a result list that is this large?

UPDATE: the third server is now not working either, all showing same results

My cnf settings just have:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 512K
innodb_buffer_pool_size = 1G
innodb_log_file_size = 64M
join_buffer_size = 128K
innodb_buffer_pool_instances = 1

Have to setup the symbolic-links section still, but at the moment this is still a non-production server so not a big issue.

The two statements that are failing are inserting and deleting rows from a MySQL table that is > 4 million rows. Both are inside a stored procedure that reads values from a transfer table (transfer table is filled via triggers on the main tables which read in whether the query should send a delete or insert request to the mysql table)

    DELETE
        a
    FROM
        [LinkServerTable]...Table1 as a
    INNER JOIN
        [dbo].[TransferTable] as b
    ON
        a.Key1 COLLATE DATABASE_DEFAULT = b.Key1 COLLATE DATABASE_DEFAULT
    AND
        a.Key2 = b.Key2
    AND
        a.Key3 = b.Key3
    AND
        a.Key4 = b.Key4
    AND
        a.Key5 = b.Key5
    WHERE
        a.Branch = 'Databases Branch'
    AND
        (b.QueryType = 'Delete' OR b.QueryType = 'Update')
    AND
        b.DateQueried < @DateNowVar

    INSERT INTO [LinkServerTable]...Table1
    (
            Columns,
            Branch
        )
    SELECT
        b.Columns,
        Branch = 'Databases Branch'
    FROM
        [dbo].[TransferTable] as a
    INNER JOIN
        [dbo].OriginalTable as b
    ON
        a.Key1 COLLATE DATABASE_DEFAULT = b.Key1 COLLATE DATABASE_DEFAULT
    AND
        a.Key2 = b.Key2
    AND
        a.Key3 = b.Key3
    AND
        a.Key4 = b.Key4
    AND
        a.Key5 = b.Key5
    WHERE
        (QueryType = 'Insert' OR QueryType = 'Update')
    AND
        DateQueried < @DateNowVar
    AND
        NOT EXISTS
            (SELECT
                1
             FROM
                Transfertable as c
             WHERE
                a.Key1 = c.Key1
             AND
                a.Key2 = c.Key2
            AND
                a.Key3 = c.Key3
            AND
                a.Key4 = c.Key4
            AND
                a.Key5 = c.Key5
             AND
                a.DateQueried < c.DateQueried)

That extra statement at the end of the insert section is so it won't process multiple updates / deletes if not required (if they've been updated multiple times in the time it takes to run the procedure)

migrated from stackoverflow.com May 9 '17 at 8:10

This question came from our site for professional and enthusiast programmers.

  • It is rare for MySQL to get "out of memory". Please show us your my.cnf settings. – Rick James May 9 '17 at 16:38
  • Hey Rick, I don't think its actually MySQL but rather the ODBC drivers from SQL. Just noticed that what i thought was the 64-bit server is actually running 32-bit sql and using 32-bit odbc drivers as well. Have updated my question with my my.cnf settings but – Rando May 9 '17 at 23:58
  • my.cnf looks fine if you have 4GB of RAM; tight if you have 2GB. Any clue of what SQL was involved when it ran out of memory? – Rick James May 10 '17 at 0:03
  • Sweet, sitting on a t2.medium Amazon instance which is 4GB mem. For the SQL it fails on delete and insert statements to a table that has > 4 million rows, will update my question with the 2 statements that fail. – Rando May 10 '17 at 0:11
  • Why is AND a.Key2 = b.Key2 in the last subquery? b is relevant to it? – Rick James May 10 '17 at 0:27
1

Do the DELETE in chunks. More discussion .

  • Hey Rick, not 100% sure how to push a query like that through a linked table, at the moment when i use openquery on too many results it still says out of memory so would have to do something like OPENQUERY('LinkedDB', DELETE FROM LinkedTable WHERE Key1 = @Key1 AND Key2 = @Key2') etc. Or is there a more optimal structure for chunking data across a linked table? – Rando May 10 '17 at 0:44
  • Well, you are 100% ahead of me. I've never used SQL Server or a linked table or OPENQUERY. Sorry. I am trying to come up with ways to decrease the "size" of whatever is going on -- in the hopes that the problem would go away. If you can work on MySQL directly, I might be able to help. – Rick James May 10 '17 at 0:51
  • Ah bugger, no worries, I'll check out the chunking there could possibly be a way to parameterize all of the items and send through OPENQUERY. I know for sure if these tables had only 1 key i could've easily just created a NOT IN statement but can't be done. Unfortunately I can't work direct from MySQL as it doesn't have a way to link back to the MsSQL server for the original table values (at the moment Linked Tables are only available for MsSQL -> MySQL not MySQL -> MsSQL). I could possibly instead create a second set of transfer tables on MySQL that MsSQL copies its transfer tables to. – Rando May 10 '17 at 0:56
  • Perhaps "linking" copies the entire table, then works with it. (Again, that is part of my 0% knowledge.) – Rick James May 10 '17 at 1:25
  • I hoped so, but unfortunately not, only hope for accessing the items would be OpenQuery. But it did open a new possibility of transferring the current transfer table to MySQL, then from MsSQL using OpenQuery to collect the values from MySQL, joining to original table in MsSQL then processing...This may still end in an out of memory issue buuuuut worth a shot. – Rando May 10 '17 at 2:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.