AW: Locks on Heap tables
| 4294967295 | | myisam_data_pointer_size| 4 | | myisam_max_extra_sort_file_size | 2147483648 | | myisam_max_sort_file_size | 2147483647 | | myisam_recover_options | OFF | | myisam_repair_threads | 1 | | myisam_sort_buffer_size | 8388608 | | ndb_autoincrement_prefetch_sz | 32 | | ndb_force_send | ON | | ndb_use_exact_count | ON | | ndb_use_transactions| ON | | net_buffer_length | 16384 | | net_read_timeout| 30 | | net_retry_count | 10 | | net_write_timeout | 60 | | new | OFF | | old_passwords | ON | | open_files_limit| 32000 | | pid_file| /var/run/mysqld/mysqld.pid | | port| 3306 | | preload_buffer_size | 32768 | | protocol_version| 10 | | query_alloc_block_size | 8192 | | query_cache_limit | 1048576 | | query_cache_min_res_unit| 4096 | | query_cache_size| 10485760 | | query_cache_type| DEMAND | | query_cache_wlock_invalidate| OFF | | query_prealloc_size | 8192 | | range_alloc_block_size | 2048 | | read_buffer_size| 1044480 | | read_only | OFF | | read_rnd_buffer_size| 262144 | | relay_log_purge | ON | | rpl_recovery_rank | 0 | | secure_auth | OFF | | server_id | 1 | | skip_external_locking | ON | | skip_networking | OFF | | skip_show_database | OFF | | slave_net_timeout | 3600 | | slow_launch_time| 2 | | socket | /var/run/mysqld/mysqld.sock | | sort_buffer_size| 1048568 | | sql_mode| | | storage_engine | MyISAM | | sync_binlog | 0 | | sync_frm| ON | | system_time_zone| CEST | | table_cache | 256 | | table_type | MyISAM | | thread_cache_size | 64 | | thread_stack| 131072 | | time_format | %H:%i:%s | | time_zone | SYSTEM | | tmp_table_size | 33554432 | | tmpdir | /tmp | | transaction_alloc_block_size| 8192 | | transaction_prealloc_size | 4096 | | tx_isolation| REPEATABLE-READ | | version | 4.1.9-Debian_0.dotdeb.0-log | | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (January 11, 2005) | | version_comment | Source distribution | | version_compile_machine | i386 | | version_compile_os | pc-linux-gnu | | wait_timeout| 10 | +-+----- -+ Herzliche Grüße Hannes Rohde ¯ incoWEB.de - agentur für neue medien Stapenhorststr. 10 D-45329 Essen [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> http://www.incoWEB.de Phone & Fax 0700-0-4626932 0700-0-INCOWEB Diese E-Mail enthält vertrauliche Informationen, die nur für den o.g. Empfänger bestimmt sind! Jede Kenntnisnahme, Verteilung oder Vervielfältigung durch andere Personen ist nicht zulässig. Sollten Sie diese E-Mail irrtümlich erhalten haben, melden Sie uns dies bitte unverzüglich. This email, its content and any files transmitted with it are intended solely for the addressee(s). Access, distribution or copying by any other party is not permitted. If you are not the intended recipient, then please notify us immediately by returning it to the originator. -Ursprüngliche Nachricht- Von: Gleb Paharenko [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 7. Juli 2005 10:35 An: mysql@lists.mysql.com Betreff: Re: Locks on Heap tables Hello. Are you sure that your server doesn't swap? Providing output of 'SHOW STATUS', 'SHOW VARIABLES' and your table definition could give more information for suggestions. Also, if you have a hash index on a MEMORY table that has a high degree of key duplication (many index entries containing the same value), updates to the table that affect key values and all deletes are significantly slower. The degree of slowdown is proportional to the degree of duplication (or, inversely proportional to the index cardinality). You can use a BTREE index to avoid this problem. "Hannes Rohde" <[EMAIL PROTECTED]> wrote: > Hello everyone, > >We are using MySQL as the database backend on quite a big portal > page with about 50.000 users and 3 mio. PIs per day. MySQL is as well = > the > backend for the (php) session management. We are using a heap for that = > case > as we
Locks on Heap tables
Hello everyone, We are using MySQL as the database backend on quite a big portal page with about 50.000 users and 3 mio. PIs per day. MySQL is as well the backend for the (php) session management. We are using a heap for that case as well as for instance phpbb does. Lately we are experiencing long lasting table locks due to deletes or updates on the session table. I know that heap tables only support table wide locking, but shouldn't those locks be gone quite fast? I have already checked the obvious reasons for this kind of behaviour like swapping but I couldn't find anything. Even googling didn't bring anything useful up. Hopefully someone got some ideas to solve this problem :-) Thank you in advance Hannes Rohde ¯ incoWEB.de - agentur für neue medien Stapenhorststr. 10 D-45329 Essen [EMAIL PROTECTED] <mailto:[EMAIL PROTECTED]> http://www.incoWEB.de Phone & Fax 0700-0-4626932 0700-0-INCOWEB Diese E-Mail enthält vertrauliche Informationen, die nur für den o.g. Empfänger bestimmt sind! Jede Kenntnisnahme, Verteilung oder Vervielfältigung durch andere Personen ist nicht zulässig. Sollten Sie diese E-Mail irrtümlich erhalten haben, melden Sie uns dies bitte unverzüglich. This email, its content and any files transmitted with it are intended solely for the addressee(s). Access, distribution or copying by any other party is not permitted. If you are not the intended recipient, then please notify us immediately by returning it to the originator. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: AW: Slow Replication
We did get the problem kind of solved up to now. We basically use as advised the option innodb flush_log_at_trx_commit=2. This did get the timelage solved. I still do not really understand why we had to speed the slave up like this because it should have been faster due to the hardware already. I guess it is just as Alec said that the fact of using a single-threaded Slave-SQL thread slows the slove down a whole lot. Does anyone have any solution or suggestion how to "work" around that? I don't really feel good using the flush_log_at_trx_commit option. P.S.: The old Show Master or slave status information basically showed that the I/O Thread has been up to date and the SQL Thread of the slave lagged. Thank you, Hannes Rohde -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 10. Februar 2005 13:15 An: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Betreff: Re: AW: Slow Replication "Hannes Rohde" <[EMAIL PROTECTED]> wrote on 10/02/2005 11:44:13: >I don't think we are dealing with an IO bottleneck here because the > slave server should quite faster with writings to the disc at least since we > are using Raid 0 here. Or is there any way which could explain an IO > bottleneck even though the slave is not running as many selects as the > master is? In this case we are talking about one replicated database on a > dedicated slave system. As I understand the previous posts, the problem is that the replication process is single-threaded while the updates on the original master are multi-threaded. On the original server, if Update 1 stalls because it has to fetch data of disk, Update 2 can proceed. If Update 2 stalls, Update 3 can proceed - and so on. This means firstly that Updates which can take advantage of the cache take no effective time - they come in, do their job, and exit while peer updates are stalled in Disk wait. This also means that lower-level software can optimise disk performance by re-ordering IO operations to minimise head movements. In my experience, having up to 4 parallel streams of disk operations, and allowing the disk to pick its preferred order of execution, usually adds about 50% to disk performance and can double it. However, when they are replicated to the slave server, the updates are put into a strictly First In, First out queue. If Update 1 stalls, Update 2 cannot be started - and nor can Update 3. When Update 3 does finally start, it cannot overlap the others, so that the time it takes, albeit small because it does not access disk, is added on to the other times rather than included within them. And since you are performing strictly one operation at a time (on the Updates side at least) Raid 0 does not help you, because there are no overlapping reads to get from alternate disks. Alec Cawley -- 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]
AW: Slow Replication
I don't think we are dealing with an IO bottleneck here because the slave server should quite faster with writings to the disc at least since we are using Raid 0 here. Or is there any way which could explain an IO bottleneck even though the slave is not running as many selects as the master is? In this case we are talking about one replicated database on a dedicated slave system. Thanks, Hannes -Ursprüngliche Nachricht- Von: Mechain Marc [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 10. Februar 2005 12:04 An: Hannes Rohde; Marc Slemko Cc: mysql@lists.mysql.com Betreff: RE: Slow Replication I think he is talking about the "innodb_flush_log_at_trx_commit" parameter. Try to put it at a value of 0. innodb_flush_log_at_trx_commit = 0 If you have an IO bottleneck, this may help. Marc. -Message d'origine----- De : Hannes Rohde [mailto:[EMAIL PROTECTED] Envoyé : jeudi 10 février 2005 11:46 À : 'Marc Slemko' Cc : mysql@lists.mysql.com Objet : AW: Slow Replication I don't quite get what you mean with the second paragraph. Do you mean increasing the thread concurrency to 6 or something like that? I have already put it on 4 because we do have HT active on the cpu. On the other it is just a single processor P IV system. On other hand I think it wouldn't speed up the SQL thread on the slave a whole lot. It would be excellent if you could run two or more SQL threads on the slave you priories them somehow. Anyway I will try your last paragraph's suggestion. Thanks, Hannes -Ursprüngliche Nachricht- Von: Marc Slemko [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 10. Februar 2005 11:24 An: Hannes Rohde Cc: mysql@lists.mysql.com Betreff: Re: Slow Replication On Wed, 9 Feb 2005 22:07:19 +0100, Hannes Rohde <[EMAIL PROTECTED]> wrote: > Hi all, > > We use MySQL as a database backend on a portal site. We have a two > database server setup (one master, one slave). The master is a PIV 3,2 GHz., > 2 GB Ram and a 80GB Raid-1 system. The slave is a PIV 3.2 GHz., 4 GB Ram and > a 80GB Raid-0 system. Both run on MySQL 4.1.9 and only use InnoDB. Even > though the slave is a bigger system and is quite fast with selects, it > always falls behind in replication (Seconds behind the server keeps growing > at high-load times). > Is there any way to speed up the replication a little more? I have already > tried a whole lot of things but have never been successful, yet :-( That can be problematic since innodb allows much higher concurrency than myisam, although you can still have this issue with myisam. What you have to realize is that due to how mysql replication works, every transaction needs to be serialized. The slave is only running a single statement at once. So if you have multiple CPUs on the server, or multiple disks that can't be saturated by a single concurrent operation ... then multiple simultaneous operations can get better performance on the server than you can get in replication to the client. If most of your stuff is innodb, then setting the innodb option to not sync to disk on every transaction may speed things up a lot ... if you don't care about your data. But, then again, I don't think mysql replication is actually fully transactional yet anyway. -- 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]
AW: Slow Replication
I don't quite get what you mean with the second paragraph. Do you mean increasing the thread concurrency to 6 or something like that? I have already put it on 4 because we do have HT active on the cpu. On the other it is just a single processor P IV system. On other hand I think it wouldn't speed up the SQL thread on the slave a whole lot. It would be excellent if you could run two or more SQL threads on the slave you priories them somehow. Anyway I will try your last paragraph's suggestion. Thanks, Hannes -Ursprüngliche Nachricht- Von: Marc Slemko [mailto:[EMAIL PROTECTED] Gesendet: Donnerstag, 10. Februar 2005 11:24 An: Hannes Rohde Cc: mysql@lists.mysql.com Betreff: Re: Slow Replication On Wed, 9 Feb 2005 22:07:19 +0100, Hannes Rohde <[EMAIL PROTECTED]> wrote: > Hi all, > > We use MySQL as a database backend on a portal site. We have a two > database server setup (one master, one slave). The master is a PIV 3,2 GHz., > 2 GB Ram and a 80GB Raid-1 system. The slave is a PIV 3.2 GHz., 4 GB Ram and > a 80GB Raid-0 system. Both run on MySQL 4.1.9 and only use InnoDB. Even > though the slave is a bigger system and is quite fast with selects, it > always falls behind in replication (Seconds behind the server keeps growing > at high-load times). > Is there any way to speed up the replication a little more? I have already > tried a whole lot of things but have never been successful, yet :-( That can be problematic since innodb allows much higher concurrency than myisam, although you can still have this issue with myisam. What you have to realize is that due to how mysql replication works, every transaction needs to be serialized. The slave is only running a single statement at once. So if you have multiple CPUs on the server, or multiple disks that can't be saturated by a single concurrent operation ... then multiple simultaneous operations can get better performance on the server than you can get in replication to the client. If most of your stuff is innodb, then setting the innodb option to not sync to disk on every transaction may speed things up a lot ... if you don't care about your data. But, then again, I don't think mysql replication is actually fully transactional yet anyway. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slow Replication
Hi all, We use MySQL as a database backend on a portal site. We have a two database server setup (one master, one slave). The master is a PIV 3,2 GHz., 2 GB Ram and a 80GB Raid-1 system. The slave is a PIV 3.2 GHz., 4 GB Ram and a 80GB Raid-0 system. Both run on MySQL 4.1.9 and only use InnoDB. Even though the slave is a bigger system and is quite fast with selects, it always falls behind in replication (Seconds behind the server keeps growing at high-load times). Is there any way to speed up the replication a little more? I have already tried a whole lot of things but have never been successful, yet :-( Here is a snapshot of the configuration: skip-name-resolve key_buffer=1M max_allowed_packet=1M thread_cache_size=128 thread_stack=128K table_cache=1024 join_buffer_size=5M read_buffer_size=5M sort_buffer_size=5M thread_concurrency=4 query_cache_size = 32M query_cache_limit = 1M query_cache_type = 2 max_connections=900 innodb_data_file_path=ibdata1:2G:autoextend innodb_buffer_pool_size=1200M innodb_additional_mem_pool_size=20M P.S.: I hope I have given you enough information - it's my post on the list...;-) I appreciate your help, Hannes Rohde -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
