Benvenuti!

uno.jpg


Newsfeeds
Planet MySQL
Planet MySQL - https://planet.mysql.com

  • How can I tell which Tungsten Connector mode I am using: Bridge, Proxy/Direct or Proxy/SmartScale?
    Overview The Skinny Part of the power of Tungsten Clustering for MySQL / MariaDB is its intelligent MySQL Proxy, known as the Tungsten Connector. Tungsten Connector has three main modes, and depending on the type of operations you are performing (such as if you need read-write splitting), we help you choose which mode is best. The Question Recently, a customer asked us: How can I tell which Tungsten Connector mode I am using: Bridge, Proxy/Direct or Proxy/SmartScale? The Answer Connect and Observe You may login through the Connector to tell the difference between Bridge mode and Proxy mode (either Direct or SmartScale): In Proxy mode, you will see the -tungsten tag appended to the Server version string: tungsten@db1:/opt/continuent/software/tungsten-clustering-6.0.5-41 # tpm connector Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.7.26-log-tungsten MySQL Community Server (GPL) Once logged into the Connector in Proxy mode, you have the full set of interactive tungsten commands available: mysql> tungsten help; +---------------------------------------------------------------------------------------------------------------------------------+ | Message | +---------------------------------------------------------------------------------------------------------------------------------+ | tungsten connection status: display information about the connection used for the last request ran | | tungsten connection count: gives the count of current connections to each one of the cluster datasources | | tungsten cluster status: prints detailed information about the cluster view this connector has | | tungsten show [full] processlist: list all running queries handled by this connector instance | | tungsten show variables [like '<string>']: list connector configuration options in use. The <string> may contain '%' wildcards | | tungsten flush privileges: reload user.map and refresh user credentials | | tungsten mem info: display memory information about current JVM | | tungsten gc: calls garbage collector | | tungsten help: display this help message | +---------------------------------------------------------------------------------------------------------------------------------+ For more information about the Connector’s command-line interface, please visit http://docs.continuent.com/tungsten-clustering-6.0/connector-inline.html For Bridge mode, you will not see that: tungsten@db1:/opt/continuent/software/tungsten-clustering-6.0.5-41 # tpm connector Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 34 Server version: 5.7.26-log MySQL Community Server (GPL) In Bridge mode, the tungsten commands do not work: mysql> tungsten help; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tungsten help' at line 1 The Library Please read the docs! For more information about the Tungsten Connector: Blog Post: MySQL Client Connection Handling in the Tungsten Connector Documentation: Connector Routing Types For more documentation about Tungsten software, please visit https://docs.continuent.com Summary The Wrap-Up In this blog post we discussed how one can tell which Tungsten Connector mode is in use: Bridge, Proxy/Direct or Proxy/SmartScale. Tungsten Clustering is the most flexible, performant global database layer available today – use it underlying your SaaS offering as a strong base upon which to grow your worldwide business! For more information, please visit https://www.continuent.com/solutions Want to learn more or run a POC? Contact us

  • Shinguz: Who else is using my memory - File System Cache analysis
    See also our former articles: Do not underestimate performance impacts of swapping on NUMA database systems MariaDB and MySQL swap analysis When we do analysis of MariaDB Database servers we also check the memory (RAM and Swap) available: # free --kilo --wide total used free shared buffers cache available Mem: 16106252 4329952 703356 199008 307872 10765072 11042748 Swap: 31250428 528684 30721744 The values for buffers and especially for cache can be sometimes quite big. In this case they use about 10 GiB. So let us have a look what these things called buffers and cache are, using our valuable RAM... When we check the man pages of free we will find: # man free ... buffers Memory used by kernel buffers (Buffers in /proc/meminfo) cache Memory used by the page cache and slabs (Cached and Slab in /proc/meminfo) buff/cache Sum of buffers and cache So let us check a more fine grained information in /proc/meminfo which is an interface to the kernel data structures: # cat /proc/meminfo | grep -e ^Cached -e Slab -e Buffers Buffers: 307872 kB Cached: 10155156 kB Slab: 609916 kB Same values! Then let us have a look at the man pages of proc what we can find about these values: # man proc ... Buffers Relatively temporary storage for raw disk blocks that shouldn't get tremendously large (20MB or so). Cached In-memory cache for files read from the disk (the page cache). Doesn't include SwapCached. Slab In-kernel data structures cache. So it looks like we have a raw I/O Cache (called Buffer Cache) and a File System I/O Cache (called Page Cache). So how does this work? What is a raw I/O? And is a Files System I/O cached once (Cached) or twice (Cached and Buffers)? When we dig a bit deeper we can find that prior to Linux Kernels 2.4 the two Caches were distinct. So that was a waste of memory (RAM). It seems like today this is not the case any more [1], [2], [3]. And man pages are a bit out of date or at least not very precise? Analysing the Linux Page Cache A very good source when it comes to Linux Performance Tuning and Measuring is Brendan Gregg's Website. To measure Linux Page Cache Hit Ratio he provides a tool called cachestat which is part of the perf-tools collection on GitHub. With cachestat we get a per second statistics of the Buffer Cache and the Page Cache (without Slabs), Cache Hits, Cache Misses, Dirty Buffer Entries in the Cache and a Cache Hit Ratio: # sudo cachestat Counting cache functions... Output every 1 seconds. HITS MISSES DIRTIES RATIO BUFFERS_MB CACHE_MB 1419 8 0 99.4% 338 9406 1368 0 0 100.0% 338 9406 1391 0 0 100.0% 338 9406 8558 0 29 100.0% 338 9406 31870 0 163 100.0% 338 9406 1374 0 24 100.0% 338 9406 1388 0 0 100.0% 338 9406 1370 0 0 100.0% 338 9406 1388 0 0 100.0% 338 9406 Brendan Gregg also mentions a tool called pcstat (on GitHub) by Al Tobey which gets Page Cache Statistics for Files. Unfortunately I had some problems building it on my Ubuntu 16.04 with Go version 1.6. So I built it on an Ubuntu 18.04 (Go 1.10) and copied it over to to Ubuntu 16.04): # export GOPATH=/tmp/ # cd $GOPATH # go get golang.org/x/sys/unix # go get github.com/tobert/pcstat/pcstat # bin/pcstat $GOPATH/bin/pcstat Then I tried pcstat out against a MariaDB 10.4 instance. In the output we can see how big the files are in bytes, how many pages of 4 kib this corresponds to, how many of these 4 kib pages are cached and the percentage of pages cached: # pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 14642 | 4 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 13631488 | 3328 | 0 | 000.000 | +------------------------------------------------------+----------------+------------+-----------+---------+ When we run pcstat over time with the famous watch command we can even see how the Page Cache is heating up: # watch -d -n 1 'pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* ; free -w' +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 14642 | 4 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 2416 | 012.418 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 3165 | 004.829 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 5890 | 008.987 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 1 | 100.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 13631488 | 3328 | 1164 | 034.976 | +------------------------------------------------------+----------------+------------+-----------+---------+ total used free shared buffers cache available Mem: 16106252 4329952 703356 199008 307872 10765072 11042748 Swap: 31250428 528684 30721744 An other tool which was discussed on Brendans Website was vmtouch - the Virtual Memory Toucher (on GitHub, Documentation). With vmtouch we can see for example how much of the directory /home/mysql/database/mariadb-104/data (datadir) is currently in cache: # vmtouch -f /home/mysql/database/mariadb-104/data Files: 503 Directories: 9 Resident Pages: 29356/231060 114M/902M 12.7% Elapsed: 0.009668 seconds Or more fine grained how much of InnoDB System Files are currently in memory: # vmtouch -f -v /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/ib_buffer_pool [ ] 0/4 /home/mysql/database/mariadb-104/data/ibdata1 [oOooooo ooooo ] 2416/19456 /home/mysql/database/mariadb-104/data/ib_logfile0 [o oOO] 3165/65536 /home/mysql/database/mariadb-104/data/ib_logfile1 [OOOOOOOOOOOOOOOOOOOOOo ] 23192/65536 /home/mysql/database/mariadb-104/data/ibtmp1 [ ] 0/3072 Files: 5 Directories: 0 Resident Pages: 28773/153604 112M/600M 18.7% Elapsed: 0.005499 seconds A further question to answer is: Can I see all files cached in the Page Cache? So it seem like this is not easily possible: There is no efficient search mechanism for doing the reverse - getting a file name belonging to a data block would require reading all inodes and indirect blocks on the file system. If you need to know about every single file's blocks stored in the page cache, you would need to supply a list of all files on your file system(s) to fincore. But that again is likely to spoil the measurement as a large amount of data would be read traversing the directories and getting all inodes and indirect blocks - putting them into the page cache and evicting the very page cache data you were trying to examine. [5] Also in this article we can read about the Linux File Tools (linux-ftools) by Google. It seems to be a bit more complicated to make them work. So I let it be. How is the Page Cache related to MariaDB After all this technical O/S discussion, how is Linux Page Cache related to your MariaDB Database? Your MariaDB Database caches Data and Indexes as well. For the InnoDB Storage Engine this is the InnoDB Buffer Pool and for the Aria Storage Engine this is the Aria Page Cache Buffer. So if your MariaDB Database caches pages and if your Linux O/S caches pages the probability is high they cache the same data twice and thus waste valuable RAM! Fortunately InnoDB is configurable in a way it does NOT cache InnoDB files in the Page Cache. This is controlled with the InnoDB Server Variable innodb_flush_method. When we look at InnoDB Files which were opened in a "normal" way (default: innodb_flush_method = fsync) we get the following information about how the files were opened (man 2 open and [6]): # lsof +fg ./ib* COMMAND PID USER FD TYPE FILE-FLAG DEVICE SIZE/OFF NODE NAME mysqld 2098 mysql 7uW REG RW,LG,0x80000 8,1 79691776 9175185 ./ibdata1 mysqld 2098 mysql 11uW REG RW,LG,0x80000 8,1 268435456 9175186 ./ib_logfile0 mysqld 2098 mysql 12uW REG RW,LG,0x80000 8,1 268435456 9175187 ./ib_logfile1 mysqld 2098 mysql 13uW REG RW,LG,0x80000 8,1 12582912 9175280 ./ibtmp1 The interesting column here is the FILE-FLAG column which indicates (man lsof): # man lsof FILE-FLAG when g or G has been specified to +f, this field contains the contents of the f_flag[s] member of the kernel file structure and the kernel's per-process open file flags (if available); `G' causes them to be displayed in hexadecimal; `g', as short-hand names; two lists may be displayed with entries separated by commas, the lists separated by a semicolon (`;'); the first list may contain short-hand names for f_flag[s] values from the following table: DIR direct LG large file RW read and write access The output is not so clear or completely understandable yet thus we want to have the open file flags in hexadecimal notation: # lsof +fG ./ib* COMMAND PID USER FD TYPE FILE-FLAG DEVICE SIZE/OFF NODE NAME mysqld 2098 mysql 7uW REG 0x88002;0x0 8,1 79691776 9175185 ./ibdata1 mysqld 2098 mysql 11uW REG 0x88002;0x0 8,1 268435456 9175186 ./ib_logfile0 mysqld 2098 mysql 12uW REG 0x88002;0x0 8,1 268435456 9175187 ./ib_logfile1 mysqld 2098 mysql 13uW REG 0x88002;0x0 8,1 12582912 9175280 ./ibtmp1 The Linux Kernel open file flags can be found here: fcntl.h. I have extracted the most relevant open file flags for our examination: #define O_RDWR 00000002 (oct, 0x00002) #define O_DIRECT 00040000 (oct, 0x04000) /* direct disk access hint */ #define O_LARGEFILE 00100000 (oct, 0x08000) #define O_CLOEXEC 02000000 (oct, 0x80000) /* set close_on_exec */ So we can see that these 4 InnoDB files where opened with O_RDWR (RW), O_LARGE_FILE (LG) and O_CLOEXEC (not available (yet?) in lsof translation output). Now let us start the MariaDB Database with the server variable set to: innodb_flush_method = O_DIRECT and check how the files where opened: # lsof +fg ./ib* COMMAND PID USER FD TYPE FILE-FLAG DEVICE SIZE/OFF NODE NAME mysqld 2098 mysql 7uW REG RW,DIR,LG,0x80000 8,1 79691776 9175185 ./ibdata1 mysqld 2098 mysql 11uW REG RW,LG,0x80000 8,1 268435456 9175186 ./ib_logfile0 mysqld 2098 mysql 12uW REG RW,LG,0x80000 8,1 268435456 9175187 ./ib_logfile1 mysqld 2098 mysql 13uW REG RW,DIR,LG,0x80000 8,1 12582912 9175280 ./ibtmp1 # lsof +fG ./ib* COMMAND PID USER FD TYPE FILE-FLAG DEVICE SIZE/OFF NODE NAME mysqld 2098 mysql 7uW REG 0x8c002;0x0 8,1 79691776 9175185 ./ibdata1 mysqld 2098 mysql 11uW REG 0x88002;0x0 8,1 268435456 9175186 ./ib_logfile0 mysqld 2098 mysql 12uW REG 0x88002;0x0 8,1 268435456 9175187 ./ib_logfile1 mysqld 2098 mysql 13uW REG 0x8c002;0x0 8,1 12582912 9175280 ./ibtmp1 We can see a new flag DIR or 0x04000 which means the files where opened with O_DIRECT. But only the InnoDB Temporary Table Tablespace and the InnoDB System Tablespace but not the two InnoDB Transaction Logs. Translation of hex to oct: 0x8c002 = 02140002. But what does O_DIRECT mean? Looking at the open(2) man pages we can find: # man 2 open O_DIRECT (since Linux 2.4.10) Try to minimize cache effects of the I/O to and from this file. In general this will degrade performance, but it is useful in special situations, such as when applications do their own caching. File I/O is done directly to/from user-space buffers. The O_DIRECT flag on its own makes an effort to transfer data synchronously, but does not give the guarantees of the O_SYNC flag that data and necessary meta‐ data are transferred. To guarantee synchronous I/O, O_SYNC must be used in addition to O_DIRECT. So O_DIRECT is exactly what we want in this case: Bypassing the File System Page Cache to not cache the Database blocks twice! To verify the impact we run pcstat again: # pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 16020 | 4 | 4 | 100.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 140 | 000.720 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 36844 | 056.219 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 65536 | 100.000 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 1 | 100.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 67108864 | 16384 | 13400 | 081.787 | +------------------------------------------------------+----------------+------------+-----------+---------+ But... part of the InnoDB Tablespace files is still cached! Also checking the total amount of Buffers and Cache shows the same: # free total used free shared buff/cache available Mem: 16106252 4401788 368200 456716 11336264 10691792 Swap: 31250428 1348440 29901988 So restarting the MariaDB database does not purge the Page Cache! Note: This is important to notice because bypassing the Page Cache helps to not wasting valuable RAM but it makes Database restart much more costly because Page Cache does not help/support InnoDB Buffer Pool heating any more! Then let us clear the Linux Page Cache and check the result: # echo 1 > /proc/sys/vm/drop_caches # free -w total used free shared buffers cache available Mem: 16106252 4395892 10539864 441708 696 1169800 10882984 Swap: 31250428 1348428 29902000 Checking with pcstat shows now that all InnoDB pages are wiped out of the Page Cache: # pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 16020 | 4 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 67108864 | 16384 | 0 | 000.000 | +------------------------------------------------------+----------------+------------+-----------+---------+ And after a while running traffic on the test table we can see that InnoDB Transaction Log Files are cached again in the Page Cache but NOT the InnoDB Tablespace files: # pcstat /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* +------------------------------------------------------+----------------+------------+-----------+---------+ | Name | Size (bytes) | Pages | Cached | Percent | |------------------------------------------------------+----------------+------------+-----------+---------| | /home/mysql/database/mariadb-104/data/ib_buffer_pool | 16020 | 4 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibdata1 | 79691776 | 19456 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ib_logfile0 | 268435456 | 65536 | 3012 | 004.596 | | /home/mysql/database/mariadb-104/data/ib_logfile1 | 268435456 | 65536 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/ibtmp1 | 12582912 | 3072 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.frm | 1097 | 1 | 0 | 000.000 | | /home/mysql/database/mariadb-104/data/test/test.ibd | 71303168 | 17408 | 0 | 000.000 | +------------------------------------------------------+----------------+------------+-----------+---------+ Also with vmtouch we can see the difference: ./vmtouch -f -v /home/mysql/database/mariadb-104/data/ib* /home/mysql/database/mariadb-104/data/test/test* /home/mysql/database/mariadb-104/data/ib_buffer_pool [ ] 0/4 /home/mysql/database/mariadb-104/data/ibdata1 [ ] 0/19456 /home/mysql/database/mariadb-104/data/ib_logfile0 [o oOOOo ] 4252/65536 /home/mysql/database/mariadb-104/data/ib_logfile1 [ ] 0/65536 /home/mysql/database/mariadb-104/data/ibtmp1 [ ] 0/3072 /home/mysql/database/mariadb-104/data/test/test.frm [ ] 0/1 /home/mysql/database/mariadb-104/data/test/test.ibd [ ] 0/17408 Files: 7 Directories: 0 Resident Pages: 4252/171013 16M/668M 2.49% Elapsed: 0.003264 seconds And also cachestat shows the effect of a flushed Buffer Cache and Page Cache: # ./cachestat Counting cache functions... Output every 1 seconds. HITS MISSES DIRTIES RATIO BUFFERS_MB CACHE_MB 677882 19 740 100.0% 67 1087 679213 10 700 100.0% 67 1087 677236 0 732 100.0% 67 1087 685673 11 932 100.0% 67 1088 677933 5 703 100.0% 67 1088 Caution: Depending on your underlying I/O system it makes nevertheless sense to run your MariaDB Database with innodb_flush_method = fsync in certain cases! See also PostgreSQL behaviour. Note: This information could also be interesting for PostgreSQL DBAs because they do redundant buffering with their shared_buffers (why plural? It is just one!?!) and the O/S Page Cache as well! What is Slab Beside Buffer Cache and the Page Cache itself we have a third thing in the /proc/meminfo statistics listed as Slabs. So what are Slabs? Slab seems to be a specific memory management (allocation) mechanism. It is used for frequently used objects in the Linux Kernel (buffer heads, inodes, dentries, etc.) [7-15]. So it contains something like other Linux Kernel Buffers and Kernel Caches. What kind of other Linux Kernel Buffer and Kernel Caches exists can be found with the following command: # sudo cat /proc/slabinfo slabinfo - version: 2.1 # name : tunables : slabdata nf_conntrack_1 14183 15275 320 25 2 : tunables 0 0 0 : slabdata 611 611 0 ext4_groupinfo_4k 8575 8596 144 28 1 : tunables 0 0 0 : slabdata 307 307 0 i915_gem_vma 523 950 320 25 2 : tunables 0 0 0 : slabdata 38 38 0 UDPv6 120 120 1088 30 8 : tunables 0 0 0 : slabdata 4 4 0 tw_sock_TCPv6 2668 2668 280 29 2 : tunables 0 0 0 : slabdata 92 92 0 request_sock_TCPv6 24 72 328 24 2 : tunables 0 0 0 : slabdata 3 3 0 TCPv6 68 105 2112 15 8 : tunables 0 0 0 : slabdata 7 7 0 cfq_queue 391 442 232 17 1 : tunables 0 0 0 : slabdata 26 26 0 mqueue_inode_cache 72 72 896 18 4 : tunables 0 0 0 : slabdata 4 4 0 fuse_request 20 40 400 20 2 : tunables 0 0 0 : slabdata 2 2 0 fuse_inode 1 21 768 21 4 : tunables 0 0 0 : slabdata 1 1 0 fat_cache 102 408 40 102 1 : tunables 0 0 0 : slabdata 4 4 0 hugetlbfs_inode_cache 28 84 584 28 4 : tunables 0 0 0 : slabdata 3 3 0 squashfs_inode_cache 25 50 640 25 4 : tunables 0 0 0 : slabdata 2 2 0 jbd2_journal_handle 340 340 48 85 1 : tunables 0 0 0 : slabdata 4 4 0 jbd2_journal_head 2040 2040 120 34 1 : tunables 0 0 0 : slabdata 60 60 0 jbd2_revoke_table_s 260 512 16 256 1 : tunables 0 0 0 : slabdata 2 2 0 jbd2_revoke_record_s1152 1408 32 128 1 : tunables 0 0 0 : slabdata 11 11 0 ext4_inode_cache 208751 210840 1072 30 8 : tunables 0 0 0 : slabdata 7028 7028 0 ext4_free_data 320 448 64 64 1 : tunables 0 0 0 : slabdata 7 7 0 ext4_allocation_cont 128 128 128 32 1 : tunables 0 0 0 : slabdata 4 4 0 ext4_io_end 392 560 72 56 1 : tunables 0 0 0 : slabdata 10 10 0 ext4_extent_status 64412 77928 40 102 1 : tunables 0 0 0 : slabdata 764 764 0 dquot 144 160 256 16 1 : tunables 0 0 0 : slabdata 10 10 0 mbcache 226 292 56 73 1 : tunables 0 0 0 : slabdata 4 4 0 dio 273 350 640 25 4 : tunables 0 0 0 : slabdata 14 14 0 pid_namespace 42 42 2224 14 8 : tunables 0 0 0 : slabdata 3 3 0 ip4-frags 32 64 248 16 1 : tunables 0 0 0 : slabdata 4 4 0 RAW 396 396 896 18 4 : tunables 0 0 0 : slabdata 22 22 0 UDP 68 68 960 17 4 : tunables 0 0 0 : slabdata 4 4 0 tw_sock_TCP 10750 11136 280 29 2 : tunables 0 0 0 : slabdata 384 384 0 request_sock_TCP 96 96 328 24 2 : tunables 0 0 0 : slabdata 4 4 0 TCP 119 136 1920 17 8 : tunables 0 0 0 : slabdata 8 8 0 blkdev_queue 27 48 1336 24 8 : tunables 0 0 0 : slabdata 2 2 0 blkdev_requests 394 506 368 22 2 : tunables 0 0 0 : slabdata 23 23 0 blkdev_ioc 516 546 104 39 1 : tunables 0 0 0 : slabdata 14 14 0 user_namespace 104 104 304 26 2 : tunables 0 0 0 : slabdata 4 4 0 dmaengine-unmap-256 15 15 2112 15 8 : tunables 0 0 0 : slabdata 1 1 0 sock_inode_cache 1707 1950 640 25 4 : tunables 0 0 0 : slabdata 78 78 0 file_lock_cache 665 665 208 19 1 : tunables 0 0 0 : slabdata 35 35 0 net_namespace 40 40 7296 4 8 : tunables 0 0 0 : slabdata 10 10 0 shmem_inode_cache 3315 3432 656 24 4 : tunables 0 0 0 : slabdata 143 143 0 taskstats 96 96 328 24 2 : tunables 0 0 0 : slabdata 4 4 0 proc_inode_cache 6895 7072 624 26 4 : tunables 0 0 0 : slabdata 272 272 0 sigqueue 100 100 160 25 1 : tunables 0 0 0 : slabdata 4 4 0 bdev_cache 29 76 832 19 4 : tunables 0 0 0 : slabdata 4 4 0 kernfs_node_cache 43625 44982 120 34 1 : tunables 0 0 0 : slabdata 1323 1323 0 mnt_cache 518 546 384 21 2 : tunables 0 0 0 : slabdata 26 26 0 inode_cache 17519 17668 568 28 4 : tunables 0 0 0 : slabdata 631 631 0 dentry 424185 439992 192 21 1 : tunables 0 0 0 : slabdata 20952 20952 0 buffer_head 1112865 1112865 104 39 1 : tunables 0 0 0 : slabdata 28535 28535 0 vm_area_struct 53945 55300 200 20 1 : tunables 0 0 0 : slabdata 2765 2765 0 files_cache 260 299 704 23 4 : tunables 0 0 0 : slabdata 13 13 0 signal_cache 509 630 1088 30 8 : tunables 0 0 0 : slabdata 21 21 0 sighand_cache 346 405 2112 15 8 : tunables 0 0 0 : slabdata 27 27 0 task_struct 1189 1269 3584 9 8 : tunables 0 0 0 : slabdata 141 141 0 Acpi-Operand 5703 5824 72 56 1 : tunables 0 0 0 : slabdata 104 104 0 Acpi-Parse 1314 1314 56 73 1 : tunables 0 0 0 : slabdata 18 18 0 Acpi-State 204 204 80 51 1 : tunables 0 0 0 : slabdata 4 4 0 Acpi-Namespace 4077 4182 40 102 1 : tunables 0 0 0 : slabdata 41 41 0 anon_vma 19831 21522 80 51 1 : tunables 0 0 0 : slabdata 422 422 0 numa_policy 170 170 24 170 1 : tunables 0 0 0 : slabdata 1 1 0 radix_tree_node 321937 327740 584 28 4 : tunables 0 0 0 : slabdata 11705 11705 0 trace_event_file 3985 4002 88 46 1 : tunables 0 0 0 : slabdata 87 87 0 ftrace_event_field 86541 88570 48 85 1 : tunables 0 0 0 : slabdata 1042 1042 0 idr_layer_cache 533 555 2096 15 8 : tunables 0 0 0 : slabdata 37 37 0 kmalloc-8192 1246 1246 8192 4 8 : tunables 0 0 0 : slabdata 502 502 0 kmalloc-4096 658 720 4096 8 8 : tunables 0 0 0 : slabdata 90 90 0 kmalloc-2048 1955 2144 2048 16 8 : tunables 0 0 0 : slabdata 134 134 0 kmalloc-1024 44217 44384 1024 16 4 : tunables 0 0 0 : slabdata 2774 2774 0 kmalloc-512 3037 3808 512 16 2 : tunables 0 0 0 : slabdata 238 238 0 kmalloc-256 17465 20384 256 16 1 : tunables 0 0 0 : slabdata 1274 1274 0 kmalloc-192 27708 28665 192 21 1 : tunables 0 0 0 : slabdata 1365 1365 0 kmalloc-128 140581 143744 128 32 1 : tunables 0 0 0 : slabdata 4492 4492 0 kmalloc-96 168044 168378 96 42 1 : tunables 0 0 0 : slabdata 4009 4009 0 kmalloc-64 117533 123264 64 64 1 : tunables 0 0 0 : slabdata 1926 1926 0 kmalloc-32 80425 90368 32 128 1 : tunables 0 0 0 : slabdata 706 706 0 kmalloc-16 9513 11264 16 256 1 : tunables 0 0 0 : slabdata 44 44 0 kmalloc-8 6616 7168 8 512 1 : tunables 0 0 0 : slabdata 14 14 0 kmem_cache_node 320 320 64 64 1 : tunables 0 0 0 : slabdata 5 5 0 kmem_cache 208 208 256 16 1 : tunables 0 0 0 : slabdata 13 13 0 If you want to see the most frequently used (hottest) Slabs you can see them top-like with slabtop. If you press c you can sort the Slabs by CACHE_SIZE: # sudo slabtop Active / Total Objects (% used) : 2249113 / 2280136 (98.6%) Active / Total Slabs (% used) : 70256 / 70256 (100.0%) Active / Total Caches (% used) : 86 / 121 (71.1%) Active / Total Size (% used) : 597547.86K / 605445.30K (98.7%) Minimum / Average / Maximum Object : 0.01K / 0.26K / 18.56K OBJS ACTIVE USE OBJ SIZE SLABS OBJ/SLAB CACHE SIZE NAME 294308 289889 98% 0.57K 10511 28 168176K radix_tree_node 105030 104435 99% 1.05K 3501 30 112032K ext4_inode_cache 745446 745446 100% 0.10K 19114 39 76456K buffer_head 59984 59909 99% 1.00K 3749 16 59984K ecryptfs_inode_cache 47520 47157 99% 1.00K 2970 16 47520K kmalloc-1024 215166 214987 99% 0.19K 10246 21 40984K dentry 139744 138452 99% 0.12K 4367 32 17468K kmalloc-128 179508 179011 99% 0.09K 4274 42 17096K kmalloc-96 47140 45768 97% 0.20K 2357 20 9428K vm_area_struct 14700 14700 100% 0.55K 525 28 8400K inode_cache ... Literature [1] What is the major difference between the buffer cache and the page cache? Why were they separate entities in older kernels? Why were they merged later on? (2012-10-21) [2] What is the difference between Buffers and Cached columns in /proc/meminfo output? (2012-10-19) [3] The future of the page cache (2017-01-25) [4] Linux Page Cache Basics [5] Is it possible to list the files that are cached? [6] RFC: Clarifying Direct I/O Semantics [7] Slab allocation [8] Memory management [9] Overview of Linux Memory Management Concepts: Slabs [10] Memory management - Buddy allocator, Slab allocator [11] The SLUB allocator [12] Slab Allocator [13] What are the different caches reported by slabtop? [14] How /proc/slabinfo is not quite telling you what it looks like [15] Short users guide for SLUB Taxonomy upgrade extras:  memory RAM cache file system o_direct innodb_flush_method postgresql innodb tablespace

  • Assessing MySQL Performance Amongst AWS Options – Part Two
    See part one of this series here.  This post is part two of my series “Assessing MySQL Performance Amongst AWS Options”, taking a look at how current Amazon RDS services – Amazon Aurora and Amazon RDS for MySQL – compare with Percona Server with InnoDB and RocksDB engines on EC2 instances. This time around, I am reviewing the total cost of one test run for each database as well as seeing which databases are the most efficient. First, a quick recap of the evaluation scenario: The benchmark scripts For these evaluations, we use the sysbench/tpcc LUA test with a scale factor of 500 warehouses/10 tables. This is the equivalent of 5000 warehouses of the official TPC-C benchmark. Amazon MySQL Environments These are the AWS MySQL environments under analysis: Amazon RDS Aurora Amazon RDS for MySQL with the InnoDB storage engine Percona Server for MySQL with the InnoDB storage engine on Amazon EC2 Percona Server for MySQL with the RocksDB storage engine on Amazon EC2 Technical Setup – Server These general notes apply across the board: AWS region us-east-1(N.Virginia) was used for all tests Server and client instances were spawned in the same availability zone All data for tests were prepared in advance, stored as snapshots, and restored before the test Encryption was not used And we believe that these configuration notes allow for a fair comparison of the different technologies: AWS EBS optimization was enabled for EC2 instances For RDS/Amazon Aurora only a primary DB instance was created and used In the case of RDS/MySQL, a single AZ deployment was used for RDS/MySQL EC2/Percona Server for MySQL tests were run with binary log enabled Finally, here are the individual server configurations per environment: Server test #1: Amazon RDS Aurora Database server: Aurora MySQL 5.7 DB instances: r5.large, r5.xlarge, r5.2xlarge, r5.4xlarge volume: used ~450GB(>15000 IOPS) Server test #2: Amazon RDS for MySQL with InnoDB Storage Engine Database server: MySQL Server 5.7.25 RDS instances: db.m5.large, db.m5.xlarge, db.m5.2xlarge, db.m5.4xlarge volumes(allocated space): gp2: 5400GB(~16000 IOPs) io1: 700GB(15000 IOPs) Server test #3: Percona Server for MySQL with InnoDB Storage Engine Database server: Percona Server 5.7.25 EC2 instances: m5.large, m5.xlarge, m5.2xlarge, m5.4xlarge volumes(allocated space): gp2: 5400GB(~16000 IOPs) io1: 700GB(15000 IOPs) Server test #4: Percona Server for MySQL with RocksDB using LZ4 compression Database server: Percona Server 5.7.25 EC2 instances: m5.large, m5.xlarge, m5.2xlarge, m5.4xlarge volumes(allocated space): gp2: 5400GB(~16000 IOPs) io1: 350GB(15000 IOPs) Technical Setup – Client Common to all tests, we used an EC2 instance: m5.xlarge. And now that we have established the setup, let’s take a look at what we found. Costs Now we are getting down to the $’s! First, let’s review the total cost of one test run for each database: Sorting the costs of one test run in order from cheapest to most expensive we see this order emerge: EC2/gp2 carrying server tests #3 or #4 featuring Percona Server for MySQL [represents the LEAST cost in $’s] RDS/gp2 carrying server test #2, RDS/MySQL EC2/io1 carrying server tests #3 or #4 RDS/io1 carrying server test #2, RDS/MySQL RDS/Aurora, server test #1  [GREATEST COST IN $’s] How does that translate to $’s? Let’s find out how the structure of these costs looks like for every database. Before we study that, though, there are some things to bear in mind: Our calculations include only server-side costs Per instance, the price we used as a baseline was RESERVED INSTANCE STANDARD 1-YEAR TERM For RDS/Amazon Aurora the values for volume size and amount of I/O requests represent real data obtained from CloudWatch metrics (VolumeBytesUsed for used volume space and VolumeReadIOPs+VolumeWriteIOPs for IOPs used) after the test run In the case of Percona Server/RocksDB due to LZ4 compression, the database on disk is 5x smaller, so we used a half-sized io1 volume – 350GB vs 700GB for either Percona Server with InnoDB or RDS/MySQL. This still complies with the requirement for io1 volumes to deliver 50 IOPS per GB. The duration set for the test run is 30 mins Our total cost formulas These are the formulas we used in calculating these costs: EC2/gp2, EC2/io1, RDS/gp2, RDS/io1 total cost = server instance size cost + allocated volume size cost + requested amount of IOPS cost RDS/Amazon Aurora total cost = server instance size cost + allocated volume size cost + actually used amount of I/O cost The results Here are our calculations in chart form, you can click on the chart to enlarge it on screen: One interesting observation here is that, as you can see from the costs structure chart, the most significant part of costs is IO provisioning – either the requested amount of IOPS (EC2/io1 or RDS/io1) or the actually used amount of IOPS (RDS/Aurora). In the former case, the cost is a function of time, and in the latter case, costs depend only on the amount of I/O requests actually issued. Let’s check how these costs might look like if we provision EC2/io1, RDS/io1 volumes and RDS/aurora storage for one month. From the cost structure, it’s clear that in case of RDS/aurora 4xlarge – db instance performed 51M I/O requests for half an hour. So we effectively got 51000000 (I/O request) / 1800(seconds) ~= 28000 IOPs. EC2/io1: (28000 (IOPS) * 0.065(IOPs price) * 24(hours)*30(days)/(24(hours)*30(days)) 1820$ RDS/io1: (28000 (IOPS) * 0.1(IOPs price) * 24(hours)*30(days)/(24(hours)*30(days)) 2800$ RDS/aurora: 102M(I/O per hour) * 0.2(I/O req price) * 24(hours)*30(days) 14688$ In this way, IO provisioning of 28000 IOPS for EC2/io1 costs 8x less and for RDS/io1 costs 5x less. That means that to be cost-efficient, the throughput of RDS/Aurora should be at least 5x or even 8x better than that of EC2 or RDS with io1 volume. Conclusion: the IO provisioning factor should be taken into account during your planning of deployments with io1 volumes or RDS/aurora Efficiency Now it’s time to review which databases perform the most efficiently by analyzing their transaction/cost ratio: Below you can find the minimum and maximum prices for 1000 transactions for each of the database servers in our tests, again running from cheapest to most expensive in $ terms: Server Min $’s per 1000 TX Server Config Min $’s per 1000 TX Server Config Server test #4 EC2#Percona Server/RocksDB 0.42 4xlarge/io1 1.93 large/io1 Server test #3 EC2#Percona Server/InnoDB 1.66 4xlarge/gp2 12.11 large/io1 Server test #2 RDS#MySQL/InnoDB 2.23 4xlarge/gp2 22.3 large/io1 Server test #1 RDS#Amazon Aurora 8.29 4xlarge 13.31 xlarge Some concluding thoughts EC2#Percona Server/RocksDB offers the lowest price per 1000 transactions – $0.42 on m5.4xlarge instance with 350GB io1 volume/15000 IOPs RDS/MySQL looked to be the most expensive in this evaluation – $22.3 for 1000 transactions – db.m5.large with 700GB io1 volume/15000 IOPs Lowest price for each database was obtained on 4xlarge instances, most expensive on large instances. IO provisioning is a key factor that impacts run costs For both EC2 and RDS gp2/5400GB (~16000 IOPS) is the cost wise choice RDS/Aurora – the lowest price per 1000 transactions is $8.29, but that is 4x more expensive than the best price of 1000 transactions for RDS/MySQL, 5x more expensive than for EC2#Percona/InnoDB, and 20x more expensive than for EC2#Percona/RockDB. That means that despite the fact that Amazon Aurora shows very good throughput (actually the best among InnoDB-like engines), it may not be as cost-effective as other options. One Final Note When estimating your expenses, you will need to keep in mind that each company is different in terms of what they offer, how they build and manage those offerings, and of course, their pricing structure and cost per transaction. For AWS, you do need to be aware of the expenses of building and managing those things yourself that AWS handles for you; i.e. built into their cost. We can see, however, that in these examples, MyRocks is definitely a cost-effective solution when comparing direct costs.

  • SQL TOP, LIMIT Or ROWNUM Clause Example Tutorial
    SQL TOP, LIMIT Or ROWNUM Clause Example Tutorial is today’s topic. SQL SELECT TOP clause is used to specify the number of records to be returned. The SELECT TOP clause is used on large tables having thousands of records because returning a very large number of records that can impact on the performance. The operation performed by TOP, LIMIT, and ROWNUM clause has almost the same functionality. SQL TOP, LIMIT Or ROWNUM Clause In some situations, you may not be interested in all of of the rows returned by a query, for example, if you want to retrieve the top 10 employees who have recently joined the organization and get top 3 students by score, or something like that. If we want to handle such situations, you can use the SQL’s TOP clause in the SELECT statement. However, a TOP clause is only supported by the SQL Server and MS Access database systems.  Not all the database systems support the SELECT TOP clause. MySQL supports the LIMIT clause to select a limited number of records. Oracle uses ROWNUM. #SYNTAX: (For SQL SERVER) SELECT TOP number|percent column_name(s) FROM table_name WHERE condition; #Parameters TOP number: The numbers of records to be retrieved. TOP percent: Percentage of records to be retrieved. Table_name: Name of the table. Condition: Condition to be imposed on the Select statement. #Syntax: (For Oracle) SELECT column_name(s) FROM table_name WHERE ROWNUM <= number; See the syntax of MySQL Databases. #Syntax: (For MySQL databases) SELECT&nbsp;column_name(s) FROM&nbsp;table_name WHERE&nbsp;condition LIMIT&nbsp;number; Let’s understand all these with examples. Consider table: CUSTOMERS ID NAME AGE ADDRESS SALARY  1 Tom 21 Kolkata 500 2 Karan 22 Allahabad 600 3 Hardik 23 Dhanbad 700 4 Komal 24 Mumbai 800   QUERY: (For SQL SERVER) Select Top 3 * From Customers; Output: ID NAME AGE ADDRESS SALARY  1 Tom 21 Kolkata 500 2 Karan 22 Allahabad 600 3 Hardik 23 Dhanbad 700   So, here the first three records are displayed as we have used Top number clause. #SQL TOP PERCENT Example The following SQL statement selects the first 50% of the records from the “Customers” table. SELECT TOP 50 PERCENT * FROM Customers; #QUERY: (For MySQL databases) Select * from Customers where ID >= 1 LIMIT 3; #Output: ID NAME AGE ADDRESS SALARY  1 Tom 21 Kolkata 500 2 Karan 22 Allahabad 600 3 Hardik 23 Dhanbad 700 So, here, the records whose Id’s are more than 1 and is equal to 1 are displayed up to limit 3. #QUERY: (For Oracle) SELECT * FROM Customers WHERE ROWNUM <= 3; #Output: ID NAME AGE ADDRESS SALARY  1 Tom 21 Kolkata 500 2 Karan 22 Allahabad 600 3 Hardik 23 Dhanbad 700   #Using LIMIT along with OFFSET LIMIT a OFFSET b means skip the first b entries and then return the next a entries. OFFSET can only be used with an ORDER BY clause. It cannot be used on its own. An OFFSET value must be greater than or equal to zero. It cannot be negative, else it returns the error. See the following syntax. SELECT expressions FROM tables [WHERE conditions] [ORDER BY expression [ ASC | DESC ]] LIMIT number_rows [ OFFSET offset_value ]; In the above query, we are using the SELECT, WHERE, ORDER BY, and LIMIT Clause. #Parameters or Arguments #expressions The columns that you wish to retrieve. #tables The tables that you wish to retrieve the records from. There must be at least one table listed in a FROM clause. #WHERE conditions Optional. The conditions that must be met for the records to be returned. #ORDER BY expression Optional. It is used in a SELECT LIMIT statement so that you can order the results and target those that you wish to return. The ASC is ascending order and DESC which means descending order. #LIMIT number_rows It specifies the limited number of rows in the result set to be returned based on the number_rows. Let’s say, LIMIT 11 would return the first 11 rows matching the SELECT criteria. This is where the sorting order matters, so you need to be sure to use the ORDER BY clause appropriately. #OFFSET offset_value Optional. The first row returned by the LIMIT will be determined by offset_value. #Using LIMIT ALL The LIMIT ALL clause implies no limit. See the following syntax. SELECT * FROM Student LIMIT ALL; The above query returns all the entries in the table. Finally, SQL TOP, LIMIT Or ROWNUM Clause Example Tutorial is over. The post SQL TOP, LIMIT Or ROWNUM Clause Example Tutorial appeared first on AppDividend.

  • How to Install phpMyAdmin with Nginx on Ubuntu 18.04 LTS
    phpMyAdmin is a free and open-source web-based database management tool for MySQL. In this tutorial, we will learn how to install phpMyAdmin with Nginx on Ubuntu 18.04 server.

Estate

estate004.jpg

Calendario impegni

September
S M T W T F S
26 27 28 29 30 31 1
2 3 4 5 6 7 8
9 10 11 12 13 14 15
16 17 18 19 20 21 22
23 24 25 26 27 28 29
30 1 2 3 4 5 6