Number Of Tables Created

[jestep]

This is a production server and monyog has recently been showing a huge number of created tables under the Number of tables created section of schema changes.

Looking at the information schema, there are the same number range of tables in the database as always, right around 800. Monyog is showing 31151 for number of tables created. I've manually checked every database on the server just to verify that there isn't 31000 tables somewhere.

How can I log, or verify the created tables being shown here. I'm assuming that there is something creating temp tables and then destroying them, which may be legitimate, but I would like to know what is doing it so that I can verify everything is ok.

Any suggestions

[peterlaursen]

Try to execute 

Code:

SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

.. this is how MONyog retrieves the information from the server. Does this match what MONyog reports?

Also note that the text 'Total tables created' is a link and by clicking it more information will display

I think you miss that the 'Total tables created' refer to TEMPORARY tables (the group of this counter is named 'Temporary tables') .  The MySQL server will create temporary tables in lots of situations (for instance for specific queries like JOINs and SUBQUERIES).  Creating a temporary table is an expensive operation in particular if the table will have to be created on disk.  A large number of temporary tables (to disk in particular) could indicate that MyISAM related fuffer sizes should be raised (these temporary tables are always MyISAM) or queries should be rewritten.

(Also note that the statement “SHOW TABLES” (and similar queries to Information_Schema – with the possible modification that if may be added to I_S in very recent server versions) does not list temporary tables created by the server itself. Only “SHOW GLOBAL|LOCAL STATUS LIKE 'Created_tmp_tables” does.

Btw: what are your MONyog and MySQL server versions?  Please always provide this information when contacting us. Even if it does not seem important for the matter it sometimes is.

[jestep]

MySQL 5.1.45, and Monyog version is 2.82

mysql> SHOW GLOBAL STATUS LIKE 'Created_tmp_tables';

+

---

+

---

+

| Variable_name | Value |

+

---

+

---

+

| Created_tmp_tables | 18931 |

+

---

+

---

+

1 row in set (0.00 sec)

Monyog shows: 31379. Clicking on the link, the formula is 'Com_create_table'.

mysql> SHOW GLOBAL STATUS LIKE 'Com_create_table';

+

---

+

---

+

| Variable_name | Value |

+

---

+

---

+

| Com_create_table | 31379 |

+

---

+

---

+

1 row in set (0.00 sec)

As far as the database, all user created databases are InnoDB. The only MyISAM tables are in information_schema and mysql. Platform is CentOS 5.5×64.

[Mahesh]

Com_create_table

The Com_create_table counter variables indicate the number of times each CREATE TABLE statement has been executed.

So, in your case MONyog shows expected result as 31379 as these many time CREATE TABLE statement has been executed on server irrespective of Table engine types(InnoDB, MyISAM etc..)

mysql> SHOW GLOBAL STATUS LIKE 'Com_create_table';

+

---

+

---

+

| Variable_name | Value |

+

---

+

---

+

| Com_create_table | 31379 |

[MychotVette]

Id like to ask If there is a maximum number of tables that should be opened

within a database, and also maximum number of datamodulesdelphi 3….

Im connecting to MSsql, and i find out that on every run of my application

about 78 user connections opened and the delphi application gives an

exception Geeneral SQL error

Do u have any suggestion?

Thank you for your help

[peterlaursen]

@MychotVette

Any question involving Delphi and/or SQL Server is not relevant in this Forums. MONyog and SQLyog are tools for the MySQL server and no other database. We cannot help and we will have to ask you to ask such questions a proper place and not here. But good luck anyway!

[Author]

Posts