Performance problems on MySQL

am 02.09.2010 13:50:05 von Alexandre Vieira

--0014853d010650e1b3048f456af8
Content-Type: text/plain; charset=ISO-8859-1

Hi list,

I'm having some performance problems on my 5.0.45-log DB running on Solaris
8 (V240).

We only have one table and two apps selecting, updating, inserting and
deleting massively and randomly from this table.

The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only
one condition on an unique varchar indexed column.

The table has 500k records and has been OPTIMIZED 32h ago.

I've ran some sampling and:

A SELECT costs between 400ms and 600ms.
An UPDATE costs between 800ms and 1300ms.
A DELETE costs between 900ms and 1300ms
An INSERT costs always 900ms 2000ms.

At any given time the DB is handling 60-80 operations every second. It does
not scale any more than this because all the application connections to the
DB are being used and waiting for the DB to move. Our application queues
requests and it lags our clients.

The perl mysqltuner only whines about "Query cache disabled" but since I get
an ~20 updates every second I can't get any query cache hits, so I disabled
it.

If it makes any difference, we're replicating everything to another server
that don't serve any queries.

The DB has a 32 hour uptime.

Any help is most welcome.

You can find my.cnf, show status and show innodb status below.

Kind regards
Alex

###############################
my.cnf:

sql-mode ="STRICT_ALL_TABLES"
old_passwords =1
skip-bdb
max_connections =100
max_allowed_packet =1M
table_cache =512
sort_buffer_size =2M
read_buffer_size =4M
read_rnd_buffer_size =8M
thread_cache_size =16
query_cache_limit =32M
thread_concurrency =8
max_heap_table_size =28M
tmp_table_size =12M
innodb_buffer_pool_size =350M
innodb_additional_mem_pool_size =15M
innodb_log_buffer_size =6M
innodb_flush_log_at_trx_commit =1
innodb_lock_wait_timeout =50

###############################

mysql> show status where Value NOT LIKE 0;
+-----------------------------------+------------+
| Variable_name | Value |
+-----------------------------------+------------+
| Aborted_clients | 88 |
| Aborted_connects | 37590 |
| Binlog_cache_use | 2148392 |
| Bytes_received | 1117 |
| Bytes_sent | 8772 |
| Com_change_db | 1 |
| Com_delete | 4 |
| Com_insert | 3 |
| Com_select | 2 |
| Com_show_databases | 1 |
| Com_show_fields | 3 |
| Com_show_status | 2 |
| Com_show_tables | 1 |
| Compression | OFF |
| Connections | 276096 |
| Created_tmp_files | 5 |
| Created_tmp_tables | 4 |
| Flush_commands | 1 |
| Handler_commit | 14 |
| Handler_prepare | 14 |
| Handler_read_key | 8 |
| Handler_read_rnd_next | 263 |
| Handler_write | 395 |
| Innodb_buffer_pool_pages_data | 6019 |
| Innodb_buffer_pool_pages_dirty | 1858 |
| Innodb_buffer_pool_pages_flushed | 593993 |
| Innodb_buffer_pool_pages_free | 15784 |
| Innodb_buffer_pool_pages_misc | 597 |
| Innodb_buffer_pool_pages_total | 22400 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_requests | 42797013 |
| Innodb_buffer_pool_reads | 3497 |
| Innodb_buffer_pool_write_requests | 19096507 |
| Innodb_data_fsyncs | 4319683 |
| Innodb_data_pending_fsyncs | 1 |
| Innodb_data_read | 60231680 |
| Innodb_data_reads | 3514 |
| Innodb_data_writes | 4496721 |
| Innodb_data_written | 1259458560 |
| Innodb_dblwr_pages_written | 593993 |
| Innodb_dblwr_writes | 12967 |
| Innodb_log_write_requests | 2111208 |
| Innodb_log_writes | 4285654 |
| Innodb_os_log_fsyncs | 4303114 |
| Innodb_os_log_pending_fsyncs | 1 |
| Innodb_os_log_written | 3264897024 |
| Innodb_page_size | 16384 |
| Innodb_pages_created | 2476 |
| Innodb_pages_read | 3543 |
| Innodb_pages_written | 593993 |
| Innodb_row_lock_time | 1339668 |
| Innodb_row_lock_time_avg | 379 |
| Innodb_row_lock_time_max | 10631 |
| Innodb_row_lock_waits | 3531 |
| Innodb_rows_deleted | 31904 |
| Innodb_rows_inserted | 530870 |
| Innodb_rows_read | 7885336 |
| Innodb_rows_updated | 2100083 |
| Key_blocks_unused | 7159 |
| Key_blocks_used | 14 |
| Key_read_requests | 106 |
| Key_reads | 14 |
| Last_query_cost | 10.499000 |
| Max_used_connections | 66 |
| Ndb_config_from_host | |
| Open_files | 54 |
| Open_tables | 126 |
| Qcache_free_blocks | 1 |
| Qcache_hits | 18 |
| Qcache_inserts | 595 |
| Qcache_not_cached | 7611 |
| Questions | 12971115 |
| Rpl_status | NULL |
| Select_scan | 4 |
| Slave_running | OFF |
| Ssl_cipher | |
| Ssl_cipher_list | |
| Ssl_session_cache_mode | NONE |
| Ssl_version | |
| Table_locks_immediate | 4927708 |
| Table_locks_waited | 67 |
| Threads_cached | 10 |
| Threads_connected | 42 |
| Threads_created | 4133 |
| Threads_running | 36 |
| Uptime | 120796 |
| Uptime_since_flush_status | 120796 |
+-----------------------------------+------------+
87 rows in set (0.01 sec)

###############################

=====================================
100902 12:39:47 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 19 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 63731, signal count 62253
Mutex spin waits 0, rounds 1686893, OS waits 41516
RW-shared spins 23291, OS waits 11428; RW-excl spins 4076, OS waits 3446
------------
TRANSACTIONS
------------
Trx id counter 0 1953324058
Purge done for trx's n:o < 0 1953323526 undo n:o < 0 0
History list length 172
Total number of lock structs in row lock hash table 9
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 0 0, not started, OS thread id 4121 waiting in InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 276150, query id 12973147 192.168.87.6 myuser_adm Sorting
result
SELECT********************
---TRANSACTION 0 0, not started, OS thread id 4119 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 276149, query id 12973129 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 0, not started, OS thread id 4067 waiting in InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 276148, query id 12973102 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953324017, not started, OS thread id 4098 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 276144, query id 12973093 192.168.87.6 myuser_adm Updating
UPDATE********************
---TRANSACTION 0 1953323942, not started, OS thread id 4140
MySQL thread id 276135, query id 12972947 192.168.87.6 myuser_adm
---TRANSACTION 0 1953324045, not started, OS thread id 4074 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 276133, query id 12973157 192.168.87.6 myuser_adm update
INSERT********************
---TRANSACTION 0 1953324021, not started, OS thread id 4038
MySQL thread id 276130, query id 12973043 192.168.87.6 myuser_adm
---TRANSACTION 0 1953323971, not started, OS thread id 3873 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 276129, query id 12973112 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953324024, not started, OS thread id 4145 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 276124, query id 12973159 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953324027, not started, OS thread id 4003
MySQL thread id 276111, query id 12973050 192.168.87.6 myuser_adm
---TRANSACTION 0 1953323945, not started, OS thread id 3918 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 276107, query id 12973115 192.168.87.6 myuser_adm Sorting
result
SELECT********************
---TRANSACTION 0 1953324023, not started, OS thread id 4089 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 276093, query id 12973106 192.168.87.6 myuser_adm Updating
UPDATE********************
---TRANSACTION 0 1953323946, not started, OS thread id 4102
MySQL thread id 276081, query id 12972959 192.168.87.6 myuser_adm
---TRANSACTION 0 1953324029, not started, OS thread id 4143 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 276079, query id 12973167 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953324048, not started, OS thread id 3945 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 275987, query id 12973161 192.168.87.6 myuser_adm Updating
UPDATE********************
---TRANSACTION 0 1953324026, not started, OS thread id 4048 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 275976, query id 12973110 192.168.87.6 myuser_adm Updating
UPDATE********************
---TRANSACTION 0 1953324040, not started, OS thread id 4142 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 275975, query id 12973154 192.168.87.6 myuser_adm Updating
UPDATE********************
---TRANSACTION 0 1953323944, not started, OS thread id 3979 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 275946, query id 12973121 192.168.87.6 myuser_adm Sorting
result
SELECT********************
---TRANSACTION 0 1953324016, not started, OS thread id 4058 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 275931, query id 12973092 192.168.87.6 myuser_adm Updating
UPDATE********************
---TRANSACTION 0 1953324015, not started, OS thread id 4065 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 275913, query id 12973090 192.168.87.6 myuser_adm Updating
UPDATE********************
---TRANSACTION 0 1953324033, not started, OS thread id 4111 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 275907, query id 12973137 192.168.87.6 myuser_adm Updating
UPDATE********************
---TRANSACTION 0 1953323993, not started, OS thread id 4078 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 275889, query id 12973132 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953324011, not started, OS thread id 4136 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 275887, query id 12973089 192.168.87.6 myuser_adm Updating
UPDATE********************
---TRANSACTION 0 1953324034, not started, OS thread id 4014 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 275888, query id 12973166 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953323997, not started, OS thread id 4123 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 275856, query id 12973133 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953324002, not started, OS thread id 3999 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 275827, query id 12973151 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953323985, not started, OS thread id 4115 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 275809, query id 12973105 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953324036, not started, OS thread id 4133 sleeping before
joining InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 275784, query id 12973169 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953324031, not started, OS thread id 4134 waiting in
InnoDB queue
mysql tables in use 1, locked 1
MySQL thread id 275715, query id 12973136 192.168.87.6 myuser_adm Updating
UPDATE********************
---TRANSACTION 0 1953324000, not started, OS thread id 4124 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 275665, query id 12973149 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953324009, not started, OS thread id 4130 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 275539, query id 12973155 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953323988, not started, OS thread id 4010 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 275263, query id 12973108 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953323990, not started, OS thread id 4086 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 274948, query id 12973123 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953324032, not started, OS thread id 3934 waiting in
InnoDB queue
mysql tables in use 1, locked 0
MySQL thread id 274703, query id 12973163 192.168.87.6 myuser_adm statistics
SELECT********************
---TRANSACTION 0 1953292243, not started, OS thread id 4046
MySQL thread id 274066, query id 12973170 localhost root
show innodb status
---TRANSACTION 0 1953324057, ACTIVE 0 sec, OS thread id 4127, thread
declared inside InnoDB 498
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 276140, query id 12973085 192.168.87.6 myuser_adm update
INSERT********************
---TRANSACTION 0 1953324055, ACTIVE 0 sec, OS thread id 4144, thread
declared inside InnoDB 498
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 276147, query id 12973082 192.168.87.6 myuser_adm update
INSERT********************
---TRANSACTION 0 1953324053, ACTIVE 0 sec, OS thread id 4141, thread
declared inside InnoDB 499
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 275720, query id 12973078 192.168.87.6 myuser_adm end
UPDATE********************
---TRANSACTION 0 1953324051, ACTIVE 0 sec, OS thread id 4029, thread
declared inside InnoDB 499
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 273472, query id 12973075 192.168.87.6 myuser_adm end
UPDATE********************
---TRANSACTION 0 1953324049, ACTIVE 1 sec, OS thread id 4109, thread
declared inside InnoDB 499
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 274973, query id 12973084 192.168.87.6 myuser_adm end
UPDATE********************
---TRANSACTION 0 1953324046, ACTIVE 1 sec, OS thread id 4126, thread
declared inside InnoDB 498
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 276146, query id 12973081 192.168.87.6 myuser_adm update
INSERT********************
---TRANSACTION 0 1953324043, ACTIVE 1 sec, OS thread id 4122, thread
declared inside InnoDB 499
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 274989, query id 12973069 192.168.87.6 myuser_adm end
UPDATE********************
---TRANSACTION 0 1953324041, ACTIVE 1 sec, OS thread id 4099, thread
declared inside InnoDB 498
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 276145, query id 12973066 192.168.87.6 myuser_adm update
INSERT********************
---TRANSACTION 0 1953324038, ACTIVE (PREPARED) 1 sec, OS thread id 4077
preparing
mysql tables in use 1, locked 1
2 lock struct(s), heap size 320, undo log entries 1
MySQL thread id 275321, query id 12973067 192.168.87.6 myuser_adm end
UPDATE********************
--------
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: 1; buffer pool: 0
3514 OS file reads, 4497412 OS file writes, 4320374 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 41.05 writes/s, 40.73 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 0, seg size 2,
0 inserts, 0 merged recs, 0 merges
Hash table size 1452727, used cells 496505, node heap has 597 buffer(s)
31.26 hash searches/s, 15.31 non-hash searches/s
---
LOG
---
Log sequence number 61 3783563525
Log flushed up to 61 3783563173
Last checkpoint at 61 3778869606
1 pending log writes, 0 pending chkp writes
4297652 log i/o's done, 40.63 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 419047082; in additional pool allocated 2578048
Buffer pool size 22400
Free buffers 15784
Database pages 6019
Modified db pages 1895
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages read 3543, created 2476, written 594057
0.00 reads/s, 0.00 creates/s, 6.47 writes/s
Buffer pool hit rate 1000 / 1000
--------------
ROW OPERATIONS
--------------
8 queries inside InnoDB, 29 queries in queue
1 read views open inside InnoDB
Main thread id 11, state: sleeping
Number of rows inserted 530873, updated 2100423, deleted 31904, read 7886015
0.21 inserts/s, 19.95 updates/s, 0.00 deletes/s, 40.05 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

###############################

--0014853d010650e1b3048f456af8--

Re: Performance problems on MySQL

am 02.09.2010 14:50:51 von Johnny Withers

Can you show us the table structure and sample queries?

On Thursday, September 2, 2010, Alexandre Vieira wrote:
> Hi list,
>
> I'm having some performance problems on my 5.0.45-log DB running on Solar=
is
> 8 (V240).
>
> We only have one table and two apps selecting, updating, inserting and
> deleting massively and randomly from this table.
>
> The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have on=
ly
> one condition on an unique varchar indexed column.
>
> The table has 500k records and has been OPTIMIZED 32h ago.
>
> I've ran some sampling and:
>
> A SELECT costs between 400ms and 600ms.
> An UPDATE costs between 800ms and 1300ms.
> A DELETE costs between 900ms and 1300ms
> An INSERT costs always 900ms 2000ms.
>
> At any given time the DB is handling 60-80 operations every second. It do=
es
> not scale any more than this because all the application connections to t=
he
> DB are being used and waiting for the DB to move. Our application queues
> requests and it lags our clients.
>
> The perl mysqltuner only whines about "Query cache disabled" but since I =
get
> an ~20 updates every second I can't get any query cache hits, so I disabl=
ed
> it.
>
> If it makes any difference, we're replicating everything to another serve=
r
> that don't serve any queries.
>
> The DB has a 32 hour uptime.
>
> Any help is most welcome.
>
> You can find my.cnf, show status and show innodb status below.
>
> Kind regards
> Alex
>
> ###############################
> my.cnf:
>
> sql-mode =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 ="STRICT_ALL_TA=
BLES"
> old_passwords =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =3D1
> skip-bdb
> max_connections =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =3D100
> max_allowed_packet =A0 =A0 =A0 =A0 =A0 =A0 =1M
> table_cache =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =3D512
> sort_buffer_size =A0 =A0 =A0 =A0 =A0 =A0 =A0 =2M
> read_buffer_size =A0 =A0 =A0 =A0 =A0 =A0 =A0 =4M
> read_rnd_buffer_size =A0 =A0 =A0 =A0 =A0 =8M
> thread_cache_size =A0 =A0 =A0 =A0 =A0 =A0 =A0 =3D16
> query_cache_limit =A0 =A0 =A0 =A0 =A0 =A0 =A0 =3D32M
> thread_concurrency =A0 =A0 =A0 =A0 =A0 =A0 =8
> max_heap_table_size =A0 =A0 =A0 =A0 =A0 =A0 =3D28M
> tmp_table_size =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =12M
> innodb_buffer_pool_size =A0 =A0 =A0 =A0 =3D350M
> innodb_additional_mem_pool_size =3D15M
> innodb_log_buffer_size =A0 =A0 =A0 =A0 =6M
> innodb_flush_log_at_trx_commit =1
> innodb_lock_wait_timeout =A0 =A0 =A0 =50
>
> ###############################
>
> mysql> show status where Value NOT LIKE 0;
> +-----------------------------------+------------+
> | Variable_name =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | Value =A0 =A0 =
=A0|
> +-----------------------------------+------------+
> | Aborted_clients =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 88 =A0 =A0 =A0 =
=A0 |
> | Aborted_connects =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 37590 =A0 =A0 =A0=
|
> | Binlog_cache_use =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 2148392 =A0 =A0|
> | Bytes_received =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 1117 =A0 =A0 =
=A0 |
> | Bytes_sent =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 8772 =A0 =
=A0 =A0 |
> | Com_change_db =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 1 =A0 =A0 =A0 =
=A0 =A0|
> | Com_delete =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 4 =A0 =A0 =
=A0 =A0 =A0|
> | Com_insert =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 3 =A0 =A0 =
=A0 =A0 =A0|
> | Com_select =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 2 =A0 =A0 =
=A0 =A0 =A0|
> | Com_show_databases =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 1 =A0 =A0 =A0 =A0 =
=A0|
> | Com_show_fields =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 3 =A0 =A0 =A0 =A0=
=A0|
> | Com_show_status =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 2 =A0 =A0 =A0 =A0=
=A0|
> | Com_show_tables =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 1 =A0 =A0 =A0 =A0=
=A0|
> | Compression =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | OFF =A0 =A0 =
=A0 =A0|
> | Connections =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 276096 =A0 =
=A0 |
> | Created_tmp_files =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 5 =A0 =A0 =A0 =A0 =
=A0|
> | Created_tmp_tables =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 4 =A0 =A0 =A0 =A0 =
=A0|
> | Flush_commands =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 1 =A0 =A0 =A0 =
=A0 =A0|
> | Handler_commit =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 14 =A0 =A0 =A0 =
=A0 |
> | Handler_prepare =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 14 =A0 =A0 =A0 =
=A0 |
> | Handler_read_key =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 8 =A0 =A0 =A0 =A0=
=A0|
> | Handler_read_rnd_next =A0 =A0 =A0 =A0 =A0 =A0 | 263 =A0 =A0 =A0 =A0|
> | Handler_write =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 395 =A0 =A0 =A0=
=A0|
> | Innodb_buffer_pool_pages_data =A0 =A0 | 6019 =A0 =A0 =A0 |
> | Innodb_buffer_pool_pages_dirty =A0 =A0| 1858 =A0 =A0 =A0 |
> | Innodb_buffer_pool_pages_flushed =A0| 593993 =A0 =A0 |
> | Innodb_buffer_pool_pages_free =A0 =A0 | 15784 =A0 =A0 =A0|
> | Innodb_buffer_pool_pages_misc =A0 =A0 | 597 =A0 =A0 =A0 =A0|
> | Innodb_buffer_pool_pages_total =A0 =A0| 22400 =A0 =A0 =A0|
> | Innodb_buffer_pool_read_ahead_rnd | 1 =A0 =A0 =A0 =A0 =A0|
> | Innodb_buffer_pool_read_requests =A0| 42797013 =A0 |
> | Innodb_buffer_pool_reads =A0 =A0 =A0 =A0 =A0| 3497 =A0 =A0 =A0 |
> | Innodb_buffer_pool_write_requests | 19096507 =A0 |
> | Innodb_data_fsyncs =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 4319683 =A0 =A0|
> | Innodb_data_pending_fsyncs =A0 =A0 =A0 =A0| 1 =A0 =A0 =A0 =A0 =A0|
> | Innodb_data_read =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 60231680 =A0 |
> | Innodb_data_reads =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 3514 =A0 =A0 =A0 |
> | Innodb_data_writes =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 4496721 =A0 =A0|
> | Innodb_data_written =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 1259458560 |
> | Innodb_dblwr_pages_written =A0 =A0 =A0 =A0| 593993 =A0 =A0 |
> | Innodb_dblwr_writes =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 12967 =A0 =A0 =A0|
> | Innodb_log_write_requests =A0 =A0 =A0 =A0 | 2111208 =A0 =A0|
> | Innodb_log_writes =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 4285654 =A0 =A0|
> | Innodb_os_log_fsyncs =A0 =A0 =A0 =A0 =A0 =A0 =A0| 4303114 =A0 =A0|
> | Innodb_os_log_pending_fsyncs =A0 =A0 =A0| 1 =A0 =A0 =A0 =A0 =A0|
> | Innodb_os_log_written =A0 =A0 =A0 =A0 =A0 =A0 | 3264897024 |
> | Innodb_page_size =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 16384 =A0 =A0 =A0=
|
> | Innodb_pages_created =A0 =A0 =A0 =A0 =A0 =A0 =A0| 2476 =A0 =A0 =A0 |
> | Innodb_pages_read =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 3543 =A0 =A0 =A0 |
> | Innodb_pages_written =A0 =A0 =A0 =A0 =A0 =A0 =A0| 593993 =A0 =A0 |
> | Innodb_row_lock_time =A0 =A0 =A0 =A0 =A0 =A0 =A0| 1339668 =A0 =A0|
> | Innodb_row_lock_time_avg =A0 =A0 =A0 =A0 =A0| 379 =A0 =A0 =A0 =A0|
> | Innodb_row_lock_time_max =A0 =A0 =A0 =A0 =A0| 10631 =A0 =A0 =A0|
> | Innodb_row_lock_waits =A0 =A0 =A0 =A0 =A0 =A0 | 3531 =A0 =A0 =A0 |
> | Innodb_rows_deleted =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 31904 =A0 =A0 =A0|
> | Innodb_rows_inserted =A0 =A0 =A0 =A0 =A0 =A0 =A0| 530870 =A0 =A0 |
> | Innodb_rows_read =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 7885336 =A0 =A0|
> | Innodb_rows_updated =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 2100083 =A0 =A0|
> | Key_blocks_unused =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 7159 =A0 =A0 =A0 |
> | Key_blocks_used =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 14 =A0 =A0 =A0 =
=A0 |
> | Key_read_requests =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 106 =A0 =A0 =A0 =A0=
|
> | Key_reads =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 14 =A0 =A0 =
=A0 =A0 |
> | Last_query_cost =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 10.499000 =A0|
> | Max_used_connections =A0 =A0 =A0 =A0 =A0 =A0 =A0| 66 =A0 =A0 =A0 =A0 |
> | Ndb_config_from_host =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0 =A0 =A0 =
=A0|
> | Open_files =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 54 =A0 =A0 =
=A0 =A0 |
> | Open_tables =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 126 =A0 =A0 =
=A0 =A0|
> | Qcache_free_blocks =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 1 =A0 =A0 =A0 =A0 =
=A0|
> | Qcache_hits =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 18 =A0 =A0 =
=A0 =A0 |
> | Qcache_inserts =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 595 =A0 =A0 =A0=
=A0|
> | Qcache_not_cached =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 7611 =A0 =A0 =A0 |
> | Questions =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 12971115 =
=A0 |
> | Rpl_status =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| NULL =A0 =
=A0 =A0 |
> | Select_scan =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 4 =A0 =A0 =A0=
=A0 =A0|
> | Slave_running =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | OFF =A0 =A0 =A0=
=A0|
> | Ssl_cipher =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| =A0 =A0 =A0=
=A0 =A0 =A0|
> | Ssl_cipher_list =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =A0 =
=A0 =A0|
> | Ssl_session_cache_mode =A0 =A0 =A0 =A0 =A0 =A0| NONE =A0 =A0 =A0 |
> | Ssl_version =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | =A0 =A0 =A0 =
=A0 =A0 =A0|
> | Table_locks_immediate =A0 =A0 =A0 =A0 =A0 =A0 | 4927708 =A0 =A0|
> | Table_locks_waited =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 67 =A0 =A0 =A0 =A0 =
|
> | Threads_cached =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 10 =A0 =A0 =A0 =
=A0 |
> | Threads_connected =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 42 =A0 =A0 =A0 =A0 =
|
> | Threads_created =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 4133 =A0 =A0 =A0 =
|
> | Threads_running =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 | 36 =A0 =A0 =A0 =
=A0 |
> | Uptime =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0| 120796 =
=A0 =A0 |
> | Uptime_since_flush_status =A0 =A0 =A0 =A0 | 120796 =A0 =A0 |
> +-----------------------------------+------------+
> 87 rows in set (0.01 sec)
>
> ###############################
>
> ==================== =====
=============3D
> 100902 12:39:47 INNODB MONITOR OUTPUT
> ==================== =====
=============3D
> Per second averages calculated from the last 19 seconds
> ----------
> SEMAPHORES
> ----------
> OS WAIT ARRAY INFO: reservation count 63731, signal count 62253
> Mutex spin waits 0, rounds 1686893, OS waits 41516
> RW-shared spins 23291, OS waits 11428; RW-excl spins 4076, OS waits 3446
> ------------
> TRANSACTIONS
> ------------
> Trx id counter 0 1953324058
> Purge done for trx's n:o < 0 1953323526 undo n:o < 0 0
> History list length 172
> Total number of lock structs in row lock hash table 9
> LIST OF TRANSACTIONS FOR EACH SESSION:
> ---TRANSACTION 0 0, not started, OS thread id 4121 waiting in InnoDB queu=
e
> mysql tables in use 1, locked 1
> MySQL thread id 276150, query id 12973147 192.168.87.6 myuser_adm Sorting
> result
> SELECT********************
> ---TRANSACTION 0 0, not started, OS thread id 4119 waiting in InnoDB queu=
e
> mysql tables in use 1, locked 0
> MySQL thread id 276149, query id 12973129 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 0, not started, OS thread id 4067 waiting in InnoDB queu=
e
> mysql tables in use 1, locked 0
> MySQL thread id 276148, query id 12973102 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953324017, not started, OS thread id 4098 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276144, query id 12973093 192.168.87.6 myuser_adm Updatin=
g
> UPDATE********************
> ---TRANSACTION 0 1953323942, not started, OS thread id 4140
> MySQL thread id 276135, query id 12972947 192.168.87.6 myuser_adm
> ---TRANSACTION 0 1953324045, not started, OS thread id 4074 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276133, query id 12973157 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324021, not started, OS thread id 4038
> MySQL thread id 276130, query id 12973043 192.168.87.6 myuser_adm
> ---TRANSACTION 0 1953323971, not started, OS thread id 3873 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276129, query id 12973112 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953324024, not started, OS thread id 4145 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276124, query id 12973159 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953324027, not started, OS thread id 4003
> MySQL thread id 276111, query id 12973050 192.168.87.6 myuser_adm
> ---TRANSACTION 0 1953323945, not started, OS thread id 3918 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276107, query id 12973115 192.168.87.6 myuser_adm Sorting
> result
> SELECT********************
> ---TRANSACTION 0 1953324023, not started, OS thread id 4089 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276093, query id 12973106 192.168.87.6 myuser_adm Updatin=
g
> UPDATE********************
> ---TRANSACTION 0 1953323946, not started, OS thread id 4102
> MySQL thread id 276081, query id 12972959 192.168.87.6 myuser_adm
> ---TRANSACTION 0 1953324029, not started, OS thread id 4143 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276079, query id 12973167 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953324048, not started, OS thread id 3945 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275987, query id 12973161 192.168.87.6 myuser_adm Updatin=
g
> UPDATE********************
> ---TRANSACTION 0 1953324026, not started, OS thread id 4048 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275976, query id 12973110 192.168.87.6 myuser_adm Updatin=
g
> UPDATE********************
> ---TRANSACTION 0 1953324040, not started, OS thread id 4142 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275975, query id 12973154 192.168.87.6 myuser_adm Updatin=
g
> UPDATE********************
> ---TRANSACTION 0 1953323944, not started, OS thread id 3979 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275946, query id 12973121 192.168.87.6 myuser_adm Sorting
> result
> SELECT********************
> ---TRANSACTION 0 1953324016, not started, OS thread id 4058 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275931, query id 12973092 192.168.87.6 myuser_adm Updatin=
g
> UPDATE********************
> ---TRANSACTION 0 1953324015, not started, OS thread id 4065 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275913, query id 12973090 192.168.87.6 myuser_adm Updatin=
g
> UPDATE********************
> ---TRANSACTION 0 1953324033, not started, OS thread id 4111 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275907, query id 12973137 192.168.87.6 myuser_adm Updatin=
g
> UPDATE********************
> ---TRANSACTION 0 1953323993, not started, OS thread id 4078 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275889, query id 12973132 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953324011, not started, OS thread id 4136 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275887, query id 12973089 192.168.87.6 myuser_adm Updatin=
g
> UPDATE********************
> ---TRANSACTION 0 1953324034, not started, OS thread id 4014 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275888, query id 12973166 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953323997, not started, OS thread id 4123 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275856, query id 12973133 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953324002, not started, OS thread id 3999 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275827, query id 12973151 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953323985, not started, OS thread id 4115 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275809, query id 12973105 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953324036, not started, OS thread id 4133 sleeping befo=
re
> joining InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275784, query id 12973169 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953324031, not started, OS thread id 4134 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275715, query id 12973136 192.168.87.6 myuser_adm Updatin=
g
> UPDATE********************
> ---TRANSACTION 0 1953324000, not started, OS thread id 4124 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275665, query id 12973149 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953324009, not started, OS thread id 4130 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275539, query id 12973155 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953323988, not started, OS thread id 4010 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275263, query id 12973108 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953323990, not started, OS thread id 4086 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 274948, query id 12973123 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953324032, not started, OS thread id 3934 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 274703, query id 12973163 192.168.87.6 myuser_adm statist=
ics
> SELECT********************
> ---TRANSACTION 0 1953292243, not started, OS thread id 4046
> MySQL thread id 274066, query id 12973170 localhost root
> show innodb status
> ---TRANSACTION 0 1953324057, ACTIVE 0 sec, OS thread id 4127, thread
> declared inside InnoDB 498
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 276140, query id 12973085 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324055, ACTIVE 0 sec, OS thread id 4144, thread
> declared inside InnoDB 498
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 276147, query id 12973082 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324053, ACTIVE 0 sec, OS thread id 4141, thread
> declared inside InnoDB 499
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 275720, query id 12973078 192.168.87.6 myuser_adm end
> UPDATE********************
> ---TRANSACTION 0 1953324051, ACTIVE 0 sec, OS thread id 4029, thread
> declared inside InnoDB 499
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 273472, query id 12973075 192.168.87.6 myuser_adm end
> UPDATE********************
> ---TRANSACTION 0 1953324049, ACTIVE 1 sec, OS thread id 4109, thread
> declared inside InnoDB 499
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 274973, query id 12973084 192.168.87.6 myuser_adm end
> UPDATE********************
> ---TRANSACTION 0 1953324046, ACTIVE 1 sec, OS thread id 4126, thread
> declared inside InnoDB 498
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 276146, query id 12973081 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324043, ACTIVE 1 sec, OS thread id 4122, thread
> declared inside InnoDB 499
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 274989, query id 12973069 192.168.87.6 myuser_adm end
> UPDATE********************
> ---TRANSACTION 0 1953324041, ACTIVE 1 sec, OS thread id 4099, thread
> declared inside InnoDB 498
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 276145, query id 12973066 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324038, ACTIVE (PREPARED) 1 sec, OS thread id 4077
> preparing
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 275321, query id 12973067 192.168.87.6 myuser_adm end
> UPDATE********************
> --------
> 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,
> =A0ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
> Pending flushes (fsync) log: 1; buffer pool: 0
> 3514 OS file reads, 4497412 OS file writes, 4320374 OS fsyncs
> 0.00 reads/s, 0 avg bytes/read, 41.05 writes/s, 40.73 fsyncs/s
> -------------------------------------
> INSERT BUFFER AND ADAPTIVE HASH INDEX
> -------------------------------------
> Ibuf: size 1, free list len 0, seg size 2,
> 0 inserts, 0 merged recs, 0 merges
> Hash table size 1452727, used cells 496505, node heap has 597 buffer(s)
> 31.26 hash searches/s, 15.31 non-hash searches/s
> ---
> LOG
> ---
> Log sequence number 61 3783563525
> Log flushed up to =A0 61 3783563173
> Last checkpoint at =A061 3778869606
> 1 pending log writes, 0 pending chkp writes
> 4297652 log i/o's done, 40.63 log i/o's/second
> ----------------------
> BUFFER POOL AND MEMORY
> ----------------------
> Total memory allocated 419047082; in additional pool allocated 2578048
> Buffer pool size =A0 22400
> Free buffers =A0 =A0 =A0 15784
> Database pages =A0 =A0 6019
> Modified db pages =A01895
> Pending reads 0
> Pending writes: LRU 0, flush list 0, single page 0
> Pages read 3543, created 2476, written 594057
> 0.00 reads/s, 0.00 creates/s, 6.47 writes/s
> Buffer pool hit rate 1000 / 1000
> --------------
> ROW OPERATIONS
> --------------
> 8 queries inside InnoDB, 29 queries in queue
> 1 read views open inside InnoDB
> Main thread id 11, state: sleeping
> Number of rows inserted 530873, updated 2100423, deleted 31904, read 7886=
015
> 0.21 inserts/s, 19.95 updates/s, 0.00 deletes/s, 40.05 reads/s
> ----------------------------
> END OF INNODB MONITOR OUTPUT
> ==================== =====
====
>
> ###############################
>

--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Performance problems on MySQL

am 02.09.2010 14:52:56 von John Daisley

--000feaf115700d5569048f464ba7
Content-Type: text/plain; charset=ISO-8859-1

What is the hardware spec? Anything else running on the box?

Why are you replicating but not making use of the slave?

Can you post the output of SHOW CREATE TABLE?

Regards
John

On 2 September 2010 12:50, Alexandre Vieira wrote:

> Hi list,
>
> I'm having some performance problems on my 5.0.45-log DB running on Solaris
> 8 (V240).
>
> We only have one table and two apps selecting, updating, inserting and
> deleting massively and randomly from this table.
>
> The table is very simple. All SELECTs,INSERTs,UPDATEs and DELETEs have only
> one condition on an unique varchar indexed column.
>
> The table has 500k records and has been OPTIMIZED 32h ago.
>
> I've ran some sampling and:
>
> A SELECT costs between 400ms and 600ms.
> An UPDATE costs between 800ms and 1300ms.
> A DELETE costs between 900ms and 1300ms
> An INSERT costs always 900ms 2000ms.
>
> At any given time the DB is handling 60-80 operations every second. It does
> not scale any more than this because all the application connections to the
> DB are being used and waiting for the DB to move. Our application queues
> requests and it lags our clients.
>
> The perl mysqltuner only whines about "Query cache disabled" but since I
> get
> an ~20 updates every second I can't get any query cache hits, so I disabled
> it.
>
> If it makes any difference, we're replicating everything to another server
> that don't serve any queries.
>
> The DB has a 32 hour uptime.
>
> Any help is most welcome.
>
> You can find my.cnf, show status and show innodb status below.
>
> Kind regards
> Alex
>
> ###############################
> my.cnf:
>
> sql-mode ="STRICT_ALL_TABLES"
> old_passwords =1
> skip-bdb
> max_connections =100
> max_allowed_packet =1M
> table_cache =512
> sort_buffer_size =2M
> read_buffer_size =4M
> read_rnd_buffer_size =8M
> thread_cache_size =16
> query_cache_limit =32M
> thread_concurrency =8
> max_heap_table_size =28M
> tmp_table_size =12M
> innodb_buffer_pool_size =350M
> innodb_additional_mem_pool_size =15M
> innodb_log_buffer_size =6M
> innodb_flush_log_at_trx_commit =1
> innodb_lock_wait_timeout =50
>
> ###############################
>
> mysql> show status where Value NOT LIKE 0;
> +-----------------------------------+------------+
> | Variable_name | Value |
> +-----------------------------------+------------+
> | Aborted_clients | 88 |
> | Aborted_connects | 37590 |
> | Binlog_cache_use | 2148392 |
> | Bytes_received | 1117 |
> | Bytes_sent | 8772 |
> | Com_change_db | 1 |
> | Com_delete | 4 |
> | Com_insert | 3 |
> | Com_select | 2 |
> | Com_show_databases | 1 |
> | Com_show_fields | 3 |
> | Com_show_status | 2 |
> | Com_show_tables | 1 |
> | Compression | OFF |
> | Connections | 276096 |
> | Created_tmp_files | 5 |
> | Created_tmp_tables | 4 |
> | Flush_commands | 1 |
> | Handler_commit | 14 |
> | Handler_prepare | 14 |
> | Handler_read_key | 8 |
> | Handler_read_rnd_next | 263 |
> | Handler_write | 395 |
> | Innodb_buffer_pool_pages_data | 6019 |
> | Innodb_buffer_pool_pages_dirty | 1858 |
> | Innodb_buffer_pool_pages_flushed | 593993 |
> | Innodb_buffer_pool_pages_free | 15784 |
> | Innodb_buffer_pool_pages_misc | 597 |
> | Innodb_buffer_pool_pages_total | 22400 |
> | Innodb_buffer_pool_read_ahead_rnd | 1 |
> | Innodb_buffer_pool_read_requests | 42797013 |
> | Innodb_buffer_pool_reads | 3497 |
> | Innodb_buffer_pool_write_requests | 19096507 |
> | Innodb_data_fsyncs | 4319683 |
> | Innodb_data_pending_fsyncs | 1 |
> | Innodb_data_read | 60231680 |
> | Innodb_data_reads | 3514 |
> | Innodb_data_writes | 4496721 |
> | Innodb_data_written | 1259458560 |
> | Innodb_dblwr_pages_written | 593993 |
> | Innodb_dblwr_writes | 12967 |
> | Innodb_log_write_requests | 2111208 |
> | Innodb_log_writes | 4285654 |
> | Innodb_os_log_fsyncs | 4303114 |
> | Innodb_os_log_pending_fsyncs | 1 |
> | Innodb_os_log_written | 3264897024 |
> | Innodb_page_size | 16384 |
> | Innodb_pages_created | 2476 |
> | Innodb_pages_read | 3543 |
> | Innodb_pages_written | 593993 |
> | Innodb_row_lock_time | 1339668 |
> | Innodb_row_lock_time_avg | 379 |
> | Innodb_row_lock_time_max | 10631 |
> | Innodb_row_lock_waits | 3531 |
> | Innodb_rows_deleted | 31904 |
> | Innodb_rows_inserted | 530870 |
> | Innodb_rows_read | 7885336 |
> | Innodb_rows_updated | 2100083 |
> | Key_blocks_unused | 7159 |
> | Key_blocks_used | 14 |
> | Key_read_requests | 106 |
> | Key_reads | 14 |
> | Last_query_cost | 10.499000 |
> | Max_used_connections | 66 |
> | Ndb_config_from_host | |
> | Open_files | 54 |
> | Open_tables | 126 |
> | Qcache_free_blocks | 1 |
> | Qcache_hits | 18 |
> | Qcache_inserts | 595 |
> | Qcache_not_cached | 7611 |
> | Questions | 12971115 |
> | Rpl_status | NULL |
> | Select_scan | 4 |
> | Slave_running | OFF |
> | Ssl_cipher | |
> | Ssl_cipher_list | |
> | Ssl_session_cache_mode | NONE |
> | Ssl_version | |
> | Table_locks_immediate | 4927708 |
> | Table_locks_waited | 67 |
> | Threads_cached | 10 |
> | Threads_connected | 42 |
> | Threads_created | 4133 |
> | Threads_running | 36 |
> | Uptime | 120796 |
> | Uptime_since_flush_status | 120796 |
> +-----------------------------------+------------+
> 87 rows in set (0.01 sec)
>
> ###############################
>
> =====================================
> 100902 12:39:47 INNODB MONITOR OUTPUT
> =====================================
> Per second averages calculated from the last 19 seconds
> ----------
> SEMAPHORES
> ----------
> OS WAIT ARRAY INFO: reservation count 63731, signal count 62253
> Mutex spin waits 0, rounds 1686893, OS waits 41516
> RW-shared spins 23291, OS waits 11428; RW-excl spins 4076, OS waits 3446
> ------------
> TRANSACTIONS
> ------------
> Trx id counter 0 1953324058
> Purge done for trx's n:o < 0 1953323526 undo n:o < 0 0
> History list length 172
> Total number of lock structs in row lock hash table 9
> LIST OF TRANSACTIONS FOR EACH SESSION:
> ---TRANSACTION 0 0, not started, OS thread id 4121 waiting in InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276150, query id 12973147 192.168.87.6 myuser_adm Sorting
> result
> SELECT********************
> ---TRANSACTION 0 0, not started, OS thread id 4119 waiting in InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276149, query id 12973129 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 0, not started, OS thread id 4067 waiting in InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276148, query id 12973102 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953324017, not started, OS thread id 4098 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276144, query id 12973093 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953323942, not started, OS thread id 4140
> MySQL thread id 276135, query id 12972947 192.168.87.6 myuser_adm
> ---TRANSACTION 0 1953324045, not started, OS thread id 4074 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276133, query id 12973157 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324021, not started, OS thread id 4038
> MySQL thread id 276130, query id 12973043 192.168.87.6 myuser_adm
> ---TRANSACTION 0 1953323971, not started, OS thread id 3873 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276129, query id 12973112 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953324024, not started, OS thread id 4145 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276124, query id 12973159 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953324027, not started, OS thread id 4003
> MySQL thread id 276111, query id 12973050 192.168.87.6 myuser_adm
> ---TRANSACTION 0 1953323945, not started, OS thread id 3918 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276107, query id 12973115 192.168.87.6 myuser_adm Sorting
> result
> SELECT********************
> ---TRANSACTION 0 1953324023, not started, OS thread id 4089 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 276093, query id 12973106 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953323946, not started, OS thread id 4102
> MySQL thread id 276081, query id 12972959 192.168.87.6 myuser_adm
> ---TRANSACTION 0 1953324029, not started, OS thread id 4143 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 276079, query id 12973167 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953324048, not started, OS thread id 3945 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275987, query id 12973161 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953324026, not started, OS thread id 4048 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275976, query id 12973110 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953324040, not started, OS thread id 4142 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275975, query id 12973154 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953323944, not started, OS thread id 3979 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275946, query id 12973121 192.168.87.6 myuser_adm Sorting
> result
> SELECT********************
> ---TRANSACTION 0 1953324016, not started, OS thread id 4058 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275931, query id 12973092 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953324015, not started, OS thread id 4065 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275913, query id 12973090 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953324033, not started, OS thread id 4111 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275907, query id 12973137 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953323993, not started, OS thread id 4078 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275889, query id 12973132 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953324011, not started, OS thread id 4136 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275887, query id 12973089 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953324034, not started, OS thread id 4014 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275888, query id 12973166 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953323997, not started, OS thread id 4123 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275856, query id 12973133 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953324002, not started, OS thread id 3999 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275827, query id 12973151 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953323985, not started, OS thread id 4115 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275809, query id 12973105 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953324036, not started, OS thread id 4133 sleeping before
> joining InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275784, query id 12973169 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953324031, not started, OS thread id 4134 waiting in
> InnoDB queue
> mysql tables in use 1, locked 1
> MySQL thread id 275715, query id 12973136 192.168.87.6 myuser_adm Updating
> UPDATE********************
> ---TRANSACTION 0 1953324000, not started, OS thread id 4124 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275665, query id 12973149 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953324009, not started, OS thread id 4130 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275539, query id 12973155 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953323988, not started, OS thread id 4010 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 275263, query id 12973108 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953323990, not started, OS thread id 4086 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 274948, query id 12973123 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953324032, not started, OS thread id 3934 waiting in
> InnoDB queue
> mysql tables in use 1, locked 0
> MySQL thread id 274703, query id 12973163 192.168.87.6 myuser_adm
> statistics
> SELECT********************
> ---TRANSACTION 0 1953292243, not started, OS thread id 4046
> MySQL thread id 274066, query id 12973170 localhost root
> show innodb status
> ---TRANSACTION 0 1953324057, ACTIVE 0 sec, OS thread id 4127, thread
> declared inside InnoDB 498
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 276140, query id 12973085 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324055, ACTIVE 0 sec, OS thread id 4144, thread
> declared inside InnoDB 498
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 276147, query id 12973082 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324053, ACTIVE 0 sec, OS thread id 4141, thread
> declared inside InnoDB 499
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 275720, query id 12973078 192.168.87.6 myuser_adm end
> UPDATE********************
> ---TRANSACTION 0 1953324051, ACTIVE 0 sec, OS thread id 4029, thread
> declared inside InnoDB 499
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 273472, query id 12973075 192.168.87.6 myuser_adm end
> UPDATE********************
> ---TRANSACTION 0 1953324049, ACTIVE 1 sec, OS thread id 4109, thread
> declared inside InnoDB 499
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 274973, query id 12973084 192.168.87.6 myuser_adm end
> UPDATE********************
> ---TRANSACTION 0 1953324046, ACTIVE 1 sec, OS thread id 4126, thread
> declared inside InnoDB 498
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 276146, query id 12973081 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324043, ACTIVE 1 sec, OS thread id 4122, thread
> declared inside InnoDB 499
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 274989, query id 12973069 192.168.87.6 myuser_adm end
> UPDATE********************
> ---TRANSACTION 0 1953324041, ACTIVE 1 sec, OS thread id 4099, thread
> declared inside InnoDB 498
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 276145, query id 12973066 192.168.87.6 myuser_adm update
> INSERT********************
> ---TRANSACTION 0 1953324038, ACTIVE (PREPARED) 1 sec, OS thread id 4077
> preparing
> mysql tables in use 1, locked 1
> 2 lock struct(s), heap size 320, undo log entries 1
> MySQL thread id 275321, query id 12973067 192.168.87.6 myuser_adm end
> UPDATE********************
> --------
> 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: 1; buffer pool: 0
> 3514 OS file reads, 4497412 OS file writes, 4320374 OS fsyncs
> 0.00 reads/s, 0 avg bytes/read, 41.05 writes/s, 40.73 fsyncs/s
> -------------------------------------
> INSERT BUFFER AND ADAPTIVE HASH INDEX
> -------------------------------------
> Ibuf: size 1, free list len 0, seg size 2,
> 0 inserts, 0 merged recs, 0 merges
> Hash table size 1452727, used cells 496505, node heap has 597 buffer(s)
> 31.26 hash searches/s, 15.31 non-hash searches/s
> ---
> LOG
> ---
> Log sequence number 61 3783563525
> Log flushed up to 61 3783563173
> Last checkpoint at 61 3778869606
> 1 pending log writes, 0 pending chkp writes
> 4297652 log i/o's done, 40.63 log i/o's/second
> ----------------------
> BUFFER POOL AND MEMORY
> ----------------------
> Total memory allocated 419047082; in additional pool allocated 2578048
> Buffer pool size 22400
> Free buffers 15784
> Database pages 6019
> Modified db pages 1895
> Pending reads 0
> Pending writes: LRU 0, flush list 0, single page 0
> Pages read 3543, created 2476, written 594057
> 0.00 reads/s, 0.00 creates/s, 6.47 writes/s
> Buffer pool hit rate 1000 / 1000
> --------------
> ROW OPERATIONS
> --------------
> 8 queries inside InnoDB, 29 queries in queue
> 1 read views open inside InnoDB
> Main thread id 11, state: sleeping
> Number of rows inserted 530873, updated 2100423, deleted 31904, read
> 7886015
> 0.21 inserts/s, 19.95 updates/s, 0.00 deletes/s, 40.05 reads/s
> ----------------------------
> END OF INNODB MONITOR OUTPUT
> ============================
>
> ###############################
>

--
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.daisley@butterflysystems.co.uk

--000feaf115700d5569048f464ba7--

Performance problems on MySQL

am 02.09.2010 16:46:07 von Alexandre Vieira

--0016361e84a8dd1495048f47dfb4
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

John, Johnny,

Thanks for the prompt answer.

mysql> SHOW CREATE TABLE clientinfo;
+------------+---------------------------------------------- ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------+
| Table | Create
Table
|
+------------+---------------------------------------------- ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------+
| clientinfo | CREATE TABLE `clientinfo` (
`userid` varchar(21) NOT NULL default '',
`units` float default NULL,
`date_last_query` datetime default NULL,
`last_acc` int(10) unsigned default NULL,
`date_last_units` datetime default NULL,
`notification` int(10) unsigned NOT NULL default '0',
`package` char(1) default NULL,
`user_type` varchar(5) default NULL,
PRIMARY KEY (`userid`)
) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1 |
+------------+---------------------------------------------- ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------+
1 row in set (0.00 sec)
mysql> SHOW INDEX FROM clientinfo;
+------------+------------+----------+--------------+------- ------+--------=
---+-------------+----------+--------+------+------------+-- -------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+------- ------+--------=
---+-------------+----------+--------+------+------------+-- -------+
| clientinfo | 0 | PRIMARY | 1 | userid |
A | 460056 | NULL | NULL | | BTREE | |
+------------+------------+----------+--------------+------- ------+--------=
---+-------------+----------+--------+------+------------+-- -------+
1 row in set (0.00 sec)

SELECT * FROM clientinfo WHERE userid=3D'182106617';

UPDATE clientinfo SET
units=3D0.0,date_last_query=3Dnow(),user_type=3D'POS',last_a cc=3D167,date_l=
ast_units=3Dnow(),notification=3D0
WHERE userid=3D'152633876';

INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE')
ON DUPLICATE KEY UPDATE units=3D101.0, date_last_query=3Dnow(), last_acc=3D=
1,
date_last_units=3Dnow(), notification=3D0, package=3D'D', user_type=3D'PRE'=
;

DELETE FROM clientinfo WHERE units=3D'155618918';

There are no other type of queries.

We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.

We also run some other applications in the server, but nothing that consume=
s
all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
idle CPU time at any time.

TIA

BR
Alex

On Thu, Sep 2, 2010 at 1:52 PM, John Daisley wr=
ote:

> What is the hardware spec? Anything else running on the box?
>
> Why are you replicating but not making use of the slave?
>
> Can you post the output of SHOW CREATE TABLE?
>
> Regards
> John
>
>
>

--0016361e84a8dd1495048f47dfb4--

Re: Performance problems on MySQL

am 02.09.2010 17:47:52 von Jangita

On 02/09/2010 4:46 p, Alexandre Vieira wrote:
> John, Johnny,
>
> Thanks for the prompt answer.
>
....
> We also run some other applications in the server, but nothing that consumes
> all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
> idle CPU time at any time.
>
> TIA
>
> BR
> Alex

Increase innodb_buffer_pool_size say to 1GB?
--
Jangita | +256 76 91 8383 | Y! & MSN: jangita@yahoo.com
Skype: jangita | GTalk: jangita.nyagudi@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Performance problems on MySQL

am 02.09.2010 18:05:40 von Alexandre Vieira

--000e0cd48340507fee048f48fc85
Content-Type: text/plain; charset=ISO-8859-1

Hi Jangita,

I'm 15779 innodb_buffer_pool_pages_free from a total of 22400. That's 246MB
of 350MB free.

| Innodb_buffer_pool_pages_data | 6020 |
| Innodb_buffer_pool_pages_dirty | 1837 |
| Innodb_buffer_pool_pages_flushed | 673837 |
| Innodb_buffer_pool_pages_free | 15779 |
| Innodb_buffer_pool_pages_latched | 0 |
| Innodb_buffer_pool_pages_misc | 601 |
| Innodb_buffer_pool_pages_total | 22400 |
| Innodb_buffer_pool_read_ahead_rnd | 1 |
| Innodb_buffer_pool_read_ahead_seq | 0 |
| Innodb_buffer_pool_read_requests | 48471963 |
| Innodb_buffer_pool_reads | 3497 |
| Innodb_buffer_pool_wait_free | 0 |
| Innodb_buffer_pool_write_requests | 21700478 |

Why would I need to increase?

Thanks

BR
AJ

On Thu, Sep 2, 2010 at 4:47 PM, Jangita wrote:

> On 02/09/2010 4:46 p, Alexandre Vieira wrote:
>
>> John, Johnny,
>>
>> Thanks for the prompt answer.
>>
>> ...
>
> We also run some other applications in the server, but nothing that
>> consumes
>> all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
>> idle CPU time at any time.
>>
>> TIA
>>
>> BR
>> Alex
>>
>
> Increase innodb_buffer_pool_size say to 1GB?
> --
> Jangita | +256 76 91 8383 | Y! & MSN: jangita@yahoo.com
> Skype: jangita | GTalk: jangita.nyagudi@gmail.com
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=nullpt@gmail.com
>
>

--
Alexandre Vieira - nullpt@gmail.com

--000e0cd48340507fee048f48fc85--

RE: Performance problems on MySQL

am 02.09.2010 18:23:58 von Travis Ard

Have you considered adding a secondary index on the units column for your
delete queries?

DELETE FROM clientinfo WHERE units='155618918';

-----Original Message-----
From: Alexandre Vieira [mailto:nullpt@gmail.com]
Sent: Thursday, September 02, 2010 8:46 AM
To: John Daisley; johnny@pixelated.net
Cc: mysql@lists.mysql.com
Subject: Performance problems on MySQL

John, Johnny,

Thanks for the prompt answer.

mysql> SHOW CREATE TABLE clientinfo;
+------------+---------------------------------------------- ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ----+
| Table | Create
Table
|
+------------+---------------------------------------------- ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ----+
| clientinfo | CREATE TABLE `clientinfo` (
`userid` varchar(21) NOT NULL default '',
`units` float default NULL,
`date_last_query` datetime default NULL,
`last_acc` int(10) unsigned default NULL,
`date_last_units` datetime default NULL,
`notification` int(10) unsigned NOT NULL default '0',
`package` char(1) default NULL,
`user_type` varchar(5) default NULL,
PRIMARY KEY (`userid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------+---------------------------------------------- ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ----+
1 row in set (0.00 sec)
mysql> SHOW INDEX FROM clientinfo;
+------------+------------+----------+--------------+------- ------+---------
--+-------------+----------+--------+------+------------+--- ------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+------- ------+---------
--+-------------+----------+--------+------+------------+--- ------+
| clientinfo | 0 | PRIMARY | 1 | userid |
A | 460056 | NULL | NULL | | BTREE | |
+------------+------------+----------+--------------+------- ------+---------
--+-------------+----------+--------+------+------------+--- ------+
1 row in set (0.00 sec)

SELECT * FROM clientinfo WHERE userid='182106617';

UPDATE clientinfo SET
units=0.0,date_last_query=now(),user_type='POS',last_acc=167 ,date_last_units
=now(),notification=0
WHERE userid='152633876';

INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE')
ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1,
date_last_units=now(), notification=0, package='D', user_type='PRE';

DELETE FROM clientinfo WHERE units='155618918';

There are no other type of queries.

We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.

We also run some other applications in the server, but nothing that consumes
all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
idle CPU time at any time.

TIA

BR
Alex

On Thu, Sep 2, 2010 at 1:52 PM, John Daisley
wrote:

> What is the hardware spec? Anything else running on the box?
>
> Why are you replicating but not making use of the slave?
>
> Can you post the output of SHOW CREATE TABLE?
>
> Regards
> John
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Performance problems on MySQL

am 02.09.2010 18:41:55 von Alexandre Vieira

--000e0cd308e8f85212048f497d97
Content-Type: text/plain; charset=ISO-8859-1

Hi Travis,

Sorry, bad copy/paste. That DELETE statement is wrong.

The application executes:

DELETE FROM clientinfo WHERE userid='xxxxxxxxx';

BR
AJ

On Thu, Sep 2, 2010 at 5:23 PM, Travis Ard wrote:

> Have you considered adding a secondary index on the units column for your
> delete queries?
>
> DELETE FROM clientinfo WHERE units='155618918';
>
> -----Original Message-----
> From: Alexandre Vieira [mailto:nullpt@gmail.com]
> Sent: Thursday, September 02, 2010 8:46 AM
> To: John Daisley; johnny@pixelated.net
> Cc: mysql@lists.mysql.com
> Subject: Performance problems on MySQL
>
> John, Johnny,
>
> Thanks for the prompt answer.
>
> mysql> SHOW CREATE TABLE clientinfo;
>
> +------------+---------------------------------------------- ----------------
>
> ------------------------------------------------------------ ----------------
>
> ------------------------------------------------------------ ----------------
>
> ------------------------------------------------------------ ----------------
>
> ------------------------------------------------------------ ----------------
> ------------------------------------------------------------ ----+
> | Table | Create
> Table
> |
>
> +------------+---------------------------------------------- ----------------
>
> ------------------------------------------------------------ ----------------
>
> ------------------------------------------------------------ ----------------
>
> ------------------------------------------------------------ ----------------
>
> ------------------------------------------------------------ ----------------
> ------------------------------------------------------------ ----+
> | clientinfo | CREATE TABLE `clientinfo` (
> `userid` varchar(21) NOT NULL default '',
> `units` float default NULL,
> `date_last_query` datetime default NULL,
> `last_acc` int(10) unsigned default NULL,
> `date_last_units` datetime default NULL,
> `notification` int(10) unsigned NOT NULL default '0',
> `package` char(1) default NULL,
> `user_type` varchar(5) default NULL,
> PRIMARY KEY (`userid`)
> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
>
> +------------+---------------------------------------------- ----------------
>
> ------------------------------------------------------------ ----------------
>
> ------------------------------------------------------------ ----------------
>
> ------------------------------------------------------------ ----------------
>
> ------------------------------------------------------------ ----------------
> ------------------------------------------------------------ ----+
> 1 row in set (0.00 sec)
> mysql> SHOW INDEX FROM clientinfo;
>
> +------------+------------+----------+--------------+------- ------+---------
> --+-------------+----------+--------+------+------------+--- ------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
>
> +------------+------------+----------+--------------+------- ------+---------
> --+-------------+----------+--------+------+------------+--- ------+
> | clientinfo | 0 | PRIMARY | 1 | userid |
> A | 460056 | NULL | NULL | | BTREE | |
>
> +------------+------------+----------+--------------+------- ------+---------
> --+-------------+----------+--------+------+------------+--- ------+
> 1 row in set (0.00 sec)
>
>
> SELECT * FROM clientinfo WHERE userid='182106617';
>
> UPDATE clientinfo SET
>
> units=0.0,date_last_query=now(),user_type='POS',last_acc=167 ,date_last_units
> =now(),notification=0
> WHERE userid='152633876';
>
> INSERT INTO clientinfo VALUES ('171918726',101.0,now(),1,now(),0,'D','PRE')
> ON DUPLICATE KEY UPDATE units=101.0, date_last_query=now(), last_acc=1,
> date_last_units=now(), notification=0, package='D', user_type='PRE';
>
> DELETE FROM clientinfo WHERE units='155618918';
>
> There are no other type of queries.
>
> We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.
>
> We also run some other applications in the server, but nothing that
> consumes
> all the CPU/Memory. The machine has almost 1GB of free memory and 50% of
> idle CPU time at any time.
>
> TIA
>
> BR
> Alex
>
>
> On Thu, Sep 2, 2010 at 1:52 PM, John Daisley
> wrote:
>
> > What is the hardware spec? Anything else running on the box?
> >
> > Why are you replicating but not making use of the slave?
> >
> > Can you post the output of SHOW CREATE TABLE?
> >
> > Regards
> > John
> >
> >
> >
>
>

--
Alexandre Vieira - nullpt@gmail.com

--000e0cd308e8f85212048f497d97--

Re: Performance problems on MySQL

am 03.09.2010 11:44:36 von Alexandre Vieira

--0016363b882c5feeb0048f57c7ee
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Johnny,

mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid=3D'911930694';
+----+-------------+------------+-------+---------------+--- ------+--------=
-+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len
| ref | rows | Extra |
+----+-------------+------------+-------+---------------+--- ------+--------=
-+-------+------+-------+
| 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23
| const | 1 | |
+----+-------------+------------+-------+---------------+--- ------+--------=
-+-------+------+-------+
1 row in set (0.53 sec)

Thanks

BR
AJ

On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers wrote=
:

> What about an explain of this query:
>
>
> SELECT * FROM clientinfo WHERE userid=3D'182106617';
>
> -JW
>
>
> On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira wrote=
:
>
>> John, Johnny,
>>
>> Thanks for the prompt answer.
>>
>> mysql> SHOW CREATE TABLE clientinfo;
>>
>> +------------+---------------------------------------------- ------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ------------+
>> | Table | Create
>> Table
>> |
>>
>> +------------+---------------------------------------------- ------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ------------+
>> | clientinfo | CREATE TABLE `clientinfo` (
>> `userid` varchar(21) NOT NULL default '',
>> `units` float default NULL,
>> `date_last_query` datetime default NULL,
>> `last_acc` int(10) unsigned default NULL,
>> `date_last_units` datetime default NULL,
>> `notification` int(10) unsigned NOT NULL default '0',
>> `package` char(1) default NULL,
>> `user_type` varchar(5) default NULL,
>> PRIMARY KEY (`userid`)
>> ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1 |
>>
>> +------------+---------------------------------------------- ------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ------------+
>> 1 row in set (0.00 sec)
>> mysql> SHOW INDEX FROM clientinfo;
>>
>> +------------+------------+----------+--------------+------- ------+-----=
------+-------------+----------+--------+------+------------ +---------+
>> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
>> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Commen=
t |
>>
>> +------------+------------+----------+--------------+------- ------+-----=
------+-------------+----------+--------+------+------------ +---------+
>> | clientinfo | 0 | PRIMARY | 1 | userid |
>> A | 460056 | NULL | NULL | | BTREE | =
|
>>
>> +------------+------------+----------+--------------+------- ------+-----=
------+-------------+----------+--------+------+------------ +---------+
>> 1 row in set (0.00 sec)
>>
>>
>> SELECT * FROM clientinfo WHERE userid=3D'182106617';
>>
>> UPDATE clientinfo SET
>> units=3D0.0,date_last_query=3Dnow(),user_type=3D'POS',last_a cc=3D167,dat=
e_last_units=3Dnow(),notification=3D0
>> WHERE userid=3D'152633876';
>>
>> INSERT INTO clientinfo VALUES
>> ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE
>> units=3D101.0, date_last_query=3Dnow(), last_acc=3D1, date_last_units=3D=
now(),
>> notification=3D0, package=3D'D', user_type=3D'PRE';
>>
>> DELETE FROM clientinfo WHERE units=3D'155618918';
>>
>> There are no other type of queries.
>>
>> We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM=
..
>>
>> We also run some other applications in the server, but nothing that
>> consumes all the CPU/Memory. The machine has almost 1GB of free memory a=
nd
>> 50% of idle CPU time at any time.
>>
>> TIA
>>
>> BR
>> Alex
>>
>>
>> --
>> Alexandre Vieira - nullpt@gmail.com
>>
>>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>

--=20
Alexandre Vieira - nullpt@gmail.com

--0016363b882c5feeb0048f57c7ee--

Re: Performance problems on MySQL

am 03.09.2010 11:48:35 von Jangita

On 02/09/2010 6:05 p, Alexandre Vieira wrote:
> Hi Jangita,
>
> I'm 15779 innodb_buffer_pool_pages_free from a total of 22400. That's
> 246MB of 350MB free.
>
> | Innodb_buffer_pool_pages_data | 6020 |
> | Innodb_buffer_pool_pages_dirty | 1837 |
> | Innodb_buffer_pool_pages_flushed | 673837 |
> | Innodb_buffer_pool_pages_free | 15779 |
> | Innodb_buffer_pool_pages_latched | 0 |
> | Innodb_buffer_pool_pages_misc | 601 |
> | Innodb_buffer_pool_pages_total | 22400 |
> | Innodb_buffer_pool_read_ahead_rnd | 1 |
> | Innodb_buffer_pool_read_ahead_seq | 0 |
> | Innodb_buffer_pool_read_requests | 48471963 |
> | Innodb_buffer_pool_reads | 3497 |
> | Innodb_buffer_pool_wait_free | 0 |
> | Innodb_buffer_pool_write_requests | 21700478 |
>
> Why would I need to increase?
>
> Thanks
>
> BR
> AJ
I'm guessing (just a guess) that you have alot free buffer_pool_size
because mysql doesn't use it because it cannot fit information into it
at one go so doesn't use it at all? Try and up it; if it doesn't work
you could always set it back.

--
Jangita | +256 76 91 8383 | Y! & MSN: jangita@yahoo.com
Skype: jangita | GTalk: jangita.nyagudi@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Performance problems on MySQL

am 03.09.2010 15:31:21 von Johnny Withers

Re: Performance problems on MySQL

am 03.09.2010 15:59:54 von Alexandre Vieira

--000e0cd4041a61fd9c048f5b5880
Content-Type: text/plain; charset=ISO-8859-1

Hi,

I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and it
makes a little difference but not enough for the application to run in real
time processing.

It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.

MySQL is eating 179MB of RAM and 5,4% of CPU.

PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
6229 mysql 455M 179M sleep 58 0 0:03.11 5,4% mysqld/68

The machine has ~1GB of free memory. MySQL and InnoDB has free pages to grow
and we have ~50% of free CPU time.

Currently I can't use the replication server since the application running
on top if this BD can only talk to 1 data source.
At the moment it's also not possible to change the application in order to
make it use the DB more wisely.

Basically we have a table with lots of selects, lots of update, lots of
inserts and deletes. Data manipulation is random, doesn't follow any
specific pattern. All working concurrently.

A big bottleneck is:

8 queries inside InnoDB, 28 queries in queue
1 read views open inside InnoDB

Increasing innodb_thread_concurrency might help without causing any problems
to the overall performance.

Makes total sense if you read:
http://peter-zaitsev.livejournal.com/9138.html

Thanks in advance.

BR
AJ

On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers wrote:

> Ok, so I'm stumped?
>
> What kind of hardware is behind this thing?
>
> -JW
>
> On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira wrote:
>
>> Hi Johnny,
>>
>> mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';
>>
>> +----+-------------+------------+-------+---------------+--- ------+---------+-------+------+-------+
>> | id | select_type | table | type | possible_keys | key |
>> key_len | ref | rows | Extra |
>>
>> +----+-------------+------------+-------+---------------+--- ------+---------+-------+------+-------+
>> | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY |
>> 23 | const | 1 | |
>>
>> +----+-------------+------------+-------+---------------+--- ------+---------+-------+------+-------+
>> 1 row in set (0.53 sec)
>>
>> Thanks
>>
>> BR
>> AJ
>>
>>
>>

--
Alexandre Vieira - nullpt@gmail.com

--000e0cd4041a61fd9c048f5b5880--

Re: Performance problems on MySQL

am 03.09.2010 16:18:36 von Johnny Withers

--001485f1ec903e0d3b048f5b9b49
Content-Type: text/plain; charset=ISO-8859-1

What does

SHOW TABLE STATUS LIKE 'table_name'

Say about this table?

-JW

On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira wrote:

> Hi,
>
> I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and it
> makes a little difference but not enough for the application to run in real
> time processing.
>
> It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.
>
> MySQL is eating 179MB of RAM and 5,4% of CPU.
>
> PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
> 6229 mysql 455M 179M sleep 58 0 0:03.11 5,4% mysqld/68
>
> The machine has ~1GB of free memory. MySQL and InnoDB has free pages to
> grow and we have ~50% of free CPU time.
>
> Currently I can't use the replication server since the application running
> on top if this BD can only talk to 1 data source.
> At the moment it's also not possible to change the application in order to
> make it use the DB more wisely.
>
> Basically we have a table with lots of selects, lots of update, lots of
> inserts and deletes. Data manipulation is random, doesn't follow any
> specific pattern. All working concurrently.
>
> A big bottleneck is:
>
> 8 queries inside InnoDB, 28 queries in queue
>
> 1 read views open inside InnoDB
>
> Increasing innodb_thread_concurrency might help without causing any
> problems to the overall performance.
>
> Makes total sense if you read:
> http://peter-zaitsev.livejournal.com/9138.html
>
> Thanks in advance.
>
> BR
> AJ
>
>
> On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers wrote:
>
>> Ok, so I'm stumped?
>>
>> What kind of hardware is behind this thing?
>>
>> -JW
>>
>> On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira wrote:
>>
>>> Hi Johnny,
>>>
>>> mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';
>>>
>>> +----+-------------+------------+-------+---------------+--- ------+---------+-------+------+-------+
>>> | id | select_type | table | type | possible_keys | key |
>>> key_len | ref | rows | Extra |
>>>
>>> +----+-------------+------------+-------+---------------+--- ------+---------+-------+------+-------+
>>> | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY |
>>> 23 | const | 1 | |
>>>
>>> +----+-------------+------------+-------+---------------+--- ------+---------+-------+------+-------+
>>> 1 row in set (0.53 sec)
>>>
>>> Thanks
>>>
>>> BR
>>> AJ
>>>
>>>
>>>
>
>
> --
> Alexandre Vieira - nullpt@gmail.com
>
>

--
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--001485f1ec903e0d3b048f5b9b49--

Re: Performance problems on MySQL

am 03.09.2010 16:20:18 von Alexandre Vieira

--001636310929603773048f5ba1b7
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,

mysql> SHOW TABLE STATUS LIKE 'clientinfo';
+------------+--------+---------+------------+--------+----- -----------+---=
----------+-----------------+--------------+-----------+---- ------------+--=
-------------------+-------------+------------+------------- ------+--------=
--+----------------+------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length |
Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Collation |
Checksum | Create_options | Comment |
+------------+--------+---------+------------+--------+----- -----------+---=
----------+-----------------+--------------+-----------+---- ------------+--=
-------------------+-------------+------------+------------- ------+--------=
--+----------------+------------------------+
| clientinfo | InnoDB | 10 | Compact | 504762 | 82 |
41500672 | 0 | 0 | 0 | NULL |
2010-09-01 03:21:36 | NULL | NULL | latin1_swedish_ci |
NULL | | InnoDB free: 276480 kB |
+------------+--------+---------+------------+--------+----- -----------+---=
----------+-----------------+--------------+-----------+---- ------------+--=
-------------------+-------------+------------+------------- ------+--------=
--+----------------+------------------------+
1 row in set (0.02 sec)

BR
AJ

On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers wrote=
:

> What does
>
> SHOW TABLE STATUS LIKE 'table_name'
>
> Say about this table?
>
> -JW
>
>
> On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira wrote=
:
>
>> Hi,
>>
>> I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK and
>> it makes a little difference but not enough for the application to run i=
n
>> real time processing.
>>
>> It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.
>>
>> MySQL is eating 179MB of RAM and 5,4% of CPU.
>>
>> PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
>> 6229 mysql 455M 179M sleep 58 0 0:03.11 5,4% mysqld/68
>>
>> The machine has ~1GB of free memory. MySQL and InnoDB has free pages to
>> grow and we have ~50% of free CPU time.
>>
>> Currently I can't use the replication server since the application runni=
ng
>> on top if this BD can only talk to 1 data source.
>> At the moment it's also not possible to change the application in order =
to
>> make it use the DB more wisely.
>>
>> Basically we have a table with lots of selects, lots of update, lots of
>> inserts and deletes. Data manipulation is random, doesn't follow any
>> specific pattern. All working concurrently.
>>
>> A big bottleneck is:
>>
>> 8 queries inside InnoDB, 28 queries in queue
>>
>> 1 read views open inside InnoDB
>>
>> Increasing innodb_thread_concurrency might help without causing any
>> problems to the overall performance.
>>
>> Makes total sense if you read:
>> http://peter-zaitsev.livejournal.com/9138.html
>>
>> Thanks in advance.
>>
>> BR
>> AJ
>>
>>
>> On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers wro=
te:
>>
>>> Ok, so I'm stumped?
>>>
>>> What kind of hardware is behind this thing?
>>>
>>> -JW
>>>
>>> On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira wrot=
e:
>>>
>>>> Hi Johnny,
>>>>
>>>> mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid=3D'911930694';
>>>>
>>>> +----+-------------+------------+-------+---------------+--- ------+---=
------+-------+------+-------+
>>>> | id | select_type | table | type | possible_keys | key |
>>>> key_len | ref | rows | Extra |
>>>>
>>>> +----+-------------+------------+-------+---------------+--- ------+---=
------+-------+------+-------+
>>>> | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY |
>>>> 23 | const | 1 | |
>>>>
>>>> +----+-------------+------------+-------+---------------+--- ------+---=
------+-------+------+-------+
>>>> 1 row in set (0.53 sec)
>>>>
>>>> Thanks
>>>>
>>>> BR
>>>> AJ
>>>>
>>>>
>>>>
>>
>>
>> --
>> Alexandre Vieira - nullpt@gmail.com
>>
>>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>

--=20
Alexandre Vieira - nullpt@gmail.com

--001636310929603773048f5ba1b7--

Re: Performance problems on MySQL

am 03.09.2010 16:46:26 von Johnny Withers

--001485f9a854cc1bad048f5bfe73
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Very confusing...

Why is index_length zero ?

On top of that, there's only 500K rows in the table with a data size of
41MB. Maybe InnoDB is flushing to disk too often?

What's the output of iostat -dxk 60 ? (run for a minute+ to get 2 output
girds)

------------------------------

*Johnny Withers*
jwithers@ecashsoftware.com
601.919.2275 x112
[image: eCash Software Systems]

On Fri, Sep 3, 2010 at 9:20 AM, Alexandre Vieira wrote:

> Hi,
>
> mysql> SHOW TABLE STATUS LIKE 'clientinfo';
>
> +------------+--------+---------+------------+--------+----- -----------+-=
------------+-----------------+--------------+-----------+-- --------------+=
---------------------+-------------+------------+----------- --------+------=
----+----------------+------------------------+
> | Name | Engine | Version | Row_format | Rows | Avg_row_length |
> Data_length | Max_data_length | Index_length | Data_free | Auto_increment=
|
> Create_time | Update_time | Check_time | Collation |
> Checksum | Create_options | Comment |
>
> +------------+--------+---------+------------+--------+----- -----------+-=
------------+-----------------+--------------+-----------+-- --------------+=
---------------------+-------------+------------+----------- --------+------=
----+----------------+------------------------+
> | clientinfo | InnoDB | 10 | Compact | 504762 | 82 |
> 41500672 | 0 | 0 | 0 | NULL |
> 2010-09-01 03:21:36 | NULL | NULL | latin1_swedish_ci |
> NULL | | InnoDB free: 276480 kB |
>
> +------------+--------+---------+------------+--------+----- -----------+-=
------------+-----------------+--------------+-----------+-- --------------+=
---------------------+-------------+------------+----------- --------+------=
----+----------------+------------------------+
> 1 row in set (0.02 sec)
>
> BR
> AJ
>
>
> On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers wrot=
e:
>
>> What does
>>
>> SHOW TABLE STATUS LIKE 'table_name'
>>
>> Say about this table?
>>
>> -JW
>>
>>
>> On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira wrote=
:
>>
>>> Hi,
>>>
>>> I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK an=
d
>>> it makes a little difference but not enough for the application to run =
in
>>> real time processing.
>>>
>>> It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.
>>>
>>> MySQL is eating 179MB of RAM and 5,4% of CPU.
>>>
>>> PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
>>> 6229 mysql 455M 179M sleep 58 0 0:03.11 5,4% mysqld/68
>>>
>>> The machine has ~1GB of free memory. MySQL and InnoDB has free pages to
>>> grow and we have ~50% of free CPU time.
>>>
>>> Currently I can't use the replication server since the application
>>> running on top if this BD can only talk to 1 data source.
>>> At the moment it's also not possible to change the application in order
>>> to make it use the DB more wisely.
>>>
>>> Basically we have a table with lots of selects, lots of update, lots of
>>> inserts and deletes. Data manipulation is random, doesn't follow any
>>> specific pattern. All working concurrently.
>>>
>>> A big bottleneck is:
>>>
>>> 8 queries inside InnoDB, 28 queries in queue
>>>
>>> 1 read views open inside InnoDB
>>>
>>> Increasing innodb_thread_concurrency might help without causing any
>>> problems to the overall performance.
>>>
>>> Makes total sense if you read:
>>> http://peter-zaitsev.livejournal.com/9138.html
>>>
>>> Thanks in advance.
>>>
>>> BR
>>> AJ
>>>
>>>
>>> On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers wr=
ote:
>>>
>>>> Ok, so I'm stumped?
>>>>
>>>> What kind of hardware is behind this thing?
>>>>
>>>> -JW
>>>>
>>>> On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira wro=
te:
>>>>
>>>>> Hi Johnny,
>>>>>
>>>>> mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid=3D'911930694';
>>>>>
>>>>> +----+-------------+------------+-------+---------------+--- ------+--=
-------+-------+------+-------+
>>>>> | id | select_type | table | type | possible_keys | key |
>>>>> key_len | ref | rows | Extra |
>>>>>
>>>>> +----+-------------+------------+-------+---------------+--- ------+--=
-------+-------+------+-------+
>>>>> | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY |
>>>>> 23 | const | 1 | |
>>>>>
>>>>> +----+-------------+------------+-------+---------------+--- ------+--=
-------+-------+------+-------+
>>>>> 1 row in set (0.53 sec)
>>>>>
>>>>> Thanks
>>>>>
>>>>> BR
>>>>> AJ
>>>>>
>>>>>
>>>>>
>>>
>>>
>>> --
>>> Alexandre Vieira - nullpt@gmail.com
>>>
>>>
>>
>>
>> --
>> -----------------------------
>> Johnny Withers
>> 601.209.4985
>> johnny@pixelated.net
>>
>
>
>
> --
> Alexandre Vieira - nullpt@gmail.com
>
>

--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--001485f9a854cc1bad048f5bfe73--

Re: Performance problems on MySQL

am 03.09.2010 17:37:59 von Alexandre Vieira

--00504501724f28b991048f5cb76e
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,

The DB is working on /var, which is md2 / md12 / md22.

extended device statistics
device r/s w/s kr/s kw/s wait actv svc_t %w %b

md2 0.1 80.0 0.4 471.4 0.0 1.0 12.2 0 94
md10 0.0 5.7 0.0 78.8 0.0 0.1 19.7 0 9
md11 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
md12 0.0 80.0 0.3 471.4 0.0 0.8 9.9 0 76
md20 0.0 5.7 0.0 78.8 0.0 0.1 21.1 0 9
md21 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
md22 0.0 80.0 0.1 471.3 0.0 0.8 10.6 0 81
sd0 0.2 86.8 0.3 550.5 0.0 0.9 10.6 0 78
sd1 0.2 86.8 0.2 550.4 0.0 1.0 11.3 0 83
sd30 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
nfs1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
extended device statistics
device r/s w/s kr/s kw/s wait actv svc_t %w %b
md0 0.0 5.6 0.0 83.2 0.0 0.2 28.0 0 10
md1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
md2 0.1 84.2 0.7 527.2 0.0 1.0 11.8 0 93
md10 0.0 5.6 0.0 83.2 0.0 0.1 19.0 0 8
md11 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
md12 0.0 84.2 0.3 527.2 0.0 0.8 9.7 0 77
md20 0.0 5.6 0.0 83.2 0.0 0.1 19.9 0 8
md21 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
md22 0.0 84.1 0.4 527.2 0.0 0.9 10.3 0 82
sd0 0.2 91.1 0.3 610.7 0.0 0.9 10.4 0 79
sd1 0.2 91.0 0.4 610.7 0.0 1.0 11.0 0 84
sd30 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
nfs1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0

I really can't say why Index_Lenght is 0... It might be something with the
index?

mysql> SHOW INDEX FROM clientinfo;
+------------+------------+----------+--------------+------- ------+--------=
---+-------------+----------+--------+------+------------+-- -------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+----------+--------------+------- ------+--------=
---+-------------+----------+--------+------+------------+-- -------+
| clientinfo | 0 | PRIMARY | 1 | userid |
A | 503836 | NULL | NULL | | BTREE | |
+------------+------------+----------+--------------+------- ------+--------=
---+-------------+----------+--------+------+------------+-- -------+
1 row in set (0.00 sec)

mysql> SHOW TABLE STATUS LIKE 'clientinfo';
+------------+--------+---------+------------+--------+----- -----------+---=
----------+-----------------+--------------+-----------+---- ------------+--=
-------------------+-------------+------------+------------- ------+--------=
--+----------------+------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length |
Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Collation |
Checksum | Create_options | Comment |
+------------+--------+---------+------------+--------+----- -----------+---=
----------+-----------------+--------------+-----------+---- ------------+--=
-------------------+-------------+------------+------------- ------+--------=
--+----------------+------------------------+
| clientinfo | InnoDB | 10 | Compact | 508170 | 81 |
41500672 | 0 | 0 | 0 | NULL |
2010-09-01 03:21:36 | NULL | NULL | latin1_swedish_ci |
NULL | | InnoDB free: 276480 kB |
+------------+--------+---------+------------+--------+----- -----------+---=
----------+-----------------+--------------+-----------+---- ------------+--=
-------------------+-------------+------------+------------- ------+--------=
--+----------------+------------------------+
1 row in set (0.00 sec)

BR
AJ

On Fri, Sep 3, 2010 at 3:46 PM, Johnny Withers wrote=
:

> Very confusing...
>
> Why is index_length zero ?
>
> On top of that, there's only 500K rows in the table with a data size of
> 41MB. Maybe InnoDB is flushing to disk too often?
>
> What's the output of iostat -dxk 60 ? (run for a minute+ to get 2 output
> girds)
>
>
>
> ------------------------------
>
> *Johnny Withers*
> jwithers@ecashsoftware.com
> 601.919.2275 x112
> [image: eCash Software Systems]
>
>
> On Fri, Sep 3, 2010 at 9:20 AM, Alexandre Vieira wrote=
:
>
>> Hi,
>>
>> mysql> SHOW TABLE STATUS LIKE 'clientinfo';
>>
>> +------------+--------+---------+------------+--------+----- -----------+=
-------------+-----------------+--------------+-----------+- ---------------=
+---------------------+-------------+------------+---------- ---------+-----=
-----+----------------+------------------------+
>> | Name | Engine | Version | Row_format | Rows | Avg_row_length |
>> Data_length | Max_data_length | Index_length | Data_free | Auto_incremen=
t |
>> Create_time | Update_time | Check_time | Collation |
>> Checksum | Create_options | Comment |
>>
>> +------------+--------+---------+------------+--------+----- -----------+=
-------------+-----------------+--------------+-----------+- ---------------=
+---------------------+-------------+------------+---------- ---------+-----=
-----+----------------+------------------------+
>> | clientinfo | InnoDB | 10 | Compact | 504762 | 82
>> | 41500672 | 0 | 0 | 0 | N=
ULL
>> | 2010-09-01 03:21:36 | NULL | NULL | latin1_swedish_ci |
>> NULL | | InnoDB free: 276480 kB |
>>
>> +------------+--------+---------+------------+--------+----- -----------+=
-------------+-----------------+--------------+-----------+- ---------------=
+---------------------+-------------+------------+---------- ---------+-----=
-----+----------------+------------------------+
>> 1 row in set (0.02 sec)
>>
>> BR
>> AJ
>>
>>
>> On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers wro=
te:
>>
>>> What does
>>>
>>> SHOW TABLE STATUS LIKE 'table_name'
>>>
>>> Say about this table?
>>>
>>> -JW
>>>
>>>
>>> On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira wrot=
e:
>>>
>>>> Hi,
>>>>
>>>> I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK a=
nd
>>>> it makes a little difference but not enough for the application to run=
in
>>>> real time processing.
>>>>
>>>> It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.
>>>>
>>>> MySQL is eating 179MB of RAM and 5,4% of CPU.
>>>>
>>>> PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
>>>> 6229 mysql 455M 179M sleep 58 0 0:03.11 5,4% mysqld/68
>>>>
>>>> The machine has ~1GB of free memory. MySQL and InnoDB has free pages t=
o
>>>> grow and we have ~50% of free CPU time.
>>>>
>>>> Currently I can't use the replication server since the application
>>>> running on top if this BD can only talk to 1 data source.
>>>> At the moment it's also not possible to change the application in orde=
r
>>>> to make it use the DB more wisely.
>>>>
>>>> Basically we have a table with lots of selects, lots of update, lots o=
f
>>>> inserts and deletes. Data manipulation is random, doesn't follow any
>>>> specific pattern. All working concurrently.
>>>>
>>>> A big bottleneck is:
>>>>
>>>> 8 queries inside InnoDB, 28 queries in queue
>>>>
>>>> 1 read views open inside InnoDB
>>>>
>>>> Increasing innodb_thread_concurrency might help without causing any
>>>> problems to the overall performance.
>>>>
>>>> Makes total sense if you read:
>>>> http://peter-zaitsev.livejournal.com/9138.html
>>>>
>>>> Thanks in advance.
>>>>
>>>> BR
>>>> AJ
>>>>
>>>>
>>>> On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers w=
rote:
>>>>
>>>>> Ok, so I'm stumped?
>>>>>
>>>>> What kind of hardware is behind this thing?
>>>>>
>>>>> -JW
>>>>>
>>>>> On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira wr=
ote:
>>>>>
>>>>>> Hi Johnny,
>>>>>>
>>>>>> mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid=3D'911930694';
>>>>>>
>>>>>> +----+-------------+------------+-------+---------------+--- ------+-=
--------+-------+------+-------+
>>>>>> | id | select_type | table | type | possible_keys | key |
>>>>>> key_len | ref | rows | Extra |
>>>>>>
>>>>>> +----+-------------+------------+-------+---------------+--- ------+-=
--------+-------+------+-------+
>>>>>> | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY |
>>>>>> 23 | const | 1 | |
>>>>>>
>>>>>> +----+-------------+------------+-------+---------------+--- ------+-=
--------+-------+------+-------+
>>>>>> 1 row in set (0.53 sec)
>>>>>>
>>>>>> Thanks
>>>>>>
>>>>>> BR
>>>>>> AJ
>>>>>>
>>>>>>
>>>>>>
>>>>
>>>>
>>>> --
>>>> Alexandre Vieira - nullpt@gmail.com
>>>>
>>>>
>>>
>>>
>>> --
>>> -----------------------------
>>> Johnny Withers
>>> 601.209.4985
>>> johnny@pixelated.net
>>>
>>
>>
>>
>> --
>> Alexandre Vieira - nullpt@gmail.com
>>
>>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>

--=20
Alexandre Vieira - nullpt@gmail.com

--00504501724f28b991048f5cb76e--

Re: Performance problems on MySQL

am 03.09.2010 17:54:00 von Johnny Withers

--001485f9a85477704b048f5cf001
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

I think your MySQL instance is disk bound.

If you look at your iostats, md2, 12 and 22 have a ~10ms wait time before a
request can be processed. iostat is also reporting those disks are 75%+
utilized which means they are doing about all they can do.

Anyway you can add more disks? Add faster disks?

I'm not an iostat expert, use my research and recommendations with caution
=3D)

------------------------------

*Johnny Withers*
jwithers@ecashsoftware.com
601.919.2275 x112
[image: eCash Software Systems]

On Fri, Sep 3, 2010 at 10:37 AM, Alexandre Vieira wrote:

> Hi,
>
> The DB is working on /var, which is md2 / md12 / md22.
>
> extended device statistics
> device r/s w/s kr/s kw/s wait actv svc_t %w %b
>
> md2 0.1 80.0 0.4 471.4 0.0 1.0 12.2 0 94
> md10 0.0 5.7 0.0 78.8 0.0 0.1 19.7 0 9
> md11 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
> md12 0.0 80.0 0.3 471.4 0.0 0.8 9.9 0 76
> md20 0.0 5.7 0.0 78.8 0.0 0.1 21.1 0 9
> md21 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
> md22 0.0 80.0 0.1 471.3 0.0 0.8 10.6 0 81
> sd0 0.2 86.8 0.3 550.5 0.0 0.9 10.6 0 78
> sd1 0.2 86.8 0.2 550.4 0.0 1.0 11.3 0 83
> sd30 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
> nfs1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
> extended device statistics
> device r/s w/s kr/s kw/s wait actv svc_t %w %b
> md0 0.0 5.6 0.0 83.2 0.0 0.2 28.0 0 10
> md1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
> md2 0.1 84.2 0.7 527.2 0.0 1.0 11.8 0 93
> md10 0.0 5.6 0.0 83.2 0.0 0.1 19.0 0 8
> md11 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
> md12 0.0 84.2 0.3 527.2 0.0 0.8 9.7 0 77
> md20 0.0 5.6 0.0 83.2 0.0 0.1 19.9 0 8
> md21 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
> md22 0.0 84.1 0.4 527.2 0.0 0.9 10.3 0 82
> sd0 0.2 91.1 0.3 610.7 0.0 0.9 10.4 0 79
> sd1 0.2 91.0 0.4 610.7 0.0 1.0 11.0 0 84
> sd30 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
> nfs1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
>
> I really can't say why Index_Lenght is 0... It might be something with th=
e
> index?
>
>
> mysql> SHOW INDEX FROM clientinfo;
>
> +------------+------------+----------+--------------+------- ------+------=
-----+-------------+----------+--------+------+------------+ ---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment=
|
>
> +------------+------------+----------+--------------+------- ------+------=
-----+-------------+----------+--------+------+------------+ ---------+
> | clientinfo | 0 | PRIMARY | 1 | userid |
> A | 503836 | NULL | NULL | | BTREE | =
|
>
> +------------+------------+----------+--------------+------- ------+------=
-----+-------------+----------+--------+------+------------+ ---------+
> 1 row in set (0.00 sec)
>
>
> mysql> SHOW TABLE STATUS LIKE 'clientinfo';
>
> +------------+--------+---------+------------+--------+----- -----------+-=
------------+-----------------+--------------+-----------+-- --------------+=
---------------------+-------------+------------+----------- --------+------=
----+----------------+------------------------+
> | Name | Engine | Version | Row_format | Rows | Avg_row_length |
> Data_length | Max_data_length | Index_length | Data_free | Auto_increment=
|
> Create_time | Update_time | Check_time | Collation |
> Checksum | Create_options | Comment |
>
> +------------+--------+---------+------------+--------+----- -----------+-=
------------+-----------------+--------------+-----------+-- --------------+=
---------------------+-------------+------------+----------- --------+------=
----+----------------+------------------------+
> | clientinfo | InnoDB | 10 | Compact | 508170 | 81 |
> 41500672 | 0 | 0 | 0 | NULL |
> 2010-09-01 03:21:36 | NULL | NULL | latin1_swedish_ci |
> NULL | | InnoDB free: 276480 kB |
>
> +------------+--------+---------+------------+--------+----- -----------+-=
------------+-----------------+--------------+-----------+-- --------------+=
---------------------+-------------+------------+----------- --------+------=
----+----------------+------------------------+
> 1 row in set (0.00 sec)
>
> BR
> AJ
>
>
> On Fri, Sep 3, 2010 at 3:46 PM, Johnny Withers wrot=
e:
>
>> Very confusing...
>>
>> Why is index_length zero ?
>>
>> On top of that, there's only 500K rows in the table with a data size of
>> 41MB. Maybe InnoDB is flushing to disk too often?
>>
>> What's the output of iostat -dxk 60 ? (run for a minute+ to get 2 output
>> girds)
>>
>>
>>
>> ------------------------------
>>
>> *Johnny Withers*
>> jwithers@ecashsoftware.com
>> 601.919.2275 x112
>> [image: eCash Software Systems]
>>
>>
>> On Fri, Sep 3, 2010 at 9:20 AM, Alexandre Vieira wrote=
:
>>
>>> Hi,
>>>
>>> mysql> SHOW TABLE STATUS LIKE 'clientinfo';
>>>
>>> +------------+--------+---------+------------+--------+----- -----------=
+-------------+-----------------+--------------+-----------+ ---------------=
-+---------------------+-------------+------------+--------- ----------+----=
------+----------------+------------------------+
>>> | Name | Engine | Version | Row_format | Rows | Avg_row_length =
|
>>> Data_length | Max_data_length | Index_length | Data_free | Auto_increme=
nt |
>>> Create_time | Update_time | Check_time | Collation |
>>> Checksum | Create_options | Comment |
>>>
>>> +------------+--------+---------+------------+--------+----- -----------=
+-------------+-----------------+--------------+-----------+ ---------------=
-+---------------------+-------------+------------+--------- ----------+----=
------+----------------+------------------------+
>>> | clientinfo | InnoDB | 10 | Compact | 504762 | 82
>>> | 41500672 | 0 | 0 | 0 | =
NULL
>>> | 2010-09-01 03:21:36 | NULL | NULL | latin1_swedish_ci |
>>> NULL | | InnoDB free: 276480 kB |
>>>
>>> +------------+--------+---------+------------+--------+----- -----------=
+-------------+-----------------+--------------+-----------+ ---------------=
-+---------------------+-------------+------------+--------- ----------+----=
------+----------------+------------------------+
>>> 1 row in set (0.02 sec)
>>>
>>> BR
>>> AJ
>>>
>>>
>>> On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers wr=
ote:
>>>
>>>> What does
>>>>
>>>> SHOW TABLE STATUS LIKE 'table_name'
>>>>
>>>> Say about this table?
>>>>
>>>> -JW
>>>>
>>>>
>>>> On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira wro=
te:
>>>>
>>>>> Hi,
>>>>>
>>>>> I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK
>>>>> and it makes a little difference but not enough for the application t=
o run
>>>>> in real time processing.
>>>>>
>>>>> It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.
>>>>>
>>>>> MySQL is eating 179MB of RAM and 5,4% of CPU.
>>>>>
>>>>> PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLW=
P
>>>>> 6229 mysql 455M 179M sleep 58 0 0:03.11 5,4% mysqld/68
>>>>>
>>>>> The machine has ~1GB of free memory. MySQL and InnoDB has free pages =
to
>>>>> grow and we have ~50% of free CPU time.
>>>>>
>>>>> Currently I can't use the replication server since the application
>>>>> running on top if this BD can only talk to 1 data source.
>>>>> At the moment it's also not possible to change the application in ord=
er
>>>>> to make it use the DB more wisely.
>>>>>
>>>>> Basically we have a table with lots of selects, lots of update, lots =
of
>>>>> inserts and deletes. Data manipulation is random, doesn't follow any
>>>>> specific pattern. All working concurrently.
>>>>>
>>>>> A big bottleneck is:
>>>>>
>>>>> 8 queries inside InnoDB, 28 queries in queue
>>>>>
>>>>> 1 read views open inside InnoDB
>>>>>
>>>>> Increasing innodb_thread_concurrency might help without causing any
>>>>> problems to the overall performance.
>>>>>
>>>>> Makes total sense if you read:
>>>>> http://peter-zaitsev.livejournal.com/9138.html
>>>>>
>>>>> Thanks in advance.
>>>>>
>>>>> BR
>>>>> AJ
>>>>>
>>>>>
>>>>> On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers =
wrote:
>>>>>
>>>>>> Ok, so I'm stumped?
>>>>>>
>>>>>> What kind of hardware is behind this thing?
>>>>>>
>>>>>> -JW
>>>>>>
>>>>>> On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira w=
rote:
>>>>>>
>>>>>>> Hi Johnny,
>>>>>>>
>>>>>>> mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid=3D'911930694';
>>>>>>>
>>>>>>> +----+-------------+------------+-------+---------------+--- ------+=
---------+-------+------+-------+
>>>>>>> | id | select_type | table | type | possible_keys | key |
>>>>>>> key_len | ref | rows | Extra |
>>>>>>>
>>>>>>> +----+-------------+------------+-------+---------------+--- ------+=
---------+-------+------+-------+
>>>>>>> | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY |
>>>>>>> 23 | const | 1 | |
>>>>>>>
>>>>>>> +----+-------------+------------+-------+---------------+--- ------+=
---------+-------+------+-------+
>>>>>>> 1 row in set (0.53 sec)
>>>>>>>
>>>>>>> Thanks
>>>>>>>
>>>>>>> BR
>>>>>>> AJ
>>>>>>>
>>>>>>>
>>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Alexandre Vieira - nullpt@gmail.com
>>>>>
>>>>>
>>>>
>>>>
>>>> --
>>>> -----------------------------
>>>> Johnny Withers
>>>> 601.209.4985
>>>> johnny@pixelated.net
>>>>
>>>
>>>
>>>
>>> --
>>> Alexandre Vieira - nullpt@gmail.com
>>>
>>>
>>
>>
>> --
>> -----------------------------
>> Johnny Withers
>> 601.209.4985
>> johnny@pixelated.net
>>
>
>
>
> --
> Alexandre Vieira - nullpt@gmail.com
>
>

--=20
-----------------------------
Johnny Withers
601.209.4985
johnny@pixelated.net

--001485f9a85477704b048f5cf001--

Re: Performance problems on MySQL

am 03.09.2010 19:45:52 von Alexandre Vieira

--001636310929830805048f5e803b
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,

When creating a table in MySQL with a PK it automatically creates an INDEX,
correct?

The Index_Length: 0 is rather strange..I've created a new INDEX on top of m=
y
PK column on my test system and Index_Length shows a big value different
from 0. Do you think this might have any impact?

mysql> show index from gwtraffic.clientinfo;
+------------+------------+------------+--------------+----- --------+------=
-----+-------------+----------+--------+------+------------+ ---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+------------+------------+------------+--------------+----- --------+------=
-----+-------------+----------+--------+------+------------+ ---------+
| clientinfo | 0 | PRIMARY | 1 | userid |
A | 548216 | NULL | NULL | | BTREE | |
| clientinfo | 1 | userid_idx | 1 | userid |
A | 548216 | NULL | NULL | | BTREE | |
+------------+------------+------------+--------------+----- --------+------=
-----+-------------+----------+--------+------+------------+ ---------+
2 rows in set (0.01 sec)

mysql> show table status LIKE 'clientinfo';
+------------+--------+---------+------------+--------+----- -----------+---=
----------+-----------------+--------------+-----------+---- ------------+--=
-------------------+-------------+------------+------------- ------+--------=
--+----------------+-------------------------+
| Name | Engine | Version | Row_format | Rows | Avg_row_length |
Data_length | Max_data_length | Index_length | Data_free | Auto_increment |
Create_time | Update_time | Check_time | Collation |
Checksum | Create_options | Comment |
+------------+--------+---------+------------+--------+----- -----------+---=
----------+-----------------+--------------+-----------+---- ------------+--=
-------------------+-------------+------------+------------- ------+--------=
--+----------------+-------------------------+
| clientinfo | InnoDB | 10 | Compact | 548216 | 62 |
34144256 | 0 | 5783552 | 0 | NULL |
2010-09-03 17:38:16 | NULL | NULL | latin1_swedish_ci |
NULL | | InnoDB free: 1214464 kB |
+------------+--------+---------+------------+--------+----- -----------+---=
----------+-----------------+--------------+-----------+---- ------------+--=
-------------------+-------------+------------+------------- ------+--------=
--+----------------+-------------------------+
1 row in set (0.00 sec)

I'm trying to stress my test DB but can't measure any different results wit=
h
or without the second INDEX.

Regarding the disks.. the DB is updated 20+ times every second. Writing the
log, checkpoint to disk, etc.. can cause that much load?

BR
AJ

On Fri, Sep 3, 2010 at 4:54 PM, Johnny Withers wrote=
:

> I think your MySQL instance is disk bound.
>
> If you look at your iostats, md2, 12 and 22 have a ~10ms wait time before=
a
> request can be processed. iostat is also reporting those disks are 75%+
> utilized which means they are doing about all they can do.
>
> Anyway you can add more disks? Add faster disks?
>
> I'm not an iostat expert, use my research and recommendations with cautio=
n
> =3D)
>
>
> ------------------------------
>
> *Johnny Withers*
> jwithers@ecashsoftware.com
> 601.919.2275 x112
> [image: eCash Software Systems]
>
>
> On Fri, Sep 3, 2010 at 10:37 AM, Alexandre Vieira wrote=
:
>
>> Hi,
>>
>> The DB is working on /var, which is md2 / md12 / md22.
>>
>> extended device statistics
>> device r/s w/s kr/s kw/s wait actv svc_t %w %b
>>
>> md2 0.1 80.0 0.4 471.4 0.0 1.0 12.2 0 94
>> md10 0.0 5.7 0.0 78.8 0.0 0.1 19.7 0 9
>> md11 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
>> md12 0.0 80.0 0.3 471.4 0.0 0.8 9.9 0 76
>> md20 0.0 5.7 0.0 78.8 0.0 0.1 21.1 0 9
>> md21 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
>> md22 0.0 80.0 0.1 471.3 0.0 0.8 10.6 0 81
>> sd0 0.2 86.8 0.3 550.5 0.0 0.9 10.6 0 78
>> sd1 0.2 86.8 0.2 550.4 0.0 1.0 11.3 0 83
>> sd30 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
>> nfs1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
>> extended device statistics
>> device r/s w/s kr/s kw/s wait actv svc_t %w %b
>> md0 0.0 5.6 0.0 83.2 0.0 0.2 28.0 0 10
>> md1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
>> md2 0.1 84.2 0.7 527.2 0.0 1.0 11.8 0 93
>> md10 0.0 5.6 0.0 83.2 0.0 0.1 19.0 0 8
>> md11 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
>> md12 0.0 84.2 0.3 527.2 0.0 0.8 9.7 0 77
>> md20 0.0 5.6 0.0 83.2 0.0 0.1 19.9 0 8
>> md21 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
>> md22 0.0 84.1 0.4 527.2 0.0 0.9 10.3 0 82
>> sd0 0.2 91.1 0.3 610.7 0.0 0.9 10.4 0 79
>> sd1 0.2 91.0 0.4 610.7 0.0 1.0 11.0 0 84
>> sd30 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
>> nfs1 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0 0
>>
>> I really can't say why Index_Lenght is 0... It might be something with t=
he
>> index?
>>
>>
>> mysql> SHOW INDEX FROM clientinfo;
>>
>> +------------+------------+----------+--------------+------- ------+-----=
------+-------------+----------+--------+------+------------ +---------+
>> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
>> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Commen=
t |
>>
>> +------------+------------+----------+--------------+------- ------+-----=
------+-------------+----------+--------+------+------------ +---------+
>> | clientinfo | 0 | PRIMARY | 1 | userid |
>> A | 503836 | NULL | NULL | | BTREE | =
|
>>
>> +------------+------------+----------+--------------+------- ------+-----=
------+-------------+----------+--------+------+------------ +---------+
>> 1 row in set (0.00 sec)
>>
>>
>> mysql> SHOW TABLE STATUS LIKE 'clientinfo';
>>
>> +------------+--------+---------+------------+--------+----- -----------+=
-------------+-----------------+--------------+-----------+- ---------------=
+---------------------+-------------+------------+---------- ---------+-----=
-----+----------------+------------------------+
>> | Name | Engine | Version | Row_format | Rows | Avg_row_length |
>> Data_length | Max_data_length | Index_length | Data_free | Auto_incremen=
t |
>> Create_time | Update_time | Check_time | Collation |
>> Checksum | Create_options | Comment |
>>
>> +------------+--------+---------+------------+--------+----- -----------+=
-------------+-----------------+--------------+-----------+- ---------------=
+---------------------+-------------+------------+---------- ---------+-----=
-----+----------------+------------------------+
>> | clientinfo | InnoDB | 10 | Compact | 508170 | 81
>> | 41500672 | 0 | 0 | 0 | N=
ULL
>> | 2010-09-01 03:21:36 | NULL | NULL | latin1_swedish_ci |
>> NULL | | InnoDB free: 276480 kB |
>>
>> +------------+--------+---------+------------+--------+----- -----------+=
-------------+-----------------+--------------+-----------+- ---------------=
+---------------------+-------------+------------+---------- ---------+-----=
-----+----------------+------------------------+
>> 1 row in set (0.00 sec)
>>
>> BR
>> AJ
>>
>>
>> On Fri, Sep 3, 2010 at 3:46 PM, Johnny Withers wro=
te:
>>
>>> Very confusing...
>>>
>>> Why is index_length zero ?
>>>
>>> On top of that, there's only 500K rows in the table with a data size of
>>> 41MB. Maybe InnoDB is flushing to disk too often?
>>>
>>> What's the output of iostat -dxk 60 ? (run for a minute+ to get 2 outpu=
t
>>> girds)
>>>
>>>
>>>
>>> ------------------------------
>>>
>>> *Johnny Withers*
>>> jwithers@ecashsoftware.com
>>> 601.919.2275 x112
>>> [image: eCash Software Systems]
>>>
>>>
>>> On Fri, Sep 3, 2010 at 9:20 AM, Alexandre Vieira wrot=
e:
>>>
>>>> Hi,
>>>>
>>>> mysql> SHOW TABLE STATUS LIKE 'clientinfo';
>>>>
>>>> +------------+--------+---------+------------+--------+----- ----------=
-+-------------+-----------------+--------------+----------- +--------------=
--+---------------------+-------------+------------+-------- -----------+---=
-------+----------------+------------------------+
>>>> | Name | Engine | Version | Row_format | Rows | Avg_row_length=
|
>>>> Data_length | Max_data_length | Index_length | Data_free | Auto_increm=
ent |
>>>> Create_time | Update_time | Check_time | Collation |
>>>> Checksum | Create_options | Comment |
>>>>
>>>> +------------+--------+---------+------------+--------+----- ----------=
-+-------------+-----------------+--------------+----------- +--------------=
--+---------------------+-------------+------------+-------- -----------+---=
-------+----------------+------------------------+
>>>> | clientinfo | InnoDB | 10 | Compact | 504762 | 82
>>>> | 41500672 | 0 | 0 | 0 | =
NULL
>>>> | 2010-09-01 03:21:36 | NULL | NULL | latin1_swedish_ci |
>>>> NULL | | InnoDB free: 276480 kB |
>>>>
>>>> +------------+--------+---------+------------+--------+----- ----------=
-+-------------+-----------------+--------------+----------- +--------------=
--+---------------------+-------------+------------+-------- -----------+---=
-------+----------------+------------------------+
>>>> 1 row in set (0.02 sec)
>>>>
>>>> BR
>>>> AJ
>>>>
>>>>
>>>> On Fri, Sep 3, 2010 at 3:18 PM, Johnny Withers w=
rote:
>>>>
>>>>> What does
>>>>>
>>>>> SHOW TABLE STATUS LIKE 'table_name'
>>>>>
>>>>> Say about this table?
>>>>>
>>>>> -JW
>>>>>
>>>>>
>>>>> On Fri, Sep 3, 2010 at 8:59 AM, Alexandre Vieira wr=
ote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I've done some tests with INT(8) vs the VARCHAR(23) on the userid PK
>>>>>> and it makes a little difference but not enough for the application =
to run
>>>>>> in real time processing.
>>>>>>
>>>>>> It's a Sun Fire V240 2x 1.5ghz UltraSparc IIIi with 2GB of RAM.
>>>>>>
>>>>>> MySQL is eating 179MB of RAM and 5,4% of CPU.
>>>>>>
>>>>>> PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NL=
WP
>>>>>> 6229 mysql 455M 179M sleep 58 0 0:03.11 5,4% mysqld/68
>>>>>>
>>>>>> The machine has ~1GB of free memory. MySQL and InnoDB has free pages
>>>>>> to grow and we have ~50% of free CPU time.
>>>>>>
>>>>>> Currently I can't use the replication server since the application
>>>>>> running on top if this BD can only talk to 1 data source.
>>>>>> At the moment it's also not possible to change the application in
>>>>>> order to make it use the DB more wisely.
>>>>>>
>>>>>> Basically we have a table with lots of selects, lots of update, lots
>>>>>> of inserts and deletes. Data manipulation is random, doesn't follow =
any
>>>>>> specific pattern. All working concurrently.
>>>>>>
>>>>>> A big bottleneck is:
>>>>>>
>>>>>> 8 queries inside InnoDB, 28 queries in queue
>>>>>>
>>>>>> 1 read views open inside InnoDB
>>>>>>
>>>>>> Increasing innodb_thread_concurrency might help without causing any
>>>>>> problems to the overall performance.
>>>>>>
>>>>>> Makes total sense if you read:
>>>>>> http://peter-zaitsev.livejournal.com/9138.html
>>>>>>
>>>>>> Thanks in advance.
>>>>>>
>>>>>> BR
>>>>>> AJ
>>>>>>
>>>>>>
>>>>>> On Fri, Sep 3, 2010 at 2:31 PM, Johnny Withers >wrote:
>>>>>>
>>>>>>> Ok, so I'm stumped?
>>>>>>>
>>>>>>> What kind of hardware is behind this thing?
>>>>>>>
>>>>>>> -JW
>>>>>>>
>>>>>>> On Fri, Sep 3, 2010 at 4:44 AM, Alexandre Vieira =
wrote:
>>>>>>>
>>>>>>>> Hi Johnny,
>>>>>>>>
>>>>>>>> mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid=3D'911930694'=
;
>>>>>>>>
>>>>>>>> +----+-------------+------------+-------+---------------+--- ------=
+---------+-------+------+-------+
>>>>>>>> | id | select_type | table | type | possible_keys | key =
|
>>>>>>>> key_len | ref | rows | Extra |
>>>>>>>>
>>>>>>>> +----+-------------+------------+-------+---------------+--- ------=
+---------+-------+------+-------+
>>>>>>>> | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY =
|
>>>>>>>> 23 | const | 1 | |
>>>>>>>>
>>>>>>>> +----+-------------+------------+-------+---------------+--- ------=
+---------+-------+------+-------+
>>>>>>>> 1 row in set (0.53 sec)
>>>>>>>>
>>>>>>>> Thanks
>>>>>>>>
>>>>>>>> BR
>>>>>>>> AJ
>>>>>>>>
>>>>>>>>
>>>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Alexandre Vieira - nullpt@gmail.com
>>>>>>
>>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> -----------------------------
>>>>> Johnny Withers
>>>>> 601.209.4985
>>>>> johnny@pixelated.net
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Alexandre Vieira - nullpt@gmail.com
>>>>
>>>>
>>>
>>>
>>> --
>>> -----------------------------
>>> Johnny Withers
>>> 601.209.4985
>>> johnny@pixelated.net
>>>
>>
>>
>>
>> --
>> Alexandre Vieira - nullpt@gmail.com
>>
>>
>
>
> --
> -----------------------------
> Johnny Withers
> 601.209.4985
> johnny@pixelated.net
>

--=20
Alexandre Vieira - nullpt@gmail.com

--001636310929830805048f5e803b--

Re: Performance problems on MySQL

am 03.09.2010 21:15:05 von Johnny Withers

--001485f270ce94736a048f5fbf94
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

It seems that when your index is PRIMARY on InnoDB tables, it's magic and i=
s
part of the data thereby it is not included in the index_length field.

I have never noticed this. I don't think adding a new index will make a
difference.

You could try moving your log files to a different disk array than where
your data is. If you have binary and query logging enabled, it's probably a
good idea.

------------------------------

*Johnny Withers*
jwithers@ecashsoftware.com
601.919.2275 x112
[image: eCash Software Systems]

On Fri, Sep 3, 2010 at 12:45 PM, Alexandre Vieira wrote:

> Hi,
>
> When creating a table in MySQL with a PK it automatically creates an INDE=
X,
> correct?
>
> The Index_Length: 0 is rather strange..I've created a new INDEX on top of
> my PK column on my test system and Index_Length shows a big value differe=
nt
> from 0. Do you think this might have any impact?
>
> mysql> show index from gwtraffic.clientinfo;
>
>
> +------------+------------+------------+--------------+----- --------+----=
-------+-------------+----------+--------+------+----------- -+---------+
> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment=
|
>
> +------------+------------+------------+--------------+----- --------+----=
-------+-------------+----------+--------+------+----------- -+---------+
> | clientinfo | 0 | PRIMARY | 1 | userid |
> A | 548216 | NULL | NULL | | BTREE | =
|
> | clientinfo | 1 | userid_idx | 1 | userid |
> A | 548216 | NULL | NULL | | BTREE | =
|
>
> +------------+------------+------------+--------------+----- --------+----=
-------+-------------+----------+--------+------+----------- -+---------+
>
> 2 rows in set (0.01 sec)
>
> mysql> show table status LIKE 'clientinfo';
>
>
> +------------+--------+---------+------------+--------+----- -----------+-=
------------+-----------------+--------------+-----------+-- --------------+=
---------------------+-------------+------------+----------- --------+------=
----+----------------+-------------------------+
> | Name | Engine | Version | Row_format | Rows | Avg_row_length |
> Data_length | Max_data_length | Index_length | Data_free | Auto_increment=
|
> Create_time | Update_time | Check_time | Collation |
> Checksum | Create_options | Comment |
>
> +------------+--------+---------+------------+--------+----- -----------+-=
------------+-----------------+--------------+-----------+-- --------------+=
---------------------+-------------+------------+----------- --------+------=
----+----------------+-------------------------+
> | clientinfo | InnoDB | 10 | Compact | 548216 | 62 |
> 34144256 | 0 | 5783552 | 0 | NULL |
> 2010-09-03 17:38:16 | NULL | NULL | latin1_swedish_ci |
> NULL | | InnoDB free: 1214464 kB |
>
>
> +------------+--------+---------+------------+--------+----- -----------+-=
------------+-----------------+--------------+-----------+-- --------------+=
---------------------+-------------+------------+----------- --------+------=
----+----------------+-------------------------+
> 1 row in set (0.00 sec)
>
> I'm trying to stress my test DB but can't measure any different results
> with or without the second INDEX.
>
> Regarding the disks.. the DB is updated 20+ times every second. Writing t=
he
> log, checkpoint to disk, etc.. can cause that much load?
>
> BR
> AJ
>
>
>

--001485f270ce94736a048f5fbf94--

Re: Performance problems on MySQL

am 05.09.2010 15:50:51 von shawn.l.green

On 9/3/2010 3:15 PM, Johnny Withers wrote:
> It seems that when your index is PRIMARY on InnoDB tables, it's magic and is
> part of the data thereby it is not included in the index_length field.
>
> I have never noticed this. I don't think adding a new index will make a
> difference.
>
> You could try moving your log files to a different disk array than where
> your data is. If you have binary and query logging enabled, it's probably a
> good idea.
>
Johnny is correct. The PRIMARY KEY to an InnoDB table is indeed part of
the data:

http://dev.mysql.com/doc/refman/5.1/en/innodb-index-types.ht ml

That explains why there is no length to this index.

--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Performance problems on MySQL

am 06.09.2010 06:17:11 von mos

At 04:44 AM 9/3/2010, Alexandre Vieira wrote:
>Hi Johnny,
>
>mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid='911930694';
>+----+-------------+------------+-------+---------------+-- -------+---------+-------+------+-------+
>| id | select_type | table | type | possible_keys | key | key_len
>| ref | rows | Extra |
>+----+-------------+------------+-------+---------------+-- -------+---------+-------+------+-------+
>| 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23
>| const | 1 | |
>+----+-------------+------------+-------+---------------+-- -------+---------+-------+------+-------+
>1 row in set (0.53 sec)
>
>Thanks
>
>BR
>AJ

Alexandre,
Do you have UserId declared as CHAR? It looks numeric to me. If it is
stored as an integer then don't use the ' ' in the select statement
otherwise it needs to convert it.
If UserId values are integers and you have the column defined as CHAR, then
declare the column UserId as integer or BigInt and the searches should be
faster than searching on CHAR.

Mike

>On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers wrote:
>
> > What about an explain of this query:
> >
> >
> > SELECT * FROM clientinfo WHERE userid='182106617';
> >
> > -JW
> >
> >
> > On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira wrote:
> >
> >> John, Johnny,
> >>
> >> Thanks for the prompt answer.
> >>
> >> mysql> SHOW CREATE TABLE clientinfo;
> >>
> >>
> +------------+---------------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------+
> >> | Table | Create
> >> Table
> >> |
> >>
> >>
> +------------+---------------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------+
> >> | clientinfo | CREATE TABLE `clientinfo` (
> >> `userid` varchar(21) NOT NULL default '',
> >> `units` float default NULL,
> >> `date_last_query` datetime default NULL,
> >> `last_acc` int(10) unsigned default NULL,
> >> `date_last_units` datetime default NULL,
> >> `notification` int(10) unsigned NOT NULL default '0',
> >> `package` char(1) default NULL,
> >> `user_type` varchar(5) default NULL,
> >> PRIMARY KEY (`userid`)
> >> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
> >>
> >>
> +------------+---------------------------------------------- ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------+
> >> 1 row in set (0.00 sec)
> >> mysql> SHOW INDEX FROM clientinfo;
> >>
> >>
> +------------+------------+----------+--------------+------- ------+-----------+-------------+----------+--------+------+ ------------+---------+
> >> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
> >> Collation | Cardinality | Sub_part | Packed | Null | Index_type |
> Comment |
> >>
> >>
> +------------+------------+----------+--------------+------- ------+-----------+-------------+----------+--------+------+ ------------+---------+
> >> | clientinfo | 0 | PRIMARY | 1 | userid |
> >> A | 460056 | NULL | NULL | |
> BTREE | |
> >>
> >>
> +------------+------------+----------+--------------+------- ------+-----------+-------------+----------+--------+------+ ------------+---------+
> >> 1 row in set (0.00 sec)
> >>
> >>
> >> SELECT * FROM clientinfo WHERE userid='182106617';
> >>
> >> UPDATE clientinfo SET
> >>
> units=0.0,date_last_query=now(),user_type='POS',last_acc=167 ,date_last_units=now(),notification=0
> >> WHERE userid='152633876';
> >>
> >> INSERT INTO clientinfo VALUES
> >> ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE
> >> units=101.0, date_last_query=now(), last_acc=1, date_last_units=now(),
> >> notification=0, package='D', user_type='PRE';
> >>
> >> DELETE FROM clientinfo WHERE units='155618918';
> >>
> >> There are no other type of queries.
> >>
> >> We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of RAM.
> >>
> >> We also run some other applications in the server, but nothing that
> >> consumes all the CPU/Memory. The machine has almost 1GB of free memory and
> >> 50% of idle CPU time at any time.
> >>
> >> TIA
> >>
> >> BR
> >> Alex
> >>
> >>
> >> --
> >> Alexandre Vieira - nullpt@gmail.com
> >>
> >>
> >
> >
> > --
> > -----------------------------
> > Johnny Withers
> > 601.209.4985
> > johnny@pixelated.net
> >
>
>
>
>--
>Alexandre Vieira - nullpt@gmail.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Performance problems on MySQL

am 06.09.2010 11:46:19 von Alexandre Vieira

--000e0cd348620b8410048f9427fb
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi,

We're chaning it to INT(9). Apparently someone remembered to change the typ=
e
of data in this field from an alphanumeric value to an INT(9).

I'm going to change this asap.

Thanks

BR
AJ

On Mon, Sep 6, 2010 at 5:17 AM, mos wrote:

> At 04:44 AM 9/3/2010, Alexandre Vieira wrote:
>
>> Hi Johnny,
>>
>> mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid=3D'911930694';
>>
>> +----+-------------+------------+-------+---------------+--- ------+-----=
----+-------+------+-------+
>> | id | select_type | table | type | possible_keys | key |
>> key_len
>> | ref | rows | Extra |
>>
>> +----+-------------+------------+-------+---------------+--- ------+-----=
----+-------+------+-------+
>> | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23
>> | const | 1 | |
>>
>> +----+-------------+------------+-------+---------------+--- ------+-----=
----+-------+------+-------+
>> 1 row in set (0.53 sec)
>>
>> Thanks
>>
>> BR
>> AJ
>>
>
> Alexandre,
> Do you have UserId declared as CHAR? It looks numeric to me. If it is
> stored as an integer then don't use the ' ' in the select statement
> otherwise it needs to convert it.
> If UserId values are integers and you have the column defined as CHAR, th=
en
> declare the column UserId as integer or BigInt and the searches should be
> faster than searching on CHAR.
>
> Mike
>
>
>
>
> On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers
>> wrote:
>>
>> > What about an explain of this query:
>> >
>> >
>> > SELECT * FROM clientinfo WHERE userid=3D'182106617';
>> >
>> > -JW
>> >
>> >
>> > On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira
>> wrote:
>> >
>> >> John, Johnny,
>> >>
>> >> Thanks for the prompt answer.
>> >>
>> >> mysql> SHOW CREATE TABLE clientinfo;
>> >>
>> >>
>> +------------+---------------------------------------------- ------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ------------+
>> >> | Table | Create
>> >> Table
>> >> |
>> >>
>> >>
>> +------------+---------------------------------------------- ------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ------------+
>> >> | clientinfo | CREATE TABLE `clientinfo` (
>> >> `userid` varchar(21) NOT NULL default '',
>> >> `units` float default NULL,
>> >> `date_last_query` datetime default NULL,
>> >> `last_acc` int(10) unsigned default NULL,
>> >> `date_last_units` datetime default NULL,
>> >> `notification` int(10) unsigned NOT NULL default '0',
>> >> `package` char(1) default NULL,
>> >> `user_type` varchar(5) default NULL,
>> >> PRIMARY KEY (`userid`)
>> >> ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1 |
>> >>
>> >>
>> +------------+---------------------------------------------- ------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ------------+
>> >> 1 row in set (0.00 sec)
>> >> mysql> SHOW INDEX FROM clientinfo;
>> >>
>> >>
>> +------------+------------+----------+--------------+------- ------+-----=
------+-------------+----------+--------+------+------------ +---------+
>> >> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
>> >> Collation | Cardinality | Sub_part | Packed | Null | Index_type |
>> Comment |
>> >>
>> >>
>> +------------+------------+----------+--------------+------- ------+-----=
------+-------------+----------+--------+------+------------ +---------+
>> >> | clientinfo | 0 | PRIMARY | 1 | userid |
>> >> A | 460056 | NULL | NULL | | BTREE |
>> |
>> >>
>> >>
>> +------------+------------+----------+--------------+------- ------+-----=
------+-------------+----------+--------+------+------------ +---------+
>> >> 1 row in set (0.00 sec)
>> >>
>> >>
>> >> SELECT * FROM clientinfo WHERE userid=3D'182106617';
>> >>
>> >> UPDATE clientinfo SET
>> >>
>> units=3D0.0,date_last_query=3Dnow(),user_type=3D'POS',last_a cc=3D167,dat=
e_last_units=3Dnow(),notification=3D0
>> >> WHERE userid=3D'152633876';
>> >>
>> >> INSERT INTO clientinfo VALUES
>> >> ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDATE
>> >> units=3D101.0, date_last_query=3Dnow(), last_acc=3D1, date_last_units=
=3Dnow(),
>> >> notification=3D0, package=3D'D', user_type=3D'PRE';
>> >>
>> >> DELETE FROM clientinfo WHERE units=3D'155618918';
>> >>
>> >> There are no other type of queries.
>> >>
>> >> We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of
>> RAM.
>> >>
>> >> We also run some other applications in the server, but nothing that
>> >> consumes all the CPU/Memory. The machine has almost 1GB of free memor=
y
>> and
>> >> 50% of idle CPU time at any time.
>> >>
>> >> TIA
>> >>
>> >> BR
>> >> Alex
>> >>
>> >>
>> >> --
>> >> Alexandre Vieira - nullpt@gmail.com
>> >>
>> >>
>> >
>> >
>> > --
>> > -----------------------------
>> > Johnny Withers
>> > 601.209.4985
>> > johnny@pixelated.net
>> >
>>
>>
>>
>> --
>> Alexandre Vieira - nullpt@gmail.com
>>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dnullpt@gmail.com
>
>

--=20
Alexandre Vieira - nullpt@gmail.com

--000e0cd348620b8410048f9427fb--

Re: Performance problems on MySQL

am 06.09.2010 19:39:19 von Alexandre Vieira

--002215046dcf9b7d27048f9ac29c
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

Hi List,

In a 20m interval in our max load I have:

OS WAIT ARRAY INFO: reservation count 637, signal count 625
Mutex spin waits 0, rounds 19457, OS waits 428
RW-shared spins 238, OS waits 119; RW-excl spins 13, OS waits 8

(The values are the difference between the start and end of this 20m
interval)

The machine has 2 CPU's and usually has 40-50% of idle CPU.

Our workload consists on lots of parallel simple queries (SELECTs and
UPDATEs with a simple condition on the PK) on a 500k record/40MB table with
an INDEX on the PK.

| innodb_sync_spin_loops | 20 =
|
| innodb_thread_concurrency | 16 =
|
| innodb_thread_sleep_delay | 10000 =
|

I've been sampling my innodb status and there are always "16 queries inside
InnoDB" and some 20-30 in queue. Therefore lowering thread_sleep_delay won'=
t
help.

Since I have 47 spin rounds per OS Wait, would innodb gain something with
rising sync_spin_loops a little bit?

Also, should I be capping thread_concurrency with a 2 CPU machine?

Unfortunately this machine only has 2 RAID1 disks. I can't spread the disk
load (datafile/logfiles) between disks.

extended device statistics
r/s w/s kr/s kw/s wait actv wsvc_t asvc_t %w %b device
0.0 83.7 0.0 379.3 0.0 1.0 0.1 11.5 1 94 d2 (/var)

Usually the iostat busy indicator is near 100%.

Any hints on something I could tune to have less "OS Waits" and help with
the Disk I/O?

==================== =====3D=
============
100906 18:33:40 INNODB MONITOR OUTPUT
==================== =====3D=
============
Per second averages calculated from the last 47 seconds
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 266140, signal count 259267
Mutex spin waits 0, rounds 7407879, OS waits 179189
RW-shared spins 93878, OS waits 46196; RW-excl spins 9473, OS waits 7311

---
LOG
---
Log sequence number 62 2833945222
Log flushed up to 62 2833944847
Last checkpoint at 62 2828803314
1 pending log writes, 0 pending chkp writes
18419416 log i/o's done, 37.64 log i/o's/second

--------------
ROW OPERATIONS
--------------
16 queries inside InnoDB, 27 queries in queue
1 read views open inside InnoDB
Main thread id 11, state: sleeping
Number of rows inserted 603196, updated 9006533, deleted 111028, read
30145300
0.17 inserts/s, 18.49 updates/s, 0.00 deletes/s, 41.47 reads/s

If nothing else can be done I'll advise the client to acquire new HW for
this BD.

By the way, upgrading from 5.0.45-log to 5.1.50 would make a huge differenc=
e
in terms of performance?

BR
AJ

On Mon, Sep 6, 2010 at 10:46 AM, Alexandre Vieira wrote:

> Hi,
>
> We're chaning it to INT(9). Apparently someone remembered to change the
> type of data in this field from an alphanumeric value to an INT(9).
>
> I'm going to change this asap.
>
> Thanks
>
> BR
> AJ
>
>
> On Mon, Sep 6, 2010 at 5:17 AM, mos wrote:
>
>> At 04:44 AM 9/3/2010, Alexandre Vieira wrote:
>>
>>> Hi Johnny,
>>>
>>> mysql> EXPLAIN SELECT * FROM clientinfo WHERE userid=3D'911930694';
>>>
>>> +----+-------------+------------+-------+---------------+--- ------+----=
-----+-------+------+-------+
>>> | id | select_type | table | type | possible_keys | key |
>>> key_len
>>> | ref | rows | Extra |
>>>
>>> +----+-------------+------------+-------+---------------+--- ------+----=
-----+-------+------+-------+
>>> | 1 | SIMPLE | clientinfo | const | PRIMARY | PRIMARY | 23
>>> | const | 1 | |
>>>
>>> +----+-------------+------------+-------+---------------+--- ------+----=
-----+-------+------+-------+
>>> 1 row in set (0.53 sec)
>>>
>>> Thanks
>>>
>>> BR
>>> AJ
>>>
>>
>> Alexandre,
>> Do you have UserId declared as CHAR? It looks numeric to me. If it i=
s
>> stored as an integer then don't use the ' ' in the select statement
>> otherwise it needs to convert it.
>> If UserId values are integers and you have the column defined as CHAR,
>> then declare the column UserId as integer or BigInt and the searches sho=
uld
>> be faster than searching on CHAR.
>>
>> Mike
>>
>>
>>
>>
>> On Thu, Sep 2, 2010 at 8:52 PM, Johnny Withers
>>> wrote:
>>>
>>> > What about an explain of this query:
>>> >
>>> >
>>> > SELECT * FROM clientinfo WHERE userid=3D'182106617';
>>> >
>>> > -JW
>>> >
>>> >
>>> > On Thu, Sep 2, 2010 at 9:35 AM, Alexandre Vieira
>>> wrote:
>>> >
>>> >> John, Johnny,
>>> >>
>>> >> Thanks for the prompt answer.
>>> >>
>>> >> mysql> SHOW CREATE TABLE clientinfo;
>>> >>
>>> >>
>>> +------------+---------------------------------------------- -----------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ -------------+
>>> >> | Table | Create
>>> >> Table
>>> >> |
>>> >>
>>> >>
>>> +------------+---------------------------------------------- -----------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ -------------+
>>> >> | clientinfo | CREATE TABLE `clientinfo` (
>>> >> `userid` varchar(21) NOT NULL default '',
>>> >> `units` float default NULL,
>>> >> `date_last_query` datetime default NULL,
>>> >> `last_acc` int(10) unsigned default NULL,
>>> >> `date_last_units` datetime default NULL,
>>> >> `notification` int(10) unsigned NOT NULL default '0',
>>> >> `package` char(1) default NULL,
>>> >> `user_type` varchar(5) default NULL,
>>> >> PRIMARY KEY (`userid`)
>>> >> ) ENGINE=3DInnoDB DEFAULT CHARSET=3Dlatin1 |
>>> >>
>>> >>
>>> +------------+---------------------------------------------- -----------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ ---------------=
------------------------------------------------------------ -------------+
>>> >> 1 row in set (0.00 sec)
>>> >> mysql> SHOW INDEX FROM clientinfo;
>>> >>
>>> >>
>>> +------------+------------+----------+--------------+------- ------+----=
-------+-------------+----------+--------+------+----------- -+---------+
>>> >> | Table | Non_unique | Key_name | Seq_in_index | Column_name |
>>> >> Collation | Cardinality | Sub_part | Packed | Null | Index_type |
>>> Comment |
>>> >>
>>> >>
>>> +------------+------------+----------+--------------+------- ------+----=
-------+-------------+----------+--------+------+----------- -+---------+
>>> >> | clientinfo | 0 | PRIMARY | 1 | userid |
>>> >> A | 460056 | NULL | NULL | | BTREE |
>>> |
>>> >>
>>> >>
>>> +------------+------------+----------+--------------+------- ------+----=
-------+-------------+----------+--------+------+----------- -+---------+
>>> >> 1 row in set (0.00 sec)
>>> >>
>>> >>
>>> >> SELECT * FROM clientinfo WHERE userid=3D'182106617';
>>> >>
>>> >> UPDATE clientinfo SET
>>> >>
>>> units=3D0.0,date_last_query=3Dnow(),user_type=3D'POS',last_a cc=3D167,da=
te_last_units=3Dnow(),notification=3D0
>>> >> WHERE userid=3D'152633876';
>>> >>
>>> >> INSERT INTO clientinfo VALUES
>>> >> ('171918726',101.0,now(),1,now(),0,'D','PRE') ON DUPLICATE KEY UPDAT=
E
>>> >> units=3D101.0, date_last_query=3Dnow(), last_acc=3D1, date_last_unit=
s=3Dnow(),
>>> >> notification=3D0, package=3D'D', user_type=3D'PRE';
>>> >>
>>> >> DELETE FROM clientinfo WHERE units=3D'155618918';
>>> >>
>>> >> There are no other type of queries.
>>> >>
>>> >> We're running this DB on a Sun-Fire V240. 2xUIIIi 1.5ghz with 2GB of
>>> RAM.
>>> >>
>>> >> We also run some other applications in the server, but nothing that
>>> >> consumes all the CPU/Memory. The machine has almost 1GB of free memo=
ry
>>> and
>>> >> 50% of idle CPU time at any time.
>>> >>
>>> >> TIA
>>> >>
>>> >> BR
>>> >> Alex
>>> >>
>>> >>
>>> >> --
>>> >> Alexandre Vieira - nullpt@gmail.com
>>> >>
>>> >>
>>> >
>>> >
>>> > --
>>> > -----------------------------
>>> > Johnny Withers
>>> > 601.209.4985
>>> > johnny@pixelated.net
>>> >
>>>
>>>
>>>
>>> --
>>> Alexandre Vieira - nullpt@gmail.com
>>>
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dnullpt@gmail.com
>>
>>
>
>
> --
> Alexandre Vieira - nullpt@gmail.com
>
>

--=20
Alexandre Vieira - nullpt@gmail.com

--002215046dcf9b7d27048f9ac29c--