The right table manager to use (MyISAM or InnoDB) really depends on the type of work you are requiring of MySQL.
If you are mainly doing selects, and hardly any updates/deletes/inserts, then MyISAM is faster. Its indexes, as far as I know, are smaller and more efficient. However, when you start doing a little bit more inserts and updates, MyISAM starts to bog down your system. This is because MyISAM locks the entire table in order to perform a modification on it, only after the modification is "commited" does MySQL unlock the tables for read/write.
InnoDB on the other hand, does row-level locking. This means when a record is being updated/inserted/deleted, only the relevant row is locked, and the table can still be concurrently read and insert/update/delete operations can still take place for other rows. This can majourly increase the speed of a database if there is big "mix" of queries. If you are working on something simple, and don't make too many inserts/updates/deletes, then MyISAM is best, as it takes almost twice as less space to store the data - and as you should know, the more data there is, the longer it takes for the physical hard drive to seek up the data and spit it out to MySQL.
Hope this helps!
Maximo Migliari.
At 19:28 6/1/2003 +0200, you wrote:
Gelu Gogancea wrote:We r here to help one another solving their problems. Ive never posted any solutions, to this list or another based on religion or something else but the problem itself only. I invite all people here to read the Islamic history of true muslims and youll find that we never treated people based on their religion. All the times nonmuslims get all thier rights under isalmic control. We never kill children, women, unarmed persons...etcHi, I'm sure that all the people from this list RESPECT in what you believe or in values which are considered by you to be inviolable/holy. I'M NOT JEWISH but i'm sure if you will see, like logo/slogan , something like ... "I WILL LOVE YOU FOREVER WITH MY HEART AND MY BLOOD, BEN GURION." ... i'm almost sure that you will feel frustration...and THIS IS NOT RIGHT.For this reason please remove your logo from the bottom of your e-mail body and after this i'm sure all people which are subscribed to this list will try to find a solution for your problem(maybe and SAM). Personally, i beg you to repost your problem to the list because i didn't "catch" the e-mail where is described your problem. Best Regards, Gelu _____________________________________________________ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED]Ive already done that. I asked about a solution to my problem. -- Sameh Attia Senior System Engineer T.E. Data -- __ __ _ _________ _/ /_/ /_(_)___ _ / ___/ __ `/ __/ __/ / __ `/ (__ ) /_/ / /_/ /_/ / /_/ / /____/\__,_/\__/\__/_/\__,_/ --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
So I think it is my right to sign my mails with any signature I see apropriate for myself. If this is going to hurt someone please when u see mails from [EMAIL PROTECTED] do not scroll down to the bottom of the mail because u will see my signature sitting over there.
And now back to my problem
I have a system running MySQL 3.23.41. I read many times about InnoDB performance and that it is superior to the MyISAM one. I have a table 'sessions' in a MyISAM format with about 20 milion records. Its size is 2.5 GB; the index file is 1.1 GB. In mysql client I entered the following command 'alter table sessions type=innodb' while these settings were in my.cnf
innodb_data_home_dir=/path/to/the/database/dir
innodb_data_file_path=backup/ibdata:3072M:autoextend
set-variable = innodb_buffer_pool_size=128M
set-variable = innodb_additional_mem_pool_size=64M
set-variable = innodb_log_file_size=32M
set-variable = innodb_log_buffer_size=16M
The command took about 10 hours to complete. Is this normal?
Also when it finished I issued the follwoing statement on another copy of the table in MyISAM format; it took about 5 mins
SELECT ip,no,count(*) AS term,SUM(CEILING(session_time/60)*60) AS total,AVG(CEILING(session_time/60)*60) AS average FROM sessions GROUP BY ip;
while when it was issued on the InnoDB one it took me 30 mins to give up and shutdown the server with no luck.
Also the ibdata file is now 9.1 GB????
Whats wrong? could anyone help? Im sure that InnoDB is good so it seems I missed something could anyone point me?
Regards
--
Sameh Attia
Senior System Engineer
T.E. Data
--
__ __ _
_________ _/ /_/ /_(_)___ _
/ ___/ __ `/ __/ __/ / __ `/
(__ ) /_/ / /_/ /_/ / /_/ /
/____/\__,_/\__/\__/_/\__,_/
"She who is in my mind and mouth, I love her with all my heart and blood"
We'll restore OUR Palestine
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
