Changed Logging Table from InnoDB to MyIsam, what memory buffers to tune?
We finally split our huge logging aspect of an application to its own system, and we changed the table type from InnoDB to MyIsam. This is a logging table, 1 log writer, many, many selects from customer care reps looking up the last 500 or so records. These are logs of timed events, so they always need to see the most recent 500 records. The memory buffers that are set for now are as follows: ### OPERATIONAL SETTINGS key_buffer_size=416M myisam_sort_buffer_size=128M join_buffer_size=128M read_buffer_size=16M sort_buffer=256M read_rnd_buffer_size=32M query_cache_size=32M The box has 4 GB, it is Linux, so I am thinking I can use up to 3 GB. I did not size these initial settings but the more I see on what others use, I learn of all kinds of buffers I never thought of using before. I understand that some of these buffers will degrade performance if too large, while others can grow to the sky, and only improve performance. I would appreciate some additional input on these settings, and advice how I could optimize these even further to obtain as much through-put as possible from the system. Are there any other buffers I could set? The majority of all action are inserts from the log writer, and selects from customer care reps. One other thing, what can be optimized if the log writer is split into multiple threads, and many insert processes are happening at once? Regards, Thomas A. Lekai Vonage Holdings [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Speed of Inserts into MyIsam tables from mysqlbinlog
I was running a table in InnoDB, and the table had about 6 indexes, none of which seemed to be affected when I ran mysqlbinlog against the tables in order to apply bin-logs from production against a test system. I was manage to process upwards of 2300 queries per second by throwing about 1.8 GB of memory into the innodb_buffer_pool. Now that I am dealing with MyIsam tables, I shifted my memory towards the key_buffer, and am considering using bulk_insert_buffer_size. I am not sure what is a good value for the bulk_insert_buffer_size. I have also removed all indexes that are not needed from the table while it is being loaded with data. My question, is there anything else that I can do to increase the speed of the inserts into this MyIsam table as the bin_logs are played back on the system? This is also used to help gauge performance, so this method of inserting data is important to the process as a whole. Regards, Thomas.
Logging Data: Should I use MyIsam or InnoDB?
I have a curious issue here, maybe someone can help. I have a single process that inserts data into tables that contain purely logging information. This table is then searched by our Care department to troubleshoot issues. I am looking for the best way to store this data, and the structure on the backend. There are 50 million inserts into table LOG a day. The primary index on the table is seconds from 1971. I only need to keep 60 days worth of data, and the table is only used for read purposes. This is my design criteria, but my problem is how to delete old data without crashing the log writer that is atteched to the table. I would prefer to use MyIsam, since it is simple data, and as such it is much faster than an untuned InnoDB table. But what would I do when it is time to delete data? The delete would lock the table, hence freeze the application, and I can not have that. I thought of using a combo of InnoDB and Merge tables, where the LOG table is InnoDB, and the LOG_ARCH tables are Merge. The application would know to read from both, and I can just migrate data from the InnoDB table to the Merge tables, and then delete from the LOG table and not affect the app. since the delete is running against an InnoDB table. I would use truncate table, but there will always be a certain amount of time that will elapse between copying data from the live version to the archive, hence the need for a selective delete on the original. If anyone with experience with large logging apps can chime in here, I would be most appreciative. Regards, Thomas. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Logging Data: Should I use MyIsam or InnoDB?
Jeremy, Thanks, this is what I was originally thinking of, but how I am getting rid of the data in log_view_today? OR, are you saying that log_view_today is a merge table for only the current day? That table def is defined every night? Then I would go about dropping everything in whatever fashion I need. When I recreate the merge table for just the current day, don't I have to drop the merge table, or it just gets recreated automatically. I am not sure why you reference atomic on ALTER TABLE . . . , if there is a log writer attached to that table, won't I have to wait for a lock? What do you mean by atomic? I understand the term atomic transaction, just not sure of your context to this example. Thanks for the idea, I was already in this neck of the woods, but the MERGE table just for today, I was not sure about that. My problems in the past deal with the locking of the table by the logwriter, hence the need to truncate the table. But while it is truncating, the table hung, hence the need for InnoDB. Thomas. -Original Message- From: Jeremy Cole [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 18, 2005 2:28 PM To: Thomas Lekai Cc: mysql@lists.mysql.com Subject: Re: Logging Data: Should I use MyIsam or InnoDB? Hi Thomas, I have a curious issue here, maybe someone can help. I have a single process that inserts data into tables that contain purely logging information. This table is then searched by our Care department to troubleshoot issues. I am looking for the best way to store this data, and the structure on the backend. There are 50 million inserts into table LOG a day. The primary index on the table is seconds from 1971. I only need to keep 60 days worth of data, and the table is only used for read purposes. This is my design criteria, but my problem is how to delete old data without crashing the log writer that is atteched to the table. OK, how about this: Use MyISAM and MERGE tables. Keep one table per day. E.g.: log_2005_01_15 log_2005_01_16 log_2005_01_17 log_2005_01_18 etc. Use MERGE tables to give you the 60 day (and perhaps e.g. 14 day, 30 day, 7 day, etc.) read views that you need, like so: CREATE TABLE log_view_7day ( ... ) TYPE=MERGE UNION=( log_2005_01_12, log_2005_01_13, log_2005_01_14, log_2005_01_15, log_2005_01_16, log_2005_01_17, log_2005_01_18 ); Create another MERGE table for today using INSERT_METHOD: CREATE TABLE log_view_today ( ... ) TYPE=MERGE INSERT_METHOD=FIRST UNION=( log_2005_01_18 ); You can then do all of your inserts from the log writer into the today table, and do your reads against the various MERGEs. Every day at exactly midnight, you would use ALTER TABLE (which is atomic) to redefine the UNION of the MERGE definition of the various tables. When you want to delete your old data, it's simply a matter of doing an ALTER TABLE to remove them from the MERGE, and using DROP TABLE to drop the log__mm_dd table after that. Also note that you could compress the old data and leave it live (but read only) in case you ever need it. myisampack can help you with that. I hope that helps! Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Logging Data: Should I use MyIsam or InnoDB?
Jeremy, Thanks a million, this makes perfect sense, I will test this out asap. In theory it sounds like the plan, I just need to see if it will work here. I really appreciate the help . . . Regards, Thomas. -Original Message- From: Jeremy Cole [mailto:[EMAIL PROTECTED] Sent: Tuesday, January 18, 2005 3:30 PM To: Thomas Lekai Cc: mysql@lists.mysql.com Subject: Re: Logging Data: Should I use MyIsam or InnoDB? Hi Thomas, Thanks, this is what I was originally thinking of, but how I am getting rid of the data in log_view_today? OR, are you saying that log_view_today is a merge table for only the current day? That table def is defined every night? Then I would go about dropping everything in whatever fashion I need. Basically, at midnight you would do a sequence like this: CREATE TABLE log_2005_01_19 ( ... ); ALTER TABLE log_view_today UNION=(log_2005_01_19); ALTER TABLE log_view_7day UNION=(..., log_2005_01_19); etc. etc. You could actually create the tables beforehand, and only do the ALTER TABLEs themselves at midnight. Note that this procedure has a race condition in that, depending on when the table actually switches over, you may have some records on either side of the split that don't belong. You can always move those back manually with something like: INSERT INTO log_2005_01_18 SELECT * from log_2005_01_19 WHERE datefield 2005-01-19 00:00:00; DELETE FROM log_2005_01_19 WHERE datefield 2005-01-19 00:00:00; or INSERT INTO log_2005_01_19 SELECT * from log_2005_01_18 WHERE datefield = 2005-01-19 00:00:00; DELETE FROM log_2005_01_18 WHERE datefield = 2005-01-19 00:00:00; In some cases it might be easier to do the switch always e.g. 5 seconds before midnight, so that any records falling on the wrong side of the edge will always be on the same side. That makes things easier sometimes. When I recreate the merge table for just the current day, don't I have to drop the merge table, or it just gets recreated automatically. I am not sure why you reference atomic on ALTER TABLE . . . , if there is a log writer attached to that table, won't I have to wait for a lock? What do you mean by atomic? I understand the term atomic transaction, just not sure of your context to this example. What I mean is, you can use ALTER TABLE to change the definition (e.g. which tables it contains) of the MERGE table. This happens atomically (no INSERTs will error, and no records could conceivably be split by the sudden change). Does that all make sense? Regards, Jeremy -- Jeremy Cole Technical Yahoo - MySQL (Database) Geek -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
