Category: Uncategorized

As I said all along.

Hey, Twitter! Told you so. Are you going to unblock me now, or will you doxx and ban the unanimous jury members, too?

Self defense is a human right.

 

Carl is an unpaid TZP volunteer. If you found this post useful, please consider dropping something in his tip jar. He could really use the money, what with ISP bills, site hosting and SSL certificate, new 2021 model hip, and general life expenses.Click here to donate via PayPal. (More Tip Jar Options)

The Zelman Partisans

https://cdn0.thetruthaboutguns.com/wp-content/uploads/2021/10/AP21278681741615-scaled.jpg

The Truth About Guns

https://theawesomer.com/photos/2021/11/building_a_pallet_wood_house_t.jpg

Link

We’ve seen shipping pallets recycled into coasters, pencils, a guitar, and skateboards. Bushcraft builder Lesnoy went above and beyond by making an entire tiny house primarily from reclaimed pallet wood. Watch and enjoy this soothing video as he builds a dwelling from scratch in the woods.

The Awesomer

The power of PlanetScale within a beautiful GUI — now you can connect to development branches, switch between them, and even create deploy requests in Arctype.

Read the full story

Planet MySQL

https://media.notthebee.com/articles/6195608e813826195608e81383.jpg

I could watch this all day.

Not the Bee

https://media.notthebee.com/articles/6195454436119619545443611a.jpg

Warning: graphic. It is what the headline says it is:

Not the Bee

https://media.notthebee.com/articles/619520e866ae3619520e866ae4.jpg

Three years ago, then-teenager Nicholas Sandmann had his name dragged through the mud by the media clowns for the sin of wearing a MAGA hat while a man yelled in his face.

Not the Bee

https://www.louderwithcrowder.com/media-library/image.png?id=27984877&width=980

Day Two of Rittenhouse jury deliberation has begun. As we wait, we reflect on what garbage human beings the mainstream media has been throughout this entire thing. The main reason he’s on trial is because of lies and untruths the media has spread. And this is just one man’s opinion, but I’m sure being attacked by media is on the mind of at last a few jurors if they’re thinking about voting to acquit Rittenhouse. Joe Rogan is someone who knows firsthand how much the media lies and swears by it. Once people discover this rant by Dr. Joe, MD, the media will proclaim "who, us?" Brian Stelter is probably crying into his breakfast cheesecake as we speak.

"This information is not based on reality. This is a left-wing cult. They’re pumping stuff out and then they are confirming this belief. They are all getting together, and they are ignoring contrary evidence. They are ignoring any narrative that challenges their belief about what happened, and they are not looking at it realistically. They are only looking at it like you would if you were in a f*cking cult."

As an aside, what a cast of characters! Drew Hernandez, Tim Pool, Blaire White, AND Alex Jones.

More people need to be exposed to who the media is. They won’t just go after you if you hold a different opinion than them or if they think they can use you to advance their leftist narrative. They’ll go after you if they even assume you have a different opinion. Rittenhouse is only one of the most extreme examples of it.

SNL Propaganda Isn’t Even Trying Anymore | Louder With Crowder

youtu.be

Louder With Crowder

https://www.percona.com/blog/wp-content/uploads/2021/11/Triggers-Affect-Memory-Allocated-to-Your-MySQL-Server-300×157.png

MySQL stores active table descriptors in a special memory buffer called the table open cache. This buffer is controlled by configuration variables table_open_cache that hold the maximum number of table descriptors that MySQL should store in the cache, and table_open_cache_instances that stores the number of the table cache instances. With default values of table_open_cache=4000 and table_open_cache_instances=16, MySQL will create 16 independent memory buffers that will store 250 table descriptors each. These table cache instances could be accessed concurrently, allowing DML to use cached table descriptors without locking each other.

If you use only tables, the table cache does not require a lot of memory because descriptors are lightweight, and even if you significantly increase the value of the table_open_cache, the required memory amount would not be so high. For example, 4000 tables will take up to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a huge number for this number of tables.

However, if your tables have triggers, it changes the game.

For the test I created a table with a single column and inserted a row into it:

mysql> CREATE TABLE tc_test( f1 INT);
Query OK, 0 rows affected (0,03 sec)

mysql> INSERT INTO tc_test VALUES(1);
Query OK, 1 row affected (0,01 sec)

Then I flushed the table cache and measured how much memory it uses:

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,02 sec)mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   |     2 |
+---------------+-------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
|     60.50 KiB |
+---------------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,00 sec)

Then I accessed the table to put it into the cache.

$ for i in `seq 1 1 16`; do mysql test -e "SELECT * FROM tc_test"; done
...

mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   |    20 |
+---------------+-------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
|     75.17 KiB |
+---------------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,01 sec)

16 table descriptors took less than 16 KiB in the cache.

Now let’s try to create some triggers on this table and see if it changes anything.

mysql> CREATE TRIGGER tc_test_ai AFTER INSERT ON tc_test FOR EACH ROW 
    -> BEGIN 
    ->   SIGNAL SQLSTATE '45000' SET message_text='Very long string. 
    ->     MySQL stores table descriptors in a special memory buffer, called table open cache. 
    ->     This buffer could be controlled by configuration variables table_open_cache that 
    ->     holds how many table descriptors MySQL should store in the cache and table_open_cache_instances 
    ->     that stores the number of the table cache instances. So with default values of table_open_cache=4000 
    ->     and table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 
    ->     table descriptors each. These table cache instances could be accessed concurrently, allowing DML 
    ->     to use cached table descriptors without locking each other. If you use only tables, the table cache 
    ->     does not require a lot of memory, because descriptors are lightweight, and even if you significantly 
    ->     increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take 
    ->     up to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a huge 
    ->     number for this number of open tables. However, if your tables have triggers, it changes the game.'; 
    -> END|

Then let’s flush the table cache and test memory usage again.

Initial state:

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   |     2 |
+---------------+-------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
|     60.50 KiB |
+---------------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,00 sec)

After I put the tables into the cache:

$ for i in `seq 1 1 16`; do mysql test -e "SELECT * FROM tc_test"; done
...

mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   |    20 |
+---------------+-------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
|     75.17 KiB |
+---------------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
|    611.12 KiB |
+---------------+
1 row in set (0,00 sec)

As a result, in addition to 75.17 KiB in the table cache, 611.12 KiB is occupied by the memory/sql/sp_head::main_mem_root. That is the "Mem root for parsing and representation of stored programs."

This means that each time when the table is put into the table cache, all associated triggers are put into the memory buffer, storing their definitions.

FLUSH TABLES command clears the stored programs cache as well as the table cache:

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,01 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,00 sec)

More triggers increase memory usage when put into the cache.

For example, if we create five more triggers and repeat our test we will see the following numbers:

mysql> \d |
mysql> CREATE TRIGGER tc_test_bi BEFORE INSERT ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000
' SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, calle
at holds how many table descriptors MySQL should store in the cache and table_open_cache_instances t
hat stores the number of the table cache instances. So with default values of table_open_cache=4000
and table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 t
able descriptors each. These table cache instances could be accessed concurrently, allowing DML to u
se cached table descriptors without locking each other. If you use only tables, the table cache doe
s not require a lot of memory, because descriptors are lightweight, and even if you significantly increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take u
p to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a hu
ge number for this number of open tables. However, if your tables have triggers, it changes the gam
e.'; END|
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE TRIGGER tc_test_bu BEFORE UPDATE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000
' SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, calle
at holds how many table descriptors MySQL should store in the cache and table_open_cache_instances t
hat stores the number of the table cache instances. So with default values of table_open_cache=4000
and table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 t
able descriptors each. These table cache instances could be accessed concurrently, allowing DML to u
se cached table descriptors without locking each other. If you use only tables, the table cache doe
s not require a lot of memory, because descriptors are lightweight, and even if you significantly increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take u
p to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a hu
ge number for this number of open tables. However, if your tables have triggers, it changes the gam
e.'; END|
Query OK, 0 rows affected (0,02 sec)

mysql> CREATE TRIGGER tc_test_bd BEFORE DELETE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000' SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, calle
at holds how many table descriptors MySQL should store in the cache and table_open_cache_instances that stores the number of the table cache instances. So with default values of table_open_cache=4000
and table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 table descriptors each. These table cache instances could be accessed concurrently, allowing DML to use cached table descriptors without locking each other. If you use only tables, the table cache does not require a lot of memory, because descriptors are lightweight, and even if you significantly increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take up to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a huge number for this number of open tables. However, if your tables have triggers, it changes the game.'; END|
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE TRIGGER tc_test_au AFTER UPDATE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, call
ed ta a
t holds how many table descriptors MySQL should store in the cache and table_open_cache_instances th
at stores the number of the table cache instances. So with default values of table_open_cache=4000 a
nd table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 ta
ble descriptors each. These table cache instances could be accessed concurrently, allowing DML to us
e cached table descriptors without locking each other. If you use only tables, the table cache does
not require a lot of memory, because descriptors are lightweight, and even if you significantly increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take up
to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a hug
e number for this number of open tables. However, if your tables have triggers, it changes the game
.'; END|
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE TRIGGER tc_test_ad AFTER DELETE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Very long string. MySQL stores table descriptors in a special memory buffer, call
ed table open cache. This buffer could be controlled by configuration variables table_open_cache tha
t holds how many table descriptors MySQL should store in the cache and table_open_cache_instances th
at stores the number of the table cache instances. So with default values of table_open_cache=4000 a
nd table_open_cache_instances=16, you will have 16 independent memory buffers that will store 250 ta
ble descriptors each. These table cache instances could be accessed concurrently, allowing DML to us
e cached table descriptors without locking each other. If you use only tables, the table cache does
not require a lot of memory, because descriptors are lightweight, and even if you significantly increased the value of table_open_cache, it would not be so high. For example, 4000 tables will take up
to 4000 x 4K = 16MB in the cache, 100.000 tables will take up to 390MB that is also not quite a hug
e number for this number of open tables. However, if your tables have triggers, it changes the game
.'; END|
Query OK, 0 rows affected (0,01 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables | 35 |
+---------------+-------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
| 446.23 KiB |
+---------------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 3.58 MiB |
+---------------+
1 row in set (0,00 sec)

Numbers for the event memory/sql/sp_head::main_mem_root differ six times:

mysql> SELECT 3.58*1024/611.12;
+------------------+
| 3.58*1024/611.12 |
+------------------+
|         5.998691 |
+------------------+
1 row in set (0,00 sec)

Note that the length of the trigger definition affects the amount of memory allocated by the memory/sql/sp_head::main_mem_root.

For example, if we define the triggers as follow:

mysql> DROP TABLE tc_test;
Query OK, 0 rows affected (0,02 sec)

mysql> CREATE TABLE tc_test( f1 INT);
Query OK, 0 rows affected (0,03 sec)

mysql> INSERT INTO tc_test VALUES(1);
Query OK, 1 row affected (0,01 sec)

mysql> \d |
mysql> CREATE TRIGGER tc_test_ai AFTER INSERT ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE TRIGGER tc_test_au AFTER UPDATE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,04 sec)

mysql> CREATE TRIGGER tc_test_ad AFTER DELETE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE TRIGGER tc_test_bi BEFORE INSERT ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,01 sec)

mysql> CREATE TRIGGER tc_test_bu BEFORE UPDATE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,02 sec)

mysql> CREATE TRIGGER tc_test_bd BEFORE DELETE ON tc_test FOR EACH ROW BEGIN SIGNAL SQLSTATE '45000'
SET message_text='Short string';end |
Query OK, 0 rows affected (0,01 sec)

mysql> \d ;
mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   |     2 |
+---------------+-------+
1 row in set (0,01 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
|     60.50 KiB |
+---------------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,00 sec)

$ for i in `seq 1 1 16`; do mysql test -e "select * from tc_test"; done
...

mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   |    35 |
+---------------+-------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/TABLE_SHARE::mem_root';
+---------------+
| current_alloc |
+---------------+
|    446.23 KiB |
+---------------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
|      1.89 MiB |
+---------------+
1 row in set (0,00 sec)

The resulting amount of memory is 1.89 MiB instead of 3.58 MiB for the longer trigger definition.

Note that having a single table cache instance requires less memory to store trigger definitions. E.g. for our small six triggers, it will be 121.12 KiB instead of 1.89 MiB:

mysql> SHOW GLOBAL VARIABLES LIKE 'table_open_cache_instances';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| table_open_cache_instances |     1 |
+----------------------------+-------+
1 row in set (0,00 sec)

mysql> FLUSH TABLES;
Query OK, 0 rows affected (0,00 sec)

mysql> SHOW GLOBAL STATUS LIKE 'Open_tables';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables   |     2 |
+---------------+-------+
1 row in set (0,00 sec)

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/sp_head::main_mem_root';
Empty set (0,00 sec)

$ for i in `seq 1 1 16`; do mysql test -e "select * from tc_test"; done
...

mysql> SELECT current_alloc FROM sys.memory_global_by_current_bytes 
    -> WHERE event_name='memory/sql/sp_head::main_mem_root';
+---------------+
| current_alloc |
+---------------+
| 121.12 KiB |
+---------------+
1 row in set (0,00 sec)

Conclusion

When you access tables that have associated triggers, their definitions are put into the stored programs cache even when not fired. This was reported at MySQL Bug #86821 and closed as “Not a Bug” by Oracle. This is, certainly, not a bug, but the table and stored routines cache design. Still, it is good to be prepared, so you are not surprised when you run short of memory faster than you expect. Especially if you have many triggers with long definitions.

Percona Database Performance Blog