Ygthkb

Taking an academic pseudonym?

Including proofs of known theorems in master's thesis

Would water spill from a bowl in a Bag of Holding?

Calculating the strength of an ionic bond that contains poly-atomic ions

In a post-apocalypse world, with no power and few survivors, would Satnav still work?

Why is it that Bernie Sanders is always called a "socialist"?

How can I handle players killing my NPC outside of combat?

Even as admin, I cannot edit DefaultFonts.plist for Notes.app. What am I doing wrong?

Do the speed limit reductions due to pollution also apply to electric cars in France?

Does Plato's "Ring of Gyges" have a corrupting influence on its wearer?

Identical projects by students at two different colleges: still plagiarism?

How unreachable are Jupiter's moons from Mars with the technology developed for going to Mars?

Renting a 2CV in France

How can I give a Ranger advantage on a check due to Favored Enemy without spoiling the story for the player?

Sri Keyuravati of krama tradition

Lubuntu 18.10 File Manager: How to view directory tree structure?

What is an explicit bijection in combinatorics?

Did ancient Germans take pride in leaving the land untouched?

Dealing with an internal ScriptKiddie

Are there historical references that show that "diatonic" is a version of 'di-tonic' meaning 'two tonics'?

What is the second derivative with respect to price of a put option?

Minimum Viable Product for RTS game?

Is it possible to detect 100% of SQLi with a simple regex?

Does rolling friction increase speed of a wheel?

How does MySQL store data, can I detach a mysql Database?

Error 'Unknown table engine 'InnoDB'' on query. after restarting mysqlRecover a MySQL InnoDB from ibdata1 that won't repair using innodb_force_recoveryRestore InnoDB tables / database using only data filesMySQL InnoDB logfiles resized - review mysql-err.log for potential problemsCan I move the undo log outside of ibdata1 in MySQL 5.6 on an existing server?Mysql backup from ibdata1, ib_logfile1, ib_logfile0Adding index to large mysql tablesMySQL InnoDB transactions with lot of delete instructionsMySQL Database Instance sizeHow do we determine data_length + index_length as database size in mysql

0

I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to the actual database size. They can compressed and considerably reduced in size, but things seems to be different in MySQL.

I look up the files of MySQL (InnoDB) where they are stored in /var/lib/mysql/ folder. There is files for each database (I can't share that) and there are these three files which seems to contain data

-rw-rw---- 1 mysql mysql 50M Feb 24 16:17 ibdata1

-rw-rw---- 1 mysql mysql 48M Feb 24 16:17 ib_logfile0

-rw-rw---- 1 mysql mysql 48M Feb 22 23:49 ib_logfile1

First I am not sure if all the database data is kept in one file, that ibdata1, second some of my database are large (1.4 GB one one of them) but these files are very small. I am wondering how data is maintained in MySQL.

Here is my query to find my database size Engine wise

SELECT Engine "DB Engine",

        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 

FROM information_schema.tables 

GROUP BY engine;

And the result

+--------------------+---------------+

| DB Engine          | DB Size in MB |

+--------------------+---------------+

| NULL               |          NULL |

| CSV                |           0.0 |

| InnoDB             |        3695.8 |

| MEMORY             |           0.0 |

| MyISAM             |        3120.5 |

| PERFORMANCE_SCHEMA |           0.0 |

+--------------------+---------------+

Obviously my database a lot larger than the datafiles that stores that data. On a side note, I also want to know if I can detach a database in mySQL just like in SQL Server. Part of the question is because I want to optimize my database. Currently there is only 30GB file on this drive and I want to free up some space too. Note I have additional drives on my Linux (centos) server as well. Thanks

mysql innodb optimization storage myism

share

asked 8 mins ago

JackofallJackofall

4133715

add a comment | 

0

I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to the actual database size. They can compressed and considerably reduced in size, but things seems to be different in MySQL.

I look up the files of MySQL (InnoDB) where they are stored in /var/lib/mysql/ folder. There is files for each database (I can't share that) and there are these three files which seems to contain data

-rw-rw---- 1 mysql mysql 50M Feb 24 16:17 ibdata1

-rw-rw---- 1 mysql mysql 48M Feb 24 16:17 ib_logfile0

-rw-rw---- 1 mysql mysql 48M Feb 22 23:49 ib_logfile1

First I am not sure if all the database data is kept in one file, that ibdata1, second some of my database are large (1.4 GB one one of them) but these files are very small. I am wondering how data is maintained in MySQL.

Here is my query to find my database size Engine wise

SELECT Engine "DB Engine",

        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 

FROM information_schema.tables 

GROUP BY engine;

And the result

+--------------------+---------------+

| DB Engine          | DB Size in MB |

+--------------------+---------------+

| NULL               |          NULL |

| CSV                |           0.0 |

| InnoDB             |        3695.8 |

| MEMORY             |           0.0 |

| MyISAM             |        3120.5 |

| PERFORMANCE_SCHEMA |           0.0 |

+--------------------+---------------+

Obviously my database a lot larger than the datafiles that stores that data. On a side note, I also want to know if I can detach a database in mySQL just like in SQL Server. Part of the question is because I want to optimize my database. Currently there is only 30GB file on this drive and I want to free up some space too. Note I have additional drives on my Linux (centos) server as well. Thanks

mysql innodb optimization storage myism

share

asked 8 mins ago

JackofallJackofall

4133715

add a comment | 

I have used SQL Server in the past which each database is kept in separate files (a data file and a log file) but things are different in MySQL as it seems. The file sizes of these files translates to the actual database size. They can compressed and considerably reduced in size, but things seems to be different in MySQL.

I look up the files of MySQL (InnoDB) where they are stored in /var/lib/mysql/ folder. There is files for each database (I can't share that) and there are these three files which seems to contain data

-rw-rw---- 1 mysql mysql 50M Feb 24 16:17 ibdata1

-rw-rw---- 1 mysql mysql 48M Feb 24 16:17 ib_logfile0

-rw-rw---- 1 mysql mysql 48M Feb 22 23:49 ib_logfile1

First I am not sure if all the database data is kept in one file, that ibdata1, second some of my database are large (1.4 GB one one of them) but these files are very small. I am wondering how data is maintained in MySQL.

Here is my query to find my database size Engine wise

SELECT Engine "DB Engine",

        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 

FROM information_schema.tables 

GROUP BY engine;

And the result

+--------------------+---------------+

| DB Engine          | DB Size in MB |

+--------------------+---------------+

| NULL               |          NULL |

| CSV                |           0.0 |

| InnoDB             |        3695.8 |

| MEMORY             |           0.0 |

| MyISAM             |        3120.5 |

| PERFORMANCE_SCHEMA |           0.0 |

+--------------------+---------------+

Obviously my database a lot larger than the datafiles that stores that data. On a side note, I also want to know if I can detach a database in mySQL just like in SQL Server. Part of the question is because I want to optimize my database. Currently there is only 30GB file on this drive and I want to free up some space too. Note I have additional drives on my Linux (centos) server as well. Thanks

mysql innodb optimization storage myism

share

asked 8 mins ago

JackofallJackofall

4133715

-rw-rw---- 1 mysql mysql 50M Feb 24 16:17 ibdata1

-rw-rw---- 1 mysql mysql 48M Feb 24 16:17 ib_logfile0

-rw-rw---- 1 mysql mysql 48M Feb 22 23:49 ib_logfile1

SELECT Engine "DB Engine",

        ROUND(SUM(data_length + index_length) / 1024 / 1024, 1) "DB Size in MB" 

FROM information_schema.tables 

GROUP BY engine;

+--------------------+---------------+

| DB Engine          | DB Size in MB |

+--------------------+---------------+

| NULL               |          NULL |

| CSV                |           0.0 |

| InnoDB             |        3695.8 |

| MEMORY             |           0.0 |

| MyISAM             |        3120.5 |

| PERFORMANCE_SCHEMA |           0.0 |

+--------------------+---------------+

share

asked 8 mins ago

JackofallJackofall

4133715

share

asked 8 mins ago

JackofallJackofall

4133715

asked 8 mins ago

JackofallJackofall

4133715

asked 8 mins ago

JackofallJackofall

4133715