Aborted_clients metric is growing up even though formal connection close

[Matt]

I tested MariaDB 5.5.23 with sysbench.
And that time, Aborted_clients metric value is not much grew up.

But, After I applied MariaDB to production database,
Aborted_clients is growing up ever even though normal close of client side.

So, I tested same code with MySQL 5.5.23 community version. But it's okay.

And, MariaDB(with ThreadPool)'s memory usage is really stable under 2GB(Buffer pool is not including)
when I test with sysbench.
But in real service (java application), memory usage is growing up over 6~7GB(without BufferPool).
I'm not sure Aborted_clients metric is related with this memory usage.
Could you check this out ?

Thanks.

after test status----------------------------------------------------------------------------------
root@localhost:(none) 10:22:10>show global status like 'Aborted_clients';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Aborted_clients | 100 |
+-----------------+-------+

mysql> show global status like 'Aborted_clients';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| Aborted_clients | 0 |
+-----------------+-------+

test code --------------------------------------------------------------------------------------------
public class AbortedClientsTester {
 public static void main(String[] args) throws Exception {
  Connection[] conns = new Connection[100];

  for(int idx=0; idx<100; idx++){
   conns[idx] = getConnection();
  }

  Thread.sleep(1000*10);

  for(int idx=0; idx<100; idx++){
   conns[idx].close();
  }
 }

 protected static Connection getConnection() throws Exception {
  String driver = "com.mysql.jdbc.Driver";
  String url = "jdbc:mysql://127.0.0.1:3306/sysbench";
  String uid = "sysbench";
  String pwd = "sysbench";

  Class.forName(driver).newInstance();
  Connection conn = DriverManager.getConnection(url, uid, pwd);

  return conn;
 }
}

[Elena Stepanova (elenst)]

Hi,

Could you please provide the cnf file which you are using in production (where you observe the excessive memory consumption?

Thank you.

[Elena Stepanova (elenst)]

Hi Wlad,

We are yet to see whether the reported memory consumption is related to pseudo-aborted clients, I didn't see the memory issue on my machine yet.
However, the "100 aborted clients" problem itself looks odd, and it is totally reproducible with the provided test case if you run server with pool-of-threads, but not with one-thread-per-connection. I don't expect it's supposed to be this way. Could you please take a look?

[Matt (brew74)]

Hi I attached MariaDB's configuration file and MySQL Server status related to memory.

Actually, the memory allocated by innodb engine is just 26GB.
I think the other memory except 26GB is allocated by MySQL engine for table cache.

Our production server only have 2~3 tables and 1000 connections.
So I set table_open_cache configuration value to 400.

It seems like that 400 is small for our production service.
So memory allocation is ever increasing (my guess).

What do you think about ?

[Vladislav Vaintroub (wlad-montyprogram)]

Hi,
I fixed aborted_clients status variable. Growing this variable is annoying but is a pure cosmetics issue.

On the memory usage side, it seems that all connections use 1GB memory overall, which seems ok for 1K connections (some buffers are per-connection, so it does not seem too high overall).

400 for table cache might be small, dependent on how many connections are active at the same time. Sysbench gives best results with table cache == number_of_client*number_of_tables , so you might increase the value to ~3000 or about it.

Regarding OS memory usage : to prevent OS buffercache to compete with database buffers, it could be good to use innodb_flush_method=O_DIRECT, which prevents caching log files. It could be even better to use innodb_flush_method=ALL_O_DIRECT which prevents filesystem caching for the data files. With bufferpool that big avoiding OS caching can be relevant.

[Matt (brew74)]

Hi Vladislav Vaintroub.

I tested table_open_cache parameter with sysbench (one table with 1000 partitions, 4000 connections).
And I attached memory usage graph. (memory usage is captured by "ps -oe rss" command)

Just a few hours, MariaDB took 6 GB except Innodb buffer pool on table_open_cache=4000 benchmark.
But that's not all. Finally MariaDB will takes over 18GB and use even swap space.
(same as MySQL 5.5.23 community's behaviors)
At that time, InnoDB engine use just 16GB memory (Including innodb dictionary).

So, MariaDB use only 15GB memory as buffer pool even though the machine has 32GB physical memory.
It seems that MariaDB (including MySQL Community) allocate memory for table cache,
but never free these memory even though connection is closed.

I also reported this message to MySQL Community bug reporting site.
They said "FLUSH TABLES" command. --;
I knew "FLUSH TABLES" command, but we can not run this command on production database server.

So, I'm wondering is there any to free the memory for table cache without blocking other sessions query.

I think a lot of people use mysql server with over 4000 connections and over 1000 tables.
I'm curios why these people doesn't care about this. And percona always said "set 80% of memory as Innodb buffer pool".

Thanks.
Regards,

[Vladislav Vaintroub (wlad-montyprogram)]

Hi .
I think there is a misunderstanding about table cache size. This size is tiny compared to big buffers (Innodb bufferpool). The effect of bigger table cache is that you can run more transactions in parallel, because LOCK_open mutex is less hot. More work in parallel might well also increase resident set size (which you're tracking in your graphs).

There seems also to be misunderstanding about size of innodb buffer pool seen on the graphs. As you have seen the rss(resident set size) is not automatically >= innodb buffer pool size just when you start the database, because it is virtual memory that gets allocated and it is not the same as physical memory. so when innodb uses or touches pages first, virtual memory becomes physical memory used and resident size will grow until it reaches a plateau.

Pre-populating of Innodb buffer pool was done in a Twitter's MySQL branch : https://github.com/twitter/mysql/wiki/InnoDB-Startup-Options-and-System-Variables (innodb_buffer_pool_populate ), we might look to port it if there is enough interest in this patch

[Vladislav Vaintroub (wlad-montyprogram)]

Also please read my comment about filesystem caches and innodb flush methods O_DIRECT, and ALL_O_DIRECT. you do not want OS buffercache to compete with DBMS caches, and you can save a lot of memory with it (especially with ALL_O_DIRECT)

[Matt (brew74)]

Hi, I knew about ALL_O_DIRECT of XtraDB, And this option will prevent os cache redo log files.
And our production service, We will apply ALL_O_DIRECT option.

I'm wondering how to decrease mysql server's memory usage for table cache.
In case of many connections and tables, table cache memory usage is really huge.

Thanks.
regards,

[Vladislav Vaintroub (wlad-montyprogram)]

I would recommend applying ALL_O_DIRECT right now for the testing phase and see if that brings something and avoids swapping

PS. I'm not a support person, the above is mostly based on my intuition and on the fact that ALL_O_DIRECT prevents cache not only for the redo log files, but also for the innodb data files (hence "ALL" in ALL_O_DIRECT).

PPS. Currently, we do not measure the exact amount of memory used by different caches, so there are no hard facts. That table_cache structure would be responsible for consuming lots of memory is a bit counter-intuitive, I do not see what in it would take gigs of memory, it probably requires some more investigation in the future.