Spam detection software, running on the system "marmalade.snp.org", has
identified this incoming email as possible spam. The original message
has been attached to this so you can view it (if it isn't spam) or label
similar future email. If you have any questions, see
[EMAIL PROTECTED] for details.
Content preview: Hi, I'm seeing what to me appears to be very slow
performance when inserting/ updating / deleting into a 15M row innodb
table. I'm using mysql 5.0 on Solaris 10 with 8GB RAM. MySQL is compiled
32bit so has 4GB available. [...]
Content analysis details: (6.1 points, 5.2 required)
pts rule name description
---- ---------------------- --------------------------------------------------
0.1 FORGED_RCVD_HELO Received: contains a forged HELO
1.1 SPF_FAIL SPF: sender does not match SPF record (fail)
[SPF failed: Please see
http://www.openspf.org/why.html?sender=mysqllists%40albanach.com&ip=65.40.219.158&receiver=localhost]
0.9 J_CHICKENPOX_44 BODY: 4alpha-pock-4alpha
0.1 TW_HK BODY: Odd Letter Triples with HK
-0.2 BAYES_40 BODY: Bayesian spam probability is 20 to 40%
[score: 0.2484]
2.0 RCVD_IN_SORBS_DUL RBL: SORBS: sent directly from dynamic IP address
[65.40.219.158 listed in dnsbl.sorbs.net]
1.9 RCVD_IN_NJABL_DUL RBL: NJABL: dialup sender did non-local SMTP
[65.40.219.158 listed in combined.njabl.org]
--- Begin Message ---
Hi,
I'm seeing what to me appears to be very slow performance when inserting/
updating / deleting into a 15M row innodb table. I'm using mysql 5.0 on
Solaris 10 with 8GB RAM. MySQL is compiled 32bit so has 4GB available.
The table structure is as follows:
f_id (int6) | v_id (int9)
The primary key is an index on f_id and v_id There's also an index on
v_id
An insert of about 50,000 rows takes about 15 seconds which seems long -
I'd have expected most of this to be happening in memory.
A small sql script that reads and updates the table shows the following
output when run with time from the command line:
real 0m53.457s
user 0m0.035s
sys 0m0.038s
Any suggestion why the real time is so much higher than user & sys time?
Below is the output from show innodb status and the innodb section from
my.cnf
As indicated in the my.cnf file below, My innodb data is all in its own
partition loaded with forcedirectio
Thanks for any suggestions!
Russell
=====================================
060829 0:10:46 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 8 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 1534, signal count 1513
Mutex spin waits 212, rounds 20947, OS waits 101
RW-shared spins 22911, OS waits 529; RW-excl spins 47142, OS waits 904
------------
TRANSACTIONS
------------
Trx id counter 0 74514
Purge done for trx's n:o < 0 74512 undo n:o < 0 0
History list length 0
Total number of lock structs in row lock hash table 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 11
MySQL thread id 3, query id 19 localhost root
SHOW INNODB STATUS
--------
FILE I/O
--------
I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (write thread)
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: 0; buffer pool: 0
1220 OS file reads, 177 OS file writes, 89 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf for space 0: size 1, free list len 0, seg size 2, is empty
Ibuf for space 0: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 7470761, used cells 371755, node heap has 375 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s
---
LOG
---
Log sequence number 3 1075228472
Log flushed up to 3 1075228472
Last checkpoint at 3 1075228472
0 pending log writes, 0 pending chkp writes
61 log i/o's done, 0.00 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 2109874514; in additional pool allocated 5845504
Buffer pool size 115200
Free buffers 112987
Database pages 1838
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 1320, created 518, written 1206
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
1 read views open inside InnoDB
Main thread id 9, state: waiting for server activity
Number of rows inserted 114100, updated 1, deleted 114100, read 401895
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
----- my.cnf -------
port=3306
socket=/tmp/mysql.sock
user=root
datadir=//var/lib/mysql
max_connections=1600
max_connect_errors=10
table_cache=2048
max_allowed_packet=1048576
binlog_cache_size=1048576
key_buffer_size=16777216
max_heap_table_size=67108864
sort_buffer_size=65536
join_buffer_size=1048576
query_cache_size=26214400
thread_cache=16
thread_concurrency=32
thread_stack=64K
ft_min_word_len=4
default_table_type=MYISAM
transaction_isolation=REPEATABLE-READ
tmp_table_size=64M
#######below is for innodb############
innodb_data_home_dir = /var/lib/mysql/ibdata
innodb_data_file_path = ibdata1:100M;ibdata2:100M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/ibdata
innodb_log_arch_dir = /var/lib/mysql/ibdata
innodb_status_file=0
innodb_buffer_pool_size=3000M
innodb_additional_mem_pool_size=20M
innodb_log_file_size=400M
innodb_log_buffer_size=80M
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=300
innodb_max_dirty_pages_pct=90
innodb_thread_concurrency=32
innodb_sync_spin_loops=200
innodb_data_home_dir = /var/lib/mysql/ibdata
innodb_data_file_path = ibdata1:100M;ibdata2:100M:autoextend
innodb_log_group_home_dir = /var/lib/mysql/ibdata
innodb_log_arch_dir = /var/lib/mysql/ibdata
--- End Message ---
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
