The INFORMATION_SCHEMA database is made up of temporary tables using the MEMORY storage engine.
Example: Here is the table INFORMATION_SCHEMA.TABLES in MySQL 5.5.12 (Windows Version)
mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
`TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
`TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
`TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
`TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
`ENGINE` varchar(64) DEFAULT NULL,
`VERSION` bigint(21) unsigned DEFAULT NULL,
`ROW_FORMAT` varchar(10) DEFAULT NULL,
`TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
`AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
`INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
`DATA_FREE` bigint(21) unsigned DEFAULT NULL,
`AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
`CREATE_TIME` datetime DEFAULT NULL,
`UPDATE_TIME` datetime DEFAULT NULL,
`CHECK_TIME` datetime DEFAULT NULL,
`TABLE_COLLATION` varchar(32) DEFAULT NULL,
`CHECKSUM` bigint(21) unsigned DEFAULT NULL,
`CREATE_OPTIONS` varchar(255) DEFAULT NULL,
`TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
There is no physical folder for those tables, not even .frm files. You cannot mysqldump it. You cannot drop it. You cannot add tables to it. You cannot drop tables from it. So, where are the tables ???
All tables in the INFORMATION_SCHEMA database are stored directly in memory as MEMORY storage engine tables. They are totally internal to MySQL, so the .frm mechanisms are handled in mysqld. In my answer, I first showed the table layout of INFORMATION_SCHEMA.TABLES. It is a temporary table in memory. It is manipulated using storage engine protocols. Thus, when mysqld is shutdown, all information_schema tables are dropped. When mysqld is started, all information_schema tables are created as TEMPORARY tables and repopulated with metadata for every table in the mysql instance.
The INFORMATION_SCHEMA database was first introduced in MySQL 5.0 to give you access to metadata about tables of other storage engines. For example, you could do SHOW DATABASES to get a list of databases. You could also query for them like this:
SELECT schema_name database FROM information_schema.schemata;
You could retrieve table names in a database in two ways:
use mydb
show tables;
or
SELECT table_name from information_schema.tables WHERE table_schema = 'mydb';
Since its inception, MySQL has expanded the INFORMATION_SCHEMA database to have the processlist (as of MySQL 5.1). You can actually query the processlist looking for long running queries that are still running at least 10 minutes:
SELECT * FROM information_schema.processlist WHERE time >= 600\G
You can use the INFORMATION_SCHEMA to do every elaborate things: such as :
Get counts of all tables using specific storage engines:
SELECT COUNT(1) TableCount,IFNULL(engine,'Total') StorageEngine
FROM information_schema.tables
WHERE table_schema NOT IN ('information_schema','mysql')
AND engine IS NOT NULL
GROUP BY engine WITH ROLLUP;
Get the recommended MyISAM Key Buffer Size in MB
SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_key_buffer_size
FROM (SELECT SUM(index_length) KBS FROM information_schema.tables WHERE
engine='MyISAM' AND table_schema NOT IN ('information_schema','mysql')) A,
(SELECT 2 pw) B;
Get the recommended InnoDB Buffer Pool Size in GB
SELECT CONCAT(ROUND(KBS/POWER(1024,IF(pw<0,0,IF(pw>3,0,pw)))+0.49999),
SUBSTR(' KMG',IF(pw<0,0,IF(pw>3,0,pw))+1,1)) recommended_innodb_buffer_pool_size
FROM (SELECT SUM(data_length+index_length) KBS FROM information_schema.tables
WHERE engine='InnoDB') A,(SELECT 3 pw) B;
Get Disk Usage of all Databases By Storage Engine in MB
SELECT Statistic,DataSize "Data Size",IndexSize "Index Size",TableSize "Table Size"
FROM (SELECT IF(ISNULL(table_schema)=1,10,0) schema_score,
IF(ISNULL(engine)=1,10,0) engine_score,
IF(ISNULL(table_schema)=1,'ZZZZZZZZZZZZZZZZ',table_schema) schemaname,
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=2,"Storage for All Databases",
IF(ISNULL(B.table_schema)+ISNULL(B.engine)=1,CONCAT("Storage for ",B.table_schema),
CONCAT(B.engine," Tables for ",B.table_schema))) Statistic,
CONCAT(LPAD(REPLACE(FORMAT(B.DSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') DataSize,
CONCAT(LPAD(REPLACE(FORMAT(B.ISize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') IndexSize,
CONCAT(LPAD(REPLACE(FORMAT(B.TSize/POWER(1024,pw),3),',',''),17,' '),' ',
SUBSTR(' KMGTP',pw+1,1),'B') TableSize
FROM (SELECT table_schema,engine,SUM(data_length) DSize,SUM(index_length) ISize,
SUM(data_length+index_length) TSize FROM information_schema.tables
WHERE table_schema NOT IN ('mysql','information_schema','performance_schema')
AND engine IS NOT NULL GROUP BY table_schema,engine WITH ROLLUP) B,
(SELECT 2 pw) A) AA ORDER BY schemaname,schema_score,engine_score;
Believe me, there are still more wonderful uses for INFORMATION_SCHEMA that time does not permit me to discuss further.
Please keep in mind that the INFORMATION_SCHEMA is so sensitive that if mysql is running and you do the following:
cd /var/lib/mysql
mkdir junkfolder
and then go into mysql run
mysql> SHOW DATABASES;
You will see junkfolder as one of the databases.
Knowing it is very vital for DBAs and Developers. Chapter 20 (developers) and Chapter 31 (DBAs) of the book MySQL 5.0 Certification Study Guide
are there for preparing for the Developer and DBA Certification Exams. Get the book, study those chapters well, and you could do great things with MySQL's INFORMATION_SCHEMA.
The INFORMATION_SCHEMA database as of MySQL 5.5, now features plugins, global variables (status and static), session variables (status and static), storage engine status, performance metrics instrumentation, trigger map, events (programmable) and much more.
Sorry this may seem like WTMI but I am a big proponent of using the INFORMATION_SCHEMA database.
After much searching, I have finally found a solution.
I am not much of a writer, so I will do my best to make this as concise as possible.
So as far as I could find, there are 2 possible solutions:
SQL Relay
http://sqlrelay.sourceforge.net/
This does exactly what the question asked for, and a bunch more. I wont go into too much detail on what I was able to find out about this but will mention that it was not a viable solution as it is not transparent. Meaning that the flow is as follows:
PHP -> Queries -> SQL Relay Extension -> SQL Relay -> Externally hosted MySQL
So this would have involved rewriting all of our code from mysql to sql relay. Not an option in our case.
All that being said, if someone is planning a fresh large scale project that requires any of the numerous features that SQL Relay has, it sounds beautiful.
Mysql Proxy
http://forge.mysql.com/wiki/MySQL_Proxy
This is the solution we ended up using.
The key to making this do what we want it to do is the pooling LUA script for mysql proxy.
This LUA extension can be found at:
https://github.com/cwarden/mysql-proxy/blob/315ab806bb95b8223f5afd3d238eff2a40af03d8/lib/ro-pooling.lua
Without going into too much detail, here are some basic stats... Bare in mind, this is tested at LOW usage time:
[root@HOSTNAME etc]# netstat -na | grep ":3306 " | grep TIME_WAIT | wc
6433 38598 572537
After switching to mysql-proxy, and letting things settle:
[root@HOSTNAME etc]# netstat -na | grep ":3306 " | grep TIME_WAIT | wc
32 192 2848
As you can clearly see, the TIME_WAIT ports to mysql have dropped to almost none.
The connections are now in fact persistent WITHOUT using mysql_pconnect / mysqli_connect( ... p:hostname ... ).
Worth mentioning there appear to be a few configurable settings near the top of the pooler lua script.
local min_idle_connections
and
local max_idle_connections
These appear to be pretty self explanatory. Except that: It would appear that each username (and password? untested... most likely not tho.) combination creates its own set of persistent connections.
So multiply max_idle_connections by the number of unique mysql users that will be connecting to the database. And that should give you an idea of how many idle connections you will end up having.
So, let me reiterate for so this little blurb hits some keywords for those searching via google:
When using PHP is it possible to have persistent mysql connections WITHOUT mysql_pconnect?
Yes, this can be done via SQL Relay if you dont mind rebuilding most of your code to pipe your queries thru their extension OR transparently using mysql-proxy with the ro-pooling.lua script.
We have been wanting something like this for about a year now.
ENJOY!
Best Answer
In MySQL, an "event" is not a periodic data element. It's more like a cron job (sorry— an "at" job in Windows). It's a routine of code with a schedule.
The content of
information_schema.eventsis metadata about the schedule job. For instance, the code body, the schedule, etc. This information should be unchanging no matter how many times you the event runs.It does not store any history of executions of that job, nor results or data produced by that job.