Re: some problem of InnoDB performance optimization
Hi, Lets see If I can help. Overly long queries (transactions in general) are bad for performance as a lot of unpurged versions accumulate. In this sentence, I don't know the meaning about 'unpureged version accumulate' When rows are updated new versions are created. They are later removed by purge thread - only then no active transactions may need them. This is why long open transactions are expensive. And I don't how to understanding 'open transaction'? This is transaction which is started (opened) but not yet ended by commit or rollback. Required for logical level replication to work properly. What's the meaning about logical level replication? MySQL has statement level, also caused logical as well as row based replication. statement level replication requires updates to be serializable to work. Can give problems for portable applications if you port from MySQL-4.0 to later What's the meaning about this sentence? This means you can run into the problems if you upgrade from MySQL 4.0 to later version. Probably is not much relevant any more. -- Peter Zaitsev, CEO, Percona Inc. Tel: +1 888 401 3401 ext 501 Skype: peter_zaitsev 24/7 Emergency Line +1 888 401 3401 ext 911 Percona Training Workshops http://www.percona.com/training/
Re: some problem of InnoDB performance optimization
HI Peter: Thanks for your answer. I have understand your answer. Thank you very much. ―― Best regards Timo Seven blog: http://zauc.wordpress.com #请翻墙浏览，或者指定hosts到74.200.243.252### UNIX System Admin 2010/3/23 Peter Zaitsev p...@percona.com Hi, Lets see If I can help. Overly long queries (transactions in general) are bad for performance as a lot of unpurged versions accumulate. In this sentence, I don't know the meaning about 'unpureged version accumulate' When rows are updated new versions are created. They are later removed by purge thread - only then no active transactions may need them. This is why long open transactions are expensive. And I don't how to understanding 'open transaction'? This is transaction which is started (opened) but not yet ended by commit or rollback. Required for logical level replication to work properly. What's the meaning about logical level replication? MySQL has statement level, also caused logical as well as row based replication. statement level replication requires updates to be serializable to work. Can give problems for portable applications if you port from MySQL-4.0 to later What's the meaning about this sentence? This means you can run into the problems if you upgrade from MySQL 4.0 to later version. Probably is not much relevant any more. -- Peter Zaitsev, CEO, Percona Inc. Tel: +1 888 401 3401 ext 501 Skype: peter_zaitsev 24/7 Emergency Line +1 888 401 3401 ext 911 Percona Training Workshops http://www.percona.com/training/
some problem of InnoDB performance optimization
Hi everyone: I read the presentation about InnodDB performance optimization what Heikki Tuuri written in april23 2007. But now I have some sentences don't know how to understanding. Can you help me? Overly long queries (transactions in general) are bad for performance as a lot of unpurged versions accumulate. In this sentence, I don't know the meaning about 'unpureged version accumulate' And I don't how to understanding 'open transaction'? Required for logical level replication to work properly. What's the meaning about logical level replication? Can give problems for portable applications if you port from MySQL-4.0 to later What's the meaning about this sentence? ―― Best regards Timo Seven blog: http://zauc.wordpress.com #请翻墙浏览，或者指定hosts到74.200.243.252### UNIX System Admin
Re: very slow inserts on InnoDB [InnoDB Performance Tuning]
Hi Catalin, Here are some InnoDB performance tuning tips that may boost your insert speed: Catalin Trifu wrote: ... innodb_buffer_pool_size = 256M Higher is better, in fact pushing this up to 60%-80% on a dedicated database would be good. If there are other things running like a web server, then you will have to take its memory requirements into account, but 256M could likely be bigger. Maybe this could be 512M ? Then data sets up to this size will be as fast as possible. innodb_additional_mem_pool_size = 64M Rarely does this need to be set over 8M. # Set .._log_file_size to 25 % of buffer pool size innodb_log_file_size = 32M Try setting this to 1/2 your buffer pool size, otherwise you might get too much checkpointing during lots of inserts. innodb_log_buffer_size = 8M Looks good. innodb_flush_log_at_trx_commit = 1 Try setting to trx_commit = 2 for faster insert performance, however you then lose ACID transactions, where if you have a system failure you could lose around 1 second worth committed data. These suggestions will not necessarily fix your problem. If you continue to have issues and they go unresolved on this list, you might consider getting help via our commercial offerings: http://www.mysql.com/network/ - OR - http://www.mysql.com/consulting/packaged/performance.html http://www.mysql.com/consulting/packaged/rapidresponse.html Regards, Josh -- Josh Chamas Director, Professional Services MySQL Inc., www.mysql.com Get More with MySQL! www.mysql.com/consulting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb performance issues
On Fri, 2005-07-15 at 13:28 -0700, David Griffiths wrote: David, Thanks for your suggestions, i'll give them a try. There are other tuning choices (including the thread-pool-cache). The best resource is the page on innodb performance tuning, and it can be found here: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html I didi read through this before I posted, however I am a programmer with no real admin experience in at the deep end, and it was all a little over my head :( I guess i'll get to understand it in time. Regards tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb performance issues
Hi, A few days ago i posted a quaestion about performace, I now have a little more info, hopefully someone can help. I have a table, tblShoppingCart with 3 fields, cartUid (int 11 auto increment) userUid (int 11, indexed) strCartHash (varchar 32) The table is innodb Nomally my server load is below 0.1 and everythings fine, I have a process that runs occasionally that pushes the load up to 1.5, when this happens inserts into the table seem to get blocked, ie taking up to 20 seconds, as soon as the load drops the inserts are fine again. Interestingly, if I convert the table to myisam I don't get this problem. However I really want to keep the table innodb as I use it in transactions latter. My my.cnf file is coppied from the default huge.cnf file, i have duel xeons with 4gb of ram and i'm running mysql 4.1.1 on red hat linux. Any pointers on where i can look further appreciated. Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb performance issues
Tony, You said that you copied the my.cnf file from huge.cnf - not sure what version you are using (I missed your original post), but the my-huge.cnf in mysql 4.0.24 is for MyISAM. You should have used my-innodb-heavy-4G.cnf as the starting point for an InnoDB system. The my-huge.cnf allocates way to much memory to the MyISAM engine. All the innodb stuff is commented out. If you want help, you'll need to post your my.cnf file, the full table definition (try SHOW CREATE TABLE tblSoppingCart; and pasting the results in here). You'll need to also post the queries that are hitting the database while you're having these issues. David tony wrote: Hi, A few days ago i posted a quaestion about performace, I now have a little more info, hopefully someone can help. I have a table, tblShoppingCart with 3 fields, cartUid (int 11 auto increment) userUid (int 11, indexed) strCartHash (varchar 32) The table is innodb Nomally my server load is below 0.1 and everythings fine, I have a process that runs occasionally that pushes the load up to 1.5, when this happens inserts into the table seem to get blocked, ie taking up to 20 seconds, as soon as the load drops the inserts are fine again. Interestingly, if I convert the table to myisam I don't get this problem. However I really want to keep the table innodb as I use it in transactions latter. My my.cnf file is coppied from the default huge.cnf file, i have duel xeons with 4gb of ram and i'm running mysql 4.1.1 on red hat linux. Any pointers on where i can look further appreciated. Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb performance issues
Hi David, On Fri, 2005-07-15 at 10:25 -0700, David Griffiths wrote: Tony, - not sure what version you are using 4.1.11. Server is a duel xeon machine with 4gb or ram running mysql and apache webserver and not much else. You should have used my-innodb-heavy-4G.cnf as the starting point for an InnoDB system. I can use this instead if it's going to help. If you want help, you'll need to post your my.cnf file, [client] port= 3306 socket = /var/lib/mysql/mysql.sock # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M log = /var/log/mysql/mysql.log log-slow-queries= /var/log/mysql/mysql-slow.log set-variable= max_connections=250 server-id = 1 innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20 the full table definition (try SHOW CREATE TABLE tblSoppingCart; and pasting the results in here). tblCart | CREATE TABLE `tblCart` ( `intCartUid` int(11) NOT NULL auto_increment, `intUserUid` int(11) NOT NULL default '0', `tsCartCreated` datetime NOT NULL default '-00-00 00:00:00', `tsLastUpdated` datetime NOT NULL default '-00-00 00:00:00', `strCartHash` varchar(32) NOT NULL default '', PRIMARY KEY (`intCartUid`), KEY `intUserUid` (`intUserUid`), KEY `tsLastUpdated` (`tsLastUpdated`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 You'll need to also post the queries that are hitting the database while you're having these issues. # Query_time: 20 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 SET insert_id=34475,timestamp=1121407309; INSERT INTO dbseThxWebOrders.tblCart (intUserUid,tsCartCreated,strCartHash) VALUES (0,now(),'4e5d105f7cd34268e1a5e160d479ed91'); is an example from my slow query log. All of the offending queries today were this same query. Thanks for you help Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb performance issues
Tony, Your my.cnf file is set up for MyISAM, not InnoDB. MySQL allows you to allocate memory and resources to any and all storage engines. Yours is set up to give lots of resources to MyISAM, and none to InnoDB. Reducing MyISAM key_buffer = 384M - this is way too much - I'd set to to 2-16 meg, assuming that the only MyISAM tables you have are in the mysql database. query_cache_size = 32M - read up on the query cache - it's only useful for oft-repeated queries that hit tables in which the data rarely changes. We turn ours off The big variable in InnoDB (that affects performance the most) is the innodb_buffer_pool_size. Since you are running a xeon, I am guessing it's a 32-bit architecture. There is a limit on the max size of the process The amount of memory MySQL will use is: innodb_buffer_pool_size + key_buffer + max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size) + max_connections*2MB You should make sure that stays under 2 gigabytes. If MySQL uses much more memory, it will crash. There are other tuning choices (including the thread-pool-cache). The best resource is the page on innodb performance tuning, and it can be found here: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html You might also want to consider High Performance MySQL. There is lots of good info in there on setup, tuning, replication, etc. David tony wrote: Hi David, On Fri, 2005-07-15 at 10:25 -0700, David Griffiths wrote: Tony, - not sure what version you are using 4.1.11. Server is a duel xeon machine with 4gb or ram running mysql and apache webserver and not much else. You should have used my-innodb-heavy-4G.cnf as the starting point for an InnoDB system. I can use this instead if it's going to help. If you want help, you'll need to post your my.cnf file, [client] port= 3306 socket = /var/lib/mysql/mysql.sock # The MySQL server [mysqld] port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 1M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache = 8 query_cache_size = 32M log = /var/log/mysql/mysql.log log-slow-queries= /var/log/mysql/mysql-slow.log set-variable= max_connections=250 server-id = 1 innodb_data_home_dir = /var/lib/mysql/ innodb_log_group_home_dir = /var/lib/mysql/ innodb_buffer_pool_size = 384M innodb_additional_mem_pool_size = 20 the full table definition (try SHOW CREATE TABLE tblSoppingCart; and pasting the results in here). tblCart | CREATE TABLE `tblCart` ( `intCartUid` int(11) NOT NULL auto_increment, `intUserUid` int(11) NOT NULL default '0', `tsCartCreated` datetime NOT NULL default '-00-00 00:00:00', `tsLastUpdated` datetime NOT NULL default '-00-00 00:00:00', `strCartHash` varchar(32) NOT NULL default '', PRIMARY KEY (`intCartUid`), KEY `intUserUid` (`intUserUid`), KEY `tsLastUpdated` (`tsLastUpdated`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 You'll need to also post the queries that are hitting the database while you're having these issues. # Query_time: 20 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 SET insert_id=34475,timestamp=1121407309; INSERT INTO dbseThxWebOrders.tblCart (intUserUid,tsCartCreated,strCartHash) VALUES (0,now(),'4e5d105f7cd34268e1a5e160d479ed91'); is an example from my slow query log. All of the offending queries today were this same query. Thanks for you help Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb Performance Measurement
Hello. A lot of statistics you could get from 'SHOW INNODB STATUS'. For example 'FILE I/O', 'INSERT BUFFER AND ADAPTIVE HASH INDEX', 'BUFFER POOL AND MEMORY' could be helpful. See: http://dev.mysql.com/doc/mysql/en/innodb-monitor.html Manoj [EMAIL PROTECTED] wrote: Greetings, I am using MySQL 4.0.24 and all my tables use InnoDB as default engine. I was interested in finding out the performance of my Buffer space. How can i do it?. If I were to use MyISQM tables, I could have looked at the parameters Key_read_request key_reads to find out the hit rate but am not aware of how to do the same for Innodb hence any help would be appreciated. Cheers Manoj -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb Performance Measurement
Greetings, I am using MySQL 4.0.24 and all my tables use InnoDB as default engine. I was interested in finding out the performance of my Buffer space. How can i do it?. If I were to use MyISQM tables, I could have looked at the parameters Key_read_request key_reads to find out the hit rate but am not aware of how to do the same for Innodb hence any help would be appreciated. Cheers Manoj -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
From: David Lloyd journalling file system. It's not always that clear cut. I've just switched a number of big customer databases to InnoDB and noone's noticed any difference - if anything it's going faster. For small tables (50,000 records) MyISAM is usually a lot faster. However, MyISAM gets slower as the table size increases if it is used in a medium to high concurrency environment. If you only use selects (low concurrency) MyISAM will probably be fast, but when it comes to large tables with lots of select, update and insert queries you will most likely see that the speed of InnoDB remains roughly constant as the database grows. You will see a slower performance as : - InnoDB runs out of table space and has to autoextend the data file(s) - the conf file is not suitable for the db size anymore - the hardware is not suitable for the db size anymore (e.g. not enough memory) But this is not really InnoDBs fault ;-) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
The MyIsam storage engine is a non transactional engine and InnoDb is a transactional engine. That is the main difference. So I think the MyIsam engine should be faster. what is transactional mean? - Do you Yahoo!? Yahoo! Small Business - Try our new resources site!
Re: InnoDB Performance
see this link http://dev.mysql.com/doc/mysql/en/ansi-diff-transactions.html Reto Eko Budiharto wrote: The MyIsam storage engine is a non transactional engine and InnoDb is a transactional engine. That is the main difference. So I think the MyIsam engine should be faster. what is transactional mean? - Do you Yahoo!? Yahoo! Small Business - Try our new resources site! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
Eko and all, The MyIsam storage engine is a non transactional engine and InnoDb is a transactional engine. That is the main difference. So I think the MyIsam engine should be faster. However, some file systems that have journals are faster than non journalling file system. It's not always that clear cut. I've just switched a number of big customer databases to InnoDB and noone's noticed any difference - if anything it's going faster. DSL ( mysql, sql ) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
Marcin, you must set innodb_log_file_size as recommended in the manual: http://dev.mysql.com/doc/mysql/en/innodb-configuration.html [mysqld] # You can write your other MySQL server options here # ... # Data files must be able to hold your data and indexes. # Make sure that you have enough free disk space. innodb_data_file_path = ibdata1:10M:autoextend # # Set buffer pool size to 50-80% of your computer's memory set-variable = innodb_buffer_pool_size=70M set-variable = innodb_additional_mem_pool_size=10M # # Set the log file size to about 25% of the buffer pool size set-variable = innodb_log_file_size=20M set-variable = innodb_log_buffer_size=8M # innodb_flush_log_at_trx_commit=1 Since the workload is disk-bound, the following are relevant: http://dev.mysql.com/doc/mysql/en/news-5-0-3.html InnoDB: Introduced a compact record format that does not store the number of columns or the lengths of fixed-size columns. The old format can be requested by specifying ROW_FORMAT=REDUNDANT. The new format (ROW_FORMAT=COMPACT) is the default. The above saves about 20 % of space. http://www.innodb.com/todo.php Implement transparent zip-like compression of InnoDB index pages. Compressed tables will take about 60 % less disk space than normal tables. The downside is some more CPU usage in queries and inserts. Appears in 5.1. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL Network from http://www.mysql.com/network/ - Original Message - From: Marcin Lewandowski [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, April 13, 2005 12:28 AM Subject: Re: InnoDB Performance I've changed settings to: innodb_data_file_path = ibdata1:128M:autoextend innodb_buffer_pool_size=150M innodb_additional_mem_pool_size = 50M and system load is only 2 to 3. kernel napisa(a): What does the cpu % show when the machine has the high load avg ? Now, there are about 50% of normal load and system load is only circa 1.0. On myisam there was about 0.5. And here comes few lines from top: Cpu(s): 19.9% us, 2.6% sy, 0.0% ni, 74.8% id, 2.3% wa, 0.0% hi, 0.3% si Cpu(s): 14.3% us, 1.0% sy, 0.0% ni, 82.4% id, 2.0% wa, 0.0% hi, 0.3% si Cpu(s): 26.9% us, 3.3% sy, 0.0% ni, 69.4% id, 0.0% wa, 0.0% hi, 0.3% si Usually high load avgs point to disk I/O isssues. What is the size of your ibdata1 file ? If you have more ram, you can increase I've got 512 mb of RAM, and it's full (and 200mb of swap is currently used). server root # ls -l /data/mysql/ib* -rw-rw 1 mysql mysql5242880 Apr 12 23:14 /data/mysql/ib_logfile0 -rw-rw 1 mysql mysql5242880 Apr 12 18:48 /data/mysql/ib_logfile1 -rw-rw 1 mysql mysql 1000341504 Apr 12 23:14 /data/mysql/ibdata1 innodb_buffer_pool_size or do some tweaks to the OS so it caches the disk a little more. -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
Jocelyn Fournier napisa(a): Hi, What about using another forum ? phpbb2 is well known to be far for what could be called optimized :) I hate phpbb, but currently we can't change it :( -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
Hello. Send the piece of 'SHOW PROCESSLIST', 'SHOW STATUS' output and corresponding configuration file (after applying all previous advices). It could provide more information to reflection. Marcin Lewandowski [EMAIL PROTECTED] wrote: Hi, I've got webserver. There, I've got phpbb2 with circa 6000 users (average 70-100 users online). There was problems with locking or something else, when phpbb was using myisam tables. Yesterday, we have converted tables to innodb, because it should be more effective. Since then we have high system load. server root # uptime 16:11:17 up 1 day, 23:56, 4 users, load average: 1.37, 1.35, 6.63 server root # free total used free sharedbuffers cached Mem:508284 506732 1552 0 2800 322848 -/+ buffers/cache: 181084 327200 Swap: 1000400 128308 872092 MyTop shows that there are about 40-50 queries per second. MySQL is 4.0.22-log (gentoo linux) Here comes my.cnf: [client] port= 4417 socket = /var/run/mysqld/mysqld.sock [safe_mysqld] err-log = /var/log/mysql/mysql.err [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock log-error = /var/log/mysql/mysqld.err innodb_data_file_path = ibdata1:64M:autoextend innodb_buffer_pool_size=128M innodb_flush_log_at_trx_commit=1 basedir = /usr datadir = /data/mysql tmpdir = /tmp language= /usr/share/mysql/polish log-slow-queries = /data/logs/mysql/slow.log log-update = /data/logs/mysql/update.log skip-locking skip-bdb low-priority-updates max_write_lock_count = 7 character-set = latin2 set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K long_query_time = 4 wait-timeout= 60 max-connections = 150 port= 4417 [mysqldump] quick set-variable= max_allowed_packet=1M [mysql] [isamchk] set-variable= key_buffer=16M I've tried many values in innodb_data_file_path = ibdata1:64M:autoextend Thanks in advance -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Performance
Hi, I've got webserver. There, I've got phpbb2 with circa 6000 users (average 70-100 users online). There was problems with locking or something else, when phpbb was using myisam tables. Yesterday, we have converted tables to innodb, because it should be more effective. Since then we have high system load. server root # uptime 16:11:17 up 1 day, 23:56, 4 users, load average: 1.37, 1.35, 6.63 server root # free total used free sharedbuffers cached Mem:508284 506732 1552 0 2800 322848 -/+ buffers/cache: 181084 327200 Swap: 1000400 128308 872092 MyTop shows that there are about 40-50 queries per second. MySQL is 4.0.22-log (gentoo linux) Here comes my.cnf: [client] port= 4417 socket = /var/run/mysqld/mysqld.sock [safe_mysqld] err-log = /var/log/mysql/mysql.err [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock log-error = /var/log/mysql/mysqld.err innodb_data_file_path = ibdata1:64M:autoextend innodb_buffer_pool_size=128M innodb_flush_log_at_trx_commit=1 basedir = /usr datadir = /data/mysql tmpdir = /tmp language= /usr/share/mysql/polish log-slow-queries = /data/logs/mysql/slow.log log-update = /data/logs/mysql/update.log skip-locking skip-bdb low-priority-updates max_write_lock_count = 7 character-set = latin2 set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K long_query_time = 4 wait-timeout= 60 max-connections = 150 port= 4417 [mysqldump] quick set-variable= max_allowed_packet=1M [mysql] [isamchk] set-variable= key_buffer=16M I've tried many values in innodb_data_file_path = ibdata1:64M:autoextend Thanks in advance -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
The MyIsam storage engine is a non transactional engine and InnoDb is a transactional engine. That is the main difference. So I think the MyIsam engine should be faster. Try to adjust the innodb_thread_concurrency parameter when you have a lot of users. Reto Marcin Lewandowski wrote: Hi, I've got webserver. There, I've got phpbb2 with circa 6000 users (average 70-100 users online). There was problems with locking or something else, when phpbb was using myisam tables. Yesterday, we have converted tables to innodb, because it should be more effective. Since then we have high system load. server root # uptime 16:11:17 up 1 day, 23:56, 4 users, load average: 1.37, 1.35, 6.63 server root # free total used free sharedbuffers cached Mem:508284 506732 1552 0 2800 322848 -/+ buffers/cache: 181084 327200 Swap: 1000400 128308 872092 MyTop shows that there are about 40-50 queries per second. MySQL is 4.0.22-log (gentoo linux) Here comes my.cnf: [client] port= 4417 socket = /var/run/mysqld/mysqld.sock [safe_mysqld] err-log = /var/log/mysql/mysql.err [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock log-error = /var/log/mysql/mysqld.err innodb_data_file_path = ibdata1:64M:autoextend innodb_buffer_pool_size=128M innodb_flush_log_at_trx_commit=1 basedir = /usr datadir = /data/mysql tmpdir = /tmp language= /usr/share/mysql/polish log-slow-queries = /data/logs/mysql/slow.log log-update = /data/logs/mysql/update.log skip-locking skip-bdb low-priority-updates max_write_lock_count = 7 character-set = latin2 set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K long_query_time = 4 wait-timeout= 60 max-connections = 150 port= 4417 [mysqldump] quick set-variable= max_allowed_packet=1M [mysql] [isamchk] set-variable= key_buffer=16M I've tried many values in innodb_data_file_path = ibdata1:64M:autoextend Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
Marcin Lewandowski wrote: Hi, I've got webserver. There, I've got phpbb2 with circa 6000 users (average 70-100 users online). There was problems with locking or something else, when phpbb was using myisam tables. Yesterday, we have converted tables to innodb, because it should be more effective. Since then we have high system load. server root # uptime 16:11:17 up 1 day, 23:56, 4 users, load average: 1.37, 1.35, 6.63 server root # free total used free sharedbuffers cached Mem:508284 506732 1552 0 2800 322848 -/+ buffers/cache: 181084 327200 Swap: 1000400 128308 872092 MyTop shows that there are about 40-50 queries per second. MySQL is 4.0.22-log (gentoo linux) Here comes my.cnf: [client] port= 4417 socket = /var/run/mysqld/mysqld.sock [safe_mysqld] err-log = /var/log/mysql/mysql.err [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock log-error = /var/log/mysql/mysqld.err innodb_data_file_path = ibdata1:64M:autoextend innodb_buffer_pool_size=128M innodb_flush_log_at_trx_commit=1 basedir = /usr datadir = /data/mysql tmpdir = /tmp language= /usr/share/mysql/polish log-slow-queries = /data/logs/mysql/slow.log log-update = /data/logs/mysql/update.log skip-locking skip-bdb low-priority-updates max_write_lock_count = 7 character-set = latin2 set-variable= key_buffer=16M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K long_query_time = 4 wait-timeout= 60 max-connections = 150 port= 4417 [mysqldump] quick set-variable= max_allowed_packet=1M [mysql] [isamchk] set-variable= key_buffer=16M I've tried many values in innodb_data_file_path = ibdata1:64M:autoextend Thanks in advance Marcin, What does the cpu % show when the machine has the high load avg ? Usually high load avgs point to disk I/O isssues. What is the size of your ibdata1 file ? If you have more ram, you can increase innodb_buffer_pool_size or do some tweaks to the OS so it caches the disk a little more. walt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
I've changed settings to: innodb_data_file_path = ibdata1:128M:autoextend innodb_buffer_pool_size=150M innodb_additional_mem_pool_size = 50M and system load is only 2 to 3. kernel napisa(a): What does the cpu % show when the machine has the high load avg ? Now, there are about 50% of normal load and system load is only circa 1.0. On myisam there was about 0.5. And here comes few lines from top: Cpu(s): 19.9% us, 2.6% sy, 0.0% ni, 74.8% id, 2.3% wa, 0.0% hi, 0.3% si Cpu(s): 14.3% us, 1.0% sy, 0.0% ni, 82.4% id, 2.0% wa, 0.0% hi, 0.3% si Cpu(s): 26.9% us, 3.3% sy, 0.0% ni, 69.4% id, 0.0% wa, 0.0% hi, 0.3% si Usually high load avgs point to disk I/O isssues. What is the size of your ibdata1 file ? If you have more ram, you can increase I've got 512 mb of RAM, and it's full (and 200mb of swap is currently used). server root # ls -l /data/mysql/ib* -rw-rw 1 mysql mysql5242880 Apr 12 23:14 /data/mysql/ib_logfile0 -rw-rw 1 mysql mysql5242880 Apr 12 18:48 /data/mysql/ib_logfile1 -rw-rw 1 mysql mysql 1000341504 Apr 12 23:14 /data/mysql/ibdata1 innodb_buffer_pool_size or do some tweaks to the OS so it caches the disk a little more. -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
Gary Richardson napisa(a): Hey, How much load is system vs user? I found that when my company converted some large tables on our old server, the concurrent disk IO increased. Your database server is doing more in parallel and accessing more from your disk at one time. That would be my guess. One option is to get a faster disk interconnect (SATA or SCSI, especially if you are running on IDE). I've got IDE hdd. Is there simple way to check, if it's overloaded? Would RAID1 help? (I don't know if in raid1 there are parralel reads or maybe one disk is only a mirror) The other option is to increase innodb_buffer_pool_size, which can be tough if you are running your webserver on the same box. This is where InnoDB stores your database in memory. The more it can store, the less disk IO is required. But if it would be too high, server would use swap, what makes more io calls. :( Without knowing how much data there is, I'd probably bump the systems ram up to 2 gigs (from 512) and set innodb_buffer_pool_size to 1GB. How big is the database? Your data file is set to autoextend, how big is the ibdata1 file? 700mb of innodb tables (where phpbb_posts_text contains 17 records = 116mb and phpbb_search_wordmatch contains about 500 records = 500mb, rest is smaller) and 200mb of myisam tables server root # ls -l /data/mysql/ib* -rw-rw 1 mysql mysql5242880 Apr 12 23:14 /data/mysql/ib_logfile0 -rw-rw 1 mysql mysql5242880 Apr 12 18:48 /data/mysql/ib_logfile1 -rw-rw 1 mysql mysql 1000341504 Apr 12 23:14 /data/mysql/ibdata1 As I wrote in reply to kernel's message, I've changed settings to: innodb_data_file_path = ibdata1:128M:autoextend innodb_buffer_pool_size=150M innodb_additional_mem_pool_size = 50M and system load is only 2 to 3. But in my opinion, it's still to high, and website is growing so in month or two there would be serious problem if I don't fix that issue. -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
I've got IDE hdd. Is there simple way to check, if it's overloaded? Would RAID1 help? (I don't know if in raid1 there are parralel reads or maybe one disk is only a mirror) If it's IDE, probably not. Moving the database to a different subsystem would give more IO. You can use iostat to check the io on the disk, but you kinda need iostats to compare against. But if it would be too high, server would use swap, what makes more io calls. :( Yup, that's why you'd need more ram. 700mb of innodb tables (where phpbb_posts_text contains 17 records = 116mb and phpbb_search_wordmatch contains about 500 records = 500mb, rest is smaller) and 200mb of myisam tables I think you also have to include indexes in your memory usage.. I'd probably up the RAM, especially if your webserver is running on the same box. As I wrote in reply to kernel's message, I've changed settings to: innodb_data_file_path = ibdata1:128M:autoextend innodb_buffer_pool_size=150M innodb_additional_mem_pool_size = 50M Increasing your file size to 128M doesn't really help. You should probably set it to something like: innodb_data_file_path = ibdata1:1G;ibdata2:1G;ibdata3:1G:autoextend This will create 3x 1 gig table files. You're data is around 1G, so this should give you some headroom. InnoDB uses more space for transactions and such than just the byte size of your rows * number of rows. The autoextend allows the last file to grow if/when you run out of space. I prefer to add files when I start running out of room instead of letting it autogrow. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance
Marcin Lewandowski wrote: Hi, I've got webserver. There, I've got phpbb2 with circa 6000 users (average 70-100 users online). There was problems with locking or something else, when phpbb was using myisam tables. Yesterday, we have converted tables to innodb, because it should be more effective. It depends on what you're doing. If you don't need transactions or fine-grained locking, MyISAM may be a better option. In cases where you have clients that keep locks open for an extended period of time ( MS Access ), InnoDB may become the only option. With a webserver as the only client, I don't think this will apply to you. If you don't need transactions, consider moving back if you can't get the performance you need. Since then we have high system load. snipped MySQL is 4.0.22-log (gentoo linux) Here comes my.cnf: [client] port= 4417 socket = /var/run/mysqld/mysqld.sock [safe_mysqld] err-log = /var/log/mysql/mysql.err [mysqld] user= mysql pid-file= /var/run/mysqld/mysqld.pid socket = /var/run/mysqld/mysqld.sock log-error = /var/log/mysql/mysqld.err innodb_data_file_path = ibdata1:64M:autoextend innodb_buffer_pool_size=128M innodb_flush_log_at_trx_commit=1 I don't think you need this, and this will slow things down for you too. basedir = /usr datadir = /data/mysql tmpdir = /tmp language= /usr/share/mysql/polish log-slow-queries = /data/logs/mysql/slow.log log-update = /data/logs/mysql/update.log Try turning that log-update option off. If you want to use logging for backup purposes, use the binary logging format. Also, in this section [mysqld] add: query_cache_type= 1 query_cache_size= 16M and restart mysql. This should give you a nice performance hit - how much will depend on how efficient phpbb2 is - with Access ( which issues the same query over over again ) I get a very big performance increase. You may need to tune the query_cache_size bit. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge vs multiple innodb performance
Marcin Lewandowski [EMAIL PROTECTED] wrote on 02/08/2005 05:29:39 PM: Chuck Herrick napisa(a): 200 - 400 tables is too many. Is it too many for merge, innodb or both? Try having one CUSTOMERS table. You know who is logged in, so you can use that information in a WHERE clause. Yes, but If somebody would find a password (maybe using brute-force attack) to one account, could delete data of other users... -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] No, they couldn't if you don't give them direct rights to edit that table. Make them use your PHP interface to set and reset their passwords. That way you have a secret password in your app that allows only you to access that table and they will only be able to change the records you let them change. If you don't allow them to edit EVERYONE's information, they won't be able to. I agree with the other respondent. One table per user is excessive, especially when we are talking about login information (one row per table?). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Merge vs multiple innodb performance
Hi, I'm writing an windows app, which would connect to mysql server and modify user's data. There would be a few hundred of users. Every of them should have access only to few tables. It's not a problem with mysql's authentication mechanism. Kind of compilation of the data from users' tables should be accessed on web (via php scripts). For example: every user have table LOGIN_customers with the same structure. On web, I need to make SELECT (something) FROM LOGIN1_customers, LOGIN2_customers, LOGIN3_customers WHERE (something). I thought, that MERGE tables could be good solution but they don't have transactions and they have limitations. What would be faster? MERGE tables or queries with 200-400 InnoDB tables in FROM? -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge vs multiple innodb performance
Chuck Herrick napisa(a): 200 - 400 tables is too many. Is it too many for merge, innodb or both? Try having one CUSTOMERS table. You know who is logged in, so you can use that information in a WHERE clause. Yes, but If somebody would find a password (maybe using brute-force attack) to one account, could delete data of other users... -- Marcin Lewandowski [ mailto:[EMAIL PROTECTED] gg:188068 jid:[EMAIL PROTECTED] ] [ http://www.nosoftwarepatents.com/pl/m/intro/index.html ] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Poor InnoDB Performance
DuffMan, I assume you are running 4.0.14? What is your Linux version? Please post the whole output of SHOW INNODB STATUS\G Are you using the MySQL query cache? Remember that any insert, update, or delete invalidates it. To test the impact of fsync(), try running with innodb_flush_log_at_trx_commit=0. Your log files are too small compared to the buffer pool size. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: DuffMan [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, July 24, 2003 5:07 AM Subject: Poor InnoDB Performance I converted 3 of 30 MyISAM tables to InnoDB. Since then I have been constantly reaching my limit of 100 connections because Selects on the InnoDB tables are slow. If I cut off traffic and execute a SELECT, it takes less than 0.01 seconds. If a execute the exact same query again with traffic, it takes 18+ seconds. More than 1 query appears to do this (maybe all). I have a Dual 2ghz Xeon dedicated server with (2) 73GB 10k RPM SCSI hard drives. It has 1GB of RAM. (Only 1 hard drive is used and and CPU usage is about 50-60%) The server is not dedicated to just databases, it also has apache (with PHP) running on it. My current InnoDB settings are: | innodb_additional_mem_pool_size | 1048576 | innodb_buffer_pool_size | 262144000 | innodb_data_file_path | ibdata1:10M:autoextend | innodb_data_home_dir | | innodb_file_io_threads | 4 | innodb_force_recovery | 0 | innodb_thread_concurrency | 4 | innodb_flush_log_at_trx_commit | 1 | innodb_fast_shutdown | ON | innodb_flush_method | | innodb_lock_wait_timeout | 50 | innodb_log_arch_dir | ./ | innodb_log_archive | OFF | innodb_log_buffer_size | 20971520 | innodb_log_file_size | 5242880 | innodb_log_files_in_group | 2 | innodb_log_group_home_dir | ./ | innodb_mirrored_log_groups | 1 | innodb_max_dirty_pages_pct | 90 I have also tried a buffer_pool_size of 8MB, 70MB, 100MB, 150MB, and 350MB. Some traffic stats are: (shared between InnoDB and MyISAM tables) 20 connections per second. 200 queries per second. InnoDB Table #1 is 84MB [230,000 rows] InnoDB Table #2 is 166MB [280,000 rows] InnoDB Table #3 is 151MB [570,000 rows] MySQL says: 'InnoDB free: 6144 kB' The InnoDB Monitor gives this: FILE I/O I/O thread 0 state: waiting for i/o request I/O thread 1 state: waiting for i/o request I/O thread 2 state: waiting for i/o request I/O thread 3 state: waiting for i/o request Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 1; buffer pool: 0 26279 OS file reads, 24278 OS file writes, 10809 OS fsyncs 0.80 reads/s, 16384 avg bytes/read, 3.40 writes/s, 3.40 fsyncs/s - INSERT BUFFER AND ADAPTIVE HASH INDEX - Ibuf for space 0: size 1, free list len 380, seg size 382, 125 inserts, 125 merged recs, 57 merges Hash table size 1155127, used cells 529328, node heap has 695 buffer(s) 922.82 hash searches/s, 338.13 non-hash searches/s --- LOG --- Log sequence number 0 479156959 Log flushed up to 0 479156959 Last checkpoint at 0 479148210 1 pending log writes, 0 pending chkp writes 10188 log i/o's done, 3.40 log i/o's/second -- BUFFER POOL AND MEMORY -- Total memory allocated 316173844; in additional pool allocated 1038720 Buffer pool size 16000 Free buffers 0 Database pages 15305 Modified db pages 54 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 31305, created 9, written 14289 0.80 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -- ROW OPERATIONS -- 4 queries inside InnoDB, 48 queries in queue Main thread process no 21521, state: flushing log Number of rows inserted 209, updated 15138, deleted 0, read 1547848 0.20 inserts/s, 4.20 updates/s, 0.00 deletes/s, 958.41 reads/s In the Transaction Section of the InnoDB monitor I am seeing things like: 'Trx read view will not see trx with id = 0 2505453, sees 0 2505360' a lot. I am assuming that I have a configuration problem. I know InnoDB tables can be about 30% slower and I see that when I execute a query on an idle server. That same server with traffic suddenly makes queries 10 to 2000 times slower. Has anyone seen this issue before? Can anyone give any suggestions for finding the problem or solution? Thanks. __ Do you Yahoo!? Yahoo! SiteBuilder - Free, easy-to-use web site design software http://sitebuilder.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list
Re: InnoDB Performance issues
Nicholas, - Original Message - From: Nicholas Elliott [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Friday, July 11, 2003 6:04 PM Subject: InnoDB Performance issues --=_NextPart_000_003B_01C3479C.77A1AB60 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Hey all, ... create table basic_daily_grid( date DATE NOT NULL PRIMARY KEY, variable1 MEDIUMBLOB, variable2 MEDIUMBLOB ... variable9 MEDIUMBLOB ); ... mysql select date from basic_daily_grid_innodb; ... 317 rows in set (0.00 sec) mysql explain select date, count(*) from basic_daily_grid_innodb group = by date; +-+---+---+-+-+--= +--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+-+-+--= +--+-+ | basic_daily_grid_innodb | index | NULL | PRIMARY | 3 | NULL |301 | = Using index | +-+---+---+-+-+--= +--+-+ mysql explain select date, count(*) from basic_daily_grid_innodb group = by date; ... 317 rows in set (2 min 54.95 sec) I assume this is due to versioning or some other transactional feature. = Or, is this a bug, or am I doing something wrong? I don't quite see why = grouping items that are all unique should be that much slower than not = grouping. I need InnoDB for the unlimited table size, but I don't = (Really) need transactions, commit/rollback, or checkpoints. it is a performance bug. I an ORDER BY MySQL may use more columns than are mentioned in the SELECT query, and InnoDB retrieves the whole row. If there is a big BLOB in the row, it can take quite a while. I may fix this to 4.1.1, but first I have to ask the MySQL developer if handler::extra(KEYREAD) is aware that in a clustered index all columns are in the index record. Workaround: put BLOBs to a separate table and use a surrogate key (= auto-inc column) to join it to a smaller table where the other columns are. Any suggestions on solving this last hurdle? Its entirely likely I'll = need to group by year and average the results, or something similar - = and at 3 minutes a pop, thats a little high for my liking. Perhaps I'm = expecting too much? Thanks, Nick Elliott Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Transactions, foreign keys, and a hot backup tool for MySQL Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Performance issues
Hey all, I've been experimenting with the best way to store a large (~100GB) of data for retrieval. Essentially, I'm storing 9 variables for approximately 1,000,000 locations a day for the last ten years. This can work out at around 4MB a variable a day - but not all variables are always present (could be null). (If you don't care about the details, I'll summarize at the end of this email). Inserting and retrieving from a MyISAM table seemed to be approaching impossible. (Not totally surprising.) I originally had a table along the lines of: create table basic_daily_report( date DATE NOT NULL, location_id MEDIUMINT UNSIGNED NOT NULL, variable1 float, variable2 float variable9 float, primary key (date, location_id) ); (Just a summary of the actual table) With this I had a maxiumum table size of around 100GB - just barely enough to do it. I expected I would end up segmenting by year, or something similar, as ugly as that is. I tested InnoDB as an alternative to this, but we'll get to that in a second. Basically, inserting a day's worth of data would take ages, and pretty much require an analyze table for a couple hours every morning. Selecting was getting to be pretty slow, as well. Eventually, I hit on the idea of including one row per day: create table basic_daily_grid( date DATE NOT NULL PRIMARY KEY, variable1 MEDIUMBLOB, variable2 MEDIUMBLOB ... variable9 MEDIUMBLOB ); And wrote a UDF such that you pass it the variable and a location, and it'll return the exact value. This works well because every day has a constant number of locations in a grid format, so it's simply an array lookup. So, select grid_point(location_id, variable1) from basic_daily_grid where date=20030101 would return the right value for locationid. It turns out this is almost (95%) as fast as the first version in selecting, but it has the added bonus of inserts now only take ~5 seconds per day! Alas, after inserting 260 days (less than a year) I hit the MyISAM table size limit of 4.2GB - because a BLOB is a variable length field. - I mention all the above in case someone has an alternative solution I'm looking over. Possible solutions I've found are a) use InnoDB instead, b) work with the source to create a new field type BLOBARRAY of a constant width instead of dynamic, c) work with the source to somehow overcome the 4.2GB limit on a dynamic table. c) Seems unlikely - if the actual developers can't do it, I probably can't b) Seems possible, I assume no one saw a need for a constant width column of 4MB, so hopefully its not too difficult a) Was my first try. Inserting takes about twice as long as myisam... sure, I can deal with that. Selecting a specific date is in the same ballpark as well, so little problem there. What I'm having severe performance issues on are querys that group, or do a count(*). For example: mysql explain select date from basic_daily_grid_innodb; +-+---+---+-+-+--+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+-+-+--+--+-+ | basic_daily_grid_innodb | index | NULL | PRIMARY | 3 | NULL |301 | Using index | +-+---+---+-+-+--+--+-+ mysql select date from basic_daily_grid_innodb; ... 317 rows in set (0.00 sec) mysql explain select date, count(*) from basic_daily_grid_innodb group by date; +-+---+---+-+-+--+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+-+-+--+--+-+ | basic_daily_grid_innodb | index | NULL | PRIMARY | 3 | NULL |301 | Using index | +-+---+---+-+-+--+--+-+ mysql explain select date, count(*) from basic_daily_grid_innodb group by date; ... 317 rows in set (2 min 54.95 sec) I assume this is due to versioning or some other transactional feature. Or, is this a bug, or am I doing something wrong? I don't quite see why grouping items that are all unique should be that much slower than not grouping. I need InnoDB for the unlimited table size, but I don't (Really) need transactions, commit/rollback, or checkpoints. Any suggestions on solving this last hurdle? Its entirely likely I'll need to group by year and average the results, or something similar - and at 3 minutes a pop, thats a little high for my liking. Perhaps I'm expecting too much? Thanks, Nick Elliott
Re: InnoDB Performance issues
In the last episode (Jul 11), Nicholas Elliott said: I've been experimenting with the best way to store a large (~100GB) of data for retrieval. Essentially, I'm storing 9 variables for approximately 1,000,000 locations a day for the last ten years. This can work out at around 4MB a variable a day - but not all variables are always present (could be null). [...] Alas, after inserting 260 days (less than a year) I hit the MyISAM table size limit of 4.2GB - because a BLOB is a variable length field. MyISAM doesn't have a hard 4gb table size... It may default to a 4gb limit if you forgot to give a hint as to the final table size when you created it, though. Try running ALTER TABLE mytable AVG_ROW_LENGTH=3600 MAX_ROWS=3600 ( 36MB average row length since you have 9 4mb blobs, and 10 years worth of records. ) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance issues
That did it -- show table status lists the upper limit as approx 1TB now =]. I'm still curious about the InnoDB issues, but now at least I can avoid it and work with the original plan! Thanks, Nick Elliott - Original Message - From: Dan Nelson [EMAIL PROTECTED] To: Nicholas Elliott [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, July 11, 2003 11:29 AM Subject: Re: InnoDB Performance issues In the last episode (Jul 11), Nicholas Elliott said: I've been experimenting with the best way to store a large (~100GB) of data for retrieval. Essentially, I'm storing 9 variables for approximately 1,000,000 locations a day for the last ten years. This can work out at around 4MB a variable a day - but not all variables are always present (could be null). [...] Alas, after inserting 260 days (less than a year) I hit the MyISAM table size limit of 4.2GB - because a BLOB is a variable length field. MyISAM doesn't have a hard 4gb table size... It may default to a 4gb limit if you forgot to give a hint as to the final table size when you created it, though. Try running ALTER TABLE mytable AVG_ROW_LENGTH=3600 MAX_ROWS=3600 ( 36MB average row length since you have 9 4mb blobs, and 10 years worth of records. ) -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB Performance issues
Hi Nicholas, How about storing the BLOBS outside of the DB and refering to them ? Best regards Nils Valentin Tokyo/Japan 2003 7 12 00:06Nicholas Elliott : Hey all, I've been experimenting with the best way to store a large (~100GB) of data for retrieval. Essentially, I'm storing 9 variables for approximately 1,000,000 locations a day for the last ten years. This can work out at around 4MB a variable a day - but not all variables are always present (could be null). (If you don't care about the details, I'll summarize at the end of this email). Inserting and retrieving from a MyISAM table seemed to be approaching impossible. (Not totally surprising.) I originally had a table along the lines of: create table basic_daily_report( date DATE NOT NULL, location_id MEDIUMINT UNSIGNED NOT NULL, variable1 float, variable2 float variable9 float, primary key (date, location_id) ); (Just a summary of the actual table) With this I had a maxiumum table size of around 100GB - just barely enough to do it. I expected I would end up segmenting by year, or something similar, as ugly as that is. I tested InnoDB as an alternative to this, but we'll get to that in a second. Basically, inserting a day's worth of data would take ages, and pretty much require an analyze table for a couple hours every morning. Selecting was getting to be pretty slow, as well. Eventually, I hit on the idea of including one row per day: create table basic_daily_grid( date DATE NOT NULL PRIMARY KEY, variable1 MEDIUMBLOB, variable2 MEDIUMBLOB ... variable9 MEDIUMBLOB ); And wrote a UDF such that you pass it the variable and a location, and it'll return the exact value. This works well because every day has a constant number of locations in a grid format, so it's simply an array lookup. So, select grid_point(location_id, variable1) from basic_daily_grid where date=20030101 would return the right value for locationid. It turns out this is almost (95%) as fast as the first version in selecting, but it has the added bonus of inserts now only take ~5 seconds per day! Alas, after inserting 260 days (less than a year) I hit the MyISAM table size limit of 4.2GB - because a BLOB is a variable length field. - I mention all the above in case someone has an alternative solution I'm looking over. Possible solutions I've found are a) use InnoDB instead, b) work with the source to create a new field type BLOBARRAY of a constant width instead of dynamic, c) work with the source to somehow overcome the 4.2GB limit on a dynamic table. c) Seems unlikely - if the actual developers can't do it, I probably can't b) Seems possible, I assume no one saw a need for a constant width column of 4MB, so hopefully its not too difficult a) Was my first try. Inserting takes about twice as long as myisam... sure, I can deal with that. Selecting a specific date is in the same ballpark as well, so little problem there. What I'm having severe performance issues on are querys that group, or do a count(*). For example: mysql explain select date from basic_daily_grid_innodb; +-+---+---+-+-+ --+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+-+-+ --+--+-+ | basic_daily_grid_innodb | index | NULL | PRIMARY | 3 | NULL |301 | Using | index | +-+---+---+-+-+ --+--+-+ mysql select date from basic_daily_grid_innodb; ... 317 rows in set (0.00 sec) mysql explain select date, count(*) from basic_daily_grid_innodb group by date; +-+---+---+-+-+ --+--+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---+---+-+-+ --+--+-+ | basic_daily_grid_innodb | index | NULL | PRIMARY | 3 | NULL |301 | Using | index | +-+---+---+-+-+ --+--+-+ mysql explain select date, count(*) from basic_daily_grid_innodb group by date; ... 317 rows in set (2 min 54.95 sec) I assume this is due to versioning or some other transactional feature. Or, is this a bug, or am I doing something wrong? I don't quite see why grouping items that are all unique should be that much slower than not grouping. I need InnoDB for the unlimited table size, but I don't (Really) need transactions, commit/rollback, or checkpoints. Any suggestions on solving this last hurdle? Its entirely likely I'll need to group by year and average the results, or something similar - and at 3 minutes a pop, thats a little high for my liking. Perhaps I'm
Re: Mysql Innodb performance slow
In the last episode (Oct 25), Jeff Mathis said: I'll agree to this somewhat: (1) always bind your variables. whatever code you are using to do your inserts, the fewer prepared statements you can make the better. for example: insert into TableName (col1, col2, col2, col4) values (?,?,?,?) then, once you have a prepared statment, do a loop and insert. Note that mysql does not support bind variables. If you think you're using them, whatever API you are using is filling them in before sending the statement to mysql. Bind variables do solve quoting problems, though, so if you use them, know why you're using them :) -- Dan Nelson [EMAIL PROTECTED] - 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
Re: Re: Mysql Innodb performance slow
sql,query At 15:50 -0600 10/25/02, Jeff Mathis wrote: Paul DuBois wrote: (2) drop all indexes on your table(s). rebuild them after loading (this alone can give orders of magnitude improvement) For ISAM or MyISAM, that works. It has no effect for InnoDB. Do you actually observe a reproducable difference with this strategy for InnoDB? Yes I do. I have tables with defined primary key/foreign key constraints, which I cannot drop during loading. But dropping the unique indexes I have defined on multiple columns does speed up loading times substantially. You say I am not supposed to see this behavior? Can you explain why? Sure. I get this from: http://www.innodb.com/ibman.html#Altering_InnoDB_tables Which says: InnoDB does not have a special optimization for separate index creation. Therefore it does not pay to export and import the table and create indexes afterwards. The fastest way to alter a table to InnoDB is to do the inserts directly to an InnoDB table, that is, use ALTER TABLE ... TYPE=INNODB, or create an empty InnoDB table with identical definitions and insert the rows with INSERT INTO ... SELECT * FROM I've been experimenting with putting datafiles on different disks, and am now wondering if what I'm seeing is related to disk i/o as opposed to insert optimization. jeff - 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
Re: Mysql Innodb performance slow
Mark, Note that mysql does not support bind variables. If you think you'reusing them, whatever API you are using is filling them in beforesending the statement to mysql. Bind variables do solve quotingproblems, though, so if you use them, know why you're using them :) Rumour has it, though, that DBD::MySQL (or whichever module it is) performs better with the bound variable versions than the non bound variable versions. DSL -- The Linux C Programming Lists: * http://lists.linux.org.au/listinfo/linuxcprogramming/ The Linux C++ Programming Lists: * http://lists.linux.org.au/listinfo/tuxcpprogramming/ - 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
Re: Mysql Innodb performance slow
At 15:46 -0600 10/25/02, Jeff Mathis wrote: forgive me. i was initially using the perl DBI methods to load. I am not intimately familiar with the inner workings of DBI, but obviously it must be doing something if you say mysql does not support binding variables (i am using 4.0.4). This is significantly faster than creating a new prepared statement for every insert. Right, it's the DBD::mysql driver that's emulating true parameter binding. But it's still faster, as you observe, because you avoid the prepare() call for all except the first insert statement. Parameter binding by MySQL itself should probably appear sometime in the 4.1.x series. - 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
Re: Mysql Innodb performance slow
David Lloyd wrote: Mark, Note that mysql does not support bind variables. If you think you'reusing them, whatever API you are using is filling them in beforesending the statement to mysql. Bind variables do solve quotingproblems, though, so if you use them, know why you're using them :) Rumour has it, though, that DBD::MySQL (or whichever module it is) performs better with the bound variable versions than the non bound variable versions. DSL -- The Linux C Programming Lists: * http://lists.linux.org.au/listinfo/linuxcprogramming/ The Linux C++ Programming Lists: * http://lists.linux.org.au/listinfo/tuxcpprogramming/ I don't dis-agree with this, it was Dan that brought up the point you are responding to :) -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - 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
Mysql Innodb performance slow
Hi There, We have currently an Interbase Database with millions and millions of rows which I would like to migrate to MySQL if possible to increase the speed. Transaction support is necessary, so I am using innoDB. When inserting 160K rows in the database (in an innoDB table) it takes about 700! seconds while the amount of same rows when inserted in a myisam table take about 100 seconds. Now probably this can be fine tuned (I hope), and would like to ask for some suggestions. Is anybody using innodb with this amount of rows? I'm curious of what the performance is. Is there something I should keep in mind when migrating? Kind regards, Jeroen Geusebroek - 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
Re: Mysql Innodb performance slow
Jeroen, Two things are likely to make this umpteen times faster. a) Commit the insert transaction every.. say 1000 records? b) use mysql's extended insert statement, insert into mytable values (row1_field1,row1_field2),(row2_field1,row2_field2),(?,?),(?,?) etc etc Ric. - Original Message - From: Jeroen Geusebroek [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 25, 2002 12:11 PM Subject: Mysql Innodb performance slow Hi There, We have currently an Interbase Database with millions and millions of rows which I would like to migrate to MySQL if possible to increase the speed. Transaction support is necessary, so I am using innoDB. When inserting 160K rows in the database (in an innoDB table) it takes about 700! seconds while the amount of same rows when inserted in a myisam table take about 100 seconds. Now probably this can be fine tuned (I hope), and would like to ask for some suggestions. Is anybody using innodb with this amount of rows? I'm curious of what the performance is. Is there something I should keep in mind when migrating? Kind regards, Jeroen Geusebroek - 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
RE: Mysql Innodb performance slow
Hi, What method are you using for inserting the data? Individual INSERTs can be very slow: INSERT INTO foo (x, y, z) VALUES (1, 2, 3); INSERT INTO foo (x, y, z) VALUES (4, 5, 6); ... Batch INSERTs can be massively faster: INSERT INTO foo (x, y, z) VALUES (1, 2, 3), (4, 5, 6), ...; You where right, what i did use indivudual inserts. Now I have it segmented in 1000 rows in one query which massively speeds up the whole inserting progress. 160K records now takes about 20 seconds instead of the 100 records ;) I'm really impressed by this massive speed. Begin; INSERT INTO foo VALUES (1,2,3,4,5) x 1000 Commit; That is what I use now. One question though, does innodb use transactions standard? So without using the begin; and commit; statement, does it still use some kind of transaction? Because still I don't understand why the isam table takes about 100 seconds for inserting 160K rows, while innodb takes 700 seconds. (using individual inserts) Thanks, Jeroen -Original Message- From: Jeroen Geusebroek [mailto:j.geusebroek;infraxs.com] Sent: Friday, October 25, 2002 3:11 AM To: [EMAIL PROTECTED] Subject: Mysql Innodb performance slow Hi There, We have currently an Interbase Database with millions and millions of rows which I would like to migrate to MySQL if possible to increase the speed. Transaction support is necessary, so I am using innoDB. When inserting 160K rows in the database (in an innoDB table) it takes about 700! seconds while the amount of same rows when inserted in a myisam table take about 100 seconds. Now probably this can be fine tuned (I hope), and would like to ask for some suggestions. Is anybody using innodb with this amount of rows? I'm curious of what the performance is. Is there something I should keep in mind when migrating? Kind regards, Jeroen Geusebroek - 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
Re: Mysql Innodb performance slow
I'll agree to this somewhat: (1) always bind your variables. whatever code you are using to do your inserts, the fewer prepared statements you can make the better. for example: insert into TableName (col1, col2, col2, col4) values (?,?,?,?) then, once you have a prepared statment, do a loop and insert. (2) drop all indexes on your table(s). rebuild them after loading (this alone can give orders of magnitude improvement) (3) commit every 10,000 records or so. I can load several million rows into our InnoDB tables in a few minutes. good luck jeff Richard Clarke wrote: Jeroen, Two things are likely to make this umpteen times faster. a) Commit the insert transaction every.. say 1000 records? b) use mysql's extended insert statement, insert into mytable values (row1_field1,row1_field2),(row2_field1,row2_field2),(?,?),(?,?) etc etc Ric. - Original Message - From: Jeroen Geusebroek [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 25, 2002 12:11 PM Subject: Mysql Innodb performance slow Hi There, We have currently an Interbase Database with millions and millions of rows which I would like to migrate to MySQL if possible to increase the speed. Transaction support is necessary, so I am using innoDB. When inserting 160K rows in the database (in an innoDB table) it takes about 700! seconds while the amount of same rows when inserted in a myisam table take about 100 seconds. Now probably this can be fine tuned (I hope), and would like to ask for some suggestions. Is anybody using innodb with this amount of rows? I'm curious of what the performance is. Is there something I should keep in mind when migrating? Kind regards, Jeroen Geusebroek - 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 -- Jeff Mathis, Ph.D. 505-995-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 - 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
Re: Mysql Innodb performance slow
Jeroen Geusebroek wrote: [snip] You where right, what i did use indivudual inserts. Now I have it segmented in 1000 rows in one query which massively speeds up the whole inserting progress. 160K records now takes about 20 seconds instead of the 100 records ;) I'm really impressed by this massive speed. Begin; INSERT INTO foo VALUES (1,2,3,4,5) x 1000 Commit; That is what I use now. One question though, does innodb use transactions standard? So without using the begin; and commit; statement, does it still use some kind of transaction? Because still I don't understand why the isam table takes about 100 seconds for inserting 160K rows, while innodb takes 700 seconds. (using individual inserts) Thanks, Jeroen When you don't disable autocommit, InnoDB is going to create a transaction for _each_ statement, thus you won't be able to expect a lot of speed :) -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - 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
Re: Mysql Innodb performance slow
forgive me. i was initially using the perl DBI methods to load. I am not intimately familiar with the inner workings of DBI, but obviously it must be doing something if you say mysql does not support binding variables (i am using 4.0.4). This is significantly faster than creating a new prepared statement for every insert. I'm now using JDBC, and havne't explored this yet. I'm coming from the Oracle camp, and have only been using mysql for less than a month. so far, i find it very snappy. I do miss the ability to have stored procedures, triggers and views however. jeff Dan Nelson wrote: In the last episode (Oct 25), Jeff Mathis said: I'll agree to this somewhat: (1) always bind your variables. whatever code you are using to do your inserts, the fewer prepared statements you can make the better. for example: insert into TableName (col1, col2, col2, col4) values (?,?,?,?) then, once you have a prepared statment, do a loop and insert. Note that mysql does not support bind variables. If you think you're using them, whatever API you are using is filling them in before sending the statement to mysql. Bind variables do solve quoting problems, though, so if you use them, know why you're using them :) -- Dan Nelson [EMAIL PROTECTED] -- Jeff Mathis, Ph.D. 505-995-1434 The Prediction Company [EMAIL PROTECTED] 525 Camino de los Marquez, Ste 6http://www.predict.com Santa Fe, NM 87505 - 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
Re: Mysql Innodb performance slow
Jeff Mathis wrote: forgive me. i was initially using the perl DBI methods to load. I am not intimately familiar with the inner workings of DBI, but obviously it must be doing something if you say mysql does not support binding variables (i am using 4.0.4). This is significantly faster than creating a new prepared statement for every insert. I'm now using JDBC, and havne't explored this yet. I'm coming from the Oracle camp, and have only been using mysql for less than a month. so far, i find it very snappy. I do miss the ability to have stored procedures, triggers and views however. jeff Dan Nelson wrote: [snip] Note that mysql does not support bind variables. If you think you're using them, whatever API you are using is filling them in before sending the statement to mysql. Bind variables do solve quoting problems, though, so if you use them, know why you're using them :) -- Dan Nelson [EMAIL PROTECTED] Dan speaks the truth. However, using bind variables also proves that you're forward-looking as MySQL-4.1 will have prepared statements with 'real' bound parameters, so your code will run that much faster then. The quoting problem is a valid point, and in fact I always prescribe it as dynamic SQL is the root of many an exploit in database applications, especially web-based ones. -Mark -- For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com - 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
Re: InnoDB Performance Question.
Jungshu, - Original Message - From: Heo, Jungsu [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, October 07, 2002 5:49 AM Subject: InnoDB Performance Question. Hello everyone. I'm working on migrating Oracle to MySQL 4.0.3 MySQL works on Redhat 7.3 and Pentium 550Mhz with 256 Mb RAM. Our application need Transactions, so I decided to use InnoDB. But, InnoDB is slower than I expected. - mysql SELECT COUNT(*) FROM table_name ; +--+ | COUNT(*) | +--+ | 903097 | +--+ 1 row in set (8.07 sec) - 8.07 sec. took to retrieve 903,097 rows. That's OK, faster than Oracle. But, LIMITing records make problem. - mysql SELECT * FROM table_name LIMIT 10, 10 ; .. .. 10 rows in set (3.95 sec) -- This result is normal one? Is there any way to improve perfermance? MySQL probably scans all the 1000 010 rows to get the 10 rows you want. Maybe using an auto-increment column in your table and doing SELECT ... FROM ... WHERE autoinccolumn 100 AND autoinccolumn = 110 ? Heo, Jungsu Mr. SimpleX Internet. http://www.simplexi.com Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com sql query - 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
InnoDB Performance Question.
Hello everyone.
I'm working on migrating Oracle to MySQL 4.0.3
MySQL works on Redhat 7.3 and Pentium 550Mhz with 256 Mb RAM.
Our application need Transactions, so I decided to use InnoDB.
But, InnoDB is slower than I expected.
-
mysql SELECT COUNT(*) FROM table_name ;
+--+
| COUNT(*) |
+--+
| 903097 |
+--+
1 row in set (8.07 sec)
-
8.07 sec. took to retrieve 903,097 rows. That's OK, faster than Oracle.
But, LIMITing records make problem.
-
mysql SELECT * FROM table_name LIMIT 10, 10 ;
..
..
10 rows in set (3.95 sec)
--
This result is normal one? Is there any way to improve perfermance?
I thought one way.
--
ROW = SELECT Primary key FROM table_name LIMIT 10, 10;
WHILE( ! no_more_row )
{
Fetch record WHERE Primary key = ROW.Primary key ;
}
--
This can help?
In Oracle
---
SQL SELECT A.* FROM ( SELECT rownum as rnum, B.* FROM table_name B ) A
2 where rnum = 10 and rownum = 10 ;
Elapsed: 00:00:01.20
Just 1.20 Sec. took.
Thank you for advanced Answer!
##
Heo, Jungsu Mr.
SimpleX Internet. http://www.simplexi.com
-
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 mysql-unsubscribe-##L=##[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Innodb performance (again)
Though i would give Innodb another go with the latest mysql-max RPM's (3.23.41) for linux running on a Redhat machine. This is on a Compaq ML-370, 667Mhz with 1gig mem on kernel 2.2.19. Disks are Raid-5. Huge performance difference between Innodb and MyISAM for a simple table create (7 times slower!). Total size of original maillog.email table is around 700meg (varchar, thus packed), so we ar maybe looking at around 100-150meg for the new table: mysql select count(*) from maillog.email; +--+ | count(*) | +--+ | 6744395 | +--+ 1 row in set (0.00 sec) mysql create table mailtest type=innodb select * from maillog.email limit 100; Query OK, 100 rows affected (7 min 9.92 sec) Records: 100 Duplicates: 0 Warnings: 0 mysql create table mailtest2 select * from maillog.email limit 100; Query OK, 100 rows affected (1 min 56.13 sec) Records: 100 Duplicates: 0 Warnings: 0 my.cnf: [mysqld] #log-long-format log = mysqld.log #log-slow-queries = mysqld-slow.log set-variable = key_buffer=64M #set-variable = max_allowed_packet=1M set-variable = table_cache=128 set-variable = sort_buffer=4M set-variable = record_buffer=1M #set-variable = thread_cache=8 #set-variable = thread_concurrency=8 # Try number of CPU's*2 #set-variable = myisam_sort_buffer_size=64M innodb_data_file_path = ibdata1:512M innodb_data_home_dir = /data/innodb/ibdata set-variable = innodb_mirrored_log_groups=1 innodb_log_group_home_dir = /data/innodb/iblogs set-variable = innodb_log_files_in_group=3 set-variable = innodb_log_file_size=30M set-variable = innodb_log_buffer_size=16M innodb_flush_log_at_trx_commit=1 innodb_log_arch_dir = /data/innodb/iblogs innodb_log_archive=0 set-variable = innodb_buffer_pool_size=80M set-variable = innodb_additional_mem_pool_size=10M set-variable = innodb_file_io_threads=4 set-variable = innodb_lock_wait_timeout=50 [myisamchk] set-variable = key_buffer=256M set-variable = sort_buffer=256M set-variable = read_buffer=2M set-variable = write_buffer=2M -- Richard Ellerbrock [EMAIL PROTECTED] - 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
