Re: Myisam advantages
Hi, Much more conservative approach to disk space management - each MyISAM table is stored in a separate file, which could be compressed then with myisamchk if needed. With InnoDB the tables are stored in tablespace, and not much further optimization is possible. All data except for TEXT and BLOB can occupy 8,000 bytes at most. No full text indexing is available for InnoDB. TRhe COUNT(*)s execute slower than in MyISAM due to tablespace complexity. MyISAM tables have the following characteristics: · All data values are stored with the low byte first. This makes the data machine and *operating system* independent. The only requirements for binary portability are that the machine uses two's-complement signed integers and IEEE floating-point format. These requirements are widely used among mainstream machines. Binary compatibility might not be applicable to embedded systems, which sometimes have peculiar processors. There is no significant speed penalty for storing data low byte first; the bytes in a table row normally are unaligned and it takes little more processing to read an unaligned byte in order than in reverse order. Also, the code in the server that fetches column values is not time critical compared to other code. · All numeric key values are stored with the high byte first to allow better index compression. · Large files (up to 63-bit file length) are supported on filesystems and operating systems that support large files. · The maximum number of indexes per MyISAM table is 64. This can be changed by recompiling. Beginning with MySQL 5.1.4, you can configure the build by invoking *configure* with the --with-max-indexes=*N* option, where *N* is the maximum number of indexes to permit per MyISAM table. *N* must be less thann or equal to 128. Before MySQL 5.1.4, you must change the source. The maximum number of columns per index is 16. · The maximum key length is 1000 bytes. This can also be changed by changing the source and recompiling. For the case of a key longer than 250 bytes, a larger key block size than the default of 1024 bytes is used. · When rows are inserted in sorted order (as when you are using an AUTO_INCREMENT column), the index tree is split so that the high node only contains one key. This improves space utilization in the index tree. · Internal handling of one AUTO_INCREMENT column per table is supported. MyISAM automatically updates this column for INSERT and UPDATEoperations. This makes AUTO_INCREMENT columns faster (at least 10%). Values at the top of the sequence are not reused after being deleted. (When an AUTO_INCREMENT column is defined as the last column of a multiple-column index, reuse of values deleted from the top of a sequence does occur.) The AUTO_INCREMENT value can be reset with ALTER TABLE or *myisamchk*. · Dynamic-sized rows are much less fragmented when mixing deletes with updates and inserts. This is done by automatically combining adjacent deleted blocks and by extending blocks if the next block is deleted. · If a table has no free blocks in the middle of the data file, you can INSERT new rows into it at the same time that other threads are reading from the table. (These are known as concurrent inserts.) A free block can occur as a result of deleting rows or an update of a dynamic length row with more data than its current contents. When all free blocks are used up (filled in), future inserts become concurrent again. See Section 7.3.3, “Concurrent Inserts”http://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/concurrent-inserts.html. · You can put the data file and index file on different directories to get more speed with the DATA DIRECTORY and INDEX DIRECTORY table options to CREATE TABLE. See Section 13.1.5, “CREATE TABLE Syntax”http://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/create-table.html. · BLOB and TEXT columns can be indexed. · NULL values are allowed in indexed columns. This takes 0–1 bytes per key. · Each character column can have a different character set. See Chapter 10, *Character Set Support*http://www.linuxtopia.org/online_books/database_guides/mysql_5.1_database_reference_guide/charset.html. · There is a flag in the MyISAM index file that indicates whether the table was closed correctly. If *mysqld* is started with the --myisam-recover option, MyISAM tables are automatically checked when opened, and are repaired if the table wasn't closed properly. · *myisamchk* marks tables as checked if you run it with the --update-state option. *myisamchk --fast* checks only those tables that don't have this mark. · *myisamchk --analyze* stores statistics for portions of keys, as well as for entire keys. · *myisampack* can pack BLOB and VARCHAR columns. *MyISAM also supports the following features: * · Support
RE: Why is MySQL always linked to Php?
[snip] PHP applications are, for the most part, not that ambitious and mysql is simply the most accessible database with the best developed API. [/snip] I know that you said for the most part and you are absolutely correct. I just want to point out that there are many corporations relying on PHP and MySQL to deliver robust, scalable and enterprise capable applications each and every day. I suppose that is part of the appeal - low barriers to entry with infinite possibilities. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
[ANN] PBXT 1.5.02 Beta Released!
Hi All, I have just released PBXT 1.5.02 Beta. This version adds a 2nd level (SSD based) cache! For more information on the 2nd Level Cache, please check out my blog: http://pbxt.blogspot.com/2010/07/pbxt-1502-beta-adds-2nd-level-cache.html The new version is available for download from: http://primebase.org/download The documentation (http://primebase.org/documentation) has been updated to cover versions 1.0, 1.1 and 1.5. PBXT is an ACID compliant, transactional storage engine for MySQL. It features MVCC (multi-version concurrency control), row-level locking, referential integrity and has a log-based architecture. PBXT is an open source project, licensed under GPL 2.0. Development is done on Launchpad: https://launchpad.net/pbxt. The current stable release version of PBXT is 1.0.11 Pre-GA, which can be downloaded primebase.org or from Launchpad. Bugs can be reported here: https://bugs.launchpad.net/pbxt. If you have any questions or comments, please let me know. Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: [ANN] PBXT 1.5.02 Beta Released!
Can I somehow unsubscribe (that is opt-out , as opposed to the more civilised opt-in) from the myriad of commercial announcements on this list, or do I just have to spambrand all senders ? Some weeks it feels as if there's more productspamming than posting going on. On Fri, Jul 16, 2010 at 4:25 PM, Paul McCullagh paul.mccull...@online.dewrote: Hi All, I have just released PBXT 1.5.02 Beta. This version adds a 2nd level (SSD based) cache! For more information on the 2nd Level Cache, please check out my blog: http://pbxt.blogspot.com/2010/07/pbxt-1502-beta-adds-2nd-level-cache.html The new version is available for download from: http://primebase.org/download The documentation (http://primebase.org/documentation) has been updated to cover versions 1.0, 1.1 and 1.5. PBXT is an ACID compliant, transactional storage engine for MySQL. It features MVCC (multi-version concurrency control), row-level locking, referential integrity and has a log-based architecture. PBXT is an open source project, licensed under GPL 2.0. Development is done on Launchpad: https://launchpad.net/pbxt. The current stable release version of PBXT is 1.0.11 Pre-GA, which can be downloaded primebase.org or from Launchpad. Bugs can be reported here: https://bugs.launchpad.net/pbxt. If you have any questions or comments, please let me know. Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: [ANN] PBXT 1.5.02 Beta Released!
Hi Johan, I understand what you are saying, but this is certainly not the announcement of a commercial product. It is also not spam, because the announcement of the release of a open source MySQL Storage Engine is relevant to this list. Nevertheless, I prefix my e-mails with [ANN] (for announcement), so that you can filter it out if you don't want to see it. Best regards, Paul On Jul 16, 2010, at 4:41 PM, Johan De Meersman wrote: Can I somehow unsubscribe (that is opt-out , as opposed to the more civilised opt-in) from the myriad of commercial announcements on this list, or do I just have to spambrand all senders ? Some weeks it feels as if there's more productspamming than posting going on. On Fri, Jul 16, 2010 at 4:25 PM, Paul McCullagh paul.mccull...@online.de wrote: Hi All, I have just released PBXT 1.5.02 Beta. This version adds a 2nd level (SSD based) cache! For more information on the 2nd Level Cache, please check out my blog: http://pbxt.blogspot.com/2010/07/pbxt-1502-beta-adds-2nd-level-cache.html The new version is available for download from: http://primebase.org/download The documentation (http://primebase.org/documentation) has been updated to cover versions 1.0, 1.1 and 1.5. PBXT is an ACID compliant, transactional storage engine for MySQL. It features MVCC (multi-version concurrency control), row-level locking, referential integrity and has a log-based architecture. PBXT is an open source project, licensed under GPL 2.0. Development is done on Launchpad: https://launchpad.net/pbxt. The current stable release version of PBXT is 1.0.11 Pre-GA, which can be downloaded primebase.org or from Launchpad. Bugs can be reported here: https://bugs.launchpad.net/pbxt. If you have any questions or comments, please let me know. Best regards, Paul -- Paul McCullagh PrimeBase Technologies www.primebase.org www.blobstreaming.org pbxt.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Why is MySQL always linked to PHP?
-Original Message- From: Jay Blanchard [mailto:jblanch...@pocket.com] Sent: Friday, July 16, 2010 5:25 AM To: Michael Dykman; mysql Subject: RE: Why is MySQL always linked to Php? [snip] PHP applications are, for the most part, not that ambitious and mysql is simply the most accessible database with the best developed API. [/snip] I know that you said for the most part and you are absolutely correct. I just want to point out that there are many corporations relying on PHP and MySQL to deliver robust, scalable and enterprise capable applications each and every day. I suppose that is part of the appeal - low barriers to entry with infinite possibilities. Yeah, I took a bit of offense to that original statement too. I work for Panasonic Avionics, and we have no less than 12 servers here running LAMP boxes in my department alone. They are in master/slave pairs. We have almost 100GB of data with about 1 BILLION rows. Everytime an aircraft lands, we get an offload about the (IFE) In Flight Entertainment system. What movies are watched, for how long, what games were played, what level reached, did the system reboot, was it commanded to reboot, all sorts of things. We get about an offload per minute or so 24/7. These servers are reliable and the SaaS we sell along with the IFE costs MILLIONS of dollars per airline. The GUI is all PHP with some Python as the backend to parse the offloads. We run Ubuntu 8.04LTS. When I founded WildTangent, everything there was LAMP boxes. All the games checked into a LAMP server. All the backend tools we created were LAMP. They switched to IIS I believe a few years ago because I can only assume that Alex St. John (the creator of DirectX) is a Microsoft guy, and our game technology is based upon Microsoft tech, so it probably didn't look so good to be running Linux for them. ;-) At Lockdown Networks, we sold $50k rack mount units that were all LAMP based with Ruby backends. These units secured many of the worlds networks from banks to nuclear reactors to colleges to government/military agencies. In all cases, we could have spent thousands to millions on Oracle servers or used the free alternatives like Postgress. We researched and CHOSE to use mySQL -- even paying the licensing fees at Lockdown over porting to Postgress (which we seriously looked into) So, I think someone needs to recognize that mySQL and PHP are extremely powerful and robust tools and are used in VERY ambitious projects. ;-) d http://daevid.com There are only 11 types of people in this world. Those that think binary jokes are funny, those that don't, and those that don't know binary. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [ANN] PBXT 1.5.02 Beta Released!
-Original Message- From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De Meersman Sent: Friday, July 16, 2010 7:41 AM To: MySQL List Subject: Re: [ANN] PBXT 1.5.02 Beta Released! Can I somehow unsubscribe (that is opt-out , as opposed to the more civilised opt-in) from the myriad of commercial announcements on this list, or do I just have to spambrand all senders ? Some weeks it feels as if there's more productspamming than posting going on. Interesting. I actually LIKE the announcements of new products related to mySQL. Each new product (commercial or FOSS) makes mySQL that much more robust and gives more credibility to our favorite RDBMS. Keep 'm comin guys! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why is MySQL always linked to PHP?
I realize this is somewhat off-topic for this list but: I too have extensive experience with PHP/MySQL used in large scale projects but I have experience with other languages and frameworks as well. I merely wished to dispel the common, newb impression that PHP is the only realistic choice. - md On Fri, Jul 16, 2010 at 2:38 PM, Daevid Vincent dae...@daevid.com wrote: -Original Message- From: Jay Blanchard [mailto:jblanch...@pocket.com] Sent: Friday, July 16, 2010 5:25 AM To: Michael Dykman; mysql Subject: RE: Why is MySQL always linked to Php? [snip] PHP applications are, for the most part, not that ambitious and mysql is simply the most accessible database with the best developed API. [/snip] I know that you said for the most part and you are absolutely correct. I just want to point out that there are many corporations relying on PHP and MySQL to deliver robust, scalable and enterprise capable applications each and every day. I suppose that is part of the appeal - low barriers to entry with infinite possibilities. Yeah, I took a bit of offense to that original statement too. I work for Panasonic Avionics, and we have no less than 12 servers here running LAMP boxes in my department alone. They are in master/slave pairs. We have almost 100GB of data with about 1 BILLION rows. Everytime an aircraft lands, we get an offload about the (IFE) In Flight Entertainment system. What movies are watched, for how long, what games were played, what level reached, did the system reboot, was it commanded to reboot, all sorts of things. We get about an offload per minute or so 24/7. These servers are reliable and the SaaS we sell along with the IFE costs MILLIONS of dollars per airline. The GUI is all PHP with some Python as the backend to parse the offloads. We run Ubuntu 8.04LTS. When I founded WildTangent, everything there was LAMP boxes. All the games checked into a LAMP server. All the backend tools we created were LAMP. They switched to IIS I believe a few years ago because I can only assume that Alex St. John (the creator of DirectX) is a Microsoft guy, and our game technology is based upon Microsoft tech, so it probably didn't look so good to be running Linux for them. ;-) At Lockdown Networks, we sold $50k rack mount units that were all LAMP based with Ruby backends. These units secured many of the worlds networks from banks to nuclear reactors to colleges to government/military agencies. In all cases, we could have spent thousands to millions on Oracle servers or used the free alternatives like Postgress. We researched and CHOSE to use mySQL -- even paying the licensing fees at Lockdown over porting to Postgress (which we seriously looked into) So, I think someone needs to recognize that mySQL and PHP are extremely powerful and robust tools and are used in VERY ambitious projects. ;-) d http://daevid.com There are only 11 types of people in this world. Those that think binary jokes are funny, those that don't, and those that don't know binary. -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
deletes from a VERY large table
I have to delete old records from a very large table (1.6billion rows)
in a stored procedure.
CREATE TABLE mytable(
id BIGINT(20) UNSIGNED NOT NULL DEFAULT 0,
unix_time INT(11) NOT NULL DEFAULT 0,
value DOUBLE (20, 4) NOT NULL DEFAULT 0.,
UNIQUE INDEX history_1 USING BTREE (id, unix_time)
)
ENGINE = INNODB;
So I can get the unix time string I use a variable:
DECLARE UnixTime BIGINT(20);
SET UnixTime = UNIX_TIMESTAMP(DATE_SUB(now(), INTERVAL 30 DAY));
So now I can do my delete query. For now I made a temp table:
CREATE TEMPORARY TABLE historyDropper(
id BIGINT(20) NOT NULL,
UNIQUE INDEX index1 USING BTREE (id)
);
And load it with all the unique id I want to delete, then join that to
my huge table:
SET @sql = CONCAT('DELETE h.* FROM mytable h inner join historyDropper
hd on h.id = hd.id WHERE unix_time ', UnixTime, ' ');
PREPARE s1 FROM @sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;
My question is, is this the most efficient way to delete data older than
a certain unix_timestamp out of s huge table?
Would it be better to loop thru each unique id and delete all the items
older? Is there a utility or command line or mysql dump and restore
method that is faster?
Thanks for the tips,
Bryancan
RE: Why is MySQL always linked to PHP?
[snip] I merely wished to dispel the common, newb impression that PHP is the only realistic choice. [/snip] I don't think that is the impression but I think that the low barrier to entry and extensive support community make PHP a widely acceptable choice. I have seen many a newb turned off by the communities surrounding other languages (I have seen it in PHP too) but by and large the PHP community is pretty accepting and willing to teach young programmers how to fish. You always have to use the right tool for the job though. The question is are we teaching the inexperienced programmers what the right tools are? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
