# Help need some optimization advice

**URL:** <https://forums.percona.com/t/help-need-some-optimization-advice/211>\
**Category:** Other MySQL® Questions\
**Created:** [February 23, 2007, 1:21pm UTC](https://forums.percona.com/t/help-need-some-optimization-advice/211 "2007-02-23T13:21:50Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![mysqluser81](https://avatars.discourse-cdn.com/v4/letter/m/22d042/32.png) [@mysqluser81](https://forums.percona.com/u/mysqluser81)\
**Post date:** [February 23, 2007, 1:21pm UTC](https://forums.percona.com/t/help-need-some-optimization-advice/211/1 "2007-02-23T13:21:50Z")

</div>

Hello first of here is my SHOW STATUS  
server specs  
8GB memory  
OS Fedora Core 4  
Dual Intel(R) Xeon™ CPU 3.40GHz

±----------------------------------±----------+  
| Variable\_name | Value |  
±----------------------------------±----------+  
| Aborted\_clients | 5490 |  
| Aborted\_connects | 1 |  
| Binlog\_cache\_disk\_use | 0 |  
| Binlog\_cache\_use | 0 |  
| Bytes\_received | 279 |  
| Bytes\_sent | 1594 |  
| Com\_admin\_commands | 0 |  
| Com\_alter\_db | 0 |  
| Com\_alter\_table | 0 |  
| Com\_analyze | 0 |  
| Com\_backup\_table | 0 |  
| Com\_begin | 0 |  
| Com\_change\_db | 0 |  
| Com\_change\_master | 0 |  
| Com\_check | 0 |  
| Com\_checksum | 0 |  
| Com\_commit | 0 |  
| Com\_create\_db | 0 |  
| Com\_create\_function | 0 |  
| Com\_create\_index | 0 |  
| Com\_create\_table | 0 |  
| Com\_dealloc\_sql | 0 |  
| Com\_delete | 0 |  
| Com\_delete\_multi | 0 |  
| Com\_do | 0 |  
| Com\_drop\_db | 0 |  
| Com\_drop\_function | 0 |  
| Com\_drop\_index | 0 |  
| Com\_drop\_table | 0 |  
| Com\_drop\_user | 0 |  
| Com\_execute\_sql | 0 |  
| Com\_flush | 0 |  
| Com\_grant | 0 |  
| Com\_ha\_close | 0 |  
| Com\_ha\_open | 0 |  
| Com\_ha\_read | 0 |  
| Com\_help | 0 |  
| Com\_insert | 0 |  
| Com\_insert\_select | 0 |  
| Com\_kill | 0 |  
| Com\_load | 0 |  
| Com\_load\_master\_data | 0 |  
| Com\_load\_master\_table | 0 |  
| Com\_lock\_tables | 0 |  
| Com\_optimize | 0 |  
| Com\_preload\_keys | 0 |  
| Com\_prepare\_sql | 0 |  
| Com\_purge | 0 |  
| Com\_purge\_before\_date | 0 |  
| Com\_rename\_table | 0 |  
| Com\_repair | 0 |  
| Com\_replace | 0 |  
| Com\_replace\_select | 0 |  
| Com\_reset | 0 |  
| Com\_restore\_table | 0 |  
| Com\_revoke | 0 |  
| Com\_revoke\_all | 0 |  
| Com\_rollback | 0 |  
| Com\_savepoint | 0 |  
| Com\_select | 0 |  
| Com\_set\_option | 0 |  
| Com\_show\_binlog\_events | 0 |  
| Com\_show\_binlogs | 0 |  
| Com\_show\_charsets | 0 |  
| Com\_show\_collations | 0 |  
| Com\_show\_column\_types | 0 |  
| Com\_show\_create\_db | 0 |  
| Com\_show\_create\_table | 0 |  
| Com\_show\_databases | 0 |  
| Com\_show\_errors | 0 |  
| Com\_show\_fields | 0 |  
| Com\_show\_grants | 0 |  
| Com\_show\_innodb\_status | 0 |  
| Com\_show\_keys | 0 |  
| Com\_show\_logs | 0 |  
| Com\_show\_master\_status | 0 |  
| Com\_show\_ndb\_status | 0 |  
| Com\_show\_new\_master | 0 |  
| Com\_show\_open\_tables | 0 |  
| Com\_show\_privileges | 0 |  
| Com\_show\_processlist | 0 |  
| Com\_show\_slave\_hosts | 0 |  
| Com\_show\_slave\_status | 0 |  
| Com\_show\_status | 1 |  
| Com\_show\_storage\_engines | 0 |  
| Com\_show\_tables | 0 |  
| Com\_show\_triggers | 0 |  
| Com\_show\_variables | 7 |  
| Com\_show\_warnings | 0 |  
| Com\_slave\_start | 0 |  
| Com\_slave\_stop | 0 |  
| Com\_stmt\_close | 0 |  
| Com\_stmt\_execute | 0 |  
| Com\_stmt\_fetch | 0 |  
| Com\_stmt\_prepare | 0 |  
| Com\_stmt\_reset | 0 |  
| Com\_stmt\_send\_long\_data | 0 |  
| Com\_truncate | 0 |  
| Com\_unlock\_tables | 0 |  
| Com\_update | 0 |  
| Com\_update\_multi | 0 |  
| Com\_xa\_commit | 0 |  
| Com\_xa\_end | 0 |  
| Com\_xa\_prepare | 0 |  
| Com\_xa\_recover | 0 |  
| Com\_xa\_rollback | 0 |  
| Com\_xa\_start | 0 |  
| Compression | OFF |  
| Connections | 2851 |  
| Created\_tmp\_disk\_tables | 0 |  
| Created\_tmp\_files | 52 |  
| Created\_tmp\_tables | 8 |  
| Delayed\_errors | 0 |  
| Delayed\_insert\_threads | 0 |  
| Delayed\_writes | 0 |  
| Flush\_commands | 1 |  
| Handler\_commit | 0 |  
| Handler\_delete | 0 |  
| Handler\_discover | 0 |  
| Handler\_prepare | 0 |  
| Handler\_read\_first | 0 |  
| Handler\_read\_key | 0 |  
| Handler\_read\_next | 0 |  
| Handler\_read\_prev | 0 |  
| Handler\_read\_rnd | 0 |  
| Handler\_read\_rnd\_next | 27 |  
| Handler\_rollback | 0 |  
| Handler\_savepoint | 0 |  
| Handler\_savepoint\_rollback | 0 |  
| Handler\_update | 0 |  
| Handler\_write | 150 |  
| Innodb\_buffer\_pool\_pages\_data | 19 |  
| Innodb\_buffer\_pool\_pages\_dirty | 0 |  
| Innodb\_buffer\_pool\_pages\_flushed | 0 |  
| Innodb\_buffer\_pool\_pages\_free | 493 |  
| Innodb\_buffer\_pool\_pages\_latched | 0 |  
| Innodb\_buffer\_pool\_pages\_misc | 0 |  
| Innodb\_buffer\_pool\_pages\_total | 512 |  
| Innodb\_buffer\_pool\_read\_ahead\_rnd | 1 |  
| Innodb\_buffer\_pool\_read\_ahead\_seq | 0 |  
| Innodb\_buffer\_pool\_read\_requests | 77 |  
| Innodb\_buffer\_pool\_reads | 12 |  
| Innodb\_buffer\_pool\_wait\_free | 0 |  
| Innodb\_buffer\_pool\_write\_requests | 0 |  
| Innodb\_data\_fsyncs | 3 |  
| Innodb\_data\_pending\_fsyncs | 0 |  
| Innodb\_data\_pending\_reads | 0 |  
| Innodb\_data\_pending\_writes | 0 |  
| Innodb\_data\_read | 2494464 |  
| Innodb\_data\_reads | 25 |  
| Innodb\_data\_writes | 3 |  
| Innodb\_data\_written | 1536 |  
| Innodb\_dblwr\_pages\_written | 0 |  
| Innodb\_dblwr\_writes | 0 |  
| Innodb\_log\_waits | 0 |  
| Innodb\_log\_write\_requests | 0 |  
| Innodb\_log\_writes | 1 |  
| Innodb\_os\_log\_fsyncs | 3 |  
| Innodb\_os\_log\_pending\_fsyncs | 0 |  
| Innodb\_os\_log\_pending\_writes | 0 |  
| Innodb\_os\_log\_written | 512 |  
| Innodb\_page\_size | 16384 |  
| Innodb\_pages\_created | 0 |  
| Innodb\_pages\_read | 19 |  
| Innodb\_pages\_written | 0 |  
| Innodb\_row\_lock\_current\_waits | 0 |  
| Innodb\_row\_lock\_time | 0 |  
| Innodb\_row\_lock\_time\_avg | 0 |  
| Innodb\_row\_lock\_time\_max | 0 |  
| Innodb\_row\_lock\_waits | 0 |  
| Innodb\_rows\_deleted | 0 |  
| Innodb\_rows\_inserted | 0 |  
| Innodb\_rows\_read | 0 |  
| Innodb\_rows\_updated | 0 |  
| Key\_blocks\_not\_flushed | 0 |  
| Key\_blocks\_unused | 0 |  
| Key\_blocks\_used | 14497 |  
| Key\_read\_requests | 94036894 |  
| Key\_reads | 4019574 |  
| Key\_write\_requests | 15695044 |  
| Key\_writes | 975770 |  
| Last\_query\_cost | 10.499000 |  
| Max\_used\_connections | 29 |  
| Not\_flushed\_delayed\_rows | 0 |  
| Open\_files | 136 |  
| Open\_streams | 0 |  
| Open\_tables | 89 |  
| Opened\_tables | 0 |  
| Qcache\_free\_blocks | 0 |  
| Qcache\_free\_memory | 0 |  
| Qcache\_hits | 0 |  
| Qcache\_inserts | 0 |  
| Qcache\_lowmem\_prunes | 0 |  
| Qcache\_not\_cached | 0 |  
| Qcache\_queries\_in\_cache | 0 |  
| Qcache\_total\_blocks | 0 |  
| Questions | 363495 |  
| Rpl\_status | NULL |  
| Select\_full\_join | 0 |  
| Select\_full\_range\_join | 0 |  
| Select\_range | 0 |  
| Select\_range\_check | 0 |  
| Select\_scan | 8 |  
| Slave\_open\_temp\_tables | 0 |  
| Slave\_retried\_transactions | 0 |  
| Slave\_running | OFF |  
| Slow\_launch\_threads | 0 |  
| Slow\_queries | 0 |  
| Sort\_merge\_passes | 0 |  
| Sort\_range | 0 |  
| Sort\_rows | 0 |  
| Sort\_scan | 0 |  
| Ssl\_accept\_renegotiates | 0 |  
| Ssl\_accepts | 0 |  
| Ssl\_callback\_cache\_hits | 0 |  
| Ssl\_cipher | |  
| Ssl\_cipher\_list | |  
| Ssl\_client\_connects | 0 |  
| Ssl\_connect\_renegotiates | 0 |  
| Ssl\_ctx\_verify\_depth | 0 |  
| Ssl\_ctx\_verify\_mode | 0 |  
| Ssl\_default\_timeout | 0 |  
| Ssl\_finished\_accepts | 0 |  
| Ssl\_finished\_connects | 0 |  
| Ssl\_session\_cache\_hits | 0 |  
| Ssl\_session\_cache\_misses | 0 |  
| Ssl\_session\_cache\_mode | NONE |  
| Ssl\_session\_cache\_overflows | 0 |  
| Ssl\_session\_cache\_size | 0 |  
| Ssl\_session\_cache\_timeouts | 0 |  
| Ssl\_sessions\_reused | 0 |  
| Ssl\_used\_session\_cache\_entries | 0 |  
| Ssl\_verify\_depth | 0 |  
| Ssl\_verify\_mode | 0 |  
| Ssl\_version | |  
| Table\_locks\_immediate | 285831 |  
| Table\_locks\_waited | 276 |  
| Tc\_log\_max\_pages\_used | 0 |  
| Tc\_log\_page\_size | 0 |  
| Tc\_log\_page\_waits | 0 |  
| Threads\_cached | 0 |  
| Threads\_connected | 12 |  
| Threads\_created | 2850 |  
| Threads\_running | 1 |  
| Uptime | 5080 |  
±----------------------------------±----------+

The problem we are having and I am new to MySQL is that we use mysql as a backend to our alerts kinda like syslog repository. Everytime there is an alert the data gets inserted into the DB. Alerts are being generated from about 40 servers. Nightly we run a script that goes through and removes (DELETE) deletes data that is older than 7 days(which =~100k-200k alerts per day). So at any giving day the last event should be \<7 days from current date.

During the DELETE procedure that occurs nightly we get large number of dropped alerts because (not sure) the tables that it needs to write to are locked and also being accessed by the script.

What would be the best way to optimize our SQL server for fast INSERTS, DELETES, UPDATES which is mostly what we do. It appears as though INSERTS during this DELETING period are somehow dropped not allowed if we sum up the total that should have been INSERTED with the actual number of successful INSERTS its about about 2 our of 10.

Any help ideas suggestions? As I am all out of ideas.  
thanks for the help in advanced and please let me know if you have questions.

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [February 23, 2007, 1:50pm UTC](https://forums.percona.com/t/help-need-some-optimization-advice/211/2 "2007-02-23T13:50:31Z")

</div>

If you’re using MyISAM I’d use one table per week and use merge table for querying. This way you can simply drop old weekly table instead of running expensive delete.

---

<div class="post-metadata">

**Author:** ![mysqluser81](https://avatars.discourse-cdn.com/v4/letter/m/22d042/32.png) [@mysqluser81](https://forums.percona.com/u/mysqluser81)\
**Post date:** [February 24, 2007, 10:30pm UTC](https://forums.percona.com/t/help-need-some-optimization-advice/211/3 "2007-02-24T22:30:00Z")

</div>

The problem is tha we have about 40 tables and each server corresponds to a server ID which gets created depending on which one reports first. In other words that wouldn’t even be an option due to the large amount of configuration changes that would have to be done on the server that are reporting to the MySQL server. Are there any changes that can be done to improve INSERT, DELETE, UPDATE that are being done?

Thanks for the quick reply!!

---

<div class="post-metadata">

**Author:** ![Alexey](https://avatars.discourse-cdn.com/v4/letter/a/47e85d/32.png) [@Alexey](https://forums.percona.com/u/Alexey)\
**Post date:** [February 25, 2007, 9:51am UTC](https://forums.percona.com/t/help-need-some-optimization-advice/211/4 "2007-02-25T09:51:57Z")

</div>

Please post output of SHOW GLOBAL VARIABLES, not just SHOW VARIABLES (it’s behaviour was changed in version 5, SHOW VARIABLES omits some data).

//edited  
SHOW GLOBAL STATUS too instead of SHOW STATUS.

---

<div class="post-metadata">

**Author:** ![Alexey](https://avatars.discourse-cdn.com/v4/letter/a/47e85d/32.png) [@Alexey](https://forums.percona.com/u/Alexey)\
**Post date:** [February 25, 2007, 9:53am UTC](https://forums.percona.com/t/help-need-some-optimization-advice/211/5 "2007-02-25T09:53:57Z")

</div>

| [B]Quote:[/B] |
| 

| Key\_blocks\_unused | 0 || Key\_blocks\_used | 14497 || Key\_read\_requests | 94036894 || Key\_reads | 4019574 |

 |

Uh oh, do you have my.cnf file at all? 🙂

---

<div class="post-metadata">

**Author:** ![mysqluser81](https://avatars.discourse-cdn.com/v4/letter/m/22d042/32.png) [@mysqluser81](https://forums.percona.com/u/mysqluser81)\
**Post date:** [February 25, 2007, 12:49pm UTC](https://forums.percona.com/t/help-need-some-optimization-advice/211/6 "2007-02-25T12:49:02Z")

</div>

Humm I am sure I do, is there something that I might be doing wrong? here is a copy of it, it’s located in /etc/my.cnf

When I do a ps -ef |grep mysql here is the output

\*\*root 1555 1 0 Feb23 ? 00:00:00 /bin/sh /usr/bin/mysqld\_safe --defaults-file=/etc/my.cnf --pid-file=/var/run/mysqld/mysqld.pid --log-error=/var/log/mysqld.log

\*\*mysql 1592 1555 7 Feb23 ? 03:33:35 /usr/libexec/mysqld --defaults-file=/etc/my.cnf --basedir=/usr --datadir=/var/lib/mysql --user=mysql --pid-file=/var/run/mysqld/mysqld.pid --skip-locking --socket=/var/lib/mysql/mysql.sock

MY.cnf

[mysqld]  
datadir=/var/lib/mysql  
socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with mysql 3.x

# clients (those using the mysqlclient10 compatibility package).

old\_passwords=1

[mysql.server]  
user=mysql  
basedir=/var/lib  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid

# The MySQL server

# Added 02.27.07

[mysqld]  
set-variable=max\_allowed\_packet=1M  
set-variable=max\_user\_connections=350  
set-variable=max\_connections=350  
set-variable=table\_cache=1200  
key\_buffer\_size=32M  
connect\_timeout=15  
wait\_timeout=15  
set-variable=max\_connect\_errors=999999  
set-variable=log-slow-queries=/var/log/mysql-slow.log

#[mysqld\_safe]  
log-error=/var/log/mysqld.log  
pid-file=/var/run/mysqld/mysqld.pid  
set-variable=max\_connect\_errors=999999  
set-variable=log-slow-queries=/var/log/mysql-slow.log

HERE IS SHOW GLOBAL VARIABLES

mysql\> show global variables  
 → ;  
±--------------------------------±------------------------ -------------------------------+  
| Variable\_name | Value |  
±--------------------------------±------------------------ -------------------------------+  
| auto\_increment\_increment | 1 |  
| auto\_increment\_offset | 1 |  
| automatic\_sp\_privileges | ON |  
| back\_log | 50 |  
| basedir | /usr/ |  
| bdb\_cache\_size | 8388600 |  
| bdb\_home | /var/lib/mysql/ |  
| bdb\_log\_buffer\_size | 614400 |  
| bdb\_logdir | |  
| bdb\_max\_lock | 10000 |  
| bdb\_shared\_data | OFF |  
| bdb\_tmpdir | /tmp/ |  
| binlog\_cache\_size | 32768 |  
| bulk\_insert\_buffer\_size | 8388608 |  
| character\_set\_client | latin1 |  
| character\_set\_connection | latin1 |  
| character\_set\_database | latin1 |  
| character\_set\_filesystem | binary |  
| character\_set\_results | latin1 |  
| character\_set\_server | latin1 |  
| character\_set\_system | utf8 |  
| character\_sets\_dir | /usr/share/mysql/charsets/ |  
| collation\_connection | latin1\_swedish\_ci |  
| collation\_database | latin1\_swedish\_ci |  
| collation\_server | latin1\_swedish\_ci |  
| completion\_type | 0 |  
| concurrent\_insert | 1 |  
| connect\_timeout | 15 |  
| datadir | /var/lib/mysql/ |  
| date\_format | %Y-%m-%d |  
| datetime\_format | %Y-%m-%d %H:%i:%s |  
| default\_week\_format | 0 |  
| delay\_key\_write | ON |  
| delayed\_insert\_limit | 100 |  
| delayed\_insert\_timeout | 300 |  
| delayed\_queue\_size | 1000 |  
| div\_precision\_increment | 4 |  
| engine\_condition\_pushdown | OFF |  
| expire\_logs\_days | 0 |  
| flush | OFF |  
| flush\_time | 0 |  
| ft\_boolean\_syntax | + -\>\<()~\*:“”&| |  
| ft\_max\_word\_len | 84 |  
| ft\_min\_word\_len | 4 |  
| ft\_query\_expansion\_limit | 20 |  
| ft\_stopword\_file | (built-in) |  
| group\_concat\_max\_len | 1024 |  
| have\_archive | NO |  
| have\_bdb | YES |  
| have\_blackhole\_engine | NO |  
| have\_compress | YES |  
| have\_crypt | YES |  
| have\_csv | NO |  
| have\_example\_engine | NO |  
| have\_federated\_engine | NO |  
| have\_geometry | YES |  
| have\_innodb | YES |  
| have\_isam | NO |  
| have\_ndbcluster | NO |  
| have\_openssl | DISABLED |  
| have\_query\_cache | YES |  
| have\_raid | NO |  
| have\_rtree\_keys | YES |  
| have\_symlink | YES |  
| init\_connect | |  
| init\_file | |  
| init\_slave | |  
| innodb\_additional\_mem\_pool\_size | 1048576 |  
| innodb\_autoextend\_increment | 8 |  
| innodb\_buffer\_pool\_awe\_mem\_mb | 0 |  
| innodb\_buffer\_pool\_size | 8388608 |  
| innodb\_checksums | ON |  
| innodb\_commit\_concurrency | 0 |  
| innodb\_concurrency\_tickets | 500 |  
| innodb\_data\_file\_path | ibdata1:10M:autoextend |  
| innodb\_data\_home\_dir | |  
| innodb\_doublewrite | ON |  
| innodb\_fast\_shutdown | 1 |  
| innodb\_file\_io\_threads | 4 |  
| innodb\_file\_per\_table | OFF |  
| innodb\_flush\_log\_at\_trx\_commit | 1 |  
| innodb\_flush\_method | |  
| innodb\_force\_recovery | 0 |  
| innodb\_lock\_wait\_timeout | 50 |  
| innodb\_locks\_unsafe\_for\_binlog | OFF |  
| innodb\_log\_arch\_dir | |  
| innodb\_log\_archive | OFF |  
| innodb\_log\_buffer\_size | 1048576 |  
| innodb\_log\_file\_size | 5242880 |  
| innodb\_log\_files\_in\_group | 2 |  
| innodb\_log\_group\_home\_dir | ./ |  
| innodb\_max\_dirty\_pages\_pct | 90 |  
| innodb\_max\_purge\_lag | 0 |  
| innodb\_mirrored\_log\_groups | 1 |  
| innodb\_open\_files | 300 |  
| innodb\_support\_xa | ON |  
| innodb\_sync\_spin\_loops | 20 |  
| innodb\_table\_locks | ON |  
| innodb\_thread\_concurrency | 8 |  
| innodb\_thread\_sleep\_delay | 10000 |  
| interactive\_timeout | 28800 |  
| join\_buffer\_size | 131072 |  
| key\_buffer\_size | 16777216 |  
| key\_cache\_age\_threshold | 300 |  
| key\_cache\_block\_size | 1024 |  
| key\_cache\_division\_limit | 100 |  
| language | /usr/share/mysql/english/ |  
| large\_files\_support | ON |  
| large\_page\_size | 0 |  
| large\_pages | OFF |  
| license | GPL |  
| local\_infile | ON |  
| locked\_in\_memory | OFF |  
| log | OFF |  
| log\_bin | OFF |  
| log\_bin\_trust\_function\_creators | OFF |  
| log\_error | /var/log/mysqld.log |  
| log\_slave\_updates | OFF |  
| log\_slow\_queries | ON |  
| log\_warnings | 1 |  
| long\_query\_time | 10 |  
| low\_priority\_updates | OFF |  
| lower\_case\_file\_system | OFF |  
| lower\_case\_table\_names | 0 |  
| max\_allowed\_packet | 1047552 |  
| max\_binlog\_cache\_size | 4294967295 |  
| max\_binlog\_size | 1073741824 |  
| max\_connect\_errors | 999999 |  
| max\_connections | 350 |  
| max\_delayed\_threads | 20 |  
| max\_error\_count | 64 |  
| max\_heap\_table\_size | 16777216 |  
| max\_insert\_delayed\_threads | 20 |  
| max\_join\_size | 4294967295 |  
| max\_length\_for\_sort\_data | 1024 |  
| max\_prepared\_stmt\_count | 16382 |  
| max\_relay\_log\_size | 0 |  
| max\_seeks\_for\_key | 4294967295 |  
| max\_sort\_length | 1024 |  
| max\_sp\_recursion\_depth | 0 |  
| max\_tmp\_tables | 32 |  
| max\_user\_connections | 350 |  
| max\_write\_lock\_count | 4294967295 |  
| multi\_range\_count | 256 |  
| myisam\_data\_pointer\_size | 6 |  
| myisam\_max\_sort\_file\_size | 2147483647 |  
| myisam\_recover\_options | OFF |  
| myisam\_repair\_threads | 1 |  
| myisam\_sort\_buffer\_size | 8388608 |  
| myisam\_stats\_method | nulls\_unequal |  
| net\_buffer\_length | 16384 |  
| net\_read\_timeout | 30 |  
| net\_retry\_count | 10 |  
| net\_write\_timeout | 60 |  
| new | OFF |  
| old\_passwords | ON |  
| open\_files\_limit | 2760 |  
| optimizer\_prune\_level | 1 |  
| optimizer\_search\_depth | 62 |  
| pid\_file | /var/run/mysqld/mysqld.pid |  
| prepared\_stmt\_count | 0 |  
| port | 3306 |  
| preload\_buffer\_size | 32768 |  
| protocol\_version | 10 |  
| query\_alloc\_block\_size | 8192 |  
| query\_cache\_limit | 1048576 |  
| query\_cache\_min\_res\_unit | 4096 |  
| query\_cache\_size | 0 |  
| query\_cache\_type | ON |  
| query\_cache\_wlock\_invalidate | OFF |  
| query\_prealloc\_size | 8192 |  
| range\_alloc\_block\_size | 2048 |  
| read\_buffer\_size | 131072 |  
| read\_only | OFF |  
| read\_rnd\_buffer\_size | 262144 |  
| relay\_log\_purge | ON |  
| relay\_log\_space\_limit | 0 |  
| rpl\_recovery\_rank | 0 |  
| secure\_auth | OFF |  
| server\_id | 0 |  
| skip\_external\_locking | ON |  
| skip\_networking | OFF |  
| skip\_show\_database | OFF |  
| slave\_compressed\_protocol | OFF |  
| slave\_load\_tmpdir | /tmp/ |  
| slave\_net\_timeout | 3600 |  
| slave\_skip\_errors | OFF |  
| slave\_transaction\_retries | 10 |  
| slow\_launch\_time | 2 |  
| socket | /var/lib/mysql/mysql.sock |  
| sort\_buffer\_size | 2097144 |  
| sql\_mode | |  
| sql\_notes | ON |  
| sql\_warnings | ON |  
| storage\_engine | MyISAM |  
| sync\_binlog | 0 |  
| sync\_frm | ON |  
| system\_time\_zone | EST |  
| table\_cache | 1200 |  
| table\_lock\_wait\_timeout | 50 |  
| table\_type | MyISAM |  
| thread\_cache\_size | 0 |  
| thread\_stack | 196608 |  
| time\_format | %H:%i:%s |  
| time\_zone | SYSTEM |  
| timed\_mutexes | OFF |  
| tmp\_table\_size | 33554432 |  
| tmpdir | |  
| transaction\_alloc\_block\_size | 8192 |  
| transaction\_prealloc\_size | 4096 |  
| tx\_isolation | REPEATABLE-READ |  
| updatable\_views\_with\_limit | YES |  
| version | 5.0.22-log |  
| version\_bdb | Sleepycat Software: Berkeley DB 4.1.24: (May 25, 2006) |  
| version\_comment | Source distribution |  
| version\_compile\_machine | i686 |  
| version\_compile\_os | redhat-linux-gnu |  
| wait\_timeout | 15 |  
±--------------------------------±------------------------ -------------------------------+  
218 rows in set (0.00 sec)

---

<div class="post-metadata">

**Author:** ![Alexey](https://avatars.discourse-cdn.com/v4/letter/a/47e85d/32.png) [@Alexey](https://forums.percona.com/u/Alexey)\
**Post date:** [February 25, 2007, 3:52pm UTC](https://forums.percona.com/t/help-need-some-optimization-advice/211/7 "2007-02-25T15:52:33Z")

</div>

| [B]mysqluser81 wrote on Fri, 23 February 2007 13:51[/B] |
| 

Any help ideas suggestions? As I am all out of ideas.

 |

After you sort out your configuration problems (32MB key buffer for a 8GB machine is too small, I’d start with at least 512MB) you can use “limit” trick. You don’t have to delete all rows with a single query, you can do something like (pseudocode):

do {  
DELETE FROM table WHERE date \< (…) LIMIT 1000; # takes less than 1 second  
sleep(5); # lets server process updates & inserts  
}  
until ($affected\_rows == 0); # until no more rows left to delete

---

<div class="post-metadata">

**Author:** ![Peter](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/peter/32/2_2.png) [@Peter](https://forums.percona.com/u/Peter)\
**Post date:** [March 6, 2007, 11:13am UTC](https://forums.percona.com/t/help-need-some-optimization-advice/211/8 "2007-03-06T11:13:52Z")

</div>

You still did not mention error message which it terminates with.

There is some tcpdump like tool for MySQL - I do not remember the name but you can check it on [forge.mysql.com](http://forge.mysql.com)

also check out MySQL error logs if there is anything in them which could shed some light.

---

<div class="post-metadata">

**Author:** ![mysqluser81](https://avatars.discourse-cdn.com/v4/letter/m/22d042/32.png) [@mysqluser81](https://forums.percona.com/u/mysqluser81)\
**Post date:** [March 8, 2007, 10:51am UTC](https://forums.percona.com/t/help-need-some-optimization-advice/211/9 "2007-03-08T10:51:54Z")

</div>

Here is what error show up in the messages file.

database: mysql\_error: Lost connection to MySQL server during query  
SQL=UPDATE server SET last\_sid = 0 WHERE sid = 71