Author: ZIADI Mohamed Ali

Mysql: How to find table and database size?

Sometimes you need to know the size of each mysql table to optimize them and gain some free space in the disk. To do so, you can check manually the size of each table in the mysql datadir (generally it is /var/lib/mysql), or you can do even better by asking Mysql to do it for you. Listing Mysql tables size You can use the command below when connected to the mysql shell: SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024 AS TABLE_SIZE_in_KB FROM information_schema.TABLES; To list above tables size in MB: SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024/1024 AS TABLE_SIZE_in_MB FROM information_schema.TABLES; In this blog, I am using KB as the unit because I have small tables. So depending on the size of your data you can adjust the unit. To list all tables having size bigger than 1 KB SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024 AS TABLE_SIZE_in_KB FROM information_schema.TABLES WHERE (DATA_LENGTH+INDEX_LENGTH)/1024 > 1; Example: mysql> SELECT table_schema AS DB_NAME, TABLE_NAME, (DATA_LENGTH+INDEX_LENGTH)/1024 AS TABLE_SIZE_in_KB FROM information_schema.TABLES WHERE (DATA_LENGTH+INDEX_LENGTH)/1024 > 1; Listing databases size To list each database size you can use the following command: SELECT table_schema "DATABASE_NAME", SUM(data_length + index_length) / 1024 "DATABASE SIZE IN KB" FROM information_schema.tables GROUP BY table_schema; Example: mysql> SELECT table_schema "DATABASE_NAME", SUM(data_length + index_length) / 1024 "DATABASE SIZE IN KB" FROM information_schema.tables GROUP BY table_schema; Or in MB: mysql> SELECT table_schema "DATABASE_NAME", SUM(data_length + index_length) / 1024 /...

Read More

Linux Bash History: Replace occurrences in last command

Sometimes we use Linux commands with many long arguments, and to use the same command with a little modification we are obliged to go through these args and modify them one by one. Example : touch /home/user1/test/scenario.txt /etc/my-server/users/user1.cnf /var/log/my-server/user1.log What will I do if I want to do the same to user2? To replace all occurrences in the last command you can use the command below: !:gs/old_occurrence/new_occurrence !refers to the last command gs refers to global substitute To replace a word you can use the command below: ^old_word^new_word Examples: [www1.linuxnix] root:~ # touch test1 scenario1 result1 [www1.linuxnix] root:~ # !:gs/1/2 touch test2 scenario2 result2 [www1.linuxnix] root:~ # ls -l test* scenario* result* -rw-r--r-- 1 root root 0 Nov 20 16:45 result1 -rw-r--r-- 1 root root 0 Nov 20 16:45 result2 -rw-r--r-- 1 root root 0 Nov 20 16:45 scenario1 -rw-r--r-- 1 root root 0 Nov 20 16:45 scenario2 -rw-r--r-- 1 root root 0 Nov 20 16:45 test1 -rw-r--r-- 1 root root 0 Nov 20 16:45 test2 [www1.linuxnix] root:~ # mkdir -p tests/exp/scenario1/tools/ [www1.linuxnix] root:~ # ^exp^lab mkdir -p tests/lab/scenario1/tools/ [www1.linuxnix] root:~ # tree tests tests ├── exp │   └── scenario1 │   └── tools └── lab └── scenario1 └── tools 6 directories, 0 files Note: when you use ^^ with multiple occurrences, only the first occurrence is replaced. Example: [www1.linuxnix] root:~ # ls -l test1 scenario1 result1 -rw-r--r-- 1 root...

Read More

How to optimize the use of RAM ?

Since sysadmin are looking to have racing beast servers, they are confonted to the problem of performance optimization. Nowadays it is not uncommon to see a server with 32G or 96G of RAM or even more. Why to mount so much in memory? Well it is the use that imposes it: Virtualization Video editing HD compression Heavy applicative consumption like clustering or high availability architectures etc In this blog we will speek about swapiness which is a kernel module that defines when linux has to start to write in the SWAP (an allocated space in the disk) to relieve the RAM. It may seem obvious that with access times in milliseconds for Hard Disk and in nanoseconds for RAM, it is better to write in the RAM. Recap: vm.swappiness = 0 : Swapping will be activated urgently only to avoid out of memory condition. vm.swappiness = 60 : This is the default value and means that from 40% of Ram occupancy, the kernel writes to the swap. vm.swappiness = 10 : This what is recommended to improve performance. vm.swappiness = 100 : The kernel will swap aggressively. To see your swappiness setting : cat /proc/sys/vm/swappiness 60 Let’s consider that we have a server having 32G of RAM. fixing the swappiness to 5 is enough since kernel will use swap only when the free RAM space is less or equal...

Read More

Get more stuff like this
in your inbox

Subscribe to our mailing list and get interesting stuff and updates to your email inbox.

ABOUT ME..!

My photo
My name is Surendra Kumar Anne. I hail from Vijayawada which is cultural capital of south Indian state of Andhra Pradesh. I am a Linux evangelist who believes in Hard work, A down to earth person, Likes to share knowledge with others, Loves dogs, Likes photography. At present I work at Bank of America as Sr. Analyst Systems and Administration. You can contact me at surendra (@) linuxnix dot com.

Pin It on Pinterest