[MDEV-17566] Reduce SP memory usage Created: 2018-10-30  Updated: 2022-08-02

Status: Stalled
Project: MariaDB Server
Component/s: Stored routines
Fix Version/s: 11.0

Type: Task Priority: Major
Reporter: Alexander Barkov Assignee: Unassigned
Resolution: Unresolved Votes: 2
Labels: None

Attachments: File reduce_sp_memory.diff    

 Description   

This contributed patch came from Jerome Brauge. Please find the patch attached.

As stored procedures seems to be very memory aggressive, I found two possible optimizations to avoid having three strings containing the stored procedure code in memory.

Probably, same optimizations may be applied to packages.
I haven’t add any tests because I think everything is covered by the existing tests.

What do you think about ?

In a second time, I think working on SP cache.

Currently it’ s a simple hash table and all the hash table is flushed as soon as the threshold (stored_program_cache) is reached.

It's not very good.

We could :

  • Use a more intelligent cache (LFU ?)
  • Add some performance counters

Regards.

 Comments   
Comment by Sergei Golubchik [ 2019-05-20 ]
  • as far as I understand, this patch avoids allocating memory for sp_head::m_defstr until the first SHOW CREATE. Is that right?
  • if yes, why to allocate memory for it at all? Just allocate it in THD's memroot, and recalculate for every SHOW CREATE — it's not like someone is going to run many SHOW CREATE for the same routine, over and over again.
  • This change

    @@ -796,17 +796,11 @@ sp_head::set_stmt_end(THD *thd)

    		 
    -  m_body_utf8.str= thd->strmake(lip->get_body_utf8_str(), m_body_utf8.length);
    		 
    +  m_body_utf8.str= strmake_root(m_thd_root, lip->get_body_utf8_str(), m_body_utf8.length);

    what does it mean when m_thd_root != thd->mem_root? When these two memroots differ?

Generated at Thu Feb 08 08:37:26 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.