Tag Archives: Performance

Here’s an ESXi console script to loop through each Nimble eui.* adapter and set IOPS=0 and BYTES=0 (per Nimble recommendations).

for x in `esxcli storage nmp device list | awk '/Nimble iSCSI Disk/{print $7}' | sed -e 's/(//' -e 's/)//'`; do
echo $x 
esxcli storage nmp psp roundrobin deviceconfig set -d $x -t bytes -B 0;
esxcli storage nmp psp roundrobin deviceconfig set -d $x -t iops -I 0 ;
esxcli storage nmp psp roundrobin deviceconfig get -d $x;
done

Note: If you change the order above and set bytes after iops, then the policy will be based on bytes and not IOPS.

To reset defaults, use the following script on the ESXi host console:

for x in `esxcli storage nmp device list | awk '/Nimble iSCSI Disk/{print $7}' | sed -e 's/(//' -e 's/)//'`; do
echo $x 
esxcli storage nmp psp roundrobin deviceconfig set -d $x -t bytes -B 10485760;
esxcli storage nmp psp roundrobin deviceconfig set -d $x -t iops -I 1000 ;
esxcli storage nmp psp roundrobin deviceconfig set -d $x -t default;
esxcli storage nmp psp roundrobin deviceconfig get -d $x;
done

To make sure this survives a reboot, you can set a policy:

esxcli storage nmp satp rule add --psp=VMW_PSP_RR --satp=VMW_SATP_ALUA --vendor=Nimble --psp-option="policy=iops;iops=0"

Note that if you previously configured a user-defined SATP rule for Nimble volumes to simply use the Round Robin PSP (per the Nimble VMware best practices guide), you will first need to remove that simpler rule, before you can add the above rule, or else you will get an error message that a duplicate user-defined rule exists. The command to remove the simpler rule is: –Bill

esxcli storage nmp satp rule remove --psp=VMW_PSP_RR --satp=VMW_SATP_ALUA --vendor=Nimble

So I’m trying to figure out a way to make searching a VARCHAR in MySQL “fast” when there are 400 million rows.
I tried the UNIQUE approach using alter table names add unique(name(15)); in the table but I have some duplicates apparently, so now I’m trying a different method.

I’m going to create multiple tables;  a-z, 0-9.

Based on the input query such as SELECT * FROM name WHERE name='rich' I’ll split that out and re-write the query such as SELECT * FROM r_name WHERE name='rich'.  r_name table is considerably smaller than the entire name table of 400 million rows.  I’ll just say 30 million.

Here’s my bash script to process the main plain text file that I’ll then create LOAD for in MySQL for each split file.

#!/bin/bash

# Split names.txt into a.txt, b.txt, c.txt etc.

for x in {a..z}
do
 echo Scanning $x
 grep -i ^$x names.txt >$x.txt
done
for x in {0..9}
do
 echo Scanning $x
 grep -i ^$x names.txt >$x.txt
done

echo.
echo Done

This is currently processing, so I’ll update when this is finished and see how much more optimized this will be.

Update

Well, crap.  I think I screwed up when I initially parsed out the main names.txt and tried to only grab unique lines.  I should have passed a case insensitive uniq command using uniq -i.

So now I’ll try doing that again and reloading the database.

Update 2

I reloaded the names database from text file.  Doing a create index idx_name on names(name(767)); changed a query execution time of select * from names where name='rich' from 12 minutes to 0.03 seconds.  I’m happy now. And really happy I don’t need to create a bunch of tables and add logic to some PHP/MySQL. Just goes to show I’m green behind the ears when it comes to database technology.

Note: Not quite 400 million after removing duplicates and invalid names from the file 😉

MariaDB [data001]> select max(id) from names;
+-----------+
| max(id)   |
+-----------+
| 321995408 |
+-----------+
1 row in set (0.00 sec)
MariaDB [data001]> select * from names where name='rich';
+----------+-------------+
| id       | name        |
+----------+-------------+
| 86382207 | rich        |
+----------+-------------+
1 row in set (0.00 sec)

Here is a list of Windows’ performance counters to use in monitoring performance of an SQL server.

Create Performance Collection rules targeted to a SQL Server computer group for the following performance counters:

  • LogicalDisk(*)Avg Disk sec/Read
    Should be under 20ms. Beyond 50ms is very bad
  • LogicalDisk(*)Avg Disk sec/Write
    Should be under 20ms. Beyond 50ms is very bad
  • LogicalDisk(*)Disk Read Bytes/sec
  • LogicalDisk(*)Disk Reads/sec
    The Reads and Read Bytes/sec counters can be used on conjunction with the Writes and Write Bytes/sec counters to see the ratio of Reads/Writes your database is doing. This will help determine the optimal RAID configuration to optimize for reads, writes, or a balance of both.
  • LogicalDisk(*)Disk Write Bytes/sec
  • LogicalDisk(*)Disk Writes/sec
  • MSSQL:Buffer MangerBuffer cache hit ratio
    This should be as close to 100% as possible. Below 97-98% indicates SQL server needs more physical memory. If you are much below that, the SQL Server needs more memory.
  • MSSQL:Buffer MangerPage Lookups/sec
  • MSSQL:Buffer MangerPage reads/sec
  • MSSQL:Buffer MangerPage writes/sec
    You can use this to see how many writes you are performing to your disk. Each Page is 8KB.

This is just a note for myself since I often find myself trying to analyze an application to performance tune a server/system.

Using Windows Performance Monitoring I use the following metrics when analyzing an application.

MetricExample DataDescription
Logical Disk: Avg. Disk Bytes/Read0.00IO Size Read (Data block size)
Logical Disk: Avg. Disk Bytes/Transfer8192.00IO Size
Logical Disk: Avg. Disk Bytes/Write8192.00IO Size Read (Data block size)
Logical Disk: Disk Read Bytes/sec0.00Total Read data bytes per second
Logical Disk: Disk Write Bytes/sec125000.00Total Write data bytes per second
Logical Disk: Disk Transfers/sec15.258Total IOPS
Logical Disk: Disk Reads/sec0.00Read IOPS
Logical Disk: Disk Writes/sec15.258Write IOPS

To calculate Write IOPS for the application take “Disk Write Bytes/sec” and divide by “Avg. Disk Bytes/Write”.

125000.00 / 8192.00 = 15.258 IOPS

Notice that Disk Reads/sec and Disk Writes/sec corresponds to Read IOPS and Write IOPS already.

 

Here’s some of the things I do to a new Server 2008 R2 install (physical and/or virtual settings are mixed in; (VIRTUAL ONLY) specifies I only do configuration change on Virtual systems).

  • Disable Screen Saver, Personalize -> Screen Saver -> None
  • Sound, Do not start Audio Service -> Sound
  •  No Sounds
  •  Display Performance, System -> Advanced System Settings -> Performance Settings -> Adjust for Best Performance
  • Power Options -> High Performance
  • Power Options -> Changed when the computer sleeps -> Turn off Display = Never
  •  Pagefile, System -> Advanced -> Performance -> Set no pagefile (VIRTUAL ONLY)
  •  Disable System Screensaver Regedit -> HKEY_USERS.DEFAULTControl PanelDesktop -> Delete SCRNSAVE.exe
  •  Stop Audio Service in services.msc, set to Manual
  •  Remove ipv6 support
  •  Start Menu -> remove quick launch
  •  Drive Indexing, My Computer -> C: Properties -> Unselect “Index this drive…” -> Apply recursively -> ignore all permission errors
  •  Run as administrator ‘cmd.exe’ -> powercfg -h OFF to disable hibernation and delete hiberfil.sys from C:
  •  reboot
  •  delete pagefile.sys on c: (VIRTUAL ONLY)
  •  defrag C: (I use contig http://live.sysinternals.com/contig.exe and run contig -s c:*.*)
  •  clear c:usersadministratorappdatalocaltemp folder
  •  enable remote desktop, right click my computer -> properties -> advanced properties -> remote tab
  •  activate windows
  • Run as administrtor ‘cmd.exe’ and copy/paste the following:
    powercfg -setactive scheme_min
    Powercfg -setacvalueindex scheme_current sub_processor 45bcc044-d885-43e2-8605-ee0ec6e96b59 100
    Powercfg -setactive scheme_current
    Powercfg -setacvalueindex scheme_current sub_processor 893dee8e-2bef-41e0-89c6-b55d0929964c 100
    Powercfg -setactive scheme_current
    Powercfg -setacvalueindex scheme_current sub_processor bc5038f7-23e0-4960-96da-33abaf5935ec 100
    Powercfg -setactive scheme_current
    powercfg -setacvalueindex scheme_current 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c 54533251-82be-4824-96c1-47b60b740d00 893dee8e-2bef-41e0-89c6-b55d0929964c 100
    Powercfg -setactive scheme_current