- discuss - lists.mariadb.org

[Maria-discuss] Calculating Maximum Bytes Per Record
by Jake Drew 08 Jun '11

08 Jun '11

*Hello, * * * *I have recently been doing some capacity planning exercises for a new database we are developing in MariaDB. One of the exercises I completed was to create a database with all tables loaded with maximum width records at 100,000, 500,000, and 1,000,000 records. I noticed that when I did this it seems that the larger the tables grow, the less bytes per record are required. I am curious what causes this to happen? These are all InnoDB tables saved to their own tablespaces.* * Max Bytes Per Record Record Count Record Count Record Count 100,000 500,000 1,000,000 acct_activation 241 176 164 acct_deactivated 419 336 327 disk_usage 136 75 63 keywords 713 646 633 pods 77,511 56,698 53,126 pods_joined 178 101 96 pod_activity 461 369 357 pod_files 78,224 57,220 53,615 sessions 168 92 80 users 1,426 1,351 1,338 usr_profile 78,392 57,343 53,730 * *I also created a query (using Information_Schema.Columns) to calculate maximum possible bytes per record / table (SQL attached below). I ran the query and compared the estimates to the actual values produced above. The query is close, but I would like to make it a little more accurate if possible. * *TABLE_SCHEMA* *TABLE_NAME* *PK_BYTES_TOT* *FIELD_BYTES_TOT* * IX_FIELD_BYTES_TOT* *IX_FIELD_COUNT* *IX_OVERHEAD_BYTES* *IX_BYTES_TOT* * TABLE_BYTES_TOT* pods acct_activation 0 73 33 1 29 62 *189* pods acct_deactivated 8 269 0 0 0 0 *388* pods devices 1 273 0 0 0 0 *384* pods disk_usage 8 32 0 0 0 0 *56* pods keywords 0 264 264 2 58 322 *820* pods pods 8 65,821 16 1 29 45 *92,224* pods pods_joined 0 16 16 2 58 74 *126* pods pod_activity 0 275 9 1 29 38 *438* pods pod_files 8 66,376 56 2 58 114 *93,097* pods sessions 0 10 10 2 58 68 *109* pods users 8 1,112 41 1 29 70 *1,666* pods usr_profile 8 66,628 0 0 0 0 *93,290* *Does anyone have any suggestions for my calculations in the code below to make it more accurate?* * * *Basic Assumptions * * * - Calculated field widths are defined by data type in the CASE logic below. - Primary key bytes = The sum of all defined PK calculated field widths - Index Bytes = Primary key bytes + Index calculated field width + 29 bytes overhead (for each index defined) - 1 byte is added to each field that is Nullable. - All bytes defined above are multiplied by 1.4 for BTREE overhead.* * * * *Current SQL to calculate MAX record bytes by table:* * * SELECT B.TABLE_SCHEMA , B.TABLE_NAME , SUM(PK_BYTES) AS PK_BYTES_TOT , SUM(FIELD_BYTE_SPACE) AS FIELD_BYTES_TOT , SUM(IX_BYTES) AS IX_FIELD_BYTES_TOT , SUM(CASE WHEN IX_BYTES > 0 THEN 1 ELSE 0 END) AS IX_FIELD_COUNT , SUM(CASE WHEN IX_BYTES > 0 THEN 1 ELSE 0 END) * 29 AS IX_OVERHEAD_BYTES , (SUM(CASE WHEN IX_BYTES > 0 THEN 1 ELSE 0 END) * 29) + SUM(IX_BYTES) AS IX_BYTES_TOT , (SUM(PK_BYTES) + SUM(FIELD_BYTE_SPACE) + (SUM(CASE WHEN IX_BYTES > 0 THEN 1 ELSE 0 END) * 29) + SUM(IX_BYTES)) * 1.4 AS TABLE_BYTES_TOT FROM ( SELECT A.* , CASE WHEN COLUMN_KEY = 'PRI'THEN FIELD_BYTE_SPACE ELSE 0 END AS PK_BYTES , CASE WHEN A.COLUMN_KEY <> 'PRI' AND A.COLUMN_KEY <> '' THEN (PK_BYTE_SPACE + FIELD_BYTE_SPACE) ELSE 0 END AS IX_BYTES FROM ( SELECT PK_SP.TABLE_SCHEMA , PK_SP.TABLE_NAME , PK_SP.COLUMN_NAME , DATA_TYPE , CHARACTER_MAXIMUM_LENGTH , NUMERIC_PRECISION , IS_NULLABLE , COLUMN_KEY , CHARACTER_SET_NAME , CHARACTER_OCTET_LENGTH , (CASE -- CHARACTER FIELDS WHEN DATA_TYPE = 'varchar' THEN CHARACTER_MAXIMUM_LENGTH + 1 WHEN DATA_TYPE = 'char' THEN CHARACTER_MAXIMUM_LENGTH WHEN DATA_TYPE = 'tinyblob' OR DATA_TYPE = 'tinytext' THEN CHARACTER_MAXIMUM_LENGTH + 1 WHEN DATA_TYPE = 'blob' OR DATA_TYPE = 'text' THEN CHARACTER_MAXIMUM_LENGTH + 2 WHEN DATA_TYPE = 'mediumblob' OR DATA_TYPE = 'mediumtext' THEN CHARACTER_MAXIMUM_LENGTH + 3 WHEN DATA_TYPE = 'largeblob' OR DATA_TYPE = 'largetext' THEN CHARACTER_MAXIMUM_LENGTH + 4 -- NUMERIC FIELDS WHEN DATA_TYPE = 'tinyint' THEN 1 WHEN DATA_TYPE = 'smallint' THEN 2 WHEN DATA_TYPE = 'mediumint' THEN 3 WHEN DATA_TYPE = 'int' OR DATA_TYPE = 'integer' THEN 4 WHEN DATA_TYPE = 'bigint' THEN 8 WHEN DATA_TYPE = 'float' AND (NUMERIC_PRECISION <= 24 OR NUMERIC_PRECISION IS NULL) THEN 4 WHEN DATA_TYPE = 'float' AND NUMERIC_PRECISION > 24 THEN 8 WHEN DATA_TYPE = 'bit' THEN (NUMERIC_PRECISION + 7) / 8 WHEN DATA_TYPE = 'double' OR DATA_TYPE = 'numeric' THEN (FLOOR(NUMERIC_PRECISION/9)*4) + ROUND((NUMERIC_PRECISION- FLOOR(NUMERIC_PRECISION/9)*9)*.5,0) -- DATETIME FIELDS WHEN DATA_TYPE = 'date' OR DATA_TYPE = 'time' THEN 3 WHEN DATA_TYPE = 'datetime' THEN 8 WHEN DATA_TYPE = 'timestamp' THEN 4 WHEN DATA_TYPE = 'year' THEN 1 ELSE 999999999999999 END) + (CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END) AS FIELD_BYTE_SPACE , CASE WHEN PK_BYTE_SPACE IS NULL THEN 0 ELSE PK_BYTE_SPACE END AS PK_BYTE_SPACE FROM information_schema.columns AS PK_SP LEFT OUTER JOIN (SELECT TABLE_SCHEMA , TABLE_NAME , SUM((CASE -- CHARACTER FIELDS WHEN DATA_TYPE = 'varchar' THEN CHARACTER_MAXIMUM_LENGTH + 1 WHEN DATA_TYPE = 'char' THEN CHARACTER_MAXIMUM_LENGTH WHEN DATA_TYPE = 'tinyblob' OR DATA_TYPE = 'tinytext' THEN CHARACTER_MAXIMUM_LENGTH + 1 WHEN DATA_TYPE = 'blob' OR DATA_TYPE = 'text' THEN CHARACTER_MAXIMUM_LENGTH + 2 WHEN DATA_TYPE = 'mediumblob' OR DATA_TYPE = 'mediumtext' THEN CHARACTER_MAXIMUM_LENGTH + 3 WHEN DATA_TYPE = 'largeblob' OR DATA_TYPE = 'largetext' THEN CHARACTER_MAXIMUM_LENGTH + 4 -- NUMERIC FIELDS WHEN DATA_TYPE = 'tinyint' THEN 1 WHEN DATA_TYPE = 'smallint' THEN 2 WHEN DATA_TYPE = 'mediumint' THEN 3 WHEN DATA_TYPE = 'int' OR DATA_TYPE = 'integer' THEN 4 WHEN DATA_TYPE = 'bigint' THEN 8 WHEN DATA_TYPE = 'float' AND (NUMERIC_PRECISION <= 24 OR NUMERIC_PRECISION IS NULL) THEN 4 WHEN DATA_TYPE = 'float' AND NUMERIC_PRECISION > 24 THEN 8 WHEN DATA_TYPE = 'bit' THEN (NUMERIC_PRECISION + 7) / 8 WHEN DATA_TYPE = 'double' OR DATA_TYPE = 'numeric' THEN (FLOOR(NUMERIC_PRECISION/9)*4) + ROUND((NUMERIC_PRECISION- FLOOR(NUMERIC_PRECISION/9)*9)*.5,0) -- DATETIME FIELDS WHEN DATA_TYPE = 'date' OR DATA_TYPE = 'time' THEN 3 WHEN DATA_TYPE = 'datetime' THEN 8 WHEN DATA_TYPE = 'timestamp' THEN 4 WHEN DATA_TYPE = 'year' THEN 1 ELSE 999999999999999 END) + (CASE WHEN IS_NULLABLE = 'YES' THEN 1 ELSE 0 END)) AS PK_BYTE_SPACE FROM information_schema.columns COL_SP WHERE COLUMN_KEY = 'PRI' AND TABLE_SCHEMA = 'PODS' GROUP BY TABLE_SCHEMA , TABLE_NAME) AS IX_SP ON PK_SP.TABLE_SCHEMA = IX_SP.TABLE_SCHEMA AND PK_SP.TABLE_NAME = IX_SP.TABLE_NAME WHERE PK_SP.TABLE_SCHEMA = 'PODS') AS A ) AS B GROUP BY B.TABLE_SCHEMA , B.TABLE_NAME * *

1 0

[Maria-discuss] Reqeust for SHOW FULL FIELDS FROM and virtual tables in MariaDB
by Peter Laursen 06 Jun '11

06 Jun '11

Refer http://kb.askmonty.org/v/virtual-columns - the example CREATE TABLE table1 ( a INT NOT NULL, b VARCHAR(32), c INT AS (a MOD 10) virtual, d VARCHAR(5) AS (LEFT(b,5)) persistent); now SHOW FULL FIELDS FROM table1; Field Type Collation Null Key Default Extra Privileges Comment ------ ----------- ----------------- ------ ------ ------- ------- ------------------------------- ------- a int(11) (NULL) NO (NULL) select,insert,update,references b varchar(32) latin1_swedish_ci YES (NULL) select,insert,update,references c int(11) (NULL) YES (NULL) VIRTUAL select,insert,update,references d varchar(5) latin1_swedish_ci YES (NULL) VIRTUAL select,insert,update,references I request that 'extra' column should return either "PERSISTENT" or " VIRTUAL, PERSISTENT" for column `d`. The reason is that the output from SHOW FULL FIELDS returns an *array* (that will not need to be parsed) as opposite to SHOW CREATE TABLE. Accordingly SHOW FULL FIELDS is much easier to use for an 'unintelligent client' as opposed to an 'intelligent (or ignorant for that sake :-) ) human user to decide on special properties for a column. Peter Webyog

3 4

[Maria-discuss] New Knowledgebase Question: MariaDB xtradb installation problem
by Daniel Bartholomew 04 Jun '11

04 Jun '11

Hello everyone, there's a new question in the Knowledgebase: http://kb.askmonty.org/v/mariadb-xtradb-installation-problem Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

1 0

[Maria-discuss] Support for MariaDB Configuration Options in MySQL Workbench
by Jake Drew 26 May '11

26 May '11

It looks like the MySQL Workbench team has decided to place support for MariaDB configuration file options in the next release of MySQL Workbench! (Configuration file option tabs currently display blank for any MariaDB versions greater than 5.1 in MySQL Workbench) http://bugs.mysql.com/bug.php?id=61219

1 0

[Maria-discuss] UserStats = 1
by Jake Drew 18 May '11

18 May '11

I am interested in turning on user statistics as described here: http://kb.askmonty.org/v/user-statistics. However, when I add the line userstats = 1 to the my.ini file under the [mysqld] section, the server will not restart. I am running MariaDB 5.1.53 on Windows Server 2008 R2. Is there anything else I need to install before I am able to use this option?

3 3

[Maria-discuss] 5.2.6 .msi isntaller questions issues
by Peter Laursen 14 May '11

14 May '11

I think this is for @wlad! :-) I just installed 5.2.6 using the new .msi installer. This was an upgrade 'on top' of the previous (5.2.3 or 5.2.4) .msi build distributed by wlad in mailing list. The installer worked fine, The previos 5.2 instance was upgraded (ie. executables and similar files replaced, old my.ini and datadir is still used etc.). So far so good. The problem is with the configuration wizard. What is it supposed to do? Run 'mysql_upgrade'? More than this or something else? Let me explain my setup. Except for this MariaDB instance (running as service 'maria52') I have both MySQL/Oracle 5.0, 5.1, 5.5 and 5.6 (running as services 'MySQL50, 'MySQL51' , 'MySQL55' , 'MySQL56') . The config wizard prompts me which instance should be upgraded an gives me a choice of 'MySQL50 and 'MySQL51' but not 'MySQL55', 'MySQL56' and 'maria52'. 1) Now if the wizard starts 'mysql_upgrade' it makes perfect sense not to offer to upgrade 'MySQL55' and 'MySQL56' (as MySQL51/MariaDb52 are 'lower' than those - it would be a 'downgrade' - not an 'upgrade' then - what 'mysql_upgrade' does not handle). But ideally I think they should be displayed but 'greyed out' or otherwise indicated that upgrading MySQL 5.5/5.6 --> MariaDB 5.2 is not possible. 2) More important: the instance that I *really* wanted to upgrade ('maria52') was not displayed. I assume that the services' list is filtered by the prefix 'mysql'. Am I right? In that case I suggest the filtering is done on both (OR'ed) prefixes 'mysql' and 'maria'. Anyway after just exiting the wizard before executing it everything seems fine. But the wizard GUI can still be 'polished' a little I think. -- Peter Laursen -- Webyog

2 1

[Maria-discuss] New Knowledgebase Question
by Daniel Bartholomew 12 May '11

12 May '11

Here's a new question in the Knowlegebase about running MariaDB and MySQL side-by-side on the same server. http://kb.askmonty.org/v/mariadb-coexist-with-mysql The text of the question: ---------------------------------------------------------------- Hello, I'm currently using MySQL and would like to test MariaDB. I only have one server, and don't wish to overwrite the current MySQL instance. Is it possible to have both MySQL and MariaDB to coexist within the same environment? How can I define the mysql client on which db to connect to? by port? Many thanks! ---------------------------------------------------------------- Anyone with real-world experience in running (or trying to run) MariaDB side-by-side with MySQL want to add a comment to (or answer) this question? I thought I remembered seeing an email thread on this topic a while back but my search-fu is weak today and I can't find it (well, either that or I'm imagining things). Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

2 1

[Maria-discuss] How to fix the fact that MariaDB runs as 'mysql', instead of 'mysqld' now.
by Bradley D. Thornton 07 May '11

07 May '11

-----BEGIN PGP SIGNED MESSAGE----- Hash: RIPEMD160 I've run into a problem with implementations of MariaDB where I get messages like "mysqld: unrecognized service". This threw me for a loop at first because everything seems to be fine on the surface, drop in replacement as usual, etc. The problem, seems to be related to something I hadn't noticed before, which is that MariaDB runs as 'mysql', instead of 'mysqld'. Is there a tutorial or howto related to this issue? I would imagine that it is a common occurrence (not a bug, obviously, yet still an issue that might need to be addressed on a regular basis when performing installations). Although I usually use Slackware, on the particular CentOS 5.6 box that is giving me the problem, the executibles/scripts that I have are located: /usr/bin/mysqld_multi /usr/bin/mysqld_safe /usr/bin/mysqldump /usr/bin/mysqldumpslow /usr/sbin/mysqld To be specific, I installed PiaF via a script onto a running host under VMware ESXI, that I prepared from scratch, had some libraries, yet no real install of MySQL. That made the installation of MariaDB even easier than usual, until I had prepped the box and ran the installation script, which at one point pulls binaries from the pbxinaflash.com site as a bundle. I've been sifting through the code, to see if I can correct this, yet figure that I'll have endless headaches when, for example, I perform updates to the PBX system. Therefore, I'm thinking that symlinking or something of that nature is the most effective solution, as part of my standard EllisonCO eradication philosophy, to avoid such issues in the future. Any assistance in this regard would be greatly appreciated, and go a long way towards me completing a complete removal of all MySQL Servers under my purview. Kindest regards, - -- Bradley D. Thornton Manager Network Services NorthTech Computer TEL: +1.760.666.2703 (US) TEL: +44.203.318.2755 (UK) http://NorthTech.US -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.10 (GNU/Linux) Comment: Find this cert at x-hkp://pool.sks-keyservers.net iQEcBAEBAwAGBQJNxNIRAAoJEE1wgkIhr9j3S1IH/iZHW2yYwI6K0S5nOwIMVj+C OVqNG5W5+wdzN6XAIBWhoh6PO7o9YDxoow59qdaHN4yw9fmGRFVadSg9PHcQZd7g yRVyTnuPALFWJ5YSOurC2rhyYKUQfV+oqEV6AXfznF7YOZSr47fJG1Mrw7IBO2dl GGjxmq7P/e1SsBAcOe7c6Okj5MxKlAk5S/gzRqeIcLthtRaKoNBIQhDaG36tnUNn rXWvhe1nU8vz7E9ZbFOvWE9Qe40j/P6nNtmcTeUTlP+0wc3Jue0h6+Nk7sWpZe4a 15KZx4AZHT417m+OpEpsn4CdNCkNVcdH606ijo60A/FVyyeTSZb2d5tI2bM13wM= =ng1q -----END PGP SIGNATURE-----

2 2

[Maria-discuss] Hibernate & MariaDB
by Daniel Bartholomew 21 Apr '11

21 Apr '11

New question in the Knowledgebase: http://kb.askmonty.org/v/hibernate-mariadb ---------------------------------------------------------------------- Hello, what settings do I need in a Hibernate (/Spring) configuration file (XML) for MariaDB? The same as in MySQL? Which URL, JDBC driver and Hibernate dialect I have to use? Thanks in advance! Greetings: Anke ---------------------------------------------------------------------- Anyone with any experience with Hibernate and Spring want to jump in and comment on (or answer) this question? If not I will just answer with the standard "If it works with MySQL it will work with MariaDB...." Thanks. -- Daniel Bartholomew MariaDB - http://mariadb.org Monty Program - http://montyprogram.com AskMonty Knowledgebase - http://kb.askmonty.org

2 1

[Maria-discuss] dynamic columns interface
by MARK CALLAGHAN 19 Apr '11

19 Apr '11

Will dynamic columns always require the position (column_nr argument)? I prefer an interface in which that is not required. -- Mark Callaghan mdcallag(a)gmail.com

3 3