Details
-
Bug
-
Status: Closed (View Workflow)
-
Resolution: Fixed
-
None
-
None
-
None
Description
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; }}
Attachments
Activity
Re: Aborted_clients metric is growing up even though formal connection close
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)
Re: Aborted_clients metric is growing up even though formal connection close
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,
Re: Aborted_clients metric is growing up even though formal connection close
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.
Re: Aborted_clients metric is growing up even though formal connection close
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